2

My database is associated with different usernames on the server and on my local machine. Whenever I try to restore it on my local machine from the backup downloaded from the server using the command

psql -d dbname -f backupfile.sql

I get the error output saying that

ERROR: role "myusername" does not exist

Despite the error message, the actual restore goes through. But it is annoying to see multiple error messages saying the same thing (once for each table). Is there a way I can suppress this error message. I searched quite a bit but could not find anything. There is no-owner option for pgrestore, but I could not find anything for psql.

Thank you for your help.

2 Answers 2

4

That is easy. Use the --no-owner and --no-privileges options of pg_dump.

You might be more happy if you used the custom format of pg_dump (-F c). Then you'd restore with pg_restore and could specify the options at restore time.

7
  • Just a friendly reminder, restore with .sql file extension, not with .dump file.
    – Kiry Meas
    Nov 28, 2022 at 9:42
  • @KiryMeas Thanks for the reminder, but it leaves me a bit clueless. The extension doesn't have a lot of meaning where I come from... Nov 28, 2022 at 12:29
  • @laurnz I tried with the .dump file using pg_restore it raised an error "You can not restore with .dump file". I use pg_restore (PostgreSQL) 12.3 (Debian 12.3-1.pgdg100+1)
    – Kiry Meas
    Nov 29, 2022 at 7:51
  • @KiryMeas That doesn't look like a PostgreSQL error message... How did you create that .dump file? Nov 29, 2022 at 9:12
  • Yes, it is not the exact message. here is the command I use pg_dump -U user development --no-owner --no-privileges --data-only -F c -n test > test_development_2022_11_28.dump
    – Kiry Meas
    Nov 29, 2022 at 9:24
3

Nothing you can do with psql, but plenty you can do with your backupfile.sql. You can remove the owner clauses on the fly:

grep -v '^ALTER.*OWNER TO myusername;$' backupfile.sql | psql -d dbname

or replace one user to another:

sed '/^ALTER.*OWNER TO myusername;$/s/OWNER TO myusername/OWNER TO foobar/g' backupfile.sql | psql -d dbname
1
  • Thank you. This is good to know. I think I am going to use the solution by @Laurenz, hence will check that as the answer. But yours probably answers my question more directly. It is a dilemma. Oct 1, 2020 at 11:40

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.