On 11/06/2017 12:30 PM, Stephen Frost wrote:
* 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...).
Yes, a point in time recovery has the advantage of keeping the backup
more up-to-date, but has the disadvantage of being more expensive and
complex. In my case, point in time recovery would require an upgrade of
10 TB of storage space and my stakeholders did not approved this
investment yet.
I suspect that there is lots of users that uses pg_dump as primary
backup tool and that they would benefit of a more reliable parallel backup.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
