2

I backed up a server with

pg_dumpall.exe --file "backup.sql" --host "db" --port "5432" --username "user" --no-password --database "postgres" --verbose --clean

but when I tried to restore it on a new machine, I'm getting

postgres@home-lab-db-15:/home/ubuntu$ psql -f home-lab-db.sql postgres  -v ON_ERROR_STOP=1
SET
SET
SET
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
CREATE ROLE
ALTER ROLE
psql:home-lab-db.sql:24: ERROR:  role "postgres" already exists

I tried to drop the cluster and recreate it, but same error. I could not drop the postgres role or database cannot drop role postgres because it is required by the database system

The docs say

if you are loading into an empty cluster then postgres should usually be used

so I'm confused at how to restore the backup.

Improve this question

1 Answer 1

Reset to default
0

That error is expected and should be ignored. You cannot restore a pg_dumpall with ON_ERROR_STOP set.

Improve this answer
6
  • thanks! I was coming from stackoverflow.com/questions/20427689/… and wanted to make sure I didn't have any errors causing that
    – dangelsaurus
    Commented Oct 19, 2022 at 15:49
  • It really sucks that errors are expected when restoring from a backup. How are we then expected to know which errors are expected and which are not?
    – Peter V. Mørch
    Commented Feb 25 at 14:42
  • See this answer for a way to modify the output so it doesn't generate errors: dba.stackexchange.com/a/176562/57658
    – Peter V. Mørch
    Commented Feb 25 at 15:28
  • Nobody forces you to use --clean, and you could have used --if-exists to avoid the error. Don't blame your own mistakes on the software.
    – Laurenz Albe
    Commented Feb 26 at 7:22
  • @LaurenzAlbe which command would I add "--if-exists" to? My backup and restore commands - pg_dumpall -h hostname -p 5432 -U postgres > dumpfile; psql -h hostname -p 5432 -U postgres -f dumpfile postgres
    – majorgear
    Commented Aug 20 at 3:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.