Import postgres database without roles

sql database postgresql import psql
Braden picture Braden · Jun 17, 2013 · Viewed 38.7k times · Source

I have a database that was exported with pg_dump, but now when I'm trying to import it again with:

psql -d databasename < mydump.sql

It fails trying to grant roles to people that don't exist. (error says 'Role "xxx" does not exist')

Is there a way to import and set all the roles automatically to my user?

Answer

oerich picture oerich · Jun 17, 2013

The default behavior of the import is that it replaces all roles it does not know with the role you are doing the import with. So depending on what you need the database for, you might just be fine with importing it and with ignoring the error messages.

Quoting from http://www.postgresql.org/docs/9.2/static/backup-dump.html#BACKUP-DUMP-RESTORE

Before restoring an SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, the restore will fail to recreate the objects with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is not.)

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.…

Read More
Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),…

Read More