0

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?

2
  • 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. Commented Sep 22, 2023 at 13:01
  • Maybe a solution could be drawn from dba.stackexchange.com/q/96695/279706 Commented Sep 22, 2023 at 13:04

1 Answer 1

0

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?

1
  • I see now that the create step is redundant. Commented Sep 21, 2023 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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