Skip to main content

🐘 PostgreSQL Physical Restore with pg_basebackup

pg_basebackup is used to perform a complete physical restore of PostgreSQL, recommended for node rebuilds, replication, critical failure recovery, and controlled environment rebuilds.

📌 When to use this procedure​

  • Data directory corruption (PGDATA)
  • Node lost synchronization with primary
  • PostgreSQL replica recreation
  • Recovery after severe failure
  • Complete database rebuild

⚠️ Warning: this procedure completely removes the local data on the target node.

⚙️ Prerequisites​

Before starting, ensure that:

  • PostgreSQL is installed and compatible
  • Replication user is configured (e.g., replication)
  • Connectivity to the primary node exists
  • PostgreSQL service is stopped
  • There is sufficient disk space

🔍 Identify the data directory (PGDATA)​

  1. Access the PostgreSQL server.
  2. Run the command below to identify the data directory used by the database.

Checking data_directory

sudo -u postgres psql -c "SHOW data_directory;"

Example output:

/var/lib/pgsql/data

2. Stop the PostgreSQL service​

Before the physical restore, PostgreSQL must be completely stopped.

sudo systemctl stop postgresql

Confirm that no active processes remain:

ps aux | grep '[p]ostgres'

3. Clear the data directory​

⚠️ This step is irreversible.
Removes all current database content.

sudo  rm -rf /var/lib/pgsql/data/*`

4. Run the restore with pg_basebackup​

Execute the physical restore directly from the primary node.

PGPASSWORD='SENHA_DO_USUARIO_REPLICATION' \
pg_basebackup \
  -h 10.100.0.00 \
  -p 5432 \
  -U replication \
  -D /var/lib/pgsql/data \
  -X stream \
  -P

Parameters used​

  • -h → Primary node IP address

  • -p → PostgreSQL port

  • -U → Replication user

  • -D → Data directory (PGDATA)

  • -X stream → Continuous WAL streaming

  • -P → Shows operation progress

5. Adjust directory permissions​

After completing pg_basebackup, adjust the permissions of the restored directory.

chown -R postgres:postgres /var/lib/pgsql/data

6. Start PostgreSQL​

Restart the PostgreSQL service.

sudo systemctl start postgresql

Or, if applicable:

sudo systemctl start postgresql-17

✅ Post-Restore Validations​

Check service status​

systemctl status postgresql

Confirm database access​

sudo -u postgres psql

🔁 Environments with Replication​

In environments with configured replication:

  • The restored node becomes a replica

  • Synchronization occurs automatically after startup

  • Compatible with:

    • repmgr

    • patroni

    • PostgreSQL native replication

📌 Always validate the replica status after the restore.

🧠 Best Practices​

  • Use a dedicated user for replication

  • Perform the procedure during a maintenance window

  • Monitor disk space during the restore

  • Periodically test the rebuild process

  • Monitor logs and metrics after the restore

🛠️ Troubleshooting​

Connection failure in pg_basebackup​

  • Check rules on pg_hba.conf of the primary node

  • Confirm replication user permissions

PostgreSQL does not start after restore​

  • Check directory permissions

  • Analyze PostgreSQL logs

  • Confirm version compatibility

Replication does not synchronize​

  • Check primary_conninfo

  • Confirm availability of WALs on the primary

📎 Final Notes​

  • This procedure is not reversible

  • Always validate the primary node before execution

  • Document the process for auditing and Disaster Recovery