Seven traps when backing up and moving a database

The original project account describes rebuilding a production database from a backup and encountering seven problems that had not appeared in local tests. The useful part is the sequence of checks those failures suggest.

1. PostgreSQL versions differ

The draft places the development server on PostgreSQL major version 18 and the destination on major version 16. No SELECT version() output is available here to confirm the patch versions originally claimed.

A dump produced by newer tooling can include settings an older destination does not understand, for example:

SET transaction_timeout = 0;

PostgreSQL 16 does not recognize that setting. With ON_ERROR_STOP=1, restoring stops at the error. Removing one line does not establish that the rest of a newer dump is compatible with an older server.

Record the source server, destination server, dump tool and restore tool versions. pg_dump cannot read a server newer than its own major version, and restoring into an older major version is not guaranteed. Prefer a destination on the same or a newer major, or design and test a compatible export/import path.

Before touching the real destination, restore into a scratch database there:

createdb db_scratch && psql -d db_scratch -v ON_ERROR_STOP=1 -f ddl.sql -f data.sql

2. Secrets live in unexpected columns

The first scan in the account examined an ordinary settings field and found nothing. A later scan found a reCAPTCHA secret in value_encrypted.

The project draft identifies four binary fields holding sensitive material:

ai_providers.api_key              encrypted provider API key
push_keys.private_key            push private key
system_settings.value_encrypted  reCAPTCHA secret
two_factor_secrets.secret        per-user 2FA secret

A dump intended for source control must not carry live secrets. Removing a file later does not remove its earlier Git history.

Inspect the schema rather than only searching familiar column names:

SELECT table_name||'.'||column_name FROM information_schema.columns
WHERE data_type = 'bytea' AND table_schema = 'public';

This query covers binary columns in public, not every possible secret. Text, JSON, other schemas and external configuration also need review.

3. Data loading and foreign keys need a plan

A data-only restore into tables with existing foreign keys can fail if rows arrive before their referenced parents. A partial restore can leave a database in an incomplete state.

For a fresh destination, use a suitable full archive and restore schema, data, then post-data objects such as indexes and constraints. Custom format alone does not solve every data-only restore into an already constrained schema.

Consider --disable-triggers only in a controlled procedure with sufficient privileges and subsequent integrity checks. It is not a default way to bypass data correctness.

4. “Restore completed” is not the same as “correct”

A dump can restore without errors yet still mismatch the application release. The project used its migration command as one check:

go run ./cmd/api migrate
→ "no migrations to run. current version: 51"

Compare migration state with the deployment plan. Pending migrations may be normal during an upgrade. No pending migrations does not prove the data is correct either: check record counts, constraints and important application flows.

Also avoid reading the wrong exit status from a pipeline:

psql ... | tail -2 ; echo $?

In Bash, that reports tail's status, which can be zero even when psql failed. Use set -o pipefail, read ${PIPESTATUS[0]} immediately after the pipeline, or remove the pipeline. These are Bash conventions, not interchangeable PowerShell syntax.

5. Distinguish a recovery backup from a seed dataset

A full database includes sessions, logs, conversations and customer information. Those may not belong in a seed dataset for a new environment.

But do not mechanically remove operational data from a disaster-recovery backup: some of it may be necessary for recovery or investigation.

Choose the purpose before choosing what to exclude. Protect any personal data that must be retained.

6. Database records do not include files on disk

A row in a files table can point to an image without containing the actual image. Copy filesystem assets separately and check that they match the records.

The draft describes an upload failure after directory creation:

install -d -o zukatek -g zukatek /var/lib/.../members/<id>/

Specifying ownership here is not a reason to assume all parent directories have the required permissions. Test as the application account:

su -s /bin/sh zukatek -c 'mkdir -p /path/to/_probe && echo ok'

Use a designated probe location. Verify traversal, reading and actual writing rather than inferring access from an ownership listing.

7. Cleanup can affect more than the named tables

The draft describes this test cleanup:

TRUNCATE ai_messages, ai_conversations CASCADE;

It reports that the cascade also affected leads and lead_products, which were fortunately empty at the time.

A filtered delete is narrower, for example:

DELETE FROM leads WHERE email LIKE '%@test.local';

But even a condition can match the wrong records, and DELETE can invoke cascades or triggers. Prefer explicit test IDs or batch markers, preview the scope with SELECT, use a transaction, and inspect affected rows before commit.

A compact procedure

1. Record server and tool versions.

2. Identify sensitive fields and decide what the export should contain.

3. Choose recovery backup or seed, with an explicit exclusion policy.

4. Export and review for secrets.

5. Back up the destination.

6. Rehearse the restore in a scratch database at the destination.

7. Check migrations, data and important application flows against the release.

8. Perform the planned production restore.

9. Copy filesystem assets and test with the application account.

10. Clean test records by verified identifiers, not blanket truncation.

The destination backup and rehearsal are easy to skip and especially valuable when something unexpected happens.

Incidents are retained from the October 2026 Zukatek draft; original logs are not available here for independent verification.

References: PostgreSQL pg_dump, PostgreSQL pg_restore.