PostgreSQL部分表数据的增量归档与恢复最佳实践问询

Great question! Let's walk through the best practices for archiving and restoring specific historical data from your AWS RDS PostgreSQL 9.6 transactions_table—focused on 2017 June data, with options to remove it from the source table and store archives in S3/Glacier.

Option 1: Using pg_dump (Generates Human-Readable INSERT Statements) #

pg_dump works perfectly for this use case, especially if you need easily inspectable INSERT statements for recovery. The critical detail is pairing it with a transaction to ensure the data you export is exactly what you delete from the source table (no inconsistencies from mid-process changes).

Step 1: Export the 2017 June Data #

Run this pg_dump command to generate a .sql file with INSERT statements for your target data:

pg_dump -h your-rds-endpoint -U your-db-username -d your-db-name \
  --data-only \
  --table=transactions_table \
  --where="transaction_date >= '2017-06-01' AND transaction_date < '2017-07-01'" \
  --inserts \
  -f June2017.sql

• --data-only: Skips table schema (we only need the rows)
• --where: Uses a half-open date range to avoid edge cases with time-stamped rows
• --inserts: Forces individual INSERT statements instead of bulk COPY (great for small-to-medium datasets, easier to debug if needed)

Step 2: Delete the Archived Data Atomically #

To avoid data drift between export and delete, use a transaction with a snapshot:

1. Open a psql session to your RDS instance:

   psql -h your-rds-endpoint -U your-db-username -d your-db-name
   

2. Start a transaction and capture a snapshot (save the returned ID, e.g., 00000003-0000001B-1):

   BEGIN;
   SELECT pg_export_snapshot();
   

3. Re-run the pg_dump command with the --snapshot flag to use the same transaction state:

   pg_dump -h your-rds-endpoint -U your-db-username -d your-db-name \
     --data-only \
     --table=transactions_table \
     --where="transaction_date >= '2017-06-01' AND transaction_date < '2017-07-01'" \
     --inserts \
     --snapshot=00000003-0000001B-1 \
     -f June2017.sql
   

4. Back in psql, delete the data and commit:

   DELETE FROM transactions_table WHERE transaction_date >= '2017-06-01' AND transaction_date < '2017-07-01';
   COMMIT;
   

Option 2: Using COPY (Faster for Large Datasets) #

If you're dealing with millions of rows, COPY is far more efficient than pg_dump with INSERT statements. It exports/imports data in CSV format, which is lighter and faster to process.

Step 1: Export to CSV #

psql -h your-rds-endpoint -U your-db-username -d your-db-name \
  -c "COPY (SELECT * FROM transactions_table WHERE transaction_date >= '2017-06-01' AND transaction_date < '2017-07-01') TO STDOUT WITH CSV HEADER" \
  > June2017.csv

Follow the same transaction/snapshot workflow from Option 1 to ensure consistency between export and delete.

Step 2: Delete the Data #

Use the batch delete method below for very large datasets to avoid long table locks:

BEGIN;
WHILE EXISTS (SELECT 1 FROM transactions_table WHERE transaction_date >= '2017-06-01' AND transaction_date < '2017-07-01') LOOP
    DELETE FROM transactions_table WHERE transaction_date >= '2017-06-01' AND transaction_date < '2017-07-01' LIMIT 1000;
    COMMIT;
    BEGIN;
END LOOP;
COMMIT;

Restoring the Archived Data #

Restoring from .sql (INSERT Statements) #

Run this command to restore directly to the original table:

psql -h your-rds-endpoint -U your-db-username -d your-db-name -f June2017.sql

For safety, first restore to a temporary table to validate data:

-- Create temp table matching the source schema
CREATE TEMP TABLE temp_transactions AS SELECT * FROM transactions_table LIMIT 0;
-- Import data
\i June2017.sql
-- Verify counts/contents
SELECT COUNT(*) FROM temp_transactions;
-- If all looks good, insert into original table
INSERT INTO transactions_table SELECT * FROM temp_transactions;

Restoring from CSV #

Use COPY for fast bulk import:

psql -h your-rds-endpoint -U your-db-username -d your-db-name \
  -c "COPY transactions_table FROM STDIN WITH CSV HEADER" \
  < June2017.csv

Again, test with a temp table first to avoid accidental data issues.

AWS-Specific Recommendations #

• Automate the Workflow: Use AWS Lambda or Data Pipeline to trigger monthly archiving (e.g., run on the 1st of each month to archive the previous month's data). Use the AWS CLI to upload the archive directly to S3 from your execution environment.
• Cost-Effective Storage: Upload the archive to S3, then set a lifecycle rule to automatically transition the file to Glacier after 30 days for long-term, low-cost storage.
• Permissions: Ensure your IAM role (if using Lambda) or local user has SELECT and DELETE permissions on transactions_table, plus access to S3/Glacier.

Which Option is Better? #

• Use pg_dump with --inserts if you need human-readable SQL, or for small datasets where readability matters.
• Use COPY for large datasets where speed and efficiency are priorities—it’s significantly faster than processing thousands of INSERT statements.

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