How should I be backing up Postgres for this scenario?

Question

So the way I have been backing up Postgres is with:

pg_dumpall -U eox-dev -h localhost --clean --file=/home/backups/postgres.sql

I want to make sure I have a production ready backup that has functions, relationships, triggers, in addition to tables and data.

I'm trying to restore it now in a Kubernetes cluster into a database with a different name prod_db -> staging_db, but get FATAL: database "prod_db" does not exist. I'm trying to restore it to staging_db so that makes sense. One of the first lines in the .sql does declare CREATE DATABASE prod_db. I'm not about the edit the .sql. If I have to do that, I'm doing something wrong.

What is the correct way of making this backup?

Answer 1

If you want to restore individual databases, you should probably be using pg_dump, not pg_dumpall. And if you don't intend to restore over the top of an existing populated database, you shouldn't be using --clean.

Backing up your entire cluster and migrating/cloning one database that is in it are different tasks, you probably want different commands to do them.

But you have the dilemma that if you specify --create to pg_dump it will want to restore into a database of the same name as it dumped from, and if you don't specify --create then it is up to you to create a new database with the name you want, but also with the correct ENCODING and LOCALE.

Answer 2

I don't think that pg_dump or pg_dumpall or pg_restore can change the database name to be restored. However after restoring the database (with the same name) you can rename it with:

alter database old rename to new;