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 /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

as debbackup:

$ cd /srv/backups/pg
$ mkdir ${host}.old
$ 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}

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.