Export domain in postgres

Question

I want to backup some tables of an postgres db in another empty db. To do this I use pg_dump with the -t option, but when I restore it I get an error due to a missing domain.

The only solution I found is to dump the db in --schema-only mode, to delete all the lines that didn't concern domains and to restore it in the empty db before restoring the tables.

The problem is that I need to make the backups quickly but sometimes I manipulate big db so the dump is long, even in --schema-only mode.

So is there a quickest way to export domains to another db ?

Answer 1

The easiest way is to create a custom format schema-only dump, extract the table of contents, identify the CREATE DOMAIN statements and extract only those from the dump:

pg_dump --schema-only --format=custom --file=dump dbname

pg_restore --list dump | grep DOMAIN >domlist

pg_restore --use-list=domlist --file=domain.sql dump

That will create a file domain.sql with all the domains in it.