Databases "hot" backup : PostgreSQL

I hadn't installed Postgres in a while and apparently my current software stack doesn't require it.  So I first had to install from the repos:

sudo apt-get install postgresql

I loaded some sample data using a tutorial available from https://www.postgresqltutorial.com/load-postgresql-sample-database.  However I had to fix the "CREATE DATABASE" statement in this file, apparently the codepage 1252 is specified (Windows) and my Linux Mint PostgreSQL didn't like it.  So :

• CREATE DATABASE dvdrental WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';
  

was replaced by:

• CREATE DATABASE dvdrental WITH TEMPLATE = template0 ENCODING = 'UTF8';

The pg_dumpall command is supposedly able to backup everything.  Trying manually first:

• sudo -u postgres pg_dumpall -f /var/tmp/postgres.backup.`date +%Y%m%d_%H%M%S`.psql

That worked fine, so added it in postgres's crontab:

$ sudo mkdir /var/backups/postgres

$ sudo chown postgres:postgres /var/backups/postgres

$ sudo crontab -lu postgres

# m h  dom mon dow   command

50 16 * * *  pg_dumpall -f /var/backups/postgres/postgres.backup.`date +%Y%m%d_%H%M%S`.psql

Well now that doesn't work anymore, for some reason the datestamp part of the command doesn't work quite right under cron.  So I wrote a short script to do it:

$ more dailybackup.sh
#! /bin/sh

pg_dumpall -f /var/backups/postgres/postgres.backup.`date +%Y%m%d_%H%M%S`.psql

That's OK now.

$ sudo crontab -lu postgres
# m h  dom mon dow   command
10 17 * * * /home/luc/Projects/dvdrentals/dailybackup.sh
$ ls -l /var/backups/postgres
total 96
-rw-rw-r-- 1 postgres postgres 45710 nov.  30 17:06 postgres.backup.20201130_170614.psql
-rw-rw-r-- 1 postgres postgres 45710 nov.  30 17:10 postgres.backup.20201130_171001.psql