Scheduled backups in place and confirm occurring

[zipperer]

See pg_dump info on #23 .

One alternative for scheduled backups: use cron That is, set up a scheduled job in cron to perform pg_dump on a schedule.

One alternative for confirm occurring: wait a few days and confirm backups are stored in intended place and a restore from the backup works.

[zipperer]

For cron backup, at first try to do as bash command/script so don't need other dependencies (e.g. python)

[zipperer]

Plan:

• use ubuntu container
• schedule backup for every minute
• wait
• use backup to restore

[zipperer]

Check whether cron installed determine how to install cron determine how to put timestamp in filename of output

• e.g.

  $ echo "my_backup_filename_$(date "+%Y-%m-%d--%H-%M-%S")"
  my_backup_filename_2023-11-30--11-54-28

[zipperer]

Shell interactions while I experimented: 20231130-shell-interactions-0.txt

Example crontab from docker container: 20231130_example_crontab_from_docker_container.txt

Example /root/.pgpass from docker container: example_slash_root_slashdot_pgpass.txt

I will distill these into steps.

steps

• install cron or confirm it is installed
  • apt update
  • apt install cron
• edit cron table for user
  • crontab -e
• add line at bottom * * * * * echo $(date) >> /example_cron_output.txt with newline at end of file
• wait until new minute and observe result in /example_cron_output.txt

debug

• man cron
• man crontab
• serverfault.com post

psql command in crontab file

• in order to provide a password for psql command, make a file ~/.pgpass
  • see the example above
  • stackoverflow post

psql options

• psql -Fc ...
  • documentation says compressed by default

when use psql -Fc ... to dump, use pg_restore to restore

• documentation provides example of reloading into a new database
  • $ createdb --username=postgres -T template0 db_from_restore3
  • pg_restore --username postgres -d db_from_restore3 /my_postgres_backups/postgres_backup_20231130_19_48_01_Fc_format.sql

example formatting cron action as script

crontab file

* * * * * /my_postgres_dump_script.sh 2>> /my_script_output.txt

my_postgres_dump_script.sh

#!/bin/bash

TIMESTAMP=$(date "+%Y%m%d_%H_%M_%S")
echo "script start: ${TIMESTAMP}" >> /my_script_output.txt
POSTGRES_USERNAME=postgres
POSTGRES_DBNAME=postgres
POSTGRES_BACKUP_DIRECTORY=my_postgres_backups
POSTGRES_BACKUP_FILE_PREFIX=postgres_backup_
POSTGRES_BACKUP_FILE_SUFFIX=
PG_DUMP_OPTIONS=-Fc
if [[ $PG_DUMP_OPTIONS -eq '-Fc' ]]; then
        POSTGRES_BACKUP_FILE_SUFFIX=_Fc_format
fi
POSTGRES_BACKUP_FILE_EXTENSION=.sql
pg_dump ${PG_DUMP_OPTIONS} --username=$POSTGRES_USERNAME --dbname=$POSTGRES_DBNAME > /${POSTGRES_BACKUP_DIRECTORY}/${POSTGRES_BACKUP_FILE_PREFIX}${TIMESTAMP}${POSTGRES_BACKUP_FILE_SUFFIX}${POSTGRES_BACKUP_FILE_EXTENSION}

#pg_dump -Fc --username=postgres --dbname=postgres > /my_postgres_backups/postgres_backup_${TIMESTAMP}_Fc_format.sql

echo 'script done' >> /my_script_output.txt
echo $(date) >> /my_script_output.txt

[zipperer]

Aside to think about: how to put crontab file under version control?

Can I make the official crontab file a symlink to a file under version control?

[zipperer]

Aside to think about: how to put crontab file under version control? Can I make the official crontab file a symlink to a file under version control?

One option to advance toward a crontab file under version control:

• in a git repository, make a file -- call it example_crontab_file.txt
• in the file put cron statements, e.g. * * * * * echo $(date) >> /Users/andrew/file-that-cron-updates.txt
• view current state of crontab file: $ crontab -l
• then execute $ crontab example_crontab_file.txt
• view updated state of crontab file: $crontab -l

Executing $ crontab example_crontab_file.txt makes example_crontab_file.txt the crontab file for the current user.

This does not guarantee that cron will see the current state of the file in the repository, because the user could call crontab other-file with another file or crontab -e and edit it.

[zipperer]

Aside to think about: how to put crontab file under version control? Can I make the official crontab file a symlink to a file under version control?

Another option under Ubuntu (which has /etc/cron.d/):

• in a git repository, make a file -- call it example_crontab_file.txt
• in the file put cron statements, e.g. * * * * * root echo $(date) >> /example/path/file-that-cron-updates.txt
• in /etc/cron.d/, ln -s /path/to/git/repository/example_crontab_file.txt example_crontab_file to make a symlink from /etc/cron.d/example_crontab_file to /path/to/git/repository/example_crontab_file.txt

[zipperer]

On 20240120 we followed the steps here to set up a cronjob to perform

pg_dump -Fc --username=postgres --dbname=grace_and_truth_db > /my_postgres_backups/postgres_backup_${TIMESTAMP}_Fc_format.sql