How to transfer PSQL users and roles to another database



  • 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



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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2