1

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

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.

2

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 '20 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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