Backup & Disaster Recovery

Shell Scripting PostgreSQL Point-In-Time Recovery Backups

postgresql wal backup bash ubuntu db recovery sql disaster script

Stop Trusting Daily Dumps

A stressed sysadmin in a dimly lit server room staring at a glowing red terminal screen, cinematic lighting, photorealistic, 8k, cyberpunk vibes --ar 16:9

Let's get one thing straight. If you rely entirely on a nightly data dump, you're playing Russian roulette with your database. Imagine a rogue query wiping out your users table at 2 PM. Your last dump was at midnight. That's 14 hours of data gone. Poof. Forever. Explaining that to your boss is going to be brutal. This is exactly where PostgreSQL Point-In-Time Recovery (PITR) saves your career. We are talking about literal time travel for your data. You don't just restore the database; you rewind it to the exact millisecond before the catastrophe hit.

Enter the Write-Ahead Log

Close-up of glowing neon green code scrolling rapidly on an old CRT monitor, analog aesthetic, highly detailed, macro photography, depth of field --ar 16:9

Here's the thing. Postgres is paranoid. It writes every single change to a Write-Ahead Log (WAL) before actually touching the main data files. If we grab those logs, we can replay history. Setting up a postgresql wal backup bash script isn't dark magic. It starts right in your `postgresql.conf`. You just tell the database to archive those WAL files to a safe, external directory. It's the absolute foundation of any real sql disaster script. Skip this, and you might as well not back up at all.

Scripting the Base Backup

A mechanical typewriter printing out glowing orange binary code onto heavy parchment paper, steampunk aesthetic, moody lighting, highly textured --ar 16:9

But WAL files are useless without a starting point. You need a solid base backup. Let's write some bash. We use `pg_basebackup` to grab a complete snapshot of the running cluster. Wrap that command in a shell script. Add some basic error handling so it doesn't fail silently. Then, cron it. Keep it painfully simple. Dump the files to a mounted network drive or push them to an S3 bucket. If you're setting this up on a standard rig, verify your permissions. Permission denied errors at 3 AM hit completely different.

The Oh-Sh*t Moment

Disaster strikes. The primary server dies. Panic sets in. Actually, don't panic. Ubuntu db recovery is incredibly straightforward if your scripts did their job. First, kill the Postgres service. Wipe the corrupted data directory clean. Yes, delete it all. Restore your base backup files into that empty void. Then, create an empty `recovery.signal` file and tell Postgres what specific time you want to rewind to in the configuration. Start the service. Grab a coffee. Watch the logs as it systematically replays every transaction from your archived WAL files.

Schrodinger's Backup

An untested backup is not a backup. It's just a wish. You can write the most elegant bash script on the planet, but if you never actually try to restore it, you are still going to fail when it matters. Spin up a local VM. Break it on purpose. Drop a critical table. Run your recovery procedure. Verify the data actually came back. Do it again. Keep doing it until you can restore that cluster half-asleep.