Setting up PostreSQL on ubuntu

cron-file

# Backup databases every day at 3:00 AM
# TODO: update the username and paths below
0  3  *   *   *     xxx    SHELL=/bin/bash /home/xxx/db-syncers/run-backup-tasks.sh > /home/xxx/db-syncers/backup.log 2>&1

delete-old-snapshots.sh

#!/bin/bash

BUCKET_NAME="xxx-db-snapshots"
FILES_TO_RETAIN=10

AWS_PROFILE="xxx"

echo "$(date '+%Y-%m-%d %H:%M:%S') Finding old snapshots to delete..."

files_to_delete=$(aws s3api list-objects-v2 \
  --profile "$AWS_PROFILE" \
  --bucket "$BUCKET_NAME" \
  --query "sort_by(Contents[?ends_with(Key, \`.dump\`)], &LastModified)[:-${FILES_TO_RETAIN}].Key" \
  --output text)

if [ -z "$files_to_delete" ]; then
  echo "$(date '+%Y-%m-%d %H:%M:%S') No deletable snapshots found"
  exit 0
fi

for file in $files_to_delete; do
  echo "$(date '+%Y-%m-%d %H:%M:%S') Deleting old snapshot: $file"
  aws s3 rm "s3://$BUCKET_NAME/$file" --profile "$AWS_PROFILE"
done

echo "$(date '+%Y-%m-%d %H:%M:%S') Deleting old snapshots complete."

POSTGRESQL_SETUP.md

Installing Postgresql

See this guide for more information.

IMPORTANT: xxx below are placeholders. Update with your app name, user name etc.

1. Add the PostgreSQL APT repository:

   sudo apt-get update
   sudo apt-get install -y postgresql-common
   sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
   sudo apt-get update

2. Install postgresql:

   sudo apt-get install postgresql-18

3. Open /etc/postgresql/*/main/postgresql.conf and change the following:

   sudo vim /etc/postgresql/18/main/postgresql.conf

   listen_addresses = '*'
   # ...
   shared_preload_libraries = 'pg_stat_statements'
   
   # ....
   
   #
   # INSERT Settings from https://pgtune.leopard.in.ua/#/ below
   # Make sure you select `Online transaction processing system` in DB Type
   #

4. Go to PgTune, select Online transaction processing system in DB Type and generate the configuration for the database. Add the configuration at the end of /etc/postgresql/*/main/postgresql.conf

   sudo vim /etc/postgresql/18/main/postgresql.conf

5. Change the postgres user password:

   sudo -u postgres psql

   ALTER USER postgres WITH PASSWORD '<password>';

6. Create the new users/databases as required:

   CREATE USER xxx WITH password 'pass';
   GRANT xxx to postgres;
   CREATE DATABASE xxx_prod OWNER xxx;

7. Log in as the newly created user to the create db and create pg_stat_statements extension:

   CREATE EXTENSION pg_stat_statements;

8. Allow connections from any host. Append the following at the end of /etc/postgresql/*/main/pg_hba.conf:

   sudo vim /etc/postgresql/18/main/pg_hba.conf

   # Private network
   host    all             all             10.0.0.0/16             scram-sha-256

   # Internet: ONLY if required. We normally don't need this as we connect externally using an
   # SSH tunnel.
   # hostssl all             all             0.0.0.0/0               scram-sha-256
   # hostssl all             all             ::/0                    scram-sha-256

9. Update the postgresql service to restart on failure:

   sudo systemctl edit postgresql@18-main

   And add the following lines in the file that opens up:

   [Service]
   Restart=always
   RestartSec=5

10. Restart the postgresql service:

    sudo systemctl daemon-reload
    sudo systemctl restart postgresql.service

11. Check that the service starts properly:

    sudo less /var/log/postgresql/postgresql-18-main.log

12. Test by connecting to the database from the local machine.

Configuring database backups

Using db-syncers

1. Install the aws-cli:

   sudo snap install aws-cli --classic

2. Configure the aws-cli with the credentials of the db-syncers user under the rbtz profile:

   aws configure --profile rbtz

3. Install jq and zstd:

   sudo apt-get install jq zstd

4. Add the following at the end of the ~/.bashrc file:

   if [ -f ~/.env ]; then
     source ~/.env
   fi

5. Add the relevant DB passwords to the ~/.env file:

   export XXX_PROD_DB_PASS="<password>"

6. Place the scripts in a directory like /home/xxx/data-syncers.

   Please update the scripts with the correct paths after you have created the directory.

7. Copy the cron-file in the repository to /etc/cron.d/xxx-db-backups.

   sudo cp /home/xxx/data-syncers/cron-file /etc/cron.d/xxx-db-backups

8. Check that the cron job was loaded successfully using:

   sudo service cron status

run-backup-tasks.sh

#!/bin/bash

set -e

if [ -f $HOME/.env ]; then
  source $HOME/.env
fi

SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

WEBHOOK_URL="https://hooks.slack.com/<webhook-url>"
MESSAGE=":package: New database snapshots created"

echo "$(date '+%Y-%m-%d %H:%M:%S') Starting DB backup tasks..."

${SCRIPT_DIR}/xxx-dump-prod.sh

${SCRIPT_DIR}/upload-snapshots.sh
${SCRIPT_DIR}/delete-old-snapshots.sh

rm -f ${SCRIPT_DIR}/*.dump

# Send notification to Slack
curl -X POST -H 'Content-type: application/json' \
  --data "{
    \"text\":\"$MESSAGE\",
  }" \
  "$WEBHOOK_URL" > /dev/null 2>&1

echo "$(date '+%Y-%m-%d %H:%M:%S') Completed DB backup tasks"

upload-snapshots.sh

#!/bin/bash

SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

BUCKET_NAME="xxx-db-snapshots"

AWS_PROFILE="xxx"

aws s3 sync ${SCRIPT_DIR} s3://${BUCKET_NAME}/ \
  --exclude '*' \
  --include '*.dump' \
  --profile ${AWS_PROFILE} \
  --no-progress

xxx-dump-prod.sh

#!/bin/bash

set -e

EXCLUDE_DATA_TABLES="worker_jobs"

SCRIPT_DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"

if [ -z "${XXX_PROD_DB_PASS}" ]; then
    echo "XXX_PROD_DB_PASS env variable must be specified"
    exit 1;
fi

export PGDATABASE=${XXX_PROD_DB_NAME:-XXX_prod}
export PGHOST=${XXX_PROD_DB_HOST:-localhost}
export PGUSER=${XXX_PROD_DB_USER:-XXX}
export PGPASSWORD=${XXX_PROD_DB_PASS}

timestamp=`date "+%Y-%m-%d-%H%M%S"`
filename=${PGDATABASE}-${timestamp}.dump

pg_dump --verbose --no-owner --format=c --compress=zstd:6 --exclude-table-data=${EXCLUDE_DATA_TABLES} > ${SCRIPT_DIR}/${filename}