How to transfer PSQL users and roles to another database

All we need is an easy explanation of the problem, so here it is.

Is it possible to copy an identical PostgreSQL setup to another computer running the exact same OS version?
The main thing I am interested in is copying the users, groups, and roles without having to manually create them one by one in the new system.

PSQL version: 9.4 running on Debian Linux

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use pg_dumpall for this:

pg_dumpall --globals-only -U postgres -f roles.sql

This will put the definition of all roles (=users and groups) as well as any tablespaces into the output file. You have to use the superuser (typically postgres) for this.

Then run the generated roles.sql using psql on the other computer.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply