6 ms·

Recovering a PostgreSQL database after a hard drive failure

107 points by d4a 5y ago

frogger8 5y ago
And for next time. https://gist.github.com/AtulKsol/520f5d0c9a313edea7254701f6db8966 https://gist.github.com/AtulKsol/520f5d0c9a313edea7254701f6d...
- smbv 5y ago
  Thank you very much! I'm planning on using this for my backups (in conjunction with rsync -a, and podman volume export).
- smbv 5y ago
  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
- stevenicr 5y ago
  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.
csdvrx 5y ago
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 https://github.com/csdvrx/pg_csdvrx Or just watch the demo on https://raw.githubusercontent.com/csdvrx/pg_csdvrx/master/pg_csdvrx.gif https://raw.githubusercontent.com/csdvrx/pg_csdvrx/master/pg... where I corrupt a table to show how it works
- brightball 5y ago
  Thanks for putting this together. I could have seriously used it a few years ago.
  - csdvrx 5y 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 :)
- smbv 5y ago
  Working today to create a backup script and bind it to a systemd timer. Hope it all goes well.
  - csdvrx 5y ago
    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...
  - T3OU-736 5y ago
forinti 5y ago
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.
- swlp21 5y ago
  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.
  - chousuke 5y ago
    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.
- OnlyMortal 5y ago
  I know this is what some enterprise block disk servers do.
- smbv 5y ago
  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.
ghotli 5y ago
Hope this helps someone out there. https://github.com/wal-e/wal-e 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.

107 points by d4a 5y ago

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

smbv 5y ago
Thank you very much! I'm planning on using this for my backups (in conjunction with rsync -a, and podman volume export).
smbv 5y ago
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
stevenicr 5y ago
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.

csdvrx 5y ago

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 https://github.com/csdvrx/pg_csdvrx Or just watch the demo on https://raw.githubusercontent.com/csdvrx/pg_csdvrx/master/pg_csdvrx.gif https://raw.githubusercontent.com/csdvrx/pg_csdvrx/master/pg... where I corrupt a table to show how it works

brightball 5y ago
Thanks for putting this together. I could have seriously used it a few years ago.
- csdvrx 5y 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 :)
smbv 5y ago
Working today to create a backup script and bind it to a systemd timer. Hope it all goes well.
- csdvrx 5y ago
  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...
- T3OU-736 5y ago

forinti 5y ago

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.

swlp21 5y ago
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.
- chousuke 5y ago
  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.
OnlyMortal 5y ago
I know this is what some enterprise block disk servers do.
smbv 5y ago
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.

ghotli 5y ago

Hope this helps someone out there. https://github.com/wal-e/wal-e 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.