Menu
Is it possible to query how many multiversioned rows are there in a db ?
We want to measure the impact of pg_dump on a production database, and to suspend it in case of need: is it creating too many multiversioned rows?
Thanks in advance
289
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).
Restoring the data from pg_dump doesn't overwrite the data but it appends the data to the original database. New!
If you use the --clean option of pg_restore , the old tables will be dropped before the new ones are created. If you do not use the --clean option, you will get an error message that the table already exists, but pg_restore will continue processing unless you use the --exit-on-error option.
When you use pg_dump, there's no way to preserve the index data -- the index must be rebuilt after importing. You could simply tar up your whole postgres data directory instead of doing a pg_dump however.
pg_dump doesn't create any row versions (a.k.a tuples).
The only impact of pg_dump are the increased I/O load and the long running transaction it creates. The long transaction will keep autovacuum from reclaimimg dead tuples for the duration of the dump.
Normally that is no big problem unless you have very high write activity in the database. To mitigate that problem, you could create a streaming replication standby server, set max_standby_streaming_delay to more than the duration of pg_dump and let pg_dump run there.
106
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
