PostgreSQL Backup, Restore, and High Availability

Q: Does pg_dump lock the table when backing up?

pg_dump uses a snapshot (MVCC), so it doesn't lock tables and doesn't block reads/writes. But it backs up the data snapshot at the start moment; new data written during the backup is not included.

Q: Can PITR recover to just before a single row was deleted?

Yes, as long as the WAL archive covers that time period. PITR's finest granularity is the transaction level — recovery_target_xid can pinpoint a specific transaction. But you can't skip some transactions and roll back only specific operations.

Q: Can WAL archive files be deleted?

WAL that has passed a Checkpoint and is no longer needed by a standby can be deleted. But PITR recovery needs all WAL segments from the base backup to the target time, so confirm they're no longer needed before deleting.

Q: Can a streaming-replication standby serve read queries?

Yes. With hot_standby = on , the standby accepts read-only queries (SELECT), called a "read replica," and is good for offloading analytical query load.

Q: Can logical replication cross PostgreSQL versions?

Yes. Logical replication sends logical changes (SQL operations), not relying on the physical format, so it supports cross-major-version upgrades and migrations. This is an important advantage of PG logical replication.

Q: Which to choose, pg_basebackup or pg_dump?

Choose pg_basebackup (physical backup) if you need PITR or full-cluster recovery; choose pg_dump (logical backup) for selective restore, cross-version migration, or single-table export. Production usually uses both.

Q: Which is faster to import, COPY or INSERT?

COPY by far. COPY is a bulk write with a single protocol round-trip; INSERT parses and executes row by row. Prefer COPY for large data imports; INSERT is more flexible for small amounts.

Q: What does a pg_restore --list error in the backup script mean?

The backup file may be corrupt or incomplete. --list only reads the archive directory without performing the restore, so it's a quick way to verify backup integrity. If corrupt, restore from another backup.

1. What You'll Learn

pg_dump / pg_dumpall logical backup
pg_restore restore
COPY command import/export (CSV / Binary)
WAL (Write-Ahead Log) principle
PITR point-in-time recovery (a PG specialty)
Streaming Replication
Logical Replication (a PG specialty: replicate per table)
pg_basebackup physical backup
Backup strategy: full + incremental

2. The Story

Bob is the DBA for an e-commerce platform. At 3 a.m., a developer mistakenly ran DELETE FROM products WHERE category = 'Electronics', deleting $2 million worth of product data.

Bob needs to recover to the 2:59 AM state — one minute before the mistaken deletion — with zero data loss. He chooses the PITR (Point-in-Time Recovery) solution.

3. Concept: Logical Backup

(1) pg_dump Options

Option	Meaning	Example
`-Fc`	Custom format (compressed, recommended)	`pg_dump -Fc db > db.dump`
`-Fd`	Directory format (parallel backup)	`pg_dump -Fd db -f dir/`
`-Fp`	Plain SQL text	`pg_dump -Fp db > db.sql`
`-j N`	Number of parallel jobs (needs -Fd)	`pg_dump -Fd db -j 4 -f dir/`
`-t table`	Back up only the specified table	`pg_dump -t products db > p.dump`
`-n schema`	Back up only the specified schema	`pg_dump -n public db > s.dump`
`--exclude-table`	Exclude a table	`pg_dump --exclude-table=logs db > d.dump`
`-Z 0-9`	Compression level	`pg_dump -Fc -Z6 db > db.dump`

▶ Example: Back Up a Single Database with pg_dump

BASH

# Custom format (recommended, compressed, parallel restore possible)
pg_dump -h localhost -U postgres -Fc shop_db > /backup/shop_db_$(date +%Y%m%d).dump

# Directory format with 4 parallel workers
pg_dump -h localhost -U postgres -Fd shop_db -j 4 -f /backup/shop_db_dir/

# Plain SQL format (human readable, editable before restore)
pg_dump -h localhost -U postgres -Fp shop_db > /backup/shop_db.sql

TEXT

# command executed successfully

▶ Example: Back Up Specific Tables with pg_dump

BASH

# Backup only orders and order_items tables
pg_dump -h localhost -U postgres -Fc \
  -t orders -t order_items \
  shop_db > /backup/order_tables.dump

