Testing PostgreSQL Backup and Restore with Core Panel

A backup job can finish successfully while producing a recovery point that is incomplete, inaccessible, too slow to restore, or unusable by the application. A restore drill turns that uncertainty into evidence. This runbook focuses on testing recovery for PostgreSQL databases hosted as part of the Core Panel PostgreSQL workflow. Perform the drill in an isolated environment and adapt every command to your versions, roles, extensions, and data-handling rules.

What a restore test should prove

The drill should answer five questions: Can operators retrieve the intended recovery point? Is the file intact? Can the required PostgreSQL environment be recreated? Does the database restore without unacceptable errors? Can the application use the restored data within the RTO?

Define pass and fail criteria before starting. A command that returns exit code zero is necessary but not sufficient; business-level checks and measured recovery time belong in the acceptance criteria.

Scope the drill and assign roles

Choose a database and recovery scenario that represents meaningful risk: accidental deletion, a failed release, loss of the database host, or recovery to a specific point in time. Name a drill leader, database operator, application validator, and observer. Record the planned start, the RPO and RTO, and the communication path.

• Use production-sized data when policy permits, or a realistic sanitized dataset.
• Confirm where the drill may run and who may access the restored data.
• Disable outbound integrations so test jobs cannot contact customers or production services.
• Prepare a cleanup owner and retention time for the test environment.

Select the recovery point from the real destination

Retrieve the archive from the same off-server destination and through the same credentials or emergency process that would be used in an incident. Do not test only the convenient local copy. Record the object name, timestamp, size, backup job, PostgreSQL tool version, encryption method, and checksum.

If a key, access token, or second operator is required, verify that the documented process works without exposing secrets in the drill record.

Prepare an isolated target

Create a non-public target with enough CPU, memory, disk, and temporary space for the restore. Match the PostgreSQL major version and required extensions unless the drill explicitly tests an upgrade. Restrict network access and make it impossible for the production application to connect accidentally.

psql --version
pg_restore --version
psql -d postgres -c "SHOW server_version;"
df -h

Record package versions and configuration differences. If the target cannot reproduce required extensions, locale, encoding, or tablespaces, treat that as a recovery gap rather than working around it silently.

Download, decrypt, and verify the archive

Start the recovery clock at the point defined by your incident plan—often when the operator begins retrieval. Capture download and decryption duration separately so the team can see where the RTO is spent.

sha256sum -c app_database-2026-09-01.dump.sha256
pg_restore --list app_database-2026-09-01.dump > archive.list
test -s archive.list

Stop if the checksum fails, the archive cannot be listed, the file is unexpectedly small, or the recovery point is older than allowed by the RPO. Preserve logs and investigate; do not turn a failed test into a pass by selecting an easier file without recording the failure.

Review roles and global objects

Logical database dumps do not automatically contain every cluster-level object. Review any separately captured roles and tablespaces before restoration. Never import privileged role definitions blindly into a shared or public server.

For a routine application test, it may be safer to create explicit test roles and restore with ownership and ACLs disabled. For a full disaster-recovery drill, validate the controlled procedure for required roles, memberships, and ownership.

createuser --no-createdb --no-createrole --no-superuser app_restore_user
createdb --owner=app_restore_user app_database_restore_test

Restore with errors made visible

Use options appropriate to the archive and target. The following example restores a custom-format archive into an empty database while avoiding source ownership and ACLs.

time pg_restore   --exit-on-error   --no-owner   --no-acl   --dbname=app_database_restore_test   app_database-2026-09-01.dump   2>&1 | tee pg_restore.log

For a large archive, test parallel restore only after confirming the target has enough CPU, memory, I/O, and connection capacity. More jobs can increase contention and make recovery slower. Capture wall-clock time, resource use, warnings, and the first error.

Run database-level integrity checks

Build a small, version-controlled validation pack for each important database. It should check facts that must be true after recovery, not merely that tables exist.

SELECT count(*) FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

SELECT extname, extversion FROM pg_extension ORDER BY extname;

SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

• Compare expected schemas, tables, views, functions, extensions, and migrations.
• Check critical row counts or bounded invariants rather than publishing sensitive values.
• Verify primary keys, sequences, required indexes, and representative permissions.
• Review restore warnings and database logs even when the command succeeded.
• Run ANALYZE when appropriate before judging application performance.

Run an isolated application smoke test

Point a non-public copy of the application at the restored database using a scoped test identity. Disable email, payment, webhook, queue, analytics, and other production integrations. Confirm startup migrations do not mutate the evidence before validation is complete.

1. Authenticate with a test account or approved synthetic path.
2. Load critical read journeys and compare expected results.
3. Perform a controlled write and verify the transaction commits.
4. Run one background job with outbound effects disabled.
5. Check application and database logs for permission, encoding, and query errors.
6. Confirm the restored data represents the expected recovery point.

Measure RPO and RTO honestly

RPO is evaluated against the timestamp of the newest recoverable data, not the backup job’s scheduled start. RTO includes retrieval, decryption, environment preparation, restore, validation, and the steps required to return the application to service. Report both the total and each phase.

If the drill misses an objective, identify whether the constraint is backup frequency, transfer speed, archive format, environment provisioning, restore throughput, missing automation, or validation time. Assign corrective work and schedule a repeat test.

Exercise failure branches

A mature drill occasionally tests less convenient cases: a missing newest archive, failed checksum, unavailable key custodian, incompatible target version, insufficient disk, or a restore that exposes missing extensions. The goal is not to surprise operators; announce the scenario and verify that the runbook produces a safe stop and escalation.

Record evidence without leaking data

Keep the recovery point identifier, checksum result, versions, commands, timestamps, restore duration, validation results, errors, decisions, owners, and follow-up dates. Do not paste passwords, private data, tokens, or encryption keys into the report. Have a second operator review the result for critical systems.

Clean up safely

After acceptance and required evidence capture, disconnect the test application, revoke temporary credentials, remove the isolated database and decrypted artifacts according to policy, and confirm that outbound integrations remained disabled. Deletion is a separate controlled step; identify exact targets and obtain the required approval in your organization.

Set a repeat cadence

Run drills often enough to catch staff, version, size, credential, and architecture changes. A common starting point is quarterly for important systems and after major database upgrades or backup-design changes, but risk and obligations should determine the cadence.

Use the automated PostgreSQL backup guide to improve how recovery points are created and protected. Add the result to the production PostgreSQL readiness review, and check connection capacity and monitoring before testing a large parallel restore.

Where Core Panel fits

Core Panel can provide the hosting-side database and user workflow, scheduled tasks, and operational context. The database owner still chooses the backup architecture, recovery objectives, restore method, validation pack, and acceptance criteria. Review the complete PostgreSQL hosting workflow in Core Panel and test the procedure on a non-production server before relying on it in an incident.