{"id":1683,"date":"2025-11-11T15:22:53","date_gmt":"2025-11-11T12:22:53","guid":{"rendered":"https:\/\/www.dchost.com\/blog\/i-broke-my-database-on-a-tuesday-and-learned-pgbackrest-pitr-the-calm-way\/"},"modified":"2025-11-11T15:22:53","modified_gmt":"2025-11-11T12:22:53","slug":"i-broke-my-database-on-a-tuesday-and-learned-pgbackrest-pitr-the-calm-way","status":"publish","type":"post","link":"https:\/\/www.dchost.com\/blog\/en\/i-broke-my-database-on-a-tuesday-and-learned-pgbackrest-pitr-the-calm-way\/","title":{"rendered":"I Broke My Database on a Tuesday (and Learned pgBackRest PITR the Calm Way)"},"content":{"rendered":"<div class=\"dchost-blog-content-wrapper\"><\/p>\n<div id=\"toc_container\" class=\"toc_transparent no_bullets\"><p class=\"toc_title\">\u0130&ccedil;indekiler<\/p><ul class=\"toc_list\"><li><a href=\"#A_quiet_panic_a_cup_of_coffee_and_why_PITR_matters\"><span class=\"toc_number toc_depth_1\">1<\/span> A quiet panic, a cup of coffee, and why PITR matters<\/a><\/li><li><a href=\"#WAL_PITR_and_a_simple_mental_model\"><span class=\"toc_number toc_depth_1\">2<\/span> WAL, PITR, and a simple mental model<\/a><\/li><li><a href=\"#Prep_your_VPS_and_choose_where_backups_live\"><span class=\"toc_number toc_depth_1\">3<\/span> Prep your VPS and choose where backups live<\/a><ul><li><a href=\"#Quick_checklist_before_we_begin\"><span class=\"toc_number toc_depth_2\">3.1<\/span> Quick checklist before we begin<\/a><\/li><\/ul><\/li><li><a href=\"#Install_pgBackRest_and_create_your_first_stanza\"><span class=\"toc_number toc_depth_1\">4<\/span> Install pgBackRest and create your first stanza<\/a><ul><li><a href=\"#Install_on_DebianUbuntu\"><span class=\"toc_number toc_depth_2\">4.1<\/span> Install on Debian\/Ubuntu<\/a><\/li><li><a href=\"#Repository_and_permissions\"><span class=\"toc_number toc_depth_2\">4.2<\/span> Repository and permissions<\/a><\/li><li><a href=\"#Write_the_core_configuration\"><span class=\"toc_number toc_depth_2\">4.3<\/span> Write the core configuration<\/a><\/li><li><a href=\"#Create_the_stanza\"><span class=\"toc_number toc_depth_2\">4.4<\/span> Create the stanza<\/a><\/li><\/ul><\/li><li><a href=\"#Turn_on_WAL_archiving_in_PostgreSQL_the_lever_most_people_miss\"><span class=\"toc_number toc_depth_1\">5<\/span> Turn on WAL archiving in PostgreSQL (the lever most people miss)<\/a><ul><li><a href=\"#Edit_postgresqlconf\"><span class=\"toc_number toc_depth_2\">5.1<\/span> Edit postgresql.conf<\/a><\/li><li><a href=\"#Why_WAL_archiving_matters_now_not_later\"><span class=\"toc_number toc_depth_2\">5.2<\/span> Why WAL archiving matters now, not later<\/a><\/li><\/ul><\/li><li><a href=\"#Run_your_first_backup_and_validate_it_like_you_mean_it\"><span class=\"toc_number toc_depth_1\">6<\/span> Run your first backup and validate it like you mean it<\/a><ul><li><a href=\"#Full_backup\"><span class=\"toc_number toc_depth_2\">6.1<\/span> Full backup<\/a><\/li><li><a href=\"#Incremental_and_differential_backups\"><span class=\"toc_number toc_depth_2\">6.2<\/span> Incremental and differential backups<\/a><\/li><li><a href=\"#Schedule_it\"><span class=\"toc_number toc_depth_2\">6.3<\/span> Schedule it<\/a><\/li><\/ul><\/li><li><a href=\"#OffVM_options_SSH_and_S3_without_the_drama\"><span class=\"toc_number toc_depth_1\">7<\/span> Off\u2011VM options: SSH and S3 without the drama<\/a><ul><li><a href=\"#SSH_repository_repo_on_a_backup_server\"><span class=\"toc_number toc_depth_2\">7.1<\/span> SSH repository (repo on a backup server)<\/a><\/li><li><a href=\"#S3_repository_object_storage\"><span class=\"toc_number toc_depth_2\">7.2<\/span> S3 repository (object storage)<\/a><\/li><\/ul><\/li><li><a href=\"#Practice_the_thing_that_saves_you_a_real_PITR_restore\"><span class=\"toc_number toc_depth_1\">8<\/span> Practice the thing that saves you: a real PITR restore<\/a><ul><li><a href=\"#Make_a_small_deliberate_mistake_to_recover_from\"><span class=\"toc_number toc_depth_2\">8.1<\/span> Make a small, deliberate mistake to recover from<\/a><\/li><li><a href=\"#Stop_Postgres_and_prepare_the_restore_target\"><span class=\"toc_number toc_depth_2\">8.2<\/span> Stop Postgres and prepare the restore target<\/a><\/li><li><a href=\"#Restore_to_a_timestamp\"><span class=\"toc_number toc_depth_2\">8.3<\/span> Restore to a timestamp<\/a><\/li><li><a href=\"#Restore_to_a_named_stop_point\"><span class=\"toc_number toc_depth_2\">8.4<\/span> Restore to a named stop point<\/a><\/li><li><a href=\"#What_pgBackRest_does_for_you_during_restore\"><span class=\"toc_number toc_depth_2\">8.5<\/span> What pgBackRest does for you during restore<\/a><\/li><\/ul><\/li><li><a href=\"#Retention_pruning_and_watching_for_trouble\"><span class=\"toc_number toc_depth_1\">9<\/span> Retention, pruning, and watching for trouble<\/a><ul><li><a href=\"#Set_retention_you_can_afford\"><span class=\"toc_number toc_depth_2\">9.1<\/span> Set retention you can afford<\/a><\/li><li><a href=\"#Prune_and_info\"><span class=\"toc_number toc_depth_2\">9.2<\/span> Prune and info<\/a><\/li><li><a href=\"#Monitoring_and_alerts\"><span class=\"toc_number toc_depth_2\">9.3<\/span> Monitoring and alerts<\/a><\/li><li><a href=\"#Vacuum_and_bloat_matter_too\"><span class=\"toc_number toc_depth_2\">9.4<\/span> Vacuum and bloat matter too<\/a><\/li><\/ul><\/li><li><a href=\"#Common_gotchas_and_the_friendly_fixes\"><span class=\"toc_number toc_depth_1\">10<\/span> Common gotchas and the friendly fixes<\/a><ul><li><a href=\"#archive_command_failed_on_repeat\"><span class=\"toc_number toc_depth_2\">10.1<\/span> \u201carchive_command failed\u201d on repeat<\/a><\/li><li><a href=\"#Backups_are_huge_even_after_compression\"><span class=\"toc_number toc_depth_2\">10.2<\/span> Backups are huge even after compression<\/a><\/li><li><a href=\"#Restore_works_but_the_target_time_is_off\"><span class=\"toc_number toc_depth_2\">10.3<\/span> Restore works, but the target time is off<\/a><\/li><li><a href=\"#Testing_restores_without_downtime\"><span class=\"toc_number toc_depth_2\">10.4<\/span> Testing restores without downtime<\/a><\/li><li><a href=\"#Security_and_encryption\"><span class=\"toc_number toc_depth_2\">10.5<\/span> Security and encryption<\/a><\/li><li><a href=\"#Document_the_path_not_just_the_tools\"><span class=\"toc_number toc_depth_2\">10.6<\/span> Document the path, not just the tools<\/a><\/li><\/ul><\/li><li><a href=\"#Wrapup_make_Tuesday_boring_again\"><span class=\"toc_number toc_depth_1\">11<\/span> Wrap\u2011up: make Tuesday boring again<\/a><\/li><\/ul><\/div>\n<h2 id=\"section-1\"><span id=\"A_quiet_panic_a_cup_of_coffee_and_why_PITR_matters\">A quiet panic, a cup of coffee, and why PITR matters<\/span><\/h2>\n<p>I still remember the Tuesday morning that taught me more about backups than a dozen whitepapers ever could. One of my clients had a tidy little <a href=\"https:\/\/www.dchost.com\/vps\">VPS<\/a>, the kind you feel good about because it just hums along quietly doing its job. The app was happy. The users were happy. And then a migration script ran on the wrong database. You know that cold, sinking feeling behind your ribs? That one. We had a full backup from the night before, but that would roll us back nearly a day\u2014too much lost work. What we needed was to wind the clock back just thirty minutes. Not a day. Not an hour. Thirty minutes.<\/p>\n<p>That\u2019s when point\u2011in\u2011time recovery (PITR) went from \u201csomething we should set up\u201d to \u201cI will never ship a database without this again.\u201d If you\u2019ve ever wished you could rewind a few minutes of bad changes, you\u2019re in the right place. In this guide we\u2019ll walk through a calm, step\u2011by\u2011step setup of PostgreSQL backups and PITR on a VPS using <strong>pgBackRest<\/strong>, plus the mental model to make it stick. We\u2019ll set up WAL archiving, run a real backup, practice a restore, and leave you with a plan you can trust when Tuesday goes sideways.<\/p>\n<h2 id=\"section-2\"><span id=\"WAL_PITR_and_a_simple_mental_model\">WAL, PITR, and a simple mental model<\/span><\/h2>\n<p>Here\u2019s the thing: backups that only capture a snapshot are like a single photograph of a busy street. They\u2019re helpful, but they don\u2019t show the motion. PostgreSQL\u2019s write\u2011ahead log (WAL) is the movie reel of your database\u2014the frame\u2011by\u2011frame list of every change. PITR works by restoring a base backup (the snapshot) and then replaying WAL until you reach the exact moment you want, whether that\u2019s a timestamp, a transaction mark, or a recovery target name.<\/p>\n<p>In practice, there are three moving parts you care about. First, a base backup captured at a point in time. Second, a continuous stream of WAL files safely archived somewhere other than your active data directory. Third, a restore tool that can reassemble those pieces with confidence. That\u2019s where <strong>pgBackRest<\/strong> shines. It handles base backups, WAL archiving, retention rules, and restores with an elegance that saves you from a thousand tiny gotchas.<\/p>\n<p>Think of it like this: the base backup is your save point. WAL files are your time machine. pgBackRest is the friendly operator who knows which levers to pull and in what order so you land exactly where you intend. And on a VPS, keeping those pieces organized and off the same disk as production is the difference between \u201cWe\u2019re fine\u201d and \u201cWe lost everything when the VM died.\u201d<\/p>\n<h2 id=\"section-3\"><span id=\"Prep_your_VPS_and_choose_where_backups_live\">Prep your VPS and choose where backups live<\/span><\/h2>\n<p>Before we type a single command, let\u2019s set the stage. The biggest early decision is where your backups and WAL archives will live. I\u2019ve tried three patterns that each work well depending on your constraints: a second block storage volume mounted on the same VPS, a separate backup server reachable over SSH, or object storage like S3. If you\u2019re just getting started, a second volume is a great starting point for fast restores and simple permissions. If you already have an offsite server or object storage account, skip straight to that for true off\u2011VM resilience.<\/p>\n<p>In my experience, the happy path looks like this: give PostgreSQL its own user (it already has one), make sure you have a clean directory for pgBackRest\u2019s repository, and confirm that your VPS has enough bandwidth and CPU to compress backups without choking your app. And remember, the whole point is to keep your data safe when your primary disk or VM is unhappy. If the backups live on the same disk as your database, you have a single point of failure. It\u2019s better than nothing for practice, but don\u2019t stop there.<\/p>\n<h3><span id=\"Quick_checklist_before_we_begin\">Quick checklist before we begin<\/span><\/h3>\n<p>Make sure you can log in as a user with sudo privileges. Confirm PostgreSQL is installed and running (version 12+ is perfectly fine). Ensure your server clock is sane and synced; recovery targets by timestamp are a lot less fun when your clock drifts. Finally, decide on a directory for your backup repository\u2014for example, <strong>\/var\/lib\/pgbackrest<\/strong> on a secondary volume.<\/p>\n<h2 id=\"section-4\"><span id=\"Install_pgBackRest_and_create_your_first_stanza\">Install pgBackRest and create your first stanza<\/span><\/h2>\n<p>I like starting locally because it lets you feel the whole flow before you introduce remote targets. You can pivot to SSH or S3 in a snap once the basics click.<\/p>\n<h3><span id=\"Install_on_DebianUbuntu\">Install on Debian\/Ubuntu<\/span><\/h3>\n<p>If you\u2019re on Debian or Ubuntu, installing pgBackRest is straightforward. Use the repository provided by the maintainers or your distro\u2019s packages. The official docs stay current and are worth bookmarking. When in doubt, the <a href=\"https:\/\/pgbackrest.org\/documentation.html\" rel=\"nofollow noopener\" target=\"_blank\">pgBackRest documentation<\/a> is gold.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo apt update\nsudo apt install pgbackrest\n<\/code><\/pre>\n<p>On most systems, pgBackRest installs into <strong>\/usr\/bin\/pgbackrest<\/strong> with configuration in <strong>\/etc\/pgbackrest<\/strong>. We\u2019ll create a configuration that defines two things: your PostgreSQL \u201cstanza\u201d (think of it as a named database cluster) and the repository where backups and WAL will land.<\/p>\n<h3><span id=\"Repository_and_permissions\">Repository and permissions<\/span><\/h3>\n<p>Create the repository directory and hand ownership to the <strong>postgres<\/strong> user:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo mkdir -p \/var\/lib\/pgbackrest\nsudo chown -R postgres:postgres \/var\/lib\/pgbackrest\nsudo chmod 750 \/var\/lib\/pgbackrest\n<\/code><\/pre>\n<h3><span id=\"Write_the_core_configuration\">Write the core configuration<\/span><\/h3>\n<p>We\u2019ll use a simple local repository configuration to start. You can safely extend this later for SSH or S3 without rethinking everything.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres mkdir -p \/etc\/pgbackrest\/conf.d\nsudo -u postgres tee \/etc\/pgbackrest\/pgbackrest.conf &gt;\/dev\/null &lt;&lt;'CONF'\n[global]\nrepo1-path=\/var\/lib\/pgbackrest\nrepo1-retention-full=2\nrepo1-retention-diff=7\nstart-fast=y\ncompress-type=zstd\nlog-level-console=info\nlog-level-file=info\n\n[main]\npg1-path=\/var\/lib\/postgresql\/15\/main\nCONF\n<\/code><\/pre>\n<p>Adjust <strong>pg1-path<\/strong> to your actual data directory. On Debian-based systems it\u2019s usually something like <strong>\/var\/lib\/postgresql\/14\/main<\/strong> or <strong>\/var\/lib\/postgresql\/15\/main<\/strong>. On other distros you might see <strong>\/var\/lib\/pgsql\/15\/data<\/strong>.<\/p>\n<h3><span id=\"Create_the_stanza\">Create the stanza<\/span><\/h3>\n<p>A stanza links pgBackRest to your Postgres data directory and prepares the repository metadata. It\u2019s a one\u2011time setup per cluster.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres pgbackrest --stanza=main --log-level-console=info stanza-create\n<\/code><\/pre>\n<p>If this succeeds, you\u2019ll see a reassuring \u201cstanza create complete.\u201d If it fails with permission complaints, double\u2011check directory ownerships and that you\u2019re running as the <strong>postgres<\/strong> user.<\/p>\n<h2 id=\"section-5\"><span id=\"Turn_on_WAL_archiving_in_PostgreSQL_the_lever_most_people_miss\">Turn on WAL archiving in PostgreSQL (the lever most people miss)<\/span><\/h2>\n<p>If backups are your snapshot, WAL archiving is the river of changes that lets you rewind time. PostgreSQL needs to be told to save copies of finished WAL segments somewhere safe. pgBackRest acts as the handler for that: it grabs each completed segment and tucks it into the repository.<\/p>\n<h3><span id=\"Edit_postgresqlconf\">Edit postgresql.conf<\/span><\/h3>\n<p>Open your PostgreSQL configuration and set a few critical parameters. If your distro uses include files, you can drop these in a dedicated file like <strong>conf.d\/10-archiving.conf<\/strong>.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\"># postgresql.conf\narchive_mode = on\narchive_command = 'pgbackrest --stanza=main archive-push %p'\nwal_level = replica\narchive_timeout = 60s   # optional; forces periodic WAL segments even if quiet\n<\/code><\/pre>\n<p>Restart PostgreSQL to apply changes:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo systemctl restart postgresql\n<\/code><\/pre>\n<p>Within a minute or two of regular activity, pgBackRest should start to receive WAL segments into your repository. You can watch logs or simply run:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres pgbackrest info\n<\/code><\/pre>\n<p>If the output complains about missing stanza or archive errors, re\u2011check the command paths and permissions. The number one culprit I see is a typo in <strong>archive_command<\/strong> or the <strong>pgbackrest<\/strong> binary not being in the PATH for the Postgres service. Using the absolute path in the command\u2014<strong>\/usr\/bin\/pgbackrest<\/strong>\u2014can remove any doubt:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">archive_command = '\/usr\/bin\/pgbackrest --stanza=main archive-push %p'\n<\/code><\/pre>\n<h3><span id=\"Why_WAL_archiving_matters_now_not_later\">Why WAL archiving matters now, not later<\/span><\/h3>\n<p>I once helped a team that ran nightly full backups but never enabled archiving. They thought, reasonably, that a nightly snapshot was fine. Then a destructive command fired at noon. They could only restore to midnight. Six gut\u2011wrenching hours gone. PITR is the difference between \u201cwe can get it back\u201d and \u201cwe can get <strong>the right version<\/strong> back.\u201d Turning on <strong>archive_mode<\/strong> today is the single highest\u2011leverage step you can take.<\/p>\n<h2 id=\"section-6\"><span id=\"Run_your_first_backup_and_validate_it_like_you_mean_it\">Run your first backup and validate it like you mean it<\/span><\/h2>\n<p>With archiving on, a base backup will anchor your PITR timeline. pgBackRest handles all the hard parts\u2014quiescing, checksums, checks, WAL coordination\u2014without you juggling pg_start_backup and friends.<\/p>\n<h3><span id=\"Full_backup\">Full backup<\/span><\/h3>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres pgbackrest --stanza=main --type=full --log-level-console=info backup\n<\/code><\/pre>\n<p>Depending on database size, CPU, and compression, the first backup can take a while. You\u2019ll see steps for manifest building, file copy, and WAL finalize. When it finishes, you can check status:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres pgbackrest info\n<\/code><\/pre>\n<p>You should see one full backup listed, plus a growing set of archived WAL segments. This is the moment to breathe. You\u2019ve got a snapshot and a stream.<\/p>\n<h3><span id=\"Incremental_and_differential_backups\">Incremental and differential backups<\/span><\/h3>\n<p>You don\u2019t need to run full backups every time. Let the first full establish a baseline, then schedule incrementals throughout the day and a differential or full once a day or week, depending on churn and window size. For example:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\"># differential backup\nsudo -u postgres pgbackrest --stanza=main --type=diff backup\n\n# incremental backup\nsudo -u postgres pgbackrest --stanza=main --type=incr backup\n<\/code><\/pre>\n<p>That retention configuration we set earlier\u2014two fulls, seven diffs\u2014will prune old backups while keeping a safe path through your timeline. You can tune those numbers once you see how big your backups get and how fast your data changes.<\/p>\n<h3><span id=\"Schedule_it\">Schedule it<\/span><\/h3>\n<p>I know, cron isn\u2019t glamorous, but it gets the job done. A simple pattern is full on Sunday night, differentials on weekdays at night, incrementals every hour during business hours. On a small VPS this spreads the load and keeps your recovery points tight. Here\u2019s a starter crontab for the <strong>postgres<\/strong> user:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">crontab -u postgres -e\n\n# Full on Sunday 02:00\n0 2 * * 0 pgbackrest --stanza=main --type=full backup &gt;&gt; \/var\/log\/pgbackrest-cron.log 2&gt;&amp;1\n\n# Diff Mon-Sat 02:00\n0 2 * * 1-6 pgbackrest --stanza=main --type=diff backup &gt;&gt; \/var\/log\/pgbackrest-cron.log 2&gt;&amp;1\n\n# Incremental at :15 each hour 08:00-20:00\n15 8-20 * * * pgbackrest --stanza=main --type=incr backup &gt;&gt; \/var\/log\/pgbackrest-cron.log 2&gt;&amp;1\n<\/code><\/pre>\n<p>Adjust frequency to your comfort with load and your target RPO. If you\u2019re curious about broader recovery planning and realistic test loops, I walked through a calm approach in <a href=\"https:\/\/www.dchost.com\/blog\/en\/felaket-kurtarma-plani-nasil-yazilir-rto-rpoyu-kafada-netlestirip-yedek-testleri-ve-runbooklari-gercekten-calisir-hale-getirmek\/\">How I write a no\u2011drama DR plan with runbooks you\u2019ll actually use<\/a>.<\/p>\n<h2 id=\"section-7\"><span id=\"OffVM_options_SSH_and_S3_without_the_drama\">Off\u2011VM options: SSH and S3 without the drama<\/span><\/h2>\n<p>Once the local flow is working, it\u2019s time to push backups off the VPS. I like two clean paths: an SSH\u2011reachable backup host or an object store.<\/p>\n<h3><span id=\"SSH_repository_repo_on_a_backup_server\">SSH repository (repo on a backup server)<\/span><\/h3>\n<p>On the backup server, install pgBackRest and create a repository path owned by a dedicated system user (often also named <strong>postgres<\/strong> or <strong>pgbackrest<\/strong>). Set up SSH keys from your database server to the backup server, locked down to the pgBackRest command if you want to be extra careful.<\/p>\n<p>On the database server, your config gains a <strong>repo1-host<\/strong> line:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">[global]\nrepo1-host=backup.example.com\nrepo1-host-user=pgbackrest\nrepo1-path=\/var\/lib\/pgbackrest\nrepo1-retention-full=2\nrepo1-retention-diff=7\ncompress-type=zstd\nstart-fast=y\n<\/code><\/pre>\n<p>Now when you run backups or archive WAL, pgBackRest will store them remotely. It\u2019s the same commands, just a safer destination.<\/p>\n<h3><span id=\"S3_repository_object_storage\">S3 repository (object storage)<\/span><\/h3>\n<p>If you prefer object storage, pgBackRest supports S3\u2011compatible endpoints. Add credentials and bucket information to your config:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">[global]\nrepo1-type=s3\nrepo1-s3-bucket=my-pgbackups\nrepo1-s3-endpoint=s3.amazonaws.com\nrepo1-s3-region=eu-central-1\nrepo1-s3-key=AKIA...\nrepo1-s3-key-secret=...redacted...\nrepo1-path=\/\nrepo1-retention-full=2\nrepo1-retention-diff=7\ncompress-type=zstd\nstart-fast=y\n<\/code><\/pre>\n<p>With S3, two best practices have saved me headaches. First, enable <strong>server\u2011side or client\u2011side encryption<\/strong>. pgBackRest can encrypt before upload with AES\u2011256:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">repo1-cipher-type=aes-256-cbc\nrepo1-cipher-pass=&lt;your-strong-passphrase&gt;\n<\/code><\/pre>\n<p>Second, keep lifecycle policies in the bucket aligned with pgBackRest retention. Let pgBackRest prune; let the bucket retain what pgBackRest expects. If the bucket deletes objects behind pgBackRest\u2019s back, you can end up with gaps.<\/p>\n<h2 id=\"section-8\"><span id=\"Practice_the_thing_that_saves_you_a_real_PITR_restore\">Practice the thing that saves you: a real PITR restore<\/span><\/h2>\n<p>I make a big deal about <strong>practice restores<\/strong> because that\u2019s where the confidence comes from. It doesn\u2019t have to be fancy. Spin up a throwaway VM, or temporarily restore to a different directory and port on the same VPS. What matters is going through the motions until it feels ordinary.<\/p>\n<h3><span id=\"Make_a_small_deliberate_mistake_to_recover_from\">Make a small, deliberate mistake to recover from<\/span><\/h3>\n<p>Create a test table, insert a few rows, and then drop it. That gives you something to find in the past. Note the current timestamp so you have a clean recovery target.<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">-- inside psql\nCREATE TABLE pitr_demo(id serial primary key, note text);\nINSERT INTO pitr_demo(note) VALUES ('before');\nSELECT now();  -- copy this timestamp\n-- simulate a mistake\nDROP TABLE pitr_demo;\n<\/code><\/pre>\n<p>After the drop, wait a minute so the WAL segment containing the change can be archived. You can force WAL rotation by running a quick checkpoint if you\u2019re impatient.<\/p>\n<h3><span id=\"Stop_Postgres_and_prepare_the_restore_target\">Stop Postgres and prepare the restore target<\/span><\/h3>\n<p>If you\u2019re restoring in place (be careful on production), stop PostgreSQL and move the current data directory aside. In a practice environment, I often restore to a new directory and bind it to a temporary port to keep things safe.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo systemctl stop postgresql\nsudo mv \/var\/lib\/postgresql\/15\/main \/var\/lib\/postgresql\/15\/main.broken-$(date +%s)\nsudo -u postgres mkdir -p \/var\/lib\/postgresql\/15\/main\n<\/code><\/pre>\n<h3><span id=\"Restore_to_a_timestamp\">Restore to a timestamp<\/span><\/h3>\n<p>Now the fun part. Tell pgBackRest to restore to the moment just before the mistake. Use the timestamp you saved, adjusting a few seconds earlier to be safe.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres pgbackrest \n  --stanza=main \n  --type=time \n  --target='2025-11-11 10:54:30+00' \n  --target-action=promote \n  --log-level-console=info \n  restore\n<\/code><\/pre>\n<p>pgBackRest will drop the base backup into place, configure <strong>restore_command<\/strong> under the hood, and replay WAL until it reaches your target. When it\u2019s done, start PostgreSQL and check your data:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo systemctl start postgresql\npsql -c 'SELECT * FROM pitr_demo;'\n<\/code><\/pre>\n<p>If you see your rows again, that\u2019s the good stuff. If you don\u2019t, make sure your target time is right and that your server\u2019s timezone didn\u2019t trick you. Timezones are sneaky. I like using explicit offsets in timestamps to keep my head straight.<\/p>\n<h3><span id=\"Restore_to_a_named_stop_point\">Restore to a named stop point<\/span><\/h3>\n<p>Another pattern I love is using named recovery targets. You can create a named anchor by setting a recovery target name before your risky operation and then restoring to that exact name later. In Postgres 12+, you can set this via <strong>pgbackrest restore<\/strong> options or by adding a recovery target to the command. For example:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres pgbackrest \n  --stanza=main \n  --type=name \n  --target='before-batch-42' \n  --target-action=promote \n  restore\n<\/code><\/pre>\n<p>Whether you prefer timestamps or names, the flow is the same: base backup, then WAL, then promotion to normal operation at the precise point you want.<\/p>\n<h3><span id=\"What_pgBackRest_does_for_you_during_restore\">What pgBackRest does for you during restore<\/span><\/h3>\n<p>One of the reasons I recommend pgBackRest is that it writes the messy bits for you\u2014creating <strong>recovery.signal<\/strong>, setting <strong>restore_command<\/strong> to fetch WAL via <strong>archive-get<\/strong>, and cleaning up when it\u2019s safe to promote. You focus on the \u201cwhen\u201d and \u201cwhere,\u201d pgBackRest handles the \u201chow.\u201d If you ever want to read the under\u2011the\u2011hood details, the <a href=\"https:\/\/www.postgresql.org\/docs\/current\/continuous-archiving.html\" rel=\"nofollow noopener\" target=\"_blank\">PostgreSQL docs on continuous archiving and PITR<\/a> are a friendly deep dive.<\/p>\n<h2 id=\"section-9\"><span id=\"Retention_pruning_and_watching_for_trouble\">Retention, pruning, and watching for trouble<\/span><\/h2>\n<p>Backups are like houseplants: a little bit of regular care goes a long way. With pgBackRest you\u2019re mostly telling it what to keep and then checking in occasionally to be sure the river is flowing.<\/p>\n<h3><span id=\"Set_retention_you_can_afford\">Set retention you can afford<\/span><\/h3>\n<p>We started with two full backups and seven differentials. That\u2019s sane for many small teams, but every dataset is different. If your database changes slowly, longer retention is cheap. If it churns fast or stores large binaries, consider more frequent differentials and tighter WAL retention. The goal is that you can restore to a safe point from the last few days without needing to chain a mountain of WAL segments.<\/p>\n<h3><span id=\"Prune_and_info\">Prune and info<\/span><\/h3>\n<p>pgBackRest prunes on backup runs by default, but it\u2019s handy to know you can ask for a cleanup directly:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">sudo -u postgres pgbackrest --stanza=main expire\nsudo -u postgres pgbackrest info\n<\/code><\/pre>\n<p>The first removes backups beyond your retention policy; the second shows what remains. If you ever feel uneasy, take a moment to scan the info output. Seeing a fresh full, recent diffs, and a healthy archive flow is like checking the locks before bed.<\/p>\n<h3><span id=\"Monitoring_and_alerts\">Monitoring and alerts<\/span><\/h3>\n<p>I keep an eye on a few signals: successful backup exit codes in cron logs, the size and age of WAL archives, and free space in the repository. A classic red flag is WAL piling up in the database server\u2019s <strong>pg_wal<\/strong> directory because the <strong>archive_command<\/strong> is failing. When archiving breaks, Postgres will keep WAL locally for a while, and then\u2014if the disk fills\u2014everything stops. It\u2019s noisy in logs when this happens, so even a simple tail in your monitoring can catch it early.<\/p>\n<h3><span id=\"Vacuum_and_bloat_matter_too\">Vacuum and bloat matter too<\/span><\/h3>\n<p>Sometimes folks blame backups for slow databases when the real culprit is table bloat and lazy autovacuum settings. If your backups are dragging or your WAL volume is spiking, peek at vacuuming. I shared a calm, real\u2011world approach in <a href=\"https:\/\/www.dchost.com\/blog\/en\/postgresql-autovacuum-tuning-ve-bloatla-barismak-vpste-pratik-ayarlar-ve-pg_repack-ile-neredeyse-sifir-kesinti\/\">The guide to tuning PostgreSQL autovacuum and shrinking bloat without drama<\/a>. Healthy vacuum keeps your WAL stream reasonable and your backups lean.<\/p>\n<h2 id=\"section-10\"><span id=\"Common_gotchas_and_the_friendly_fixes\">Common gotchas and the friendly fixes<\/span><\/h2>\n<p>Over the years, I\u2019ve bumped into a handful of predictable snags. Knowing them makes you look like a wizard when someone else bumps into the same wall.<\/p>\n<h3><span id=\"archive_command_failed_on_repeat\">\u201carchive_command failed\u201d on repeat<\/span><\/h3>\n<p>This usually boils down to one of three things: the <strong>pgbackrest<\/strong> binary isn\u2019t in the PATH of the Postgres service, the stanza name is wrong, or permissions on the repository are too strict or too loose. Use the full path in <strong>archive_command<\/strong>, verify the stanza name exactly, and ensure <strong>postgres<\/strong> owns the repo directory with 750 permissions.<\/p>\n<h3><span id=\"Backups_are_huge_even_after_compression\">Backups are huge even after compression<\/span><\/h3>\n<p>Two ideas help here. First, switch to <strong>zstd<\/strong> if you haven\u2019t already; it\u2019s a great balance of speed and size on a VPS. Second, check for large unchanging blobs in the database and consider moving them to object storage with references in Postgres. That reduces churn and the size of every incremental backup.<\/p>\n<h3><span id=\"Restore_works_but_the_target_time_is_off\">Restore works, but the target time is off<\/span><\/h3>\n<p>Timezones strike again. Use explicit offsets in your target time and confirm the server\u2019s timezone. When in doubt, a named recovery target avoids ambiguity. Also, back up your <strong>postgresql.conf<\/strong> and related includes with your IaC or config management so you can reproduce the exact environment on restore.<\/p>\n<h3><span id=\"Testing_restores_without_downtime\">Testing restores without downtime<\/span><\/h3>\n<p>One trick I love is restoring to a temporary directory and starting Postgres on a different port just for verification. For example, restore to <strong>\/var\/lib\/postgresql\/15\/restore<\/strong>, set <strong>port = 55432<\/strong> in a temporary config file, and start it with a custom service. You can then point a one\u2011off psql at it, run a few checks, and shut it down quietly. It\u2019s like a dress rehearsal that doesn\u2019t disturb the main stage.<\/p>\n<h3><span id=\"Security_and_encryption\">Security and encryption<\/span><\/h3>\n<p>If your backups ever leave the machine\u2014and they should\u2014encrypt them. pgBackRest\u2019s client\u2011side encryption with <strong>repo1-cipher-type=aes-256-cbc<\/strong> and a strong passphrase is simple and effective. Store the passphrase in a password manager or a vault service and make sure your runbook includes how to retrieve it during a restore. I\u2019ve seen teams nail everything technically and then stumble because only one person knew the passphrase. Share safely.<\/p>\n<h3><span id=\"Document_the_path_not_just_the_tools\">Document the path, not just the tools<\/span><\/h3>\n<p>The best backup setups are boring on game day because someone wrote down exactly what to run and where to look if it fails. If you want a friendly template and a way to pressure\u2011test your steps, I laid out a practical approach in <a href=\"https:\/\/www.dchost.com\/blog\/en\/felaket-kurtarma-plani-nasil-yazilir-rto-rpoyu-kafada-netlestirip-yedek-testleri-ve-runbooklari-gercekten-calisir-hale-getirmek\/\">this guide to writing a no\u2011drama DR plan<\/a>. It pairs beautifully with pgBackRest.<\/p>\n<h2 id=\"section-11\"><span id=\"Wrapup_make_Tuesday_boring_again\">Wrap\u2011up: make Tuesday boring again<\/span><\/h2>\n<p>If you\u2019ve made it this far, you\u2019ve got the bones of a resilient backup and recovery flow on your VPS. You understand the mental model\u2014base backup plus WAL equals a time machine. You\u2019ve installed pgBackRest, turned on WAL archiving, run a full backup, and practiced a restore to a precise moment. That practice run is the difference between hoping and knowing. It\u2019s the warm blanket you want when someone whispers \u201cI think I dropped the wrong table.\u201d<\/p>\n<p>My final advice is simple. Keep your repository off the main disk if you can. Schedule backups like you schedule brushing your teeth\u2014habit beats heroics. Encrypt if it ever leaves the box. Watch the logs for archive hiccups. And once a month, do a quick restore to a throwaway path just to keep the muscle memory fresh. That small cadence will save you hours when it matters.<\/p>\n<p>Most of all, give yourself permission to make this boring. The best backups are the ones you forget about until you need them, and when you do, they just work. Hope this was helpful! If there\u2019s a step that feels fuzzy or you want a sanity check on your setup, send me a note. See you in the next post, and may your WAL stream flow steady and your restores be gloriously uneventful.<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>\u0130&ccedil;indekiler1 A quiet panic, a cup of coffee, and why PITR matters2 WAL, PITR, and a simple mental model3 Prep your VPS and choose where backups live3.1 Quick checklist before we begin4 Install pgBackRest and create your first stanza4.1 Install on Debian\/Ubuntu4.2 Repository and permissions4.3 Write the core configuration4.4 Create the stanza5 Turn on WAL [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1684,"comment_status":"","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-1683","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teknoloji"],"_links":{"self":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/1683","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/comments?post=1683"}],"version-history":[{"count":0,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/1683\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media\/1684"}],"wp:attachment":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media?parent=1683"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/categories?post=1683"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/tags?post=1683"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}