PostgreSQL – Dumping Specific Non-Table Objects

pg-dumppostgresql

I find myself needing to migrate a specific application from one host to another. That application touches a subset of tables within a certain PostgreSQL database; in order to migrate properly, I need to dump all the data from some of these, as well as the schema only from others, while ignoring some other tables in the same database.

The obvious solution is to use pg_dump with the right combination of -t and --exclude-table-data. However, when I do this, certain functions and triggers on the tables listed do not get dumped. These are necessary for the application to work, so I need them in the output as well.

I have not been able to find a way to dump triggers and functions by name. Passing their names to -t does not produce any output. Meanwhile, doing a whole-database dump does indeed dump the triggers and functions; however, this includes the extraneous tables in the same database (used by other applications), which I don't want to bring along.

I could run a whole-database dump with -T options set to exclude all the tables I don't want. However, this would require me to update the dump script every time anyone adds a new table, which would be somewhat inconvenient. I would prefer to only change the script when I update the schemas for my application. I could also run the whole dump and post-process the output to include only the tables I want, but this would require me to basically parse SQL on my own, which I'd rather not.

Is there a way using pg_dump to include specific triggers and functions in the dump, by name? Alternately, can I tell it to include objects that are dependencies of the tables I dump, or similar?

Best Answer

I could run a whole-database dump with -T options set to exclude all the tables I don't want.

So what you want is to automatically exclude all tables but the ones you specify? You could always do this with dynamic sql..

SELECT format(
  'pg_dump %s',
  string_agg(  format('-T ''%I.%I''', schemaname, tablename), ' '  )
)
FROM pg_tables
WHERE schemaname = CURRENT_SCHEMA -- whatever schema.
  AND tablename NOT IN ('myDesiredTableName');

Run that with psql -c and then run the output from it to dump the database.

Related Question