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?


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.

| improve this answer | |

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.