What is the correct way to make an exact copy of a database with pg_dump?

All we need is an easy explanation of the problem, so here it is.

Basically, I’m trying to make a SQL dump that will make an exact copy of a database: user defined functions, relationships, constraints, tables, data, etc.

Is this doable with pg_dump or does psql need to be used instead?

I’ve been using pg_dump -U user -cOx database > pg.sql, but it isn’t entirely clear to me if it captures everything.

I was looking at all of the pg_dump flags here:

https://www.postgresql.org/docs/11/app-pgdump.html

The only one I can think that might do it, because it doesn’t really say explicitly, is the -s --schema-only flag, which excludes data. I’m fine with that and running a second pg_dump for the data, but I primarily want to make sure I am backing up the user defined functions, relationships, etc.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I’m not a PostgreSQL expert but I think their documentation is rather clear and I believe the command you’re using is pretty much all you need (you might need to specify the password with a flag – not sure), unless you do want to do it in two steps with --schema-only and then with --data-only:

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently.

pg_dump only dumps a single database. To back up an entire cluster, or to back up global objects that are common to all databases in a cluster (such as roles and tablespaces), use pg_dumpall.

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql.

This tutorial on pg_dump and pg_dumpall should help get you going too.

Finally, best thing you can do is run the scripts after they’re generated, in a test database and spot check for the different things you’d expect to get created (Table, View, Primary Key, Foreign Key, other constraints, etc) before you make any drastic changes to the original database it came from.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply