Lucas, * Lucas (luca...@gmail.com) wrote: > pg_dump was taking more than 24 hours to complete in one of my databases. I > begin to research alternatives. Parallel backup reduced the backup time to > little less than a hour, but it failed almost every time because of > concurrent queries that generated exclusive locks. It is difficult to > guarantee that my applications will not issue queries such as drop table, > alter table, truncate table, create index or drop index for a hour. And I > prefer not to create controls mechanisms to that end if I can work around > it.
I certainly understand the value of pg_dump-based backups, but have you considered doing file-based backups? That would avoid the need to do any in-database locking at all, and would give you the ability to do PITR too. Further, you could actually restore that backup to another system and then do a pg_dump there to get a logical representation (and this would test your physical database backup/restore process too...). Thanks! Stephen
signature.asc
Description: Digital signature
