This is low stakes and just a practice run for when I urgently need to do it, and also to confirm that pg_dump worked to satisfaction.
I am trying to use psql
or pg_restore
to restore my copy of a Postgres database to a new database. I am on Ubuntu 22.04.
As my usual user, rlm
, at ~
I can type dir
and get, among other files, my apgym.sql
output from pg_dump
.
So I type pg_restore -d testRestore -U postgres apgym.sql
and I get: pg_restore: error: input file appears to be a text format dump. Please use psql.
It's like ok, I'll use psql. I type it and I get psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "rlm" does not exist
I don't understand the error message, but I'll try again as the Postgres user. So I type
sudo su postgres
and become the postgres user. I enter psql -d testRestore -f apgym.sql
in the same directory as my apgym.sql
file and I get:
could not change directory to "/home/rlm": Permission denied
psql: error: apgym.sql: No such file or directory
I can't use pgAdmin to restore from the backup either, it says the status is "Failed" in 0.02 seconds. 
How can I restore my backed up postgres db? I need to be able to do this in case my data is ruined and I want to go back. Please don't suggest alternatives: I want to do it this way.
In case it helps readers or anyone looking to create their own pg_dump
output in the future, I created the backup with the command pg_dump -h localhost -U postgres -W apGymPostgres > apgym.sql