How to simplify db_restore

Question

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.

Answer 1

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.