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.

1. Find the slave in Backup Locations

2. 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.

3. List available dump, check timestamp:

   ssh "$slave" ls -lh "'$backup_path'"
   

4. Create target DB:

   ssh "$master" psql -c "'CREATE DATABASE \"$target_db_name\" OWNER \"$db_role\"'" postgres
   

5. Restore database:

   ssh "$slave" cat "'$backup_path'" \
   | ssh "$master" pg_restore --role "'$db_role'" "'$target_db_name'"
   

6. 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 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.

Experimental Secondary Backups

Warning

You need root access to access secondary backups.

Secondary backups are only available from on slaves and you need to copy / pipe dumps to master.

List available archives:

# db2.prod / db4 / db8.prod / … (even number)
$ slave=${NAME_OF_SLAVE}

$ ssh $slave sudo -i borg list
db-2023-11-02T20:39:53               Thu, 2023-11-02 20:39:54 [fd1be456750d86acdd5cada5a1a23e7e01688ca209e57cd8d5a01e1a4f91dace]
db-2023-11-03T10:18:54               Fri, 2023-11-03 10:18:55 [44417f609c80ac6c7db1b481720a25bc2ee2496c6d6782a8b69811210fbdb061]
db-2023-11-04T04:12:01               Sat, 2023-11-04 04:12:07 [de0640f91c2fa278b7c37154d538ea3946766c5dfe62df1cc9b0aa1fe07a3da6]
…

List content of archive

$ archive=db-2023-11-03T10:18:54
$ ssh $slave sudo -i borg list ::$archive
…
-rw-r----- postgres tocco  11394492 Fri, 2023-11-03 09:40:26 var/lib/postgresql-backup/nice_slf.dump
-rw-r----- postgres tocco  15520659 Fri, 2023-11-03 09:40:31 var/lib/postgresql-backup/nice_slf_history.dump
-rw-r----- postgres tocco  596834445 Fri, 2023-11-03 09:41:15 var/lib/postgresql-backup/nice_sme.dump
-rw-r----- postgres tocco  1078774579 Fri, 2023-11-03 09:42:29 var/lib/postgresql-backup/nice_sme_history.dump
-rw-r----- postgres tocco  9777321893 Fri, 2023-11-03 10:08:15 var/lib/postgresql-backup/nice_sps.dump
-rw-r----- postgres tocco  6006788872 Fri, 2023-11-03 09:46:45 var/lib/postgresql-backup/nice_sps_history.dump
-rw-r----- postgres tocco  1502473523 Fri, 2023-11-03 09:43:24 var/lib/postgresql-backup/nice_spv.dump
-rw-r----- postgres tocco  1759714387 Fri, 2023-11-03 09:44:24 var/lib/postgresql-backup/nice_spv_history.dump
…

Restore to from backup archives on slave directly to Postgres on master:

# db1.prod / db3 / db7.prod / … (odd number)
$ master=${NAME_OF_MASTER}

# nice_sps / nice_bbg (i.e. nice_${INSTALLATION_NAME})
$ owner=${OWNER_OF_RESTORED_DB}

# nice_sps_restore / nice_sps_… / …
$ target_db=${NAME_OF_RESTORED_DB}

path=var/lib/postgresql-backup/nice_sps.dump

$ ssh $master psql -c "'CREATE DATABASE $target_db WITH OWNER $owner'" postgres
$ ssh $slave sudo -i borg extract --stdout ::$archive $path \
    | ssh $master pg_restore --no-acl --no-owner --role $owner -d $target_db
$ ssh $master psql -c ANALYZE $target_db

Footnotes

[1] (1,2,3,4,5,6,7,8,9,10)

The output of the dump step in CD prints the server on which the dump is located as well as the path to it on the server.