Backing up Login Roles aka Users and Group Roles

Sometimes when you are testing or setting up a server or just porting things to another server, you just want to install the same set of users as you had before without restoring any databases. In PostgreSQL, the users (Login Roles) and group roles are stored at the server level and only the permissions to objects are stored at the database level.

Question:

How do you restore just the users and roles without having to do a full pg_dumpall of your server?

Answer

Using the Command Line:

Remember that these executables pg_dumpall, psql are located in the bin of your postgresql install

Pre 8.3 syntax
pg_dumpall -h localhost -p 5432 -U postgres -v --globals-only > /path/to/useraccts.sql

8.3 Syntax

8.3 introduced the -f option to denote the file name and -r to only backup roles which makes things a bit more predictable how they behave from OS to OS.

pg_dumpall -h localhost -p 5432 -U postgres -v --roles-only -f "/path/to/useraccts.sql"

If you want to backup all globals which includes tables spaces run

pg_dumpall -h localhost -p 5432 -U postgres -v --globals-only -f "/path/to/globals.sql"

Then to restore the accounts on the new server, open up the .sql file generated and delete all the accounts and stuff you don't want to bring over. Then just run the generate .sql file with psql something like

psql -h localhost -d postgres -U postgres -f "/path/to/useraccts.sql"

Using PgAdmin III

1. Connect to the Server
2. At Server level, right click the Server and choose Backup Globals
3. Browse to path you want and give a .sql extention

1. To restore - connect to Postgres db (or any db as a super user)
2. simply open the .sql file in the Query window
3. Delete all the stuff at the top like \connect etc
4. Run the script