pg_dump mismatch

1

As I described my confusion in an earlier post, I installed a Phoenix app with fly launch, and it asked if I wanted a Postgres Db and addded it for me; hence I have my-app and my-app-db (with internal address only).

I want to pg_dump from my_app_db, and I finally figured out that – as a last resort, not a production solution – I could simply do this in my-app:

flyctl ssh console

apt-get update
apt-get install postgresql-client

pg_dump --username=postgres --dbname=my-app --port=5432 --host=my-app-db.internal

Only to discover:

pg_dump: error: server version: 14.1 (Debian 14.1-1.pgdg110+1); pg_dump version: 13.5 (Debian 13.5-0+deb11u1)
pg_dump: error: aborting because of server version mismatch

Then I went down a rabbit hole of
apt-get install postgresql-client-14 …and endless missing libs, to try to solve this, but it seems futile.

Is there a reason why my-app-db is on v. 14 whereas my-app repositories have only v. 13?

Or is there a simple way to accomplish my goal of getting the dump file?

Thanks.

2

Same issue. Can’t migrate the db, can’t update the libpq-dev to anything reasonable (i.e. 14 and above) b/c (I guess?) the package indexes are not updateable?

3

I assume some-application and some-application-db are deployed.

Go to the some-application console:

fly ssh console -a some-application

Get the database name, login, and password:

echo $DATABASE_URL

It will show the string in the following format:

postgres://some_login:some_password@some-address.internal:5432/some_db_name?sslmode=disable

Go to the DB machine console:

fly ssh console -a some-application-db

Make a dump:

pg_dump --username=some_login --dbname=some_db_name --port=5432 --host=some-application-db.internal > some_path

Make sftp connection to the DB machine

fly ssh sftp shell -a some-application-db

Download the dump using SFTP session:

get /some_path
2 Likes
4

found out about this shorter one : fly ssh sftp get "dump.sql" -a app_name

then trying to setup a simple bash script file :

database_uri=$(fly ssh console -a app_name -C "printenv DATABASE_URL")
fly ssh console -a app_db -C "pg_dump $database_uri > dump.sql"
fly ssh sftp get "dump.sql" -a app_name

dropdb local_db && \
  createdb local_db && \
  psql -U local_username -d local_db -f dump.sql

With no success - the $database_uri in the second line is not readable on the remote instance…I might lack of basic bash knowledge. Such a simple script could work somehow ?

1 Like