{"id":498279,"date":"2026-10-12T13:02:35","date_gmt":"2026-10-12T10:02:35","guid":{"rendered":"https:\/\/savepearlharbor.com\/?p=498279"},"modified":"2026-10-12T13:02:35","modified_gmt":"2026-10-12T10:02:35","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=498279","title":{"rendered":"I Discovered Our PostgreSQL Backups Were Useless Only After Testing a Full Restore"},"content":{"rendered":"
\n

The backup job was green. Disk usage looked normal, the backup directory kept growing, and nothing in the monitoring dashboard suggested a problem. For several months, that was enough to assume the database was protected. Then I tried restoring PostgreSQL on a separate machine. The first attempt failed before the database was even ready to accept connections. The next attempt got a little further but exposed another problem. What looked like a perfectly ordinary backup system turned out to be a collection of files that nobody had actually tested. The annoying part was that the backup job itself had never reported an error.<\/p>\n

The Backup Job That Everyone Trusted<\/h3>\n

The setup was fairly ordinary: a Linux server running PostgreSQL 16, a database used by an internal application, and a scheduled backup process. Nothing particularly exciting. The database wasn’t enormous, and the backup directory was large enough to keep several copies without constantly running out of space. The original job was a small Bash script written long before I started looking at it. It stopped neither PostgreSQL nor the application. Instead, it copied files directly from the database directory and sent the resulting archive to another storage location. Cron executed the script every night, and monitoring checked its exit status. At first glance, everything looked fine. The archives existed, timestamps were updated, and the amount of stored data roughly matched expectations. There was even a cleanup job removing older archives. The problem was how the data was collected.<\/p>\n

PostgreSQL isn’t an ordinary directory of independent files. While the server is running, it can modify data pages, write WAL records, perform checkpoints, and update internal metadata. A simple file copy may capture different files at different moments. Without a proper backup mechanism, those files may not form a consistent recoverable database. WAL, or Write\u2011Ahead Logging, is especially important here. PostgreSQL uses WAL records to describe changes that must be replayed during recovery. A physical backup needs the required WAL history to reach a consistent state. Our old process didn’t manage that correctly. The script copied what it could, compressed the files, and returned success. The operating system had successfully copied bytes. That was all the monitoring actually knew. It had no idea whether PostgreSQL could recover from those bytes. Ever seen a backup dashboard showing 100% success while nobody can remember the last successful restore? That was essentially our situation.<\/p>\n

The First Restore Was Worse Than Expected<\/h3>\n

The test started with a clean Linux virtual machine and the same PostgreSQL major version. I deliberately avoided restoring anything on the production server. Apart from being risky, restoring to the original environment would hide some of the dependencies the backup was supposed to protect us from. I copied one of the recent archives, extracted it into a temporary PostgreSQL data directory, corrected the permissions, and tried to start the database. PostgreSQL refused to start cleanly.<\/p>\n

The log pointed toward recovery problems and missing WAL information. Some of the data files were present, but the WAL records needed to make the copied cluster consistent weren’t available in the backup. The mistake wasn’t a bad compression algorithm or a damaged network transfer. The backup process simply hadn’t been designed around PostgreSQL’s consistency requirements. That discovery changed the question. Instead of checking whether the archive was readable, I needed to know whether an entirely separate PostgreSQL instance could start from it and serve application data. It also exposed several smaller issues. The restore machine needed the correct PostgreSQL binaries. The service account needed access to the data directory. Configuration files referred to paths that existed only on the original server. None of those details had been recorded in the backup monitoring.<\/p>\n

A database can have valid data files and still be practically unrecoverable within the time a business can tolerate. For example, imagine a backup that takes 12\u00a0minutes to create but requires four hours of manual configuration work before the application can connect. The backup duration looks excellent, but the actual recovery process tells a very different story.<\/p>\n

Replacing the Old Script With a PostgreSQL\u2011Aware Backup<\/h3>\n

The first change was replacing the raw file\u2011copy process with pg_basebackup<\/code>. Unlike copying the active data directory with a generic file tool, pg_basebackup<\/code> works through PostgreSQL’s replication protocol and creates a physical base backup using the server’s supported backup mechanism. For this example, the backup is created in plain format, with the required WAL streamed alongside the data. A backup manifest is generated so the files can be checked afterward. The following script assumes PostgreSQL 16, an available replication user, correctly configured pg_hba.conf<\/code> permissions, and a destination on a filesystem with sufficient free space. Authentication should be configured securely, for example through a protected .pgpass<\/code> file or an appropriate credential service.<\/p>\n

#!\/usr\/bin\/env bashset -Eeuo pipefailumask 077# PostgreSQL source configurationPG_HOST=\"${PG_HOST:-db.internal}\"PG_PORT=\"${PG_PORT:-5432}\"PG_REPLICATION_USER=\"${PG_REPLICATION_USER:-backup_user}\"# Backup storageBACKUP_ROOT=\"${BACKUP_ROOT:-\/srv\/postgresql\/backups}\"BACKUP_DATE=\"$(date -u +%Y%m%dT%H%M%SZ)\"BACKUP_NAME=\"base_${BACKUP_DATE}\"# Tools must match the PostgreSQL major versionPG_BASEBACKUP=\"\/usr\/lib\/postgresql\/16\/bin\/pg_basebackup\"PG_VERIFYBACKUP=\"\/usr\/lib\/postgresql\/16\/bin\/pg_verifybackup\"log() {    printf '%s %s\\n' \\        \"$(date -u +%Y-%m-%dT%H:%M:%SZ)\" \\        \"$*\" >&2}fail() {    log \"ERROR: $*\"    exit 1}for tool in \"$PG_BASEBACKUP\" \"$PG_VERIFYBACKUP\"; do    [[ -x \"$tool\" ]] || fail \"Missing executable: $tool\"donemkdir -p \"$BACKUP_ROOT\"# Temporary directory prevents incomplete backups# from appearing in the completed backup list.STAGING=\"$(mktemp -d \"${BACKUP_ROOT}\/.incoming.XXXXXXXX\")\"FINAL=\"${BACKUP_ROOT}\/${BACKUP_NAME}\"cleanup() {    if [[ -n \"${STAGING:-}\" && -d \"$STAGING\" ]]; then        rm -rf -- \"$STAGING\"    fi}trap cleanup EXIT[[ ! -e \"$FINAL\" ]] || fail \"Backup already exists: $FINAL\"log \"Starting PostgreSQL base backup\"\"$PG_BASEBACKUP\" \\    --host=\"$PG_HOST\" \\    --port=\"$PG_PORT\" \\    --username=\"$PG_REPLICATION_USER\" \\    --pgdata=\"$STAGING\" \\    --format=plain \\    --wal-method=stream \\    --checkpoint=spread \\    --manifest-checksums=SHA256 \\    --progress \\    --verboselog \"Backup finished. Checking manifest.\"[[ -s \"$STAGING\/backup_manifest\" ]] || \\    fail \"Backup manifest missing or empty\"\"$PG_VERIFYBACKUP\" \\    --progress \\    \"$STAGING\" || fail \"Backup verification failed\"log \"Manifest verification succeeded\"# Make the validated backup visible to# downstream jobs only after verification.mv -- \"$STAGING\" \"$FINAL\"STAGING=\"\"log \"Backup published: $FINAL\"# Store backup metadata for monitoring.{    printf 'backup_name=%s\\n' \"$BACKUP_NAME\"    printf 'completed_at=%s\\n' \\        \"$(date -u +%Y-%m-%dT%H:%M:%SZ)\"    printf 'postgresql_major=16\\n'    printf 'verification=passed\\n'} > \"${FINAL}.status\"log \"Backup workflow completed successfully\"<\/code>
<\/span><\/a><\/div><\/pre>\n
This version fixes several problems in the old process. First, PostgreSQL controls the consistency of the backup. The script no longer assumes that copying an active data directory produces a valid recovery image. Streaming WAL also avoids relying on whatever WAL files happened to exist when a generic archive command was executed. Second, the backup stays in a temporary directory until verification succeeds. That matters because a half\u2011finished backup should never appear alongside completed backups where another job might select it for restoration. Third, pg_verifybackup<\/code> checks the backup against its manifest. It can detect missing files, unexpected changes, checksum mismatches, and certain WAL\u2011related problems. There is an important limitation, though.<\/p>\n
A valid manifest doesn’t prove that the recovered database will behave correctly when an application starts sending queries. The verification process doesn’t know which tables matter to the business, whether a particular extension is installed, or whether an application needs external files stored outside PostgreSQL. The script also has deliberately narrow assumptions. It covers a standard physical backup without separately located user\u2011defined tablespaces. Such tablespaces require additional handling and corresponding restore mapping. The backup destination also needs independent protection, access controls, and retention management. A directory on the same machine is not sufficient disaster recovery storage. So, after fixing the backup job, the next step was to stop trusting verification alone.<\/p>\n
Restoring the Database Without Touching Production<\/h3>\n
A proper test needed to answer a simple question: could we take the latest completed backup, place it on another machine, start PostgreSQL, and read actual application data? The test environment used a dedicated Linux VM with PostgreSQL 16\u00a0installed. The VM was isolated from production application traffic. Its database service wasn’t allowed to connect to production clients, and the restore test used a separate port and Unix socket directory. The backup contained a small application table named public.restore_probe<\/code>. Before running the test, that table had been created in the source database and populated with a known marker. The marker wasn’t intended as a substitute for checking real application tables. It was simply a quick way to prove that a specific piece of database content survived the backup and restore process. A good recovery test should also query important production\u2011shaped tables, inspect extensions, and exercise representative application operations. For this script, I kept the automatic assertion small enough to work in a controlled test environment.The script below must run as the dedicated PostgreSQL operating\u2011system user on an isolated restore host. It assumes the backup has already been transferred there, contains no external tablespaces, and has no recovery configuration intended for a standby server.<\/p>\n
#!\/usr\/bin\/env bashset -Eeuo pipefailumask 077# Execute as the PostgreSQL operating-system user# on a dedicated isolated recovery test machine.BACKUP_PATH=\"${BACKUP_PATH:-\/srv\/postgresql\/backups\/latest}\"RESTORE_ROOT=\"${RESTORE_ROOT:-\/var\/tmp\/pg-restore-tests}\"PG_BIN=\"\/usr\/lib\/postgresql\/16\/bin\"PG_CTL=\"${PG_BIN}\/pg_ctl\"PG_VERIFYBACKUP=\"${PG_BIN}\/pg_verifybackup\"PSQL=\"${PG_BIN}\/psql\"TEST_PORT=\"${TEST_PORT:-55432}\"TEST_DATABASE=\"${TEST_DATABASE:-appdb}\"TEST_ROLE=\"${TEST_ROLE:-postgres}\"EXPECTED_MARKER=\"${EXPECTED_MARKER:-restore-test-v1}\"log() {    printf '%s %s\\n' \\        \"$(date -u +%Y-%m-%dT%H:%M:%SZ)\" \\        \"$*\" >&2}fail() {    log \"ERROR: $*\"    exit 1}# Never run this test as root.[[ \"$(id -u)\" -ne 0 ]] || \\    fail \"Run as the PostgreSQL service user, not root\"[[ -d \"$BACKUP_PATH\" ]] || \\    fail \"Backup directory not found\"[[ -f \"$BACKUP_PATH\/backup_manifest\" ]] || \\    fail \"Missing backup manifest\"mkdir -p \"$RESTORE_ROOT\"WORK_DIR=\"$(mktemp -d \"${RESTORE_ROOT}\/test.XXXXXXXX\")\"DATA_DIR=\"${WORK_DIR}\/data\"SOCKET_DIR=\"${WORK_DIR}\/socket\"LOG_FILE=\"${WORK_DIR}\/postgres.log\"mkdir -m 700 \"$DATA_DIR\" \"$SOCKET_DIR\"STARTED=0TEST_PASSED=0cleanup() {    if [[ \"$STARTED\" -eq 1 ]]; then        log \"Stopping test PostgreSQL instance\"        \"$PG_CTL\" \\            -D \"$DATA_DIR\" \\            -m immediate \\            -w stop || true    fi    if [[ \"$TEST_PASSED\" -eq 1 ]]; then        rm -rf -- \"$WORK_DIR\"    else        log \"Restore files preserved: $WORK_DIR\"        log \"Inspect logs and remove files after debugging\"    fi}trap cleanup EXITlog \"Checking backup integrity\"\"$PG_VERIFYBACKUP\" \"$BACKUP_PATH\" || \\    fail \"Backup failed verification\"log \"Copying backup into isolated data directory\"cp -a -- \"$BACKUP_PATH\"\/. \"$DATA_DIR\"\/chmod 700 \"$DATA_DIR\"# This example is for independent primary recovery,# not for a replicated standby configuration.[[ ! -e \"$DATA_DIR\/standby.signal\" ]] || \\    fail \"Unexpected standby.signal detected\"[[ ! -e \"$DATA_DIR\/recovery.signal\" ]] || \\    fail \"Unexpected recovery.signal detected\"log \"Checking PostgreSQL control data\"\"${PG_BIN}\/pg_controldata\" \"$DATA_DIR\" \\    > \"${WORK_DIR}\/control-info.txt\"log \"Starting restored PostgreSQL\"# Never publish this test instance externally.# Unix socket access is restricted to the test directory.\"$PG_CTL\" \\    -D \"$DATA_DIR\" \\    -l \"$LOG_FILE\" \\    -o \"-p ${TEST_PORT} \\        -c listen_addresses= \\        -c unix_socket_directories=${SOCKET_DIR}\" \\    -w start || fail \"PostgreSQL could not start\"STARTED=1log \"Checking database connectivity\"\"$PSQL\" \\    -X \\    -v ON_ERROR_STOP=1 \\    -h \"$SOCKET_DIR\" \\    -p \"$TEST_PORT\" \\    -U \"$TEST_ROLE\" \\    -d \"$TEST_DATABASE\" \\    -c \"SELECT current_database(), pg_is_in_recovery();\"log \"Checking application restore marker\"RESULT=\"$(    \"$PSQL\" \\        -X \\        -v ON_ERROR_STOP=1 \\        -h \"$SOCKET_DIR\" \\        -p \"$TEST_PORT\" \\        -U \"$TEST_ROLE\" \\        -d \"$TEST_DATABASE\" \\        -A -t \\        -v expected_marker=\"$EXPECTED_MARKER\" \\        -c \"            SELECT COUNT(*)            FROM public.restore_probe            WHERE marker = :'expected_marker';        \")\"[[ \"$RESULT\" == \"1\" ]] || \\    fail \"Restore marker validation failed: $RESULT\"log \"Marker validation passed\"log \"Checking installed extensions\"\"$PSQL\" \\    -X \\    -v ON_ERROR_STOP=1 \\    -h \"$SOCKET_DIR\" \\    -p \"$TEST_PORT\" \\    -U \"$TEST_ROLE\" \\    -d \"$TEST_DATABASE\" \\    -c \"        SELECT extname, extversion        FROM pg_extension        ORDER BY extname;    \"log \"Running basic database consistency checks\"\"$PSQL\" \\    -X \\    -v ON_ERROR_STOP=1 \\    -h \"$SOCKET_DIR\" \\    -p \"$TEST_PORT\" \\    -U \"$TEST_ROLE\" \\    -d \"$TEST_DATABASE\" \\    -c \"        SELECT            current_database() AS database_name,            COUNT(*) AS user_table_count        FROM pg_class        WHERE relkind IN ('r', 'p')          AND relnamespace NOT IN (              'pg_catalog'::regnamespace,              'information_schema'::regnamespace          );    \"TEST_PASSED=1log \"RESTORE TEST PASSED\"log \"PostgreSQL started and application marker was verified\"<\/code>
<\/span><\/a><\/div><\/pre>\n
The script verifies the backup before copying it, creates an isolated data directory, starts a restored instance, and checks whether an expected application record is present. If a test fails after copying, the temporary restore files remain available for inspection. A few environmental details still matter. The restored pg_hba.conf<\/code> must permit the chosen local database connection, and the test role must exist in the restored cluster. If the original setup uses external configuration files, custom extensions, or operating\u2011system libraries, those dependencies need to exist on the restore machine as well. One detail worth mentioning: the script removes successful test environments but keeps failed ones for debugging. On a dedicated VM this is convenient, although a production\u2011quality test runner should also enforce disk quotas and automatically clean up old failed runs. Starting PostgreSQL is only the beginning of validation. A server can start successfully even when an important application table is missing, an expected record is absent, or an external dependency prevents the application from working. That’s why the marker check is useful, but not enough to certify an entire recovery plan.<\/p>\n
\"Example<\/p>\n
Example of a failed PostgreSQL recovery caused by missing WAL files, followed by a successful restore and SQL verification. Illustrative output. <\/figcaption><\/div>\n<\/figure>\n
What the Backup Report Didn’t Tell Us<\/h3>\n
The interesting thing about this exercise wasn’t just the broken backup method. It was the difference between what the monitoring system measured and what the business actually needed. The original dashboard checked whether the backup script exited successfully. Later, we added file integrity validation. Both checks are useful, but neither directly measures how much work is required to recover a service. Two recovery metrics make this clearer. Recovery Point Objective, or RPO, describes how much data loss is acceptable, measured in time. If backups run once every 24\u00a0hours, restoring the newest daily copy could mean losing nearly a full day of changes. That might be acceptable for a test application, but not for a busy transactional system. Recovery Time Objective, or RTO, describes how quickly a service must be restored after an incident. It includes more than copying files. Provisioning a machine, restoring storage, starting PostgreSQL, checking the database, updating application configuration, and reconnecting clients all consume time.<\/p>\n
For example, a 200\u00a0GB database might take 25\u00a0minutes to copy from backup storage, but that figure means little if the restore environment takes another two hours to prepare. And a technically successful restore is still not necessarily a successful business recovery. What happens if PostgreSQL starts but the application can’t authenticate? What if the restored database is consistent but belongs to the wrong backup date? What if a critical file was stored outside the database and never included in the backup process? Those questions started to seem more useful than another green dashboard indicator. For point\u2011in\u2011time recovery, the requirements become more demanding. A base backup alone is not enough to restore to an arbitrary later timestamp. PostgreSQL needs the required sequence of archived WAL records, and the recovery environment must be able to retrieve them. Testing only the latest standalone base backup does not prove that the WAL archive is complete or that point\u2011in\u2011time recovery will work. That deserves a separate test, using restore_command<\/code>, recovery.signal<\/code>, and a known recovery target.<\/p>\n
The Changes That Actually Mattered<\/h3>\n
After rebuilding the backup process, the most useful change was making restore testing part of normal maintenance rather than something reserved for emergencies. Backup creation, backup verification, and restore testing became three separate checks. The first confirms that a backup operation completed. The second checks whether the stored physical backup matches its manifest and contains the required WAL for base recovery. The third answers the question that really matters: can the database be started in a clean environment, and does it contain usable application data? The test environment also helped reveal dependencies that hadn’t been obvious from the production server. PostgreSQL configuration files, extension packages, authentication rules, filesystem permissions, and storage locations all needed attention. There are still scenarios this approach doesn’t cover. Restoring one PostgreSQL cluster doesn’t prove that an entire application stack can recover. It doesn’t validate external object storage, distributed caches, message queues, or secrets that may be required to bring the service back online.<\/p>\n
And even when an individual test passes, it doesn’t guarantee that every older or future backup will work. But it does replace an assumption with evidence. The next useful improvements would be regularly scheduled restore drills, application\u2011level checks, alerts for failed recovery tests, measured restoration duration, and a separate exercise for point\u2011in\u2011time recovery. A backup should also survive the loss of the original machine, so copies need to be kept outside the source failure domain. That includes thinking about ransomware, accidentally deleted archives, stolen credentials, and backup retention settings that can be changed by the same account used by the application. The recovery process should be documented well enough that someone who didn’t write the scripts can follow it. Because during a real incident, discovering that a backup depends on one person’s memory is just another type of backup failure.<\/p>\n
The One Test I’d Run Again<\/h3>\n
Before this exercise, checking the latest backup usually meant checking the timestamp, file size, and job status. That seemed reasonable because the backups were created regularly and nothing appeared to be wrong. Now the first question is different: when was the last time the database was actually restored on another machine? Not verified with a checksum. Not opened as an archive. Actually restored, started, and queried. There’s a surprisingly large difference between having backup files and having a recovery process. A green backup job is useful. A verified backup manifest is better. But the result that finally changed my confidence was watching a clean PostgreSQL instance start from the backup and return the expected application data. If your backup system has been running quietly for months, it might be worth trying that test before you actually need it. The worst time to learn how a restore works is when the production database is already gone.<\/p>\n<\/div>\n
\u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 https:\/\/habr.com\/ru\/articles\/1093260\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
The backup job was green. Disk usage looked normal, the backup directory kept growing, and nothing in the monitoring dashboard suggested a problem. For several months, that was enough to assume the database was protected. Then I tried restoring PostgreSQL on a separate machine. The first attempt failed before the database was even ready to accept connections. The next attempt got a little further but exposed another problem. What looked like a perfectly ordinary backup system turned out to be a collection of files that nobody had actually tested. The annoying part was that the backup job itself had never reported an error.The Backup Job That Everyone TrustedThe setup was fairly ordinary: a Linux server running PostgreSQL 16, a database used by an internal application, and a scheduled backup process. Nothing particularly exciting. The database wasn’t enormous, and the backup directory was large enough to keep several copies without constantly running out of space. The original job was a small Bash script written long before I started looking at it. It stopped neither PostgreSQL nor the application. Instead, it copied files directly from the database directory and sent the resulting archive to another storage location. Cron executed the script every night, and monitoring checked its exit status. At first glance, everything looked fine. The archives existed, timestamps were updated, and the amount of stored data roughly matched expectations. There was even a cleanup job removing older archives. The problem was how the data was collected.PostgreSQL isn’t an ordinary directory of independent files. While the server is running, it can modify data pages, write WAL records, perform checkpoints, and update internal metadata. A simple file copy may capture different files at different moments. Without a proper backup mechanism, those files may not form a consistent recoverable database. WAL, or Write\u2011Ahead Logging, is especially important here. PostgreSQL uses WAL records to describe changes that must be replayed during recovery. A physical backup needs the required WAL history to reach a consistent state. Our old process didn’t manage that correctly. The script copied what it could, compressed the files, and returned success. The operating system had successfully copied bytes. That was all the monitoring actually knew. It had no idea whether PostgreSQL could recover from those bytes. Ever seen a backup dashboard showing 100% success while nobody can remember the last successful restore? That was essentially our situation.The First Restore Was Worse Than ExpectedThe test started with a clean Linux virtual machine and the same PostgreSQL major version. I deliberately avoided restoring anything on the production server. Apart from being risky, restoring to the original environment would hide some of the dependencies the backup was supposed to protect us from. I copied one of the recent archives, extracted it into a temporary PostgreSQL data directory, corrected the permissions, and tried to start the database. PostgreSQL refused to start cleanly.The log pointed toward recovery problems and missing WAL information. Some of the data files were present, but the WAL records needed to make the copied cluster consistent weren’t available in the backup. The mistake wasn’t a bad compression algorithm or a damaged network transfer. The backup process simply hadn’t been designed around PostgreSQL’s consistency requirements. That discovery changed the question. Instead of checking whether the archive was readable, I needed to know whether an entirely separate PostgreSQL instance could start from it and serve application data. It also exposed several smaller issues. The restore machine needed the correct PostgreSQL binaries. The service account needed access to the data directory. Configuration files referred to paths that existed only on the original server. None of those details had been recorded in the backup monitoring.A database can have valid data files and still be practically unrecoverable within the time a business can tolerate. For example, imagine a backup that takes 12\u00a0minutes to create but requires four hours of manual configuration work before the application can connect. The backup duration looks excellent, but the actual recovery process tells a very different story.Replacing the Old Script With a PostgreSQL\u2011Aware BackupThe first change was replacing the raw file\u2011copy process with pg_basebackup. Unlike copying the active data directory with a generic file tool, pg_basebackup works through PostgreSQL’s replication protocol and creates a physical base backup using the server’s supported backup mechanism. For this example, the backup is created in plain format, with the required WAL streamed alongside the data. A backup manifest is generated so the files can be checked afterward. The following script assumes PostgreSQL 16, an available replication user, correctly configured pg_hba.conf permissions, and a destination on a filesystem with sufficient free space. Authentication should be configured securely, for example through a protected .pgpass file or an appropriate credential service.#!\/usr\/bin\/env bashset -Eeuo pipefailumask 077# PostgreSQL source configurationPG_HOST=»${PG_HOST:-db.internal}»PG_PORT=»${PG_PORT:-5432}»PG_REPLICATION_USER=»${PG_REPLICATION_USER:-backup_user}»# Backup storageBACKUP_ROOT=»${BACKUP_ROOT:-\/srv\/postgresql\/backups}»BACKUP_DATE=»$(date -u +%Y%m%dT%H%M%SZ)»BACKUP_NAME=»base_${BACKUP_DATE}»# Tools must match the PostgreSQL major versionPG_BASEBACKUP=»\/usr\/lib\/postgresql\/16\/bin\/pg_basebackup»PG_VERIFYBACKUP=»\/usr\/lib\/postgresql\/16\/bin\/pg_verifybackup»log() {    printf ‘%s %s\\n’ \\        «$(date -u +%Y-%m-%dT%H:%M:%SZ)» \\        «$*» >&2}fail() {    log «ERROR: $*»    exit 1}for tool in «$PG_BASEBACKUP» «$PG_VERIFYBACKUP»; do    [[ -x «$tool» ]] || fail «Missing executable: $tool»donemkdir -p «$BACKUP_ROOT»# Temporary directory prevents incomplete backups# from appearing in the completed backup list.STAGING=»$(mktemp -d «${BACKUP_ROOT}\/.incoming.XXXXXXXX»)»FINAL=»${BACKUP_ROOT}\/${BACKUP_NAME}»cleanup() {    if [[ -n «${STAGING:-}» && -d «$STAGING» ]]; then        rm -rf — «$STAGING»    fi}trap cleanup EXIT[[ ! -e «$FINAL» ]] || fail «Backup already exists: $FINAL»log «Starting PostgreSQL base backup»»$PG_BASEBACKUP» \\    —host=»$PG_HOST» \\    —port=»$PG_PORT» \\    —username=»$PG_REPLICATION_USER» \\    —pgdata=»$STAGING» \\    —format=plain \\    —wal-method=stream \\    —checkpoint=spread \\    —manifest-checksums=SHA256 \\    —progress \\    —verboselog «Backup finished. Checking manifest.»[[ -s «$STAGING\/backup_manifest» ]] || \\    fail «Backup manifest missing or empty»»$PG_VERIFYBACKUP» \\    —progress \\    «$STAGING» || fail «Backup verification failed»log «Manifest verification succeeded»# Make the validated backup visible to# downstream jobs only after verification.mv — «$STAGING» «$FINAL»STAGING=»»log «Backup published: $FINAL»# Store backup metadata for monitoring.{    printf ‘backup_name=%s\\n’ «$BACKUP_NAME»    printf ‘completed_at=%s\\n’ \\        «$(date -u +%Y-%m-%dT%H:%M:%SZ)»    printf ‘postgresql_major=16\\n’    printf ‘verification=passed\\n’} > «${FINAL}.status»log «Backup workflow completed successfully»This version fixes several problems in the old process. First, PostgreSQL controls the consistency of the backup. The script no longer assumes that copying an active data directory produces a valid recovery image. Streaming WAL also avoids relying on whatever WAL files happened to exist when a generic archive command was executed. Second, the backup stays in a temporary directory until verification succeeds. That matters because a half\u2011finished backup should never appear alongside completed backups where another job might select it for restoration. Third, pg_verifybackup checks the backup against its manifest. It can detect missing files, unexpected changes, checksum mismatches, and certain WAL\u2011related problems. There is an important limitation, though.A valid manifest doesn’t prove that the recovered database will behave correctly when an application starts sending queries. The verification process doesn’t know which tables matter to the business, whether a particular extension is installed, or whether an application needs external files stored outside PostgreSQL. The script also has deliberately narrow assumptions. It covers a standard physical backup without separately located user\u2011defined tablespaces. Such tablespaces require additional handling and corresponding restore mapping. The backup destination also needs independent protection, access controls, and retention management. A directory on the same machine is not sufficient disaster recovery storage. So, after fixing the backup job, the next step was to stop trusting verification alone.Restoring the Database Without Touching ProductionA proper test needed to answer a simple question: could we take the latest completed backup, place it on another machine, start PostgreSQL, and read actual application data? The test environment used a dedicated Linux VM with PostgreSQL 16\u00a0installed. The VM was isolated from production application traffic. Its database service wasn’t allowed to connect to production clients, and the restore test used a separate port and Unix socket directory. The backup contained a small application table named public.restore_probe. Before running the test, that table had been created in the source database and populated with a known marker. The marker wasn’t intended as a substitute for checking real application tables. It was simply a quick way to prove that a specific piece of database content survived the backup and restore process. A good recovery test should also query important production\u2011shaped tables, inspect extensions, and exercise representative application operations. For this script, I kept the automatic assertion small enough to work in a controlled test environment.The…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-498279","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/498279","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=498279"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/498279\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=498279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=498279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=498279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}