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.
Best Answer
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
:This tutorial on
pg_dump
andpg_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.