3

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?

3

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.

  • I ended up doing something much like this, except in shell instead -- grepping the output of a full pg_dump -s for tables that didn't match the list I wanted. I'll mark this as accepted, since the methods are pretty equivalent. – Tom Hunt Jun 6 '17 at 21:08
  • @TomHunt 'However, when I do this, certain functions and triggers on the tables listed do not get dumped.' - how does the above solution solve this? – dezso Jun 7 '17 at 8:05
  • 1
    @dezso If -t is passed to pg_dump, all functions and triggers are excluded (since it only dumps the named tables). If -T is passed, functions and triggers still get dumped. – Tom Hunt Jun 7 '17 at 16:03
1

My approach would be taking a non-plain-text dump (using the -Fc option to pg_dump, for example), and then use pg_restore to restore the objects you need. This way you don't have to generate your command using SQL, and the dump always contains all objects. You will then filter them upon restoring using something like

pg_restore -l | grep -v -l excluded_tables.list > to_be_restored.toc && 
pg_restore -L to_be_restored.toc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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