Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Import postgres database without roles

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?

like image 493
Braden Avatar asked Jun 17 '13 17:06

Braden


People also ask

How do I back up just the roles of users in PostgreSQL?

You can use pg_dumpall for that with the --globals-only option: pg_dumpall --globals-only --file=all_roles_and_users.

Is user and role same in Postgres?

Users, groups, and roles are the same thing in PostgreSQL, with the only difference being that users have permission to log in by default. The CREATE USER and CREATE GROUP statements are actually aliases for the CREATE ROLE statement.


2 Answers

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.)

like image 126
oerich Avatar answered Sep 28 '22 17:09

oerich


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.

like image 20
Loïc Faure-Lacroix Avatar answered Sep 28 '22 19:09

Loïc Faure-Lacroix