PostgreSQL：pg_dump对生产库的影响及多版本行数查询方法

Absolutely, you can absolutely do this—let’s break it down step by step to help you assess pg_dump’s impact on your production PostgreSQL database and manage it effectively.

First, it’s key to know that pg_dump creates a consistent, read-only snapshot of your database using PostgreSQL’s MVCC (Multi-Version Concurrency Control) system. By default, it doesn’t take exclusive locks on tables, so your production workload can keep running while the dump proceeds. However, it does have two main potential impacts:

• Resource usage: pg_dump consumes CPU, memory, and I/O bandwidth to read and write backup files, which might compete with your production traffic.
• Multiversioned tuple retention: Since pg_dump holds a transaction snapshot until it finishes, PostgreSQL can’t vacuum old (dead) tuples that were modified after the snapshot was taken. This can lead to an accumulation of dead tuples (the "multiversioned rows" you’re asking about) temporarily, until the dump completes and VACUUM can clean them up.

You can track dead tuples using built-in system views and optional extensions. Here are the most useful methods:

1. Using pg_stat_user_tables (Built-In, Low Overhead) #

This view provides aggregated statistics for user tables, including counts of live and dead tuples. Run this query to get a high-level overview:

SELECT
  schemaname,
  relname AS table_name,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  CASE WHEN (n_live_tup + n_dead_tup) > 0 THEN
    round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2)
  ELSE 0 END AS dead_row_percentage
FROM pg_stat_user_tables
ORDER BY dead_row_percentage DESC;

• n_dead_tup: The number of tuples marked as dead (updated/deleted but not yet vacuumed) — these are the multiversioned rows you’re monitoring.
• Note: Statistics here are periodically updated by PostgreSQL’s autovacuum. If you need fresher data, run ANALYZE your_table_name; for specific tables first.

2. Using pgstattuple (Detailed, But Higher Overhead) #

For per-table granular details (including exact tuple sizes and dead tuple counts), use the pgstattuple extension. First, enable it (requires superuser privileges):

CREATE EXTENSION IF NOT EXISTS pgstattuple;

Then run it against a specific table:

SELECT * FROM pgstattuple('your_schema.your_table');

Look for the dead_tuple_count and dead_tuple_percent columns. Keep in mind: this scans the entire table, so avoid running it on large tables during peak production hours.

To link dead tuple growth to pg_dump:

1. Take a baseline: Run the pg_stat_user_tables query right before starting pg_dump to record initial dead tuple counts.
2. Monitor during dump: Periodically re-run the query while pg_dump is active. If you see n_dead_tup spiking significantly (e.g., dead row percentage jumps from 5% to 25%+), and your production workload is generating frequent updates/deletes, pg_dump’s snapshot is likely preventing vacuum from cleaning those tuples.
3. Check pg_dump’s snapshot duration: Longer dumps mean longer retention of dead tuples. You can track the pg_dump process in pg_stat_activity:

SELECT pid, query_start, state, query FROM pg_stat_activity WHERE query LIKE '%pg_dump%';

The process will stay in active or idle in transaction state until the dump finishes.

PostgreSQL doesn’t have a built-in "pause" command for pg_dump, but you can use operating system signals to pause and resume the process:

1. Find the pg_dump PID: Run this command in your terminal:

ps aux | grep pg_dump

2. Pause the process: Send the SIGSTOP signal to the PID:

kill -SIGSTOP <pg_dump_pid>

3. Resume the process: Send the SIGCONT signal when you’re ready:

kill -SIGCONT <pg_dump_pid>

⚠️ Important note: Pausing pg_dump will keep its transaction snapshot active, so dead tuples will still be retained until the dump completes. If dead tuple accumulation is causing severe issues (e.g., disk space exhaustion, query slowdowns), it might be better to terminate the dump entirely with kill -SIGTERM <pg_dump_pid> and restart it during a lower-traffic window.

To reduce the chance of excessive dead tuple growth in the first place:

• Run pg_dump during off-peak hours when update/delete activity is low.
• Use parallel dumps with the --jobs flag (e.g., pg_dump --jobs=4) to reduce the total snapshot duration.
• For very large databases, consider incremental backups or dumping tables in batches instead of a single full dump.

内容的提问来源于stack exchange，提问作者artejera