My use case is not so much for database backup purposes as it is for
fine grained export and import.
Our database schema consists of 15 tables that represent a terminology.
Each database can hold multiple terminologies - every table has a
terminologyId column which distinguishes one terminology from another.
I now realize that I don't need to do anything special with individual
tables since PostgreSQL handles foreign keys properly - so that question
is void - I'm fine with working with a whole database at a time.
However, quite often I will load up a terminology on our development or
test server (which also contains many other very large terminologies).
When I am satisfied that it is ready, I want to put this terminology
onto our production servers. But I don't want to have to export
millions of rows that I don't need.
With MySQL, the ability to do a dump of data which satisfies a where
clause (for example: terminologyId='foo') gives me the ability to dump
the data out to a file very quickly - move the file to the server it
needs to be loaded on, and then re-load that data into the production
database.
In PostgreSQL, the only way that I see to do this is to have my desired
data in a database all by itself, so that I can use pg_dump to backup
the entire database. Then I can load that into the existing production
database.
Is there a better way to do this? Is there a flag I could specify for
psql that would cause it to output INSERT or COPY statements as a result
of a query - select * from foo where terminologyId=foo? Then I could
just have 15 select statements batched up in a file, and pipe the output
into a new file.
I suppose this is kind of an obscure use case - but a flag on pg_dump
where I could specify a where condition would certainly be handy.
Thanks,
Dan
--
****************************
Daniel Armbrust
Biomedical Informatics
Mayo Clinic Rochester
daniel.armbrust(at)mayo.edu
http://informatics.mayo.edu/