This is an internal documentation. There is a good chance you’re looking for something else. See Disclaimer.
Database Backups¶
Get the Latest Backup or Backup Made During CD¶
The most recent backup (usually from yesterday evening) as well as
backups made during CD can be obtained using tco db-backup:
List backups of an installation:
$ tco db-backup list fdp tco db-backup restore db5.stage.tocco.cust.vshn.net db6.stage.tocco.cust.vshn.net:file:manual/nice_fdp-2025-04-28T11:22:16+02:00.dump db_name: nice_fdp time: 2025-04-28 11:22:16 ( 0 d 22 h 54 min ago) tco db-backup restore db5.stage.tocco.cust.vshn.net db6.stage.tocco.cust.vshn.net:file:nice_fdp.dump db_name: nice_fdp time: 2025-04-28 22:22:19 ( 0 d 11 h 54 min ago)Use
tco db-backup fdp/(note the trailing/) ortco db-backup list db1.prodto list all backups on the server.Then restore using command printed above:
$ tco db-backup restore db5.stage.tocco.cust.vshn.net db6.stage.tocco.cust.vshn.net:file:nice_fdp.dump [db5.stage.tocco.cust.vshn.net INFO tocco_backup_transfer::db] Dump restored as "nice_fdp_restored_fbd64778". tco db-connect db5.stage.tocco.cust.vshn.net/nice_fdp_restored_fbd64778
Note
This backups are located at
/var/lib/postgresql-backup/or one of it’s subdirectories on the slave.Backups made during CD are removed after a few days. Older backups can be found in the archives. See Get Backup from Archive.
CD prints the
tco db-backup restorecommand in the “dump DB” build step for convenience.
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 4¶
Server |
Role |
Daily Backups |
Triggered backups (during CD) |
|---|---|---|---|
db7.prod.tocco.cust.vshn.net |
master |
n/a |
|
db8.prod.tocco.cust.vshn.net |
slave |
|
|
Staging Cluster 1¶
Server |
Role |
Daily Backups |
Triggered backups (during CD) |
|---|---|---|---|
db1.stage.tocco.cust.vshn.net |
master |
n/a |
|
db2.stage.tocco.cust.vshn.net |
slave |
|
|
Staging Cluster 3¶
Server |
Role |
Daily Backups |
Triggered backups (during CD) |
|---|---|---|---|
db5.stage.tocco.cust.vshn.net |
master |
n/a |
|
db6.stage.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.
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