# Backup all tables matching pattern
pg_dump -h localhost -U postgres -Fc \
  -t 'order_*' \
  shop_db > /backup/order_prefix_tables.dump

# Exclude large log tables
pg_dump -h localhost -U postgres -Fc \
  --exclude-table='access_log_*' \
  shop_db > /backup/shop_no_logs.dump

TEXT

# command executed successfully

(2) pg_dumpall vs pg_dump

Dimension	pg_dump	pg_dumpall
Backup scope	Single database	Whole cluster (all databases)
Role info	Not included	Includes role/tablespace definitions
Output format	Optional -Fc/-Fd/-Fp	Plain SQL only (-Fp)
Parallel	Supported (-j)	Not supported
Recommended scenario	Daily single-DB backup	Role backup + full-cluster migration

▶ Example: Full-Cluster Backup with pg_dumpall

BASH

# Backup all databases and roles (plain SQL only)
pg_dumpall -h localhost -U postgres > /backup/cluster_full_$(date +%Y%m%d).sql

# Backup only roles (useful for migration)
pg_dumpall -h localhost -U postgres --roles-only > /backup/roles_only.sql

# Backup only tablespace definitions
pg_dumpall -h localhost -U postgres --tablespaces-only > /backup/tablespaces.sql

TEXT

# command executed successfully

4. Concept: Logical Restore

(1) pg_restore Options

Option	Meaning	Applies to format
`-d db`	Restore to a specified database	-Fc / -Fd
`-j N`	Parallel restore	-Fc / -Fd
`--clean`	DROP then CREATE first	-Fc / -Fd
`--if-exists`	DROP IF EXISTS (with --clean)	-Fc / -Fd
`-t table`	Restore only the specified table	-Fc / -Fd
`--list`	List archive contents	-Fc / -Fd
`--section=pre-data`	Restore pre-data (schema) only	-Fc / -Fd

▶ Example: Restore from Custom Format with pg_restore

BASH

# Restore to a new database
createdb -h localhost -U postgres shop_db_restore
pg_restore -h localhost -U postgres -d shop_db_restore /backup/shop_db.dump

# Parallel restore (4 workers, directory format)
pg_restore -h localhost -U postgres -d shop_db -j 4 /backup/shop_db_dir/

# Restore with clean (drop existing objects first)
pg_restore -h localhost -U postgres -d shop_db \
  --clean --if-exists /backup/shop_db.dump

TEXT

# command executed successfully

▶ Example: Selective Restore

BASH

# List archive contents to find table entry numbers
pg_restore --list /backup/shop_db.dump

# Restore only specific tables by name
pg_restore -h localhost -U postgres -d shop_db \
  -t products -t categories \
  /backup/shop_db.dump

# Restore only schema (no data)
pg_restore -h localhost -U postgres -d shop_db \
  --section=pre-data /backup/shop_db.dump

TEXT

# command executed successfully

(2) pg_restore vs psql < file.sql

Dimension	pg_restore	psql < file.sql
Input format	-Fc / -Fd	Plain SQL text
Parallel	Supported (-j)	Not supported
Selective restore	Supported (-t / --list)	Not supported
Clean old data	--clean	Must write DROP manually
Recommended scenario	Custom / directory format	pg_dumpall output

▶ Example: Restore from Plain SQL

BASH

# Restore from pg_dumpall output
psql -h localhost -U postgres -f /backup/cluster_full_20250601.sql

# Restore from pg_dump plain format
createdb -h localhost -U postgres shop_db_new
psql -h localhost -U postgres -d shop_db_new -f /backup/shop_db.sql

TEXT

# psql command executed successfully

5. Concept: COPY Import/Export

(1) COPY vs \copy

Command	Executes at	File access	Privilege required
`COPY` (SQL)	Server side	Reads server filesystem	Superuser
`\copy` (psql)	Client side	Reads client file	Normal user

▶ Example: Export to CSV with COPY

SQL

-- Export to CSV with header
COPY (SELECT order_id, customer_id, total_amount, order_date
      FROM orders
      WHERE order_status = 'completed'
      ORDER BY order_date DESC)
TO '/tmp/completed_orders.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- Export entire table
COPY products TO '/tmp/products.csv' WITH (FORMAT csv, HEADER true);

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: Import CSV with COPY

SQL

-- Import from CSV
COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/new_products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- Import with error handling (PG 17+)
COPY products(product_name, unit_price, category, stock_qty)
FROM '/data/new_products.csv'
WITH (FORMAT csv, HEADER true, ON_ERROR ignore);

TEXT

-- SQL statement executed successfully

(2) COPY Format Options

Option	Value	Description
FORMAT	csv / text / binary	Output format
HEADER	true / false	First row is the column names
DELIMITER	',' / '\t'	Separator (comma default for csv)
QUOTE	'"'	Quote character
NULL	''	String representing NULL
ENCODING	'UTF8'	File encoding

▶ Example: Binary Format Export/Import

SQL

-- Binary export (faster, smaller)
COPY orders TO '/tmp/orders.bin' WITH (FORMAT binary);

-- Binary import
COPY orders FROM '/tmp/orders.bin' WITH (FORMAT binary);

TEXT

-- SQL statement executed successfully

6. Concept: WAL and PITR

(1) WAL (Write-Ahead Log) Principle

WAL is the core mechanism by which PG guarantees data integrity and supports recovery.

flowchart LR
    A[Client<br/>WRITE] --> B[WAL Buffer<br/>write log first]
    B --> C[WAL File<br/>persist to disk]
    B --> D[Shared Buffer<br/>write data later]
    D --> E[Data File<br/>Checkpoint flushes to disk]

    style B fill:#ffcdd2
    style C fill:#ff8a80
    style D fill:#c8e6c9
    style E fill:#a5d6a7

Concept	Description
WAL segment	WAL file, 16MB each by default
LSN	Log Sequence Number, identifies a log position
Checkpoint	Flush dirty in-memory pages to disk, recycle WAL
wal_level	WAL detail level: replica / logical
archive_mode	Whether to archive WAL files
archive_command	Archive to a specified path

(2) PITR Configuration Steps

Step	Operation	Description
1	Enable WAL archiving	`archive_mode = on`
2	Set archive command	`archive_command = 'cp %p /archive/%f'`
3	Base backup	`pg_basebackup`
4	Continuous archiving	WAL files archived automatically
5	Recover to point in time	`recovery_target_time`

▶ Example: Configure WAL Archiving

BASH

# postgresql.conf settings
cat >> /etc/postgresql/16/main/postgresql.conf << 'EOF'
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
archive_timeout = 300
max_wal_senders = 3
EOF

# Restart PostgreSQL
pg_ctlcluster 16 main restart

TEXT

# command executed successfully

▶ Example: pg_basebackup Physical Base Backup

BASH

# Full physical backup (base for PITR)
pg_basebackup -h localhost -U replicator \
  -D /backup/base_$(date +%Y%m%d) \
  -Ft -z -P \
  --checkpoint=fast

# -Ft: tar format
# -z: gzip compression
# -P: show progress
# --checkpoint=fast: force checkpoint before backup

TEXT

# command executed successfully

▶ Example: PITR Point-in-Time Recovery

Bob recovers to one minute before the mistaken deletion (2:59 AM):

BASH

# Step 1: Stop PostgreSQL
pg_ctlcluster 16 main stop

# Step 2: Clear existing data directory
rm -rf /var/lib/postgresql/16/main/*

# Step 3: Restore base backup
tar -xzf /backup/base_20250601/base.tar.gz \
  -C /var/lib/postgresql/16/main/

# Step 4: Create recovery configuration
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2025-06-01 02:59:00'
recovery_target_action = 'promote'
EOF

# Step 5: Create recovery signal file
touch /var/lib/postgresql/16/main/recovery.signal

# Step 6: Start PostgreSQL (will recover to target time)
pg_ctlcluster 16 main start

# PostgreSQL will replay WAL up to 02:59:00 then promote

TEXT

# command executed successfully

(3) Logical Backup vs Physical Backup vs PITR

Dimension	Logical backup (pg_dump)	Physical backup (pg_basebackup)	PITR
Granularity	Table / DB	Whole cluster	Whole cluster
Recovery precision	Backup moment	Backup moment	Any point in time
Recovery speed	Slow (SQL row by row)	Fast (file copy)	Medium (replay WAL)
Storage space	Small (compressed)	Large (full copy)	Medium (base + archive)
Zero loss	No	No	Yes
Online backup	Yes	Yes	Yes

7. Concept: Replication and High Availability

(1) Streaming Replication vs Logical Replication

Dimension	Streaming Replication	Logical Replication
Replication level	Physical WAL blocks	Logical changes (INSERT/UPDATE/DELETE)
Granularity	Whole cluster	Specified table / publication
Version requirement	Primary and standby versions must match	Can cross major versions
DDL replication	Automatic	Does not replicate DDL
Target writable	No (read-only standby)	Yes
PG specialty	—	PG native logical replication (10+)

▶ Example: Configure Streaming Replication

BASH

# On primary: postgresql.conf
wal_level = replica
max_wal_senders = 5
wal_keep_size = '1GB'

# On primary: pg_hba.conf
echo 'host replication replicator 192.168.1.0/24 md5' >> pg_hba.conf

# On primary: create replication user
psql -c "CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'RepP@ss';"

# On standby: take base backup
pg_basebackup -h primary_host -U replicator \
  -D /var/lib/postgresql/16/main -Fp -Xs -P -R

# -R: create standby.signal and auto-configure
# standby will auto-connect to primary on startup

TEXT

# psql command executed successfully

▶ Example: Configure Logical Replication

SQL

-- On publisher (source database)
CREATE PUBLICATION pub_orders FOR TABLE orders, order_items;

-- Or publish all tables in a schema
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- On subscriber (target database)
CREATE SUBSCRIPTION sub_orders
  CONNECTION 'host=primary_host dbname=shop_db user=replicator password=RepP@ss'
  PUBLICATION pub_orders;

-- Check replication status
SELECT * FROM pg_stat_replication;       -- on publisher
SELECT * FROM pg_stat_subscription;      -- on subscriber

TEXT

CREATE TABLE

(2) Replication Monitoring Views

View	Location	Contents
`pg_stat_replication`	Primary	Status of all connected standbys
`pg_stat_wal_receiver`	Standby	WAL receive status
`pg_stat_subscription`	Subscriber	Logical-replication subscription status
`pg_replication_slots`	Primary	Replication-slot info

▶ Example: Monitor Streaming Replication Lag

SQL

-- Check replication lag on primary
SELECT
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  (sent_lsn - replay_lsn) AS replication_lag
FROM pg_stat_replication;

-- Check if standby is in recovery mode
SELECT pg_is_in_recovery();

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

8. Concept: Backup Strategy

(1) Full + Incremental Comparison

Strategy	Method	Recovery time	Storage cost	Zero loss
Logical full	pg_dump on schedule	Slow	Low	No
Physical full	pg_basebackup on schedule	Fast	High	No
PITR	Base backup + WAL archive	Medium	Medium	Yes
Streaming replication	Standby real-time sync	Fastest	High	Near zero loss

▶ Example: Automated Backup Script

BASH

#!/bin/bash
# Daily backup script for shop_db

BACKUP_DIR="/backup/daily"
DATE=$(date +%Y%m%d_%H%M%S)
RETAIN_DAYS=7

# Full logical backup (pg_dump custom format)
pg_dump -h localhost -U postgres -Fc -Z6 \
  shop_db > ${BACKUP_DIR}/shop_db_${DATE}.dump

# Backup roles separately
pg_dumpall -h localhost -U postgres --roles-only \
  > ${BACKUP_DIR}/roles_${DATE}.sql

# Cleanup old backups (retain 7 days)
find ${BACKUP_DIR} -name "*.dump" -mtime +${RETAIN_DAYS} -delete
find ${BACKUP_DIR} -name "*.sql" -mtime +${RETAIN_DAYS} -delete

echo "Backup completed: shop_db_${DATE}.dump"

TEXT

# command executed successfully

(2) Recommended Production Strategy

Environment	Recommended solution	RPO	RTO
Development	pg_dump daily	24h	Hours
Testing	pg_dump + streaming replication	Minutes	Minutes
Production	PITR + streaming replication	Near zero	Minutes
Critical production	PITR + streaming replication + logical replication	Zero	Seconds

▶ Example: Verify Backup Integrity

BASH

# Verify pg_dump backup is readable
pg_restore --list /backup/shop_db.dump > /dev/null
if [ $? -eq 0 ]; then
  echo "Backup verified OK"
else
  echo "Backup CORRUPT - alert ops team!"
fi

# Verify WAL archive is not falling behind
psql -c "SELECT pg_current_wal_lsn(), pg_last_archive_lsn();"

TEXT

# psql command executed successfully

9. Flowchart: Backup/Restore Decision

flowchart TD
    A[Need backup/restore?] --> B{Restore scenario?}
    B -->|Dropped table/data| C{Have logical backup?}
    B -->|Whole DB crash| D{Have PITR?}
    B -->|Planned migration| E{Data volume?}
    C -->|Yes| F[pg_restore -t table]
    C -->|No| G{Have streaming standby?}
    G -->|Yes| H[Export from standby]
    G -->|No| I[Data unrecoverable]
    D -->|Yes| J[PITR to target time]
    D -->|No| K[pg_basebackup to backup time]
    E -->|Small| L[pg_dump/pg_restore]
    E -->|Large| M[pg_basebackup + streaming]
    J --> N{Cross-version?}
    N -->|Yes| O[Logical replication migration]
    N -->|No| P[Physical recovery]

    style I fill:#ffcdd2
    style J fill:#c8e6c9
    style O fill:#bbdefb

10. Comprehensive Example

Bob's full PITR recovery flow — recover to 2:59 AM after the 3 a.m. accidental products deletion:

SQL

-- Step 1: Confirm the accident time and data loss
SELECT pg_current_wal_lsn();  -- note current LSN
SELECT COUNT(*) FROM products WHERE category = 'Electronics';  -- verify loss

-- Step 2: Verify the WAL archive has the needed segments
SELECT pg_last_archive_lsn();
-- Should be >= LSN at 02:59 AM

BASH

# Step 3: Stop PostgreSQL immediately to preserve state
pg_ctlcluster 16 main stop

# Step 4: Preserve current WAL files (do NOT delete!)
cp -r /var/lib/postgresql/16/main/pg_wal /tmp/pg_wal_backup/

# Step 5: Restore base backup
rm -rf /var/lib/postgresql/16/main/*
tar -xzf /backup/base_20250531/base.tar.gz \
  -C /var/lib/postgresql/16/main/

# Step 6: Copy preserved WAL back for complete recovery
cp /tmp/pg_wal_backup/* /var/lib/postgresql/16/main/pg_wal/

# Step 7: Configure PITR target
cat >> /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2025-06-01 02:59:00'
recovery_target_action = 'promote'
EOF

touch /var/lib/postgresql/16/main/recovery.signal

# Step 8: Start and verify
pg_ctlcluster 16 main start

# Step 9: Verify recovery
psql -c "SELECT COUNT(*) FROM products WHERE category = 'Electronics';"

SQL

-- Step 10: After successful recovery, take a fresh backup
-- Run in psql after verifying data integrity
SELECT pg_switch_wal();  -- force WAL switch for clean archive point

BASH

# Step 11: Take new base backup for future PITR
pg_basebackup -h localhost -U postgres \
  -D /backup/base_$(date +%Y%m%d) -Ft -z -P

❓ FAQ

Q Does pg_dump lock the table when backing up?

A pg_dump uses a snapshot (MVCC), so it doesn't lock tables and doesn't block reads/writes. But it backs up the data snapshot at the start moment; new data written during the backup is not included.

Q Can PITR recover to just before a single row was deleted?

A Yes, as long as the WAL archive covers that time period. PITR's finest granularity is the transaction level — recovery_target_xid can pinpoint a specific transaction. But you can't skip some transactions and roll back only specific operations.

Q Can WAL archive files be deleted?

A WAL that has passed a Checkpoint and is no longer needed by a standby can be deleted. But PITR recovery needs all WAL segments from the base backup to the target time, so confirm they're no longer needed before deleting.

Q Can a streaming-replication standby serve read queries?

A Yes. With hot_standby = on, the standby accepts read-only queries (SELECT), called a "read replica," and is good for offloading analytical query load.

Q Can logical replication cross PostgreSQL versions?

A Yes. Logical replication sends logical changes (SQL operations), not relying on the physical format, so it supports cross-major-version upgrades and migrations. This is an important advantage of PG logical replication.

Q Which to choose, pg_basebackup or pg_dump?

A Choose pg_basebackup (physical backup) if you need PITR or full-cluster recovery; choose pg_dump (logical backup) for selective restore, cross-version migration, or single-table export. Production usually uses both.

Q Which is faster to import, COPY or INSERT?

A COPY by far. COPY is a bulk write with a single protocol round-trip; INSERT parses and executes row by row. Prefer COPY for large data imports; INSERT is more flexible for small amounts.

Q What does a pg_restore --list error in the backup script mean?

A The backup file may be corrupt or incomplete. --list only reads the archive directory without performing the restore, so it's a quick way to verify backup integrity. If corrupt, restore from another backup.

📖 Summary

pg_dump logical backup supports custom/directory/plain-SQL formats; the -Fc compressed format is most common
pg_restore restores from custom/directory format, supporting parallel, selective restore, and --clean cleanup
COPY / \copy quickly import/export CSV; COPY runs on the server, \copy runs on the client
WAL is the basis of PG recovery: write the log before the data, guaranteeing crash recovery
PITR (a PG specialty) recovers to any point in time via base backup + WAL archive, achieving zero data loss
Streaming replication syncs physical WAL in real time; the standby can serve as a read replica
Logical replication (a PG specialty) replicates at the table level, supports cross-version and a writable target
pg_basebackup physical backup is the basis for PITR and streaming replication
Production recommends a PITR + streaming replication combination, with periodic backup-integrity checks

📝 Exercises

⭐ Back up the shop_db database with pg_dump -Fc, then verify backup integrity with pg_restore --list, and restore it to the shop_db_test database.
⭐⭐ Write an automated backup script: a daily pg_dump full backup (custom format, compressed), retaining 7 days, verifying with pg_restore --list after backup. Also export the orders table to CSV (with header) using COPY, named by date.
⭐⭐⭐ Configure a full PITR solution: enable WAL archiving (archive_command to a specified directory), run a pg_basebackup base backup, simulate deleting data by mistake, then recover to a specified point in time. Record the LSN and recovery time, and verify data integrity. Additionally configure a streaming-replication standby and verify that read queries work.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Backup, Restore, and High Availability

1. What You'll Learn

2. The Story

3. Concept: Logical Backup

(1) pg_dump Options

▶ Example: Back Up a Single Database with pg_dump

▶ Example: Back Up Specific Tables with pg_dump

(2) pg_dumpall vs pg_dump

▶ Example: Full-Cluster Backup with pg_dumpall

4. Concept: Logical Restore

(1) pg_restore Options

▶ Example: Restore from Custom Format with pg_restore

▶ Example: Selective Restore

(2) pg_restore vs psql < file.sql

▶ Example: Restore from Plain SQL

5. Concept: COPY Import/Export

(1) COPY vs \copy

▶ Example: Export to CSV with COPY

▶ Example: Import CSV with COPY

(2) COPY Format Options

▶ Example: Binary Format Export/Import

6. Concept: WAL and PITR

(1) WAL (Write-Ahead Log) Principle

(2) PITR Configuration Steps

▶ Example: Configure WAL Archiving

▶ Example: pg_basebackup Physical Base Backup

▶ Example: PITR Point-in-Time Recovery

(3) Logical Backup vs Physical Backup vs PITR

7. Concept: Replication and High Availability

(1) Streaming Replication vs Logical Replication

▶ Example: Configure Streaming Replication

▶ Example: Configure Logical Replication

(2) Replication Monitoring Views

▶ Example: Monitor Streaming Replication Lag

8. Concept: Backup Strategy

(1) Full + Incremental Comparison

▶ Example: Automated Backup Script

(2) Recommended Production Strategy

▶ Example: Verify Backup Integrity

9. Flowchart: Backup/Restore Decision

10. Comprehensive Example

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好