Backup and Restore Postgresql

[dc20433]

I deployed a rails app with postgresql to test out fly.io. I was able to launch and deploy the app to make it work just like on my localhost. I however have not been able to backup the database (app: reah6; db: reach6-db). To backup, I ran a 3-line Windows batch file:

fly proxy 15432:5432 -a reach6-db
set PGPASSWORD=xxxxx( the reach6-db password generated during the app launch)
pg_dump -p 15432 -h localhost -U postgres -c -f flyback.bak -d reach6-db

The batch file hangs and I will have to terminate the batch job to exit to the prompt. It does not matter what passwords I put at ‘set PGPASSWORD= ‘, I will get the same result.

I also tried to create a snapshot of the database (reach6-db-restored); but at detaching I got the following error message: error running user-delete: 500: ERROR: role “reach4” cannot be dropped because some objects depend on it (SQLSTATE 2BP01)

Can someone show how to backup and restore Postgresql in a windows 11 environment? Appreciated!

[kurt]

Try this instead:

fly proxy 15432:5433 -a reach6-db

Note port 5433. This connects you directly to postgres, bypassing the proxy that routes you to the writable instance. This could prevent timeouts.

[dc20433]

Many thanks Kurt! I tried:
fly proxy 15432:5433 -a reach6-db
set PGPASSWORD=xxxxx (the reach6-db password generated during the app launch)
pg_dump -p 15432 -h localhost -U postgres -c -f flyback.bak -d reach6-db

And go the same result, i.e., the program halts at:
Proxying local port 15432 to remote [reach6-db.internal]:5433

And it stops there without creating a backup file flyback.bak.

[kurt]

Oh I understand the problem. This command blocks, it does not run in the background: fly proxy 15432:5433 -a reach6-db

What you need to do is run that in a separate window, then do the pgdump stuff.

[dc20433]

Don’t know why still not working. First I did: C:\Users[–]\reach> fly proxy 15432:5433 -a reach6-db [the cmd prompt would not reappear]
I then open another window and did: C:\Users[–]\reach> pg_dump -p 15432 -h localhost -U postgres -W -c -f flyback.bak -d reach6-db [Password: appears]
I cut and pasted the password for reach6-db generated at the time of the app launch, then got the following message: pg_dump: error: connection to server at “localhost” (::1), port 15432 failed: Connection refused (0x0000274D/10061)
Is the server running on that host and accepting TCP/IP connections?
connection to server at “localhost” (127.0.0.1), port 15432 failed: FATAL: database “reach6-db” does not exist

[guillaume]

Looks like your database reach6-db is not there.

You can try interactively exploring your postgres with psql -p 15432 -h localhost -U postgres first (while having opened the proxy in another terminal window of course).

You can list the existing databases in the psql prompt with the command \list, if you don’t see reach6-db then it’s not there: maybe its called something else, like reach6.

[dc20433]

@guillaume

psql -p 15432 -h localhost -U postgres

C:\Users[–]\reach> psql -p 15432 -h localhost -U postgres
Password for user postgres:
psql (15.1, server 14.6 (Debian 14.6-1.pgdg110+1))
WARNING: Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql reference
page “Notes for Windows users” for details.
Type “help” for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------±-----------±---------±-----------±-----------±-----------±----------------±--------------------------
postgres | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
reach6 | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | | libc |
template0 | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/flypgadmin +
| | | | | | | flypgadmin=CTc/flypgadmin
template1 | flypgadmin | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/flypgadmin +
| | | | | | | flypgadmin=CTc/flypgadmin
(4 rows)

postgres=#

C:\Users[–]\reach> fly status -a reach6-db
ID STATE ROLE REGION HEALTH CHECKS IMAGE CREATED UPDATED

e784339f171483 started leader iad 3 total, 3 passing flyio/postgres:14.6 (v0.0.34) 2023-02-17T15:20:12Z 2023-02-17T15:20:27Z

[guillaume]

Indeed as you can see your DB name is reach6 (not reach6-db). Your backup command should be:

pg_dump -p 15432 -h localhost -U postgres -W -c -f flyback.bak -d reach6

[dc20433]

@guillaume

pg_dump -p 15432 -h localhost -U postgres -W -c -f flyback.bak -d reach6

Indeed it worked! I tried for days and couldn’t get it to work, and was sure that I missed something. Thank you so much for you and Kurt’s patience and help!