CODEWITHSUNDEEP
postgresqlpg-restore
Granny Aching
Granny Aching

Reputation: 1435

How to simplify db_restore

My current process for copying my database to the new server is to pg_dump with --no-owner --no-privileges

Then on the new server, I

  1. create the database
  2. create the two users
  3. create all the necessary permissions for both users
  4. run pg_restore as one user
  5. change ownership of the objects that should be owned by the second user

This is a very tedious and error-prone process. Just a few minutes ago, for example, I have created all the objects in the postgres database, instead of mydatabase.

Is there a way to make it simpler? Can the dump contain all the users I need, all the permissions, and database name?

Is is possible to do it all with one pg_dump line on the main server, then scp, and the one pg_restore line on the new server?

Should I be using pg_dumpall instead? I have only one database on the server.

Upvotes: 0

Views: 46

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247535

You can use pg_dumpall -r to dump the users (roles).

Apart from that, I would say that scripting is a solution. Write a script that does all the right moves, so you don't have to do them by hand.

Upvotes: 1

Related Questions