Test a Coolify PostgreSQL backup before you need it

Your Coolify database backup has a green execution, a recent timestamp, and a file in S3-compatible storage. That proves a backup job produced and copied an object. It does not prove that the archive can rebuild a usable database before your recovery deadline.

Coolify’s current backup documentation makes the same distinction: a successful backup only proves that a file was created. Its recommended proof is to restore a copy into a disposable database and verify that the application can read it. This guide turns that advice into a repeatable PostgreSQL restore drill without touching production.

The safe test target is a separate, disposable database with no production traffic. Never practise by importing over the live database.

What a useful restore test must prove

A file’s existence, size, and checksum are useful integrity evidence, but recoverability crosses more boundaries. A useful drill should establish that:

• the intended backup completed, rather than being skipped while the database was stopped;
• the archive can be retrieved from its actual recovery location;
• the PostgreSQL tools can read its format and version;
• schema, data, sequences, constraints, indexes, extensions, and ownership restore as expected;
• the application can connect privately and perform representative reads;
• the measured recovery time fits the service’s recovery objective;
• the disposable target and copied data are removed safely afterward.

1. Choose one specific recovery point

Open the Coolify database resource, then its backup schedule and execution history. Select one completed execution and record its timestamp, database name, source server, storage location, file size, and retention class. Use neutral internal notes; do not copy credentials, signed URLs, access keys, or real infrastructure addresses into the drill record.

Coolify runs a scheduled backup only while the database is running. A missing execution is different from a failed execution, and both are different from a successful archive. Check the exact run rather than inferring success from the schedule being enabled.

If off-server recovery matters, test the S3-compatible copy—not only the local file beside the database. Local and S3 retention are separate in Coolify, so confirm both policies are long enough for the recovery points you promise.

2. Build an isolated restore target

Create a disposable PostgreSQL resource through Coolify on an isolated environment or authorised test server. Match the production major version where possible. A newer pg_restore can often read an older archive, but crossing server versions can expose extension, collation, role, or application compatibility problems; test the combination you would actually use in an incident.

The target should have:

• no public database port;
• no production hostname, webhook, worker, scheduler, or outbound mail integration;
• fresh credentials stored in Coolify rather than pasted into commands or tickets;
• enough free space for the archive, restored database, indexes, and temporary work;
• a clear expiry time and owner for deletion.

If the application must be used for verification, point a separate test instance at the restored database. Disable side effects such as email, payments, webhooks, queue consumers, and scheduled jobs. A copied production database can contain personal or commercially sensitive data, so apply the same access controls and deletion discipline as production.

3. Inspect the PostgreSQL archive before importing

Coolify documents that its scheduled PostgreSQL backup workflow uses pg_dump custom format for selected databases. PostgreSQL’s own documentation says non-plain-text archives are restored with pg_restore. Listing the archive is a safe early check that catches truncation, wrong formats, and incompatible client tools before the destructive stage:

pg_restore --list /restore/sample-postgresql.dump \
      > /tmp/sample-postgresql.contents

wc -l /tmp/sample-postgresql.contents
sed -n '1,20p' /tmp/sample-postgresql.contents

Run this only in the authorised restore environment. Use a placeholder or non-sensitive local path in shared documentation. The list should contain recognisable schema objects and table data, but listing is not itself a restore test.

If pg_restore reports an unsupported archive version, inspect the versions explicitly:

pg_restore --version
postgres --version

Do not “fix” the archive by renaming its extension or feeding a custom-format dump to psql. Use a compatible PostgreSQL client image or target, then keep that version requirement in the runbook.

4. Restore through Coolify’s Import Backup workflow

On the disposable target, open Configuration → Import Backup. Coolify supports PostgreSQL imports into a running database and uses pg_restore for its default selected-database workflow. Choose the tested server file, upload, or configured S3-compatible source, review the generated import command, confirm the target again, and follow the complete restore output.

