Keith Fiske: PG Extractor - A smarter pg dump

Pg_dump is a great tool for getting your data out of the database for backups or many other uses. It is, however, lacking in fine grained filtering to get exactly the schema and/or data you want. PG Extractor (pg_extractor) was born out of this frustration to show what pg_dump could hopefully be some day.

A lack of ACLs and Comments in pg_dump output for anything but tables; an extremely long and tedious command to type out to filter out possibly hundreds of table and views; no filtering on any object types/names except for schemas and tables; these are all issues I've run into with trying to use pg_dump as a schema backup tool. What started out as a tool that contained complex internal queries to try and get the internal structure of a database out in finer detail was rewritten to use pg_dump/all & pg_restore to their fullest potential.

Want to dump out a list of tables to a file using psql and feed that back in as an include/exclude filter? Want to dump objects based on owner? Want to use regex pattern matching to rule out partitions in your schema dump? Want to use Git or SVN to automatically keep track of schema changes to your database? pg_extractor allows these things and more.

This talk will discuss some of the development process of the tool and issues (with some solutions!) that were encountered along the way. Will also give examples and how we currently use this tool in production.