Recovering a PostgreSQL database after a hard drive failure | Hacker News Reader

Recovering a PostgreSQL database after a hard drive failure

sambhav.saggis.com

107 pointsd4a17 comments

If you are not as lucky and need to attack the underlying files to recover as much as you can in CSV form, try my small tool: https://github.com/csdvrx/pg_csdvrx

Or just watch the demo on https://raw.githubusercontent.com/csdvrx/pg_csdvrx/master/pg... where I corrupt a table to show how it works

Thanks for putting this together. I could have seriously used it a few years ago.

Well, I made it for a reason ;)

It is super simple and generic and could certainly be improved with a few days of work.

For ex: saving the schemas to a text file or a sqlite with PAR records for redundancy right when the db is created or the schemas changed, then more automated (say, given a set of schemas, chose the one the most likely given the data on hand and its possible interpretations, including say the range of values, even with a basic minimization of variance approach)

But then I may want to charge for the time and effort of coding that while I'm not in the business of data recovery... so feel free to improve on that when you start needing it, like the next time a disaster strikes :)

Working today to create a backup script and bind it to a systemd timer. Hope it all goes well.

Add to your script the binary schema in case you ever need to use my tool it will make your life MUNCH simpler that reconstructing them by hand while fighting on multiple front to get your database back online...

(from bitter experience): Also consider periodically taking the backup and spinning it up on a separate host/VM.

And then codifying those steps into a script, so that if the time ever comes, restoring from backups is less of a fire drill.

And for next time. https://gist.github.com/AtulKsol/520f5d0c9a313edea7254701f6d...

Thank you very much! I'm planning on using this for my backups (in conjunction with rsync -a, and podman volume export).

I put together a quick-n-dirty backup script. It's tested.

  #!/bin/bash
  
  set -e
  
  BACKUPDIR=/srv/backup/$(/usr/bin/date -I)
  umask 137
  
  cd /tmp
  
  backup_init() {
    echo 'Initializing backup...'
    mkdir "$BACKUPDIR"
    chown root:root "$BACKUPDIR"
    chmod 751 "$BACKUPDIR"
  }
  
  backup_postgres() {
    # Postgres
    echo 'Backing up PostgreSQL...'
    sudo -u postgres /usr/bin/pg_dumpall > $BACKUPDIR/postgres.sql
  }
  
  backup_podman_volumes_for_user() {
    if id "$1" &> /dev/null; then
      echo "Podman user $1 found. Creating backups of volumes..."
      mkdir "$BACKUPDIR/$1"
      chmod 751 "$BACKUPDIR/$1"
      for volume in $(sudo -u $1 /usr/bin/podman volume ls --format {{.Name}}); do
        echo "Backing up volume $volume for user $1"
        touch "$BACKUPDIR/$1/$volume.tar"
        chown $1:$1 "$BACKUPDIR/$1/$volume.tar"
        chmod 660 "$BACKUPDIR/$1/$volume.tar"
        sudo -u $1 /usr/bin/podman volume export "$volume" --output "$BACKUPDIR/$1/$volume.tar"
        chown root:root "$BACKUPDIR/$1/$volume.tar"
        chmod 640 "$BACKUPDIR/$1/$volume.tar"
      done
    else
      echo "User $1 not found. Skipping."
    fi
  }
  
  backup_bitwarden() {
    backup_podman_volumes_for_user bitwarden
    cp /home/bitwarden/vaultwarden.conf $BACKUPDIR/bitwarden/
  }
  
  backup_etc() {
    echo 'Backing up /etc...'
    rsync -a /etc/ $BACKUPDIR/etc
  }
  
  backup_jellyfin_metadata() {
    echo 'Backing up Jellyfin metadata...'
    mkdir $BACKUPDIR/jellyfin
    rsync -a /var/lib/jellyfin/ $BACKUPDIR/jellyfin
  }
  
  backup_postprocess() {
    cd /srv/backup
    echo 'Compressing...'
    tar -czv --atime-preserve='system' -f $BACKUPDIR.tgz $BACKUPDIR
    echo 'Removing...'
    rm -rf $BACKUPDIR
  }
  
  backup_init
  
  backup_etc
  
  backup_postgres
  
  backup_bitwarden
  
  backup_jellyfin_metadata
  
  backup_postprocess

I'm needing to do something like this from one server to another.. the hard drive is 100% full and I can't bring myself to pay $310 to DO for a 900GB server just to dump the DB which needs to be thinned down. which come to find out even if I remove table entries for hours, postgres can't do the vacuum to return GB apparently unless it has another 360GB or so to do what it needs doing I guess.

So looking to pg-dump to a different server in a cheaper datacenter. Think I have something bookmarked talking about running rsync or something. Will be going into those options this week.

It's quite easy to have a copy of a Postgresql instance running on another host with streaming replication (always up to date).

This and backups will avoid great sorrows.

Indeed! That's been my route out of trouble on multiple occasions.

The setup for the first time takes some effort to get it working and that first fail-over event is both panic inducing and wonderful to see when that replica becomes a primary.

Like every disaster recovery plan, the secret is to regularly test it - do not wait until it is needed as that will be when you discover some small, but critical, element has been left out and things will not work either as expected or as needed. I've also been there and done that, unfortunately more than once - some lessons need learned more than once to get through.

While replication can be a key part of your disaster recovery plan, I think it's more often useful for operational HA, allowing you to perform database server maintenance with low (seconds) downtime. Actual real disasters where you need to fail over to the secondary in an uncontrolled manner and recovering the primary is not an option are rather rare events for any single database system.

In an actual disaster scenario, the simplest option is to always try recovering the primary database if at all possible, especially if your replication setup is a simple asynchronous one. If you just fail over to a secondary, you will have to deal with data loss from asynchronous replication due to replication delay and whatever effects that has on your application (monitor your replication lag!)

You can also set up a cluster with synchronous replicas for "true" DR, but that gets much more complicated, and honestly is likely unnecessary for most systems.

I know this is what some enterprise block disk servers do.

Running another host is difficult, especially when you only have one computer to run the server on. The server is an old Dell laptop (Inspiron 5547), and I do not have any more hard drives, SSDs or computers that I can keep always-on to run replication.

I've seen Postgresql tested on a Raspberry Pi. Performance is bad, but if you don't have much writing going on it might do as a slave.

Hope this helps someone out there.

https://github.com/wal-e/wal-e

^^ stream your write ahead logs to s3 + all the other bells and whistles you'd expect

Standard issue reminder, test your backups or they're not backups at all.

Reply on news.ycombinator.com