PG database migration from Heroku not working

Questions / Help
1

Hi,

I’m following the migration guide from Heroku for my Rails app, but the pg_dump command is failing when I try to transfer the database.

I’ve SSH’d into my Fly app, and run the command:

pg_dump --no-owner -C -d $HEROKU_DATABASE_URL | psql -d $DATABASE_URL

I then get this error:

pg_dump: server version: 13.7 (Ubuntu 13.7-1.pgdg20.04+1); pg_dump version: 11.17 (Debian 11.17-0+deb10u1)
pg_dump: aborting because of server version mismatch

Is there any way I can fix this? Any help would be appreciated.

2

Hey there,

I would maybe try running the command on your local machine and you should ensure that your local Postgres version matches the version you’re restoring into.

3

Hi,

My local Postgres is version 14.5, but I am still getting the same error. I am running the command through SSH into my Fly app, so is there a way I can upgrade the pg_dump command?

Don’t really know where I am going wrong.

Someone else had the same issue as me here: pg_dump mismatch

4

Huh, here’s what my test setup looks like:

Heroku Postgres: v11.16
Fly Postgres: v13.6

Postgres version running on my local machine:

psql --version
psql (PostgreSQL) 13.8

From my local machine, i’m running:
pg_dump --no-owner -C -d $HEROKU_DB | psql -d $PG_APP

Update:

I ran this command with Postgres 14 installed locally as well without issues.

5

If you are still having trouble, you could consider trying this out:

6

Thanks for the reply Shaun, I’m now getting an error when trying to deploy this app!

Sending build context to Docker daemon  31.55kB
Error failed to fetch an image or build from source: error building: Error response from daemon: invalid reference format

Again, any help on this would be hugely appreciated.

7

@harold What does your fly.toml look like?

8

This is the fly.toml for the migration app:

# fly.toml file generated for migration-app on 2021-10-13T19:32:25-05:00

app = "postgres-migration-tool-"

kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[env]
  POSTGRES_PASSWORD = "pass"

[experimental]
  auto_rollback = false

[[services]]
  processes = ["app"]
  protocol = "tcp"

This is my fly.toml:

# fly.toml file generated for bishbashbooked on 2022-09-03T20:00:11+01:00

app = "bishbashbooked"
kill_signal = "SIGINT"
kill_timeout = 5
processes = []

[build]
  [build.args]
    BUNDLER_VERSION = "2.1.2"
    NODE_VERSION = "18.8.0"
    RUBY_VERSION = "2.7.0"

[deploy]
  release_command = "bin/rails fly:release"

[env]
  PORT = "8080"
  SERVER_COMMAND = "bin/rails fly:server"

[experimental]
  allowed_public_ports = []
  auto_rollback = true

[[services]]
  http_checks = []
  internal_port = 8080
  processes = ["app"]
  protocol = "tcp"
  script_checks = []
  [services.concurrency]
    hard_limit = 25
    soft_limit = 20
    type = "connections"

  [[services.ports]]
    force_https = true
    handlers = ["http"]
    port = 80

  [[services.ports]]
    handlers = ["tls", "http"]
    port = 443

  [[services.tcp_checks]]
    grace_period = "1s"
    interval = "15s"
    restart_limit = 0
    timeout = "2s"

[[statics]]
  guest_path = "/app/public"
  url_prefix = "/"
9

Which app specifically are you running into issues deploying?

10

Thanks for the help Shaun, I managed to transfer my data across. I downloaded a .dump of my Heroku database and then used pg_restore to upload it to my Fly database.

2 Likes
11

I’m in a similar situation. Can you give the command you used? And did you do it from the command line or SSH? And I assume you downloaded to your local machine. I barely understand all of this but have been using Heroku for several Rails/Postgres apps and am looking for a less expensive option. Two of the apps are being developed on my local Mac and the database resides there too. I upload the database and changes to the app periodically. But some of the fly command line commands are not clear to me. Heroku kept the database and app separate whereas is seems to me fly makes that less clear.

One example of my confusion. Your OP had pg_dump --no-owner -C -d $HEROKU_DATABASE_URL | psql -d $DATABASE_URL. How can I see the the $ variables are and is this command going to Heroku and uploading to Fly?

Thank you for any pointers.

12

Try these instructions to download a dump and re-upload:

13

These are the steps I followed:

  • Put your Heroku app into maintenance mode so that no changes can be made to the DB
  • Download your data from Heroku using following command:
pg_dump -h HOST -d $DATABASE -U $USER -p $PORT -W -F t > latest.dump
  • Type the password when prompted. To get the credentials from Heroku go to your application settings and ‘Reveal Config Vars’. You’ll find the DATABASE_URL, which is in the format postgres://$USER:$PASSWORD@$HOST:$PORT/$DATABASE
  • Find the database connection URL for your Fly PG database, using the following commands:
fly ssh console
echo $DATABASE_URL
  • Set up Wiregaurd on your local machine so that you can access your Fly database. Click this link for the tutorial.
  • Restore the database using the following command:
pg_restore -v -d [FLY_POSTGRES_URL] < latest.dump

Then you should be done. One thing to note is to make sure you do all of this in your project directory in the terminal.

Hope this helps, let me know if it doesn’t work.

1 Like
14

@harold Thank you. I think I’m getting closer. One is separating fly ssh console from the command line. I didn’t use console mode with Heroku AFAIK.

echo $DATABASE_URL produces:
postgres://late_sun_7751:xyxxy@top2.nearest.of.late-sun-7751-db.internal:5432/late_sun_7751

When I substitute that for [FLY_POSTGRES_URL] I get
pg_restore: error: could not translate host name "top2.nearest.of.late-sun-7751-db.internal" to address: nodename nor servname provided, or not known

I also tried psql [database_name} < db_dump.sql and got a lot of errors. Heroku and localhost is fine with the database. The error had to dot with ‘duplicate key value’ and ‘“ar_internal_metadata” already exists’ among others.

When I installed WireGuard the responses didn’t match what was shown on the link. I accepted a default and it finished.

15

That sounds like a WireGuard error, what steps did you take to set it up?

You need to do fly wireguard create, choose which organisation to set it to (most likely your personal one, this will be autoselected if that’s the only one you have). Then, enter the name of the configuration file as ‘basic.conf’.

When you install the WireGuard client from the Mac app store, it should open up and give you the option to import a a configuration file. Select the one you just created.

You can test if the Wireguard connection has worked by installing dig and using the following command:

brew install bind

then, when that has installed:

dig +noall +answer _apps.internal txt

Your app should appear on the list that this shows.
When this is set up, using the following command should work:

pg_restore -v -d postgres://late_sun_7751:xyxxy@top2.nearest.of.late-sun-7751-db.internal:5432/late_sun_7751 < latest.dump
16

@harold. Thank you again. Glad someone understands this.

Now have Wireguard hooked up I think. I had missed the activate in the Wireguard app, but found it reviewing while writing a response.

Yes, now my app works. It’s a recent app. Will see how it does with an older Rails.

Looks to me like basic.conf should be added to .gitignore/

1 Like
17

It took me a while to figure it all out as well, glad I could help you out.

18

I had to run pg_restore several times to get all my data in :thinking:

19

I had this problem too -

pg_dump: error: server version: 14.5 (Ubuntu 14.5-2.pgdg20.04+2); pg_dump version: 13.8 (Debian 13.8-0+deb11u1)
pg_dump: error: aborting because of server version mismatch

I just added postgres-client v14 to my Dockerfile and all OK

RUN curl -sSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - \
  && echo "deb http://apt.postgresql.org/pub/repos/apt/ $(sed -n 's/VERSION_CODENAME=\(.*\)$/\1/p' /etc/os-release)-pgdg main" 14 > /etc/apt/sources.list.d/pgdg.list

ARG DEPLOY_PACKAGES="file vim curl gzip nodejs postgresql-client-14"
1 Like
20

Thank you for sharing. Some points need to be clarified though:

  • fly ssh console didn’t work for me, I had to add my rails app name as follows: fly ssh console -a may-rails-app
  • echo $DATABASE_URL did gave me the DB URL, smth like:
postgres://my_rails_app:XXXX@som_host:5432/my_rails_app?sslmode=disable
  • pg_restore -v -d [FLY_POSTGRES_URL] < latest.dump failed for me as well. I don’t think you should run it from the local directory where your app is located. I had my dump file in Downloads folder, so I ran it from there and it failed with:
/bin/sh: 2: cannot open my_dump_file.dump: No such file

as if FLY supposed me to have this dump file present at the remote host.

Any thoughts about that? Thank you.