On Wed, Oct 7, 2009 at 4:23 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "Loic d'Anterroches" <diaeresis@xxxxxxxxx> writes: >> Each night I am running: >> pg_dump --blobs --schema=%s --no-acl -U postgres indefero | gzip > >> /path/to/backups/%s/%s-%s.sql.gz >> this for each installation, so 1100 times. Substitution strings are to >> timestamp and get the right schema. > > This seems like a pretty dumb way to go at it. Why don't you just do > one -Fc dump for the whole database? If you ever actually need to > restore a single schema, there's a pg_restore switch for that. Thank you for your fast answer. This is the way I started to do the work, but then I started to have issues because of the numbers of tables to be soft locked at the same time increasing each time, I had to push the max_locks_per_transaction settings up each time. The added benefit of doing a per schema dump is that I provide it to the users directly, that way they have a full export of their data. I cannot increase the max_locks_per_transaction all the time with the increasing number of schemas, no? What is the problem if I put this settings at a high value (outside of the memory overhead per connection as far as I understood the doc)? >> I think that pg_dump, when looking at the objects to dump, also it is >> limited to a given schema, is scanning the complete database in one >> those calls: > > Yes, it has to examine all database objects in order to trace > dependencies properly. > >> Is there an option: "I know what I am doing, do not look outside of >> the schema" available which can help in my case? > > No. So it looks like I may need to go a different way. I can setup a WAL based backup server and dump the content of each schema in an application specific way (JSON export), this way I can keep the ready to use backup (at the moment a restore is just a series of import for each schema) with the slave and it will keep my customers happy with the JSON dump. If you know a better solution, I would be pleased to be guided in the right direction. loïc -- Loïc d'Anterroches - Céondo Ltd - http://www.ceondo.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