Do not copy a command from another engine, select “all databases” for a single-database archive, or add broad flags merely to suppress errors. In particular, ownership and role failures need an explicit decision: create the required non-login roles in the isolated target when the application depends on them, or use a deliberately reviewed no-owner restore for a single application database. Record any deviation from the default because it is part of the recovery procedure.

A restore can emit warnings and still leave a partly populated database. Save the terminal outcome and inspect the first error, not just the last line or the existence of a few tables.

5. Verify structure and data independently

Connect through a private, authorised route and compare non-sensitive invariants with production or a pre-recorded manifest. Good checks include:

• expected schemas, tables, views, functions, and extensions exist;
• critical tables have plausible row counts for the recovery timestamp;
• primary keys, foreign keys, unique constraints, and important indexes exist;
• sequences are ahead of the maximum existing identifier;
• the newest business record is close to the backup’s recovery point;
• database encoding, collation, timezone assumptions, and required roles are valid.

SELECT current_database(), current_setting('server_version');

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

SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

Avoid exporting real rows into logs. Prefer counts, timestamps, booleans, and expected object names. Where row counts are sensitive, compare them inside the database session and retain only pass/fail evidence.

6. Verify through the application contract

Database inspection proves only the database layer. Start the isolated application against the restored target with side effects disabled, then exercise representative read paths:

1. load a known account or content record from before the recovery point;
2. open a page that depends on a join, index, extension, or materialised view;
3. confirm migrations report the expected state without applying new production changes;
4. check application logs for permission, collation, missing-relation, or decoding errors;
5. perform a disposable write only if the test environment is designed for it, then verify and remove it.

Do not let this test application send mail, charge a payment method, invoke production webhooks, or consume production queues. Recovery confidence should not create a second incident.

7. Measure the recovery, then clean up

Record the retrieval time, restore time, verification time, archive size, restored size, PostgreSQL versions, failed steps, and manual decisions. These measurements turn a vague recovery-time objective into evidence. A two-minute toy restore says little about a production-sized archive whose indexes take an hour to rebuild.

After sign-off, stop the test application, remove the disposable database through the normal Coolify resource workflow, and verify that temporary local copies are gone. Retain the runbook and non-sensitive evidence, not an unmanaged extra copy of production data.

Common reasons a green backup still fails the drill

• Wrong source: the schedule targeted a different database or an empty default database.
• Service variable mismatch: the running database container does not expose the resolved POSTGRES_PASSWORD, POSTGRES_USER, or POSTGRES_DB values Coolify’s service backup expects.
• Local-only recovery: the local archive works, but S3 credentials, endpoint, bucket policy, retention, or object availability fail during retrieval.
• Tool mismatch: the available pg_restore cannot read the archive version.
• Missing roles or extensions: objects restore incompletely or the application cannot use them.
• Insufficient space: the compressed backup fits, but restored tables, indexes, write-ahead logs, and temporary files do not.
• No application proof: tables exist, but permissions, sequences, or schema assumptions break real queries.

A compact quarterly restore-drill checklist

1. Select one exact local or S3 recovery point and confirm its execution.
2. Create a private, disposable PostgreSQL target with enough space.
3. Retrieve the archive through the path needed during a real outage.
4. Run pg_restore --list and confirm client/server compatibility.
5. Import through Coolify and read the complete result.
6. Check schema objects, counts, constraints, sequences, extensions, and recency.
7. Run isolated application reads with every external side effect disabled.
8. Measure retrieval, restore, and verification times.
9. Update the runbook with every required role, flag, and version.
10. Delete the disposable resources and temporary copies.

Once this drill is repeatable, schedule it often enough to catch version, schema, size, credential, and storage changes. A backup policy describes intent. A clean restore into isolation provides recovery evidence.

Authoritative references: Coolify’s official guides to database backups, restoring a database, and PostgreSQL resources; and PostgreSQL’s documentation for pg_dump and pg_restore.