请求PostgreSQL的pg_basebackup提速方案及大索引删建脚本

Hey there! Let's walk through your question step by step—first checking if your plan makes sense, then giving you the scripts you need to manage those large indexes.

Absolutely, your approach is smart and totally workable! Here's why, plus some key things to keep in mind:

• The upside: Removing large indexes cuts down the total data size you need to back up, which directly reduces backup time. Skipping (or reducing) compression overhead also helps—you’re right that gzip adds CPU load during backups.
• Critical caveats:
  • Make sure the indexes you delete aren’t required for your live application’s queries before you finish rebuilding them in the new environment. Dropping indexes will slow down read queries on the original database until you switch to the new environment.
  • If your original database is still handling writes after you delete indexes but before you run pg_basebackup, those writes will still be captured in the backup (thanks to the -x flag for WAL inclusion)—so rebuilding the indexes in the new environment will catch up all those changes correctly.
  • Plan the order of operations carefully in the new environment: rebuild the indexes first, then run your DML/DDL changes. This ensures the indexes are up-to-date with the final state of your production data.

Below are ready-to-use SQL scripts to identify, drop, and rebuild indexes over 256MB. All use PostgreSQL’s system catalogs to pull accurate metadata.

1. Identify Large Indexes (>256MB) #

First, run this to list all indexes that meet your size threshold, along with their parent tables and schemas. This helps you verify which ones are safe to drop:

SELECT
    n.nspname AS schema_name,
    t.relname AS table_name,
    i.relname AS index_name,
    pg_size_pretty(pg_relation_size(i.oid)) AS index_size
FROM
    pg_class i
JOIN
    pg_index idx ON i.oid = idx.indexrelid
JOIN
    pg_class t ON idx.indrelid = t.oid
JOIN
    pg_namespace n ON t.relnamespace = n.oid
WHERE
    i.relkind = 'i' -- Only target indexes
    AND pg_relation_size(i.oid) > 256 * 1024 * 1024 -- 256MB threshold
    AND NOT idx.indisprimary -- Exclude primary key indexes (usually critical!)
ORDER BY
    pg_relation_size(i.oid) DESC;

Heads up: Primary key and unique constraint indexes are almost always required for data integrity. Only drop them if you’re 100% sure you can safely rebuild them later.

2. Generate Drop Index Statements #

Once you’ve confirmed which indexes to remove, run this to auto-generate DROP INDEX commands. You can copy the output into a SQL file and execute it during a low-traffic window:

SELECT
    'DROP INDEX IF EXISTS ' || quote_ident(n.nspname) || '.' || quote_ident(i.relname) || ';' AS drop_index_sql
FROM
    pg_class i
JOIN
    pg_index idx ON i.oid = idx.indexrelid
JOIN
    pg_class t ON idx.indrelid = t.oid
JOIN
    pg_namespace n ON t.relnamespace = n.oid
WHERE
    i.relkind = 'i'
    AND pg_relation_size(i.oid) > 256 * 1024 * 1024
    AND NOT idx.indisprimary;

Pro tip: Save the output of the next script (rebuild commands) before dropping anything—you don’t want to lose the index definitions!

3. Generate Rebuild Index Statements #

In your new environment, run this to create CREATE INDEX commands for all the indexes you dropped. This pulls the exact original index definitions from the source database’s catalogs:

SELECT
    pg_get_indexdef(idx.indexrelid) || ';' AS create_index_sql
FROM
    pg_class i
JOIN
    pg_index idx ON i.oid = idx.indexrelid
JOIN
    pg_class t ON idx.indrelid = t.oid
JOIN
    pg_namespace n ON t.relnamespace = n.oid
WHERE
    i.relkind = 'i'
    AND pg_relation_size(i.oid) > 256 * 1024 * 1024
    AND NOT idx.indisprimary;

Optimization: If the new environment is already handling live traffic, add CONCURRENTLY to each CREATE INDEX statement (e.g., CREATE INDEX CONCURRENTLY ...). This avoids locking the table during rebuild, though it will take a bit longer. If the environment is still quiet, skip it for faster rebuilds.

Beyond your index plan, here’s one more trick to speed up backups:

• Replace PostgreSQL’s built-in gzip with a parallel compression tool like pigz (which uses multiple CPU cores). Modify your backup command like this:

  $PGHOME/bin/pg_basebackup -p 5433 -U postgres -P -v -x --format=tar --pgdata=- | pigz -6 > /opt/backup_db/backup.tar.gz
  

  This is way faster than the single-threaded gzip that pg_basebackup uses by default. If you’re on PostgreSQL 12+, you can also use --compress=zstd for even better speed and compression ratio.

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