This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.
Database Backups¶
Get the Latest Backup¶
Databases are dumped to disk on the slave at /var/lib/postgresql-backup/ before they are archived. If you need the most
recent backup, you can get it from there.
Find the slave in Backup Locations
Set required variables:
db_name=${DB_NAME} master=${MASTER_HOSTNAME} slave=${SLAVE_HOSTNAME} backup_path=/var/lib/postgresql-backup/${db_name}.dump target_db_name=${db_name}_restore_$(date +%+4Y_%m_%dT%H_%M) db_role=$db_name # ¹Replace ${BOLD} with actual values.
¹
$db_roleusually matchesnice_${installation_name}which, in turn, matches$db_name. Adjustdb_roleif this isn’t the case.List available dump, check timestamp:
ssh "$slave" ls -lh "'$backup_path'"
Create target DB:
ssh "$master" psql -c "'CREATE DATABASE \"$target_db_name\" OWNER \"$db_role\"'" postgres
Restore database:
ssh "$slave" cat "'$backup_path'" \ | ssh "$master" pg_restore --role "'$db_role'" "'$target_db_name'"
Print name of target DB:
echo "$target_db_name"
Get Backup Created by CD¶
Dumps created by checking the option “dump database” in TeamCity are created in /var/lib/postgresql-backup/deploy-dumps/.
Dumps in that directory are removed after a few days. If a dump has been removed already, you’ll have to extract it out of the
archive, see below.
Name of the DB server and path to the backup can be found in the TeamCity build logs, step dump.
If the dumps was done on the slave (see Backup Locations):
Follow the steps in Get the Latest Backup and set
$backup_pathto the location printed in the TeamCity logs.
Else, if dump was done on master:
Follow instructions in Restore Database.
Backup Locations¶
Production Cluster 1¶
Server |
Role |
Daily Backups |
Triggered backups (during CD) |
|---|---|---|---|
db1.prod.tocco.cust.vshn.net |
master |
n/a |
|
db2.prod.tocco.cust.vshn.net |
slave |
|
|
Production Cluster 2¶
Server |
Role |
Daily Backups |
Triggered backups (during CD) |
|---|---|---|---|
db3.tocco.cust.vshn.net |
master |
n/a |
|
db4.tocco.cust.vshn.net |
slave |
|
|
Production Cluster 3¶
Server |
Role |
Daily Backups |
Triggered backups (during CD) |
|---|---|---|---|
db5.prod.tocco.cust.vshn.net |
master |
n/a |
|
db6.prod.tocco.cust.vshn.net |
slave |
|
|
Get Backup from Archive¶
The directory /var/lib/postgresql-backup/, which contains dumps made during backups and deployments, are archived
daily using BURP. If you need backups/dumps no longer in these directories, you can extract them from
the archive.
Warning
You need root access to access the archive.
Hint
Other directories, like /home/, /usr/local/, … are included in the archive too.
List Available Archives¶
$ sudo burp -Q -C ${NAME_OF_SLAVE} -a list
Backup: 0000155 2018-01-31 03:03:22 +0100 (deletable)
Backup: 0000162 2018-02-09 01:14:05 +0100 (deletable)
Backup: 0000169 2018-02-16 01:10:54 +0100 (deletable)
Backup: 0000176 2018-02-23 01:06:28 +0100 (deletable)
…
Hint
If -C ${NAME_OF_SLAVE} is not specified, the archives from the current host are
listed. -C allows you to restore a dumps made on the slave directly on the master.
See tables above to find out which slave belong to which master.
List Files in Archive¶
Show the content of directory /var/lib/postgresql-backup/ in archive 0000169.
$ sudo burp -Q -C ${NAME_OF_SLAVE} -a list -b 0000169 -r '^/var/lib/postgresql-backup/'
Backup: 0000169 2018-02-16 01:10:54 +0100 (deletable)
With regex: ^/var/lib/postgresql-backup/
/var/lib/postgresql-backup/nice_awpf.dump
/var/lib/postgresql-backup/nice_awpftest.dump
/var/lib/postgresql-backup/nice_bnftest.dump
/var/lib/postgresql-backup/nice_dghtest.dump
/var/lib/postgresql-backup/nice_esrplus.dump
Extract File from Archive¶
Restore nice_bnftest.dump from backup 0000169 to directory ~/restores/.
$ mkdir -p ~/restores/ $ sudo burp -Q -C ${NAME_OF_SLAVE} -a restore -b 0000169 -d ~/restores/ -r '^/var/lib/postgresql-backup/postgres-nice_bnftest.dump' … 2018-03-09 16:01:30 +0100: burp[23156] restore finished $ ls -lh ~/restores/var/lib/postgresql-backup/nice_bnftest.dump -rw-rw-r-- 1 postgres postgres 4.1G Feb 16 01:26 /home/peter.gerber/restores/var/lib/postgresql-backup/nice_bnftest.dump
Restore Database explains how to restore a dump.
Footnotes