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:

  1. 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 /) or tco db-backup list db1.prod to list all backups on the server.

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

/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