Score:0

Trouble using pg_restore or psql or pgAdmin4 to restore a postgres db backup

ni flag

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. enter image description here

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

Score:0
ni flag

The solution for me was to go around this problem. I failed to make psql or pg_restore restore the backup I had created. Supposedly because I created a plain text pg_dump file, I needed to use psql. But I couldn't make psql work.

The success started when I discovered a tutorial saying that, if I use a tar dump file, then I can use pg_restore. I was able to make a tar dump file using pgAdmin and then restore similarly using pgAdmin.

So its not a solution to making a plain text pg dump work, but it goes around it.

I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.