How to Make psql Restore Ignore Owner Information in PostgreSQL

postgresqlpsql

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.

Best Answer

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.

Related Solutions

Postgresql – Ensure correct username when using pg_restore

If you want to create the plpgsql extension I believe you have to be Superuser. My recommendation is to make app_user temporarily a superuser and then revert this. Superusers also are treated as owners of everything.

ALTER USER app_user WITH SUPERUSER;

run the restore, then when you are done

ALTER USER app_user WITH NOSUPERUSER;

This should avoid any other permissions issues but still leave you with the permissions you want.

PostgreSQL – Fix PostgreSQL Not Running on Mac

The problem can also be attributed to a crashed process that left postmaster.pid file behind.

$ brew services stop postgresql
# adjust path accordingly to your install, e.g. `/opt/homebrew/var/postgres/postmaster.pid`
$ rm /usr/local/var/postgres/postmaster.pid 
$ brew services start postgresql

Best Answer

Related Solutions

Postgresql – Ensure correct username when using pg_restore

PostgreSQL – Fix PostgreSQL Not Running on Mac

Related Question