1

I've found a number of similar problems - and even added an answer to a one similar non-dup. But I can't see or solve this problem. Here's the core problem:

I have a staging server on Heroku. I want to copy the staging server database to development on Nitrous to solve a problem. Nitrous has Postgres 9,2,4, and Heroku has Postgres 9.3.3.

My boss is away on holiday, and I have no authority to upgrade the Heroku staging service to a paid plan in which I can fork (and then use the forked Heroku database as a remote database for development).

I have used heroku pg:push to send development databases to staging, in earlier work. No problem. But I can not use heroku pg:pull - it fails, saying that:

pg_dump: server version: 9.3.3; pg_dump version: 9.2.4                                                                                                         
pg_dump: aborting because of server version mismatch

I have tried a rake db:structure:dump - fails for version mismatch reasons. I'd vaguely hoped that this used the pg gem and would magically work, ignoring rev levels. Hey, if you're ignorant enough, magic does work, sometimes.

I have a Nitrous box for development because the office firewall blocks, well, pretty much everything but 25, 80 and 443. All the useful ports like 22, 5432, 3000, etc, are blocked. So I develop on Nitrous. It's pretty neat. But it never occurred to me that Nitrous would have an old version of Postgres, and no apparent way to update it. Especially given that Nitrous often emphasises using Heroku.

I've tried using the more basic commands:

pg_dump -h ec2-XX-XXX-XX-XXX.compute-1.amazonaws.com -p 5432 -Fc --no-acl --no-owner --compress 3 -o -U ${DBNAME} > dumpfile.gz

But that fails (heroku pg:pull probably uses this command, under the hood) for the same reasons - version mismatch.

I realise that if I'd known more when I started, I could have requested that Heroku used 9.2. But I have data now, in a 9.3.3 instance, and I want that data, not the data I would have had, if only a time machine was available to me, and I could cope with the trousers of time paradoxes.

Possible solutions? Is there another web IDE that has PG 9.3? Is there a flag that I can't find that lets PG Dump 9.2 work with an up-rev DB? Is there a way to upgrade Nitrous to 9.3? At least for the critical pg_dump command?

Browser based IDE's versions of Postgres (as of 2014/08/13):

  • nitrous - 9.2
  • koding - 9.1
  • cloud9: 9.3 (Yay! - Pick me! Pick me!)
Community
  • 1
  • 1
JezC
  • 1,868
  • 17
  • 19

2 Answers2

0

I spent another couple of hours and worked out a solution, using a different browser based IDE. Cloud9 offers Postgres 9.3, pre-installed in a new VM.

You'll need to register your Cloud9 ID with Heroku (find the SSH keys in the Cloud9 Console, and paste into your ID SSH Keys in Heroku). And you'll need to sign in to Heroku from Cloud 9.

Then use pg_dump and pg_restore on Cloud9, using Heroku databases as the source and target.

pg_dump -h ec2-XX-XX-XX-XX.compute.amazonaws.com -p 5432 --no-owner --no-acl -Fc -o -U ${HEROKU_STAGING_DATABASE_USER} ${HEROKU_STAGING_DATABASE_NAME} > staging.dump
pg_restore -h ec2-XX-XX-XX-XX.compute.amazonaws.com -p 5432 --no-owner --no-acl --clean --verbose -d ${HEROKU_DEV_DATABASE_NAME} -U ${HEROKU_DEV_DATABASE_USER} < staging.dump

In your dev environment, make sure you update the config/database.yaml (or whatever it is your web apps need) to use the Heroku remote DB Service.

Simples. Eventually.

JezC
  • 1,868
  • 17
  • 19
0

I ran into precisely this problem, and solved with blind magic by

  1. Downloading a recent.dump file from the Heroku postgres dashboard
  2. Moving that file into the Nitrous box (and into the app directory)

  3. Following the instructions here: https://stackoverflow.com/a/11391586/3850418

    pg_restore -O -d MY_APPNAME_DEV recent.dump

I got a bunch of warnings, but it seemed to have worked, at least enough for my dev/testing purposes.

Community
  • 1
  • 1
Todd
  • 41
  • 1
  • 5