[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-performance
Subject: [PERFORM] Feature Request: No pg_dump lock on unlogged tables
From: "McGehee, Robert" <Robert.McGehee () geodecapital ! com>
Date: 2012-03-11 17:38:04
Message-ID: 17B09E7789D3104E8F5EEB0582A8D66FBAA6006CF5 () MSGRTPCCRF2WIN ! DMN1 ! FMR ! COM
[Download RAW message or body]
Hello,
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic