Create new postgres database from pg_dump plain-text Script

Question

I am tasked with providing a database export that clients can use to recreate & review their data without needing access to our internal system.

I dump and then try to restore the data as follows:

pg_dump --create -d dbname -U user -h host.domain.com --schema='(public|custom)' --data-only --column-inserts > output_file.dmp
createdb -d new_dbname
psql -d new_dbname -f output_file.dmp

I am met with errors like this:

psql: output_file.dmp:15172: ERROR:  relation "custom.some_table" does not exist
LINE 1: custom.some_table (id, rev, revtype,...

I am not sure if this used to work because I inherited this process, but I am able to replicate their errors on our test Linux server and locally on MacOS.

How can I load these dumps successfully?

It seems like either myself or the client have a lot more work ahead than originally expected. I may need to figure out how to programmatically recreate each relation before the INSERT. — DoritoStyle, Sep 22 '23 at 13:01
Maybe a solution could be drawn from https://dba.stackexchange.com/q/96695/279706 — DoritoStyle, Sep 22 '23 at 13:04

score 0 · Answer 1 · answered Sep 21 '23 at 19:43

That process doesn't make much sense. If you run

pg_dump --create --data-only --column-inserts dbname

The dump will look somewhat like this:

CREATE DATABASE dbname;
\c dbname
INSERT INTO tab ...;
...

That is, if you then create database new_dbname, then restore with

psql -d new_dbname dumpfile

psql will connect to new_dbname, then run CREATE DATABASE dbname, then connect to dbname and restore the dump there.

The error you encounter is unclear; I'd have to see the real, complete message. Perhaps a foreign key references a table that was not dumped?

I see now that the create step is redundant. – DoritoStyle Sep 21 '23 at 20:58 — DoritoStyle, Sep 21 '23 at 20:58

Create new postgres database from pg_dump plain-text Script

1 Answers1