Setting up a new cluster

if not already present, include postgresql-server (our states will manage some of the setup, but currently won't actually install the server for you)

the postgresql packages will have created a "main" cluster during installation, which will be listening on port 5432. If you need a separate cluster for the service:

$ sudo pg_createcluster ${postgresql_version} ${cluster_name}
$ sudo systemctl start postgresql@${postgresql_version}-${cluster_name}.service

- cluster names should only contain the characters "[0-9a-z.]"

$ sudo -u postgres createuser -p ${cluster_port} -D -P -R -S ${service}
$ sudo -u postgres createdb -p ${cluster_port} -O ${service} ${service}

Make sure that the cluster configuration (/etc/postgresql/${version}/${cluster_name}/postgresql.conf) contains:

listen_addresses = '*'

ssl = on
ssl_cert_file = '/etc/ssl/debian/certs/thishost-server.crt'
ssl_key_file = '/etc/ssl/private/thishost-server.key'

archive_mode = on
archive_command = '/usr/local/bin/pg-backup-file ${cluster_name} WAL %p'
archive_timeout = 1h

run puppet on the database server

run puppet on both backup servers this will update debbackup/.pgpass with an entry for the new cluster. make note of the password (it is the same on both backup servers, and unique to the cluster)

$ sudo -u postgres createuser -p ${cluster_port} -D -P -R -S --replication debian-backup

supply the password you noted earlier

run a base backup on each backup server $ sudo -u debbackup postgres-make-base-backups ${database_server}.debian.org:${cluster_port}

User/permission stuff with pg

revoke ALL on DATABASE "XXX" from public;

grant CONNECT, CREATE, TEMPORARY on DATABASE "XXX" to "YYY";
grant CONNECT on DATABASE "XXX" to public;
grant TEMPORARY on DATABASE "XXX" to guest;

REVOKE ALL ON SCHEMA public from public;
GRANT USAGE ON SCHEMA public TO public;
GRANT ALL ON SCHEMA public TO "YYY";

\dn+
\dp+

select * from pg_database ;



sudo -u postgres psql "XXX" << EOF
CREATE LANGUAGE plpgsql;
EOF

apt install postgresql-debversion

Pulling an initial checkpoint from another host to start a standby

Create the cluster using pg_createcluster on the standby, to initialize the Debian configuration. We will throw away the data afterwards.

On the new standby:

# systemctl stop postgresql@$ver-$cluster
# cd /var/lib/postgresql/$ver/$cluster
# rm -r * .nobackup
# sudo -u postgres /usr/lib/postgresql/15/bin/pg_basebackup \
    --pgdata=. \
    --wal-method=stream \
    --host=$SOURCE_HOST \
    --port=$SOURCE_PORT \
    --username=repuser-$TARGET_MACHINE \
    --verbose \
    --checkpoint=fast \
    --write-recovery-conf  # writes standby.signal
# egrep '^#' postgresql.auto.conf | sponge postgresql.auto.conf
# tail -F /var/log/postgresql/postgresql-$ver-$cluster.log &
# systemctl start postgresql@$ver-$cluster

On the backuphost(s):

# cd /srv/backups/pg/$SOURCE_HOST
# grep 'LABEL: pg_basebackup base backup' *.backup
# # move these spurious backup files away (e.g. to a ../$SOURCE_HOST.old dir) or our monitoring will yell

Upgrading a cluster

IF THERE IS NO REPLICATION HIERARCHY.

On the host:

# pg_upgradecluster -m link $old_ver $cluster
# mkdir -p /etc/postgresql/${old_ver}.bak
# mv /etc/postgresql/$old_ver/$cluster /etc/postgresql/${old_ver}.bak  # must not have duplicate entries
# puppet agent -t  # to update the information in Puppet

On the backuphost(s):

# puppet agent -t
# sudo -u debbackup mkdir /srv/backups/pg/${host}.old
# sudo -u debbackup mv /srv/backups/pg/${host}/${cluster}.* /srv/backuos/pg/${host}.old
# sudo -u debbackup /usr/local/bin/postgres-make-base-backups $host.debian.org:$db_port
# sudo -u debbackup /usr/lib/nagios/plugins/dsa-check-backuppg | grep BASE  # should be empty

Consider adding an at job to remove "${host}.old/${cluster}.*" in 30-45 days. If all clusters have been migrated, have the job just remove ${host}.old entirely.

Possibly obsolete notes on cleaning up backups from the previous version.

as debbackup:

$ cd /srv/backups/pg
$ cd ${host}
$ # find the first base backup for the new version; note the filename
$ find . -type f -name "${cluster}.WAL.*" \! -newer "${cluster}.BASE.${backuphost}.debian.org-${datestamp}-${host}.debian.org-${cluster}-${new_ver}-backup.tar.gz" -print0 | xargs -0r mv -t ../${host}.old
$ find . -type f -name "${cluster}.BASE.*-${cluster}-${old_ver}-backup.tar.gz" -print0 | xargs -0r mv -t ../${host}.old/
$ tar -zxvf "${cluster}.BASE.${backuphost}.debian.org-${datestamp}-${host}.debian.org-${cluster}-${new_ver}-backup.tar.gz" --to-stdout backup_label
backup_label
START WAL LOCATION: 126/A3000078 (file 0000000100000126000000A3)
CHECKPOINT LOCATION: 126/A3006C40
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2025-08-17 15:34:42 UTC
LABEL: backuphost2.debian.org-20250817-153438-danzi.debian.org-wannabuild-15-backup
START TIMELINE: 1
$ # from the above:
$ # START WAL LOCATION: 126/A3000078 (file 0000000100000126000000A3)
$ # - $file = 0000000100000126000000A3
$ mv ../${host}.old/${cluster}.WAL.${file} .
$ mv ../${host}.old/${cluster}.WAL.${file}.*.backup .
$ grep "STOP WAL LOCATION" ${cluster}.WAL.${file}.*.backup
STOP WAL LOCATION: 126/A533C368 (file 0000000100000126000000A5)
$ # if the start and stop files are different, ensure that all files within the range are present
$ # mv ../${host}.old/${cluster}.WAL.${eachfile} .
$ /usr/lib/nagios/plugins/dsa-check-backuppg | grep ${cluster}