Postgresql – How should I be backing up Postgres for this scenario

backuppg-dumppostgresql

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?

Best Answer

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.

Related Question