Import postgres database without roles
SqlDatabasePostgresqlImportPsqlSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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.)
Solution 2 - Sql
The answer that you might be looking for is adding the --no-owner
to the pg_restore
command. Unlike the accepted answer at the moment, the command should create every object with the current user even if the role in the dump don't exist in the database.
So no element will get skipped by pg_restore but if some elements imported are owned by different users, all of the records will be now owned by only one user as far as I can tell.
Solution 3 - Sql
With pg_restore
you can use the --role=rolename
option to force a role name to be used to perform the restore. But the dump must be non plain text format.
For example you can dump with:
pg_dump -F c -Z 9 -f my_file.backup my_database_name
and than you can restore it with:
pg_restore -d my_database_name --role=my_role_name my_file.backup
for more info: http://www.postgresql.org/docs/9.2/static/app-pgrestore.html
Solution 4 - Sql
Yes, you can dump all the "Global" objects from your source DB with pg_dumpall's -g option:
pg_dumpall -g > globals.sql
Then run globals.sql against your target DB before importing.
Solution 5 - Sql
I used the following:
pg_dump --no-privileges --no-owner $OLD_DB_URL | psql $NEW_DB_URL
From
https://www.postgresql.org/docs/12/app-pgdump.html
>-O > >--no-owner > > Do not output commands to set ownership of objects to match the > original database. By default, pg_dump issues ALTER OWNER or SET > SESSION AUTHORIZATION statements to set ownership of created database > objects. These statements will fail when the script is run unless it > is started by a superuser (or the same user that owns all of the > objects in the script). To make a script that can be restored by any > user, but will give that user ownership of all the objects, specify > -O. > > This option is only meaningful for the plain-text format. For the > archive formats, you can specify the option when you call pg_restore. > > -x > > --no-privileges > > --no-acl > > Prevent dumping of access privileges (grant/revoke commands).
Solution 6 - Sql
In newer versions of pg_restore it will complain about text file imports.
However, ou can just remove these lines with awk. You can pipe it to a new file to make sure it didn't break anything or just pipe it directly into psql like this:
cat my-import-file.sql | awk '!/old-role/' | psql -U target_owner -d target_db_name -1 -v ON_ERROR_STOP=1
- replace
my-import-file.sql
with your import file - replace
target_owner
with your username - replace
target_db_name
with the name of the database to create - replace
old-role
with the role it is complaining about
If you have multiple roles:
cat my-import-file.sql | awk '!/role1|role2|role3/' | psql -U target_owner -d target_db_name -1 -v ON_ERROR_STOP=1
This will remove all lines that mention that role. Everything will be created owned by the user logged in to psql.
Solution 7 - Sql
Well You can just create new role with same name as that you are missing, and then import dump with no errors.
error says 'Role "xxx" does not exist' - so create it :)