Olivier Berger <[email protected]> writes:
> Hi.
>
> TL;DR Has anyone experience with PITR backup/restoring in PostGreSQL ?
>
>
SNIP
>
> I'll try and investigate, to compare with the psql < dump current option.
>
So I've tried and follow the guidelines from [0] and obtained
interesting results.
In principle here's an excerpt of install.sh that should create a backup
(not yet tested as such) :
# stop DB
invoke-rc.d postgresql stop
# configure the backup
cp /etc/postgresql/9.1/main/postgresql.conf
/etc/postgresql/9.1/main/postgresql.conf.orig
cat >> /etc/postgresql/9.1/main/postgresql.conf <<EOF
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/backup_in_progress || (test !
-f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f)'
EOF
mkdir /var/lib/postgresql/archive/
chown -R postgres.postgres /var/lib/postgresql/archive/
# start again the DB
invoke-rc.d postgresql up
# make backup
touch /var/lib/postgresql/backup_in_progress
sudo -u postgres psql -c "select pg_start_backup('hot_backup');"
tar -cf /var/lib/postgresql/backup.tar /var/lib/postgresql/9.1/
sudo -u postgres psql -c "select pg_stop_backup();"
rm /var/lib/postgresql/backup_in_progress
tar -rf /var/lib/postgresql/backup.tar /var/lib/postgresql/archive/
# stop DB
invoke-rc.d postgresql stop
# revert config
rm /etc/postgresql/9.1/main/postgresql.conf
mv /etc/postgresql/9.1/main/postgresql.conf.orig
/etc/postgresql/9.1/main/postgresql.conf
# start again
invoke-rc.d postgresql up
I hope this works actually, as I did the above manually, and haven't had
time to test.
And here's the restoration in db_reload.sh :
echo "Restore database from backup"
cd /var/lib/postgresql/9.1
rm -fr main
rm -fr archive
tar xf ../backup.tar
mv var/lib/postgresql/9.1/main ./
mv var/lib/postgresql/archive/ ./
rm -fr var
echo "restore_command = 'cp /var/lib/postgresql/9.1/archive/%f %p'" >
/var/lib/postgresql/9.1/main/recovery.conf
echo "Starting the database"
if type invoke-rc.d 2>/dev/null
then
invoke-rc.d postgresql start
else
service postgresql start
fi
echo "Waiting for database to be up..."
i=0
while [ $i -lt 10 ] && ! is_db_up ; do
echo "...not yet ($(date))..."
i=$(( $i + 1 ))
sleep 5
done
if is_db_up ; then
echo "...OK"
else
echo "... FAIL: database still down?"
fi
I'm testing this inside the docker container (well, a modified version
cause the start/stop are different with runit), and it seems to rock !
This is quick and dirty and needs polishing, tests that the restoration
works actually, etc. but gives you an idea...
PITR is the way, baby :-)
Best regards,
>
> [0] http://www.postgresql.org/docs/9.1/static/continuous-archiving.html
>
--
Olivier BERGER
http://www-public.telecom-sudparis.eu/~berger_o/ - OpenPGP-Id: 2048R/5819D7E8
Ingenieur Recherche - Dept INF
Institut Mines-Telecom, Telecom SudParis, Evry (France)
_______________________________________________
Fusionforge-general mailing list
[email protected]
http://lists.fusionforge.org/cgi-bin/mailman/listinfo/fusionforge-general
