On Tue, 2005-10-11 at 11:20, Dan Armbrust wrote:
> Does postgresql have any facility to dump anything more fine grained
> than a database to a text file?
>
> For example, to mention a bad word, MySQL's dump command allows you to
> specify individual tables to dump - and not only that - you can also
> specify a SQL query that must be satisfied for a row to make it into the
> dump file - this is extremely useful for the way that we use our database.
>
> Also - how does postgresql handle foreign keys if you dump an entire
> database along with the create table statements? Because my foreign
> keys require that the tables be created in a certain order - MySQL fails
> on this front - but since it allows me to specify the tables to dump,
> and it dumps them in that order, I can specify the correct order.
>
> PostgreSQL's pg_dump command seems rather limited in its abilities.
> Maybe I'm missing the command I'm looking for.
Actually, this is one of those instances when it only seems more limited
than it really is.
pg_dump can dump a whole database full of dependencies and restore it.
MySQL makes you jump through hoops to do the same thing. hence, it has
the ability to dump certain tables and parts of those tables so that YOU
can do the work of figuring out what to tell it to get a coherent
backup.
Admittedly, it would be nice to have some more selectivity in pg_dump,
but for now, it's actually in pg_restore where the selectivity resides.
I.e. backup the whole database, then restore what you need.
Now, features that would create a .bad or .log file during restores and
toss bad tuples in there while continuing, or having a threshold and all
that would be very nice. But mostly, I find myself still liking the way
postgresql does backups a lot more than the way oracle does them, even
though sqlldr and such are far more powerful, they also require a lot
more of the DBA to get simple jobs done.
Normally, when I need certain tables or parts of them, I just fork the
database (create database mycopy with template masterdb) and then prune
that database down to what I need and back that up. No interruptions in
service, and I can test just those data in that copy database to make
sure it's what I really want.
It's not that MySQL is more featureful or powerful, just that PostgreSQL
generally calls for a different way of looking at things. Don't be
afraid of "big commands", like creating a database from another database
as a template for this kind of thing. PostgreSQL handles them well.