How to transfer PSQL users and roles to another database

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.

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

Leave a Comment