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_role usually matches nice_${installation_name} which, in turn, matches $db_name. Adjust db_role if 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_path to 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	`/var/lib/postgresql-backup/deploy-dumps/` [1]
db2.prod.tocco.cust.vshn.net	slave	`/var/lib/postgresql-backup/`	`/var/lib/postgresql-backup/deploy-dumps/` [1]

Production Cluster 2¶

Server	Role	Daily Backups	Triggered backups (during CD)
db3.tocco.cust.vshn.net	master	n/a	`/var/lib/postgresql-backup/deploy-dumps/` [1]
db4.tocco.cust.vshn.net	slave	`/var/lib/postgresql-backup/`	`/var/lib/postgresql-backup/deploy-dumps/` [1]

Production Cluster 4¶

Server	Role	Daily Backups	Triggered backups (during CD)
db7.prod.tocco.cust.vshn.net	master	n/a	`/var/lib/postgresql-backup/deploy-dumps/` [1]
db8.prod.tocco.cust.vshn.net	slave	`/var/lib/postgresql-backup/`	`/var/lib/postgresql-backup/deploy-dumps/` [1]

Staging Cluster 1¶

Server	Role	Daily Backups	Triggered backups (during CD)
db1.stage.tocco.cust.vshn.net	master	n/a	`/var/lib/postgresql-backup/deploy-dumps/` [1]
db2.stage.tocco.cust.vshn.net	slave	`/var/lib/postgresql-backup/`	`/var/lib/postgresql-backup/deploy-dumps/` [1]

Staging Cluster 2¶

Server	Role	Daily Backups	Triggered backups (during CD)
db3.stage.tocco.cust.vshn.net	master	n/a	`/var/lib/postgresql-backup/deploy-dumps/` [1]
db4.stage.tocco.cust.vshn.net	slave	`/var/lib/postgresql-backup/`	`/var/lib/postgresql-backup/deploy-dumps/` [1]

Staging Cluster 3¶

Server	Role	Daily Backups	Triggered backups (during CD)
db5.stage.tocco.cust.vshn.net	master	n/a	`/var/lib/postgresql-backup/deploy-dumps/` [1]
db6.stage.tocco.cust.vshn.net	slave	`/var/lib/postgresql-backup/`	`/var/lib/postgresql-backup/deploy-dumps/` [1]

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