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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
