[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Feature Request: No pg_dump lock on unlogged tables

Due to a large database size, my weekend database backup (pg_dumpall) takes about 12 hours to complete. Additionally, I add the --no-unlogged-table-data option to skip any volatile tables. One UNLOGGED table in the database is completely regenerated every day with a TRUNCATE TABLE/INSERT command. However, despite the --no-unlogged-table-data option, the table still gets locked by the pg_dump(all), preventing the operation from completing for several hours during backups.

To the extent possible, I would like to request a way to prevent certain (UNLOGGED) tables from being locked against truncate/schema changes for the extent of an entire database backup.

For example:
. pg_dump does not lock unlogged tables if --no-unlogged-table-data is set 
. pg_dump supports sequential table locks / unlocks during backups (only the table that is currently being copied is locked, rather than all tables for the entire backup).
. Support a way to automatically replace the TRUNCATE command with DELETE if TRUNCATE cannot immediately obtain a lock.

Thanks, Robert

Robert McGehee, CFA
Geode Capital Management, LLC
One Post Office Square, 28th Floor | Boston, MA | 02109
Direct: (617)392-8396

This e-mail, and any attachments hereto, are intended for use by the addressee(s) only and may contain information that is (i) confidential information of Geode Capital Management, LLC and/or its affiliates, and/or (ii) proprietary information of Geode Capital Management, LLC and/or its affiliates. If you are not the intended recipient of this e-mail, or if you have otherwise received this e-mail in error, please immediately notify me by telephone (you may call collect), or by e-mail, and please permanently delete the original, any print outs and any copies of the foregoing. Any dissemination, distribution or copying of this e-mail is strictly prohibited. 

Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Home]     [Yosemite]

Powered by Linux