Dev1 Galaxy Forum / Postgresql under runit and OS upgrade

Re: Postgresql under runit and OS upgrade

dummy@example.com (Geoff 42) — Sat, 30 Dec 2023 16:10:16 +0000

My /etc/sv/postgresql.main directory currently looks like this :-

ls -alF /etc/sv/postgresql.main/

total 28
drwxr-xr-x 3 root root 4096 Dec 30 11:38 ./
drwxr-xr-x 48 root root 4096 Dec 30 13:40 ../
lrwxrwxrwx 1 root root 12 Dec 29 15:50 config -> config.multi*
-rwxr-xr-x 1 root root 1813 Dec 26 16:43 config.auto*
-rwxr-xr-x 1 root root 163 Dec 30 11:30 config.fixed*
-rwxr-xr-x 1 root root 1939 Dec 30 13:56 config.multi*
drwxr-xr-x 2 root root 4096 Dec 30 11:41 log/
-rwxr-xr-x 1 root root 290 Dec 29 15:06 run*
lrwxrwxrwx 1 root root 36 Dec 29 15:54 supervise -> /run/runit/supervise/postgresql.main/

and the log directory :-

ls -alF /etc/sv/postgresql.main/log

total 12
drwxr-xr-x 2 root root 4096 Dec 30 11:41 ./
drwxr-xr-x 3 root root 4096 Dec 30 11:38 ../
-rwxr-xr-x 1 root root 221 Dec 29 14:54 run*
lrwxrwxrwx 1 root root 40 Dec 29 15:54 supervise -> /run/runit/supervise/postgresql.main.log/

Re: Postgresql under runit and OS upgrade

dummy@example.com (Geoff 42) — Sat, 30 Dec 2023 16:04:35 +0000

The latest version of config.auto steps through the database names in the various versions looking for the first one which is set to auto start.

# Automatically find the version and database name
# taken by postgresql/run

PGBINROOT="/usr/lib/postgresql"
RUNROOT="/var/run/postgresql"
CONFROOT="/etc/postgresql"

# Typical sort of values. I think "main" is the default name for a db.
# VERSION="13"
# DB="main"
# but we will look for the actual values in use.

# We look through the versions and then for each one, look through the db names
# stopping at the first one which is set to auto start in start.conf

start=''

get_db_name() {
    local c
    DB=''
    for c in "$CONFROOT"/"$VERSION"/*; do
	[ -e "$c"/postgresql.conf ] || continue

	DB=$(basename "$c")
        echo "$c ${VERSION} ${DB}"

        # evaluate start.conf, is it to be auto started?
	if [ -e "$c/start.conf" ]; then
	    start=$(sed 's/#.*$//; /^[[:space:]]*$/d; s/^\s*//; s/\s*$//' "$c/start.conf")
	else
	    start=auto
	fi
        echo "$c yields ${VERSION} ${DB} set to $start"

	[ "$start" = "auto" ] || continue

        # we have a db configured to auto start.

        return
    done
    # we have no valid database names in this version
    echo "Version $VERSION no database found set to auto start."
    DB=''
    return
}

get_version() {
    VERSION=''

    for ver in "$PGBINROOT"/* ; do
        VERSION=${ver##*/}
        echo "Version $VERSION."

        if [ -x "$PGBINROOT"/"$VERSION"/bin/pg_ctl ]; then

            # we have a valid version, now look through the db names
            get_db_name
            if [ -n "$DB" ] && [ "$start" = "auto" ]; then
                echo "$VERSION $DB $start"
                return
            fi
      	fi

    done
    # we have no valid databases to start
    echo "No databases set for auto start."
    VERSION=''
    DB=''
    exit 162
}

# Start here...

get_version

STATDIR="${RUNROOT}/${VERSION}-${DB}.pg_stat_tmp"

Re: Postgresql under runit and OS upgrade

dummy@example.com (Geoff 42) — Sat, 30 Dec 2023 16:00:13 +0000

The config.multi script is intended to extract the database name from the service name and then find the first version which is set to auto start.

# find the version and database name
# taken by postgresql/run
# This copy is for when you specify one of several named DBs in the service name
# e.g. postgresql.mouse for the mouse database, but we find the first postgresql
# version which we can run.

PGBINROOT="/usr/lib/postgresql"
RUNROOT="/var/run/postgresql"
CONFROOT="/etc/postgresql"

# Typical sort of values. I think "main" is the default name for a db.
# VERSION="15"
# DB="main"
# but we will look for the actual values in use.

# Pick up the DB name from the directory name
# NAME="postgresql.mouse"

NAME="${PWD}"
DB="${NAME#*.}"
echo "$DB"

# We look through the versions and then for each one, check for the db name
# and checking if it is set to auto start in start.conf

start=''

get_db_name() {
	local c

	for c in "$CONFROOT"/"$VERSION"/"$DB"; do
		[ -e "$c"/postgresql.conf ] || continue
		echo "$c ${VERSION} ${DB}"

		# evaluate start.conf, is it to be auto started?
		if [ -e "$c/start.conf" ]; then
			start=$(sed 's/#.*$//; /^[[:space:]]*$/d; s/^\s*//; s/\s*$//' "$c/start.conf")
		else
			start=auto
		fi
		echo "$c ${VERSION} ${DB} is set to $start"

		[ "$start" = "auto" ] || continue

		# we have a db configured to auto start.

		return
	done

	# we have no valid database names in this version
	echo "Version $VERSION $DB not set to auto start."
	return
}

get_version() {
	VERSION=''

	for ver in "$PGBINROOT"/*; do
		VERSION=${ver##*/}
		echo "Version $VERSION."

		if [ -x "$PGBINROOT"/"$VERSION"/bin/pg_ctl ]; then

			# we have a valid version.
			# have we picked up the wanted db name?
			get_db_name
			if [ -n "$DB" ] && [ "$start" = "auto" ]; then
				echo "$VERSION $DB $start"
				return
			fi
		fi

	done
	# we have no valid databases to start
	echo "No databases set for auto start."
        sv down /etc/sv/postgresql."${DB}"

	VERSION=''
	DB=''
	exit 162
}

# Start here...

get_version

STATDIR="${RUNROOT}/${VERSION}-${DB}.pg_stat_tmp"

Re: Postgresql under runit and OS upgrade

dummy@example.com (Geoff 42) — Sat, 30 Dec 2023 15:51:48 +0000

The very simple config.fixed script, sourced by the run script is

VERSION="15"
DB="main"

PGBINROOT="/usr/lib/postgresql"
RUNROOT="/var/run/postgresql"
CONFROOT="/etc/postgresql"
STATDIR="${RUNROOT}/${VERSION}-${DB}.pg_stat_tmp"

Re: Postgresql under runit and OS upgrade

dummy@example.com (Geoff 42) — Sat, 30 Dec 2023 15:46:15 +0000

The log/run script is intended to work with any service without needing to be edited.

#!/bin/sh

NAME="${PWD%/log}"
NAME="${NAME##*/}"

LOG="/var/log/runit/$NAME"

USER="_runit-log"
GROUP="adm"
MODE="755"

install -m "$MODE" -o "$USER" -g "$GROUP" -d "$LOG"
exec chpst -u "$USER":"$GROUP" svlogd -tt "$LOG"

Re: Postgresql under runit and OS upgrade

dummy@example.com (Geoff 42) — Sat, 30 Dec 2023 15:38:57 +0000

I have been thinking about how it might work with several Postgresql databases. I believe that each database would have its own service instance controlled by runit. This could be similar to having getty, where there are services called, e.g. getty-tty1, getty-tty2 etc.
I now have services called postgresql.a and postgresql.main. The contents of their directories are the same, apart for deleting the supervise links, so that they can be correctly recreated by update-service. The main run script still takes the config file, but config is now a link to one of several versions, config.fixed, config.auto and config.multi.

config.fixed simply sets the variables $VERSION and $DB along with the paths.
config.auto looks for the first $DB set for auto start, stepping through the ${VERSION}s.
config.multi gets the $DB from the service name extension and looks for the first $VERSION set for auto start.

I have also got the log/run script so that it picks up the service name and should not require changing.

In /etc/sv I have the service postgresql. It is necessary to copy over postgresql to postgresql.main and it should work! It is necessary to delete the supervise links and to change the config link over to config.multi.

cd /etc/sv
cp -a postgresql postgresql.main
cd postgresql.main
rm supervise
rm config
ln -s config.multi config
cd log
rm supervise

now remove the automatic set up and start the selective version :-

update-service --remove /etc/sv/postgresql
Service postgresql removed, the service daemon received the TERM and CONT signals.
sv status postgresql
fail: postgresql: unable to change to service directory: file does not exist
update-service --add /etc/sv/postgresql.main
Service postgresql.main added.
sv status postgresql.main
run: postgresql.main: (pid 4614) 4s; run: log: (pid 4613) 4s

cd /var/log/runit/postgresql.main
cat current

2023-12-29_15:54:42.13174 ok: run: dbus: (pid 2032) 7772s
2023-12-29_15:54:42.13183 main
2023-12-29_15:54:42.13193 Version 13.
2023-12-29_15:54:42.13194 /etc/postgresql/13/main 13 main
2023-12-29_15:54:42.13282 /etc/postgresql/13/main 13 main is set to manual
2023-12-29_15:54:42.13282 Version 13 main not set to auto start.
2023-12-29_15:54:42.13282 Version 15.
2023-12-29_15:54:42.13284 /etc/postgresql/15/main 15 main
2023-12-29_15:54:42.13363 /etc/postgresql/15/main 15 main is set to auto
2023-12-29_15:54:42.13364 15 main auto
2023-12-29_15:54:42.26292 2023-12-29 15:54:42 GMT [4614-1] LOG: starting PostgreSQL 15.5 (Debian 15.5-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-12-29_15:54:42.26294 2023-12-29 15:54:42 GMT [4614-2] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-12-29_15:54:42.28991 2023-12-29 15:54:42 GMT [4614-3] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-12-29_15:54:42.35872 2023-12-29 15:54:42 GMT [4624-1] LOG: database system was shut down at 2023-12-29 15:54:11 GMT
2023-12-29_15:54:42.38238 2023-12-29 15:54:42 GMT [4614-4] LOG: database system is ready to accept connections

For test purposes, I have the few files necessary to fool the scripts so that it will check the database "a" and find that it is set to be started manually, i.e. not run by runit. I have then set up postgresql.a in the same way as above. When the service postgresql.a was started it failed to find a database to start and looped about once a second. I have fixed this by adding the command

sv down /etc/sv/postgresql.a

which stops the looping, when no suitable database is found.

I will list the scripts in separate posts.

Postgresql under runit and OS upgrade

dummy@example.com (Geoff 42) — Wed, 13 Dec 2023 14:53:12 +0000

It is possible to have several databases running under Postgresql.
There is, however, the simple case of having just one database, often called "main", which is what I have been using.

It does get a bit more interesting during an upgrade where you will have 2 versions of Postgresql installed, albeit briefly.
I have set it up to run under runit, whereby it looks though the file system to find the first version and then looks for the first database which is available to be run and then runs it. I have used some code from the SysV script.

/etc/sv/postgresql/run looks like :-

#!/usr/bin/env /lib/runit/invoke-run
exec 2>&1

sv start dbus || exit 1

. ./config

# create socket and stats directories
install -d -m 2775 -o postgres -g postgres ${RUNROOT} ${STATDIR}
exec chpst -u postgres:postgres:ssl-cert pg_ctlcluster --foreground ${VERSION} ${DB} start

while config looks like this :-

# find the version and database name
# taken by postgresql/run

PGBINROOT="/usr/lib/postgresql"
RUNROOT="/var/run/postgresql"
DATAROOT="/var/lib/postgresql"
CONFROOT="/etc/postgresql"

# Typical sort of values. I think "main" is the default name for a db.
# VERSION="15"
# DB="main"
# but we will look for the actual values in use.

# We look through the versions and then for each one, look through the db names
# stopping at the first one which is set to auto start in start.conf

start=''

get_db_name() {
    local c

    DB=''
    for c in $CONFROOT/$VERSION/*; do
	[ -e $c/postgresql.conf ] || continue
        
	DB=$(basename "$c")
        echo "$c ${VERSION} ${DB}"

        # evaluate start.conf, is it to be auto started?
	if [ -e "$c/start.conf" ]; then
	    start=$(sed 's/#.*$//; /^[[:space:]]*$/d; s/^\s*//; s/\s*$//' "$c/start.conf")
	else
	    start=auto
	fi
        echo "$c yields ${VERSION} ${DB} $start"

	[ "$start" = "auto" ] || continue

        # we have a db configured to auto start.
        
        return
    done
    # we have no valid database names in this version
    echo "Version $VERSION no database found set to auto start."
    DB=''
    return
}

get_version() {
    VERSION=''

    for VERSION in `ls $PGBINROOT 2>/dev/null`; do

        echo "\nVersion $VERSION."
        if [ -x $PGBINROOT/$VERSION/bin/pg_ctl ]; then

            # we have a valid version, now look through the db names
            get_db_name
            if [ -n $DB ] && [ "$start" = "auto" ]; then
                echo "$VERSION $DB $start"
                return
            fi
      	fi

    done
    # we have no valid databases to start
    echo "No databases set for auto start."
    VERSION=''
    DB=''
    exit 162
}

# Start here...

get_version

BINFILE="${PGBINROOT}/${VERSION}/bin/postgres"
STATDIR="${RUNROOT}/${VERSION}-${DB}.pg_stat_tmp"
DATAFILE="${DATAROOT}/${VERSION}/${DB}"
CONFFILE="${CONFROOT}/${VERSION}/${DB}/postgresql.conf"

After the upgrade from Chimaera to Daedalus it is possible to see what is there with :-

pg_lsclusters

This shows both versions 13 and 15 although 15 has not been started.
Close down the new version and upgrade the old one. I think that the "--stop" is probably redundant, but it doesn't seem to cause a problem.

pg_dropcluster 15 main --stop
pg_upgradecluster 13 main

This does the upgrade so we can now drop the old one.

pg_dropcluster 13 main

This leaves 15 running, but not under runit.

pg_ctlcluster 15 main stop -m smart

stops that copy and then runit notices and fires up the supervised version.

/var/log/runit/postgresql/current reports :-

2023-12-13_06:45:59.29154
2023-12-13_06:45:59.29841 Version 13.
2023-12-13_06:45:59.29843 Version 13 no database found set to auto start.
2023-12-13_06:45:59.29843
2023-12-13_06:45:59.29844 Version 15.
2023-12-13_06:45:59.29844 /etc/postgresql/15/main 15 main
2023-12-13_06:45:59.29844 /etc/postgresql/15/main yields 15 main auto
2023-12-13_06:45:59.29844 15 main auto
2023-12-13_06:46:00.55850 ok: run: dbus: (pid 2052) 1s
2023-12-13_06:46:00.79584 2023-12-13 06:46:00 GMT [2024-1] LOG: starting PostgreSQL 15.5 (Debian 15.5-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-12-13_06:46:00.79586 2023-12-13 06:46:00 GMT [2024-2] LOG: listening on IPv4 address "0.0.0.0", port 5432
2023-12-13_06:46:00.82271 2023-12-13 06:46:00 GMT [2024-3] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-12-13_06:46:00.90202 2023-12-13 06:46:00 GMT [2418-1] LOG: database system was shut down at 2023-12-12 19:02:00 GMT
2023-12-13_06:46:00.96074 2023-12-13 06:46:00 GMT [2024-4] LOG: database system is ready to accept connections
2023-12-13_06:51:02.19918 2023-12-13 06:51:02 GMT [2416-1] LOG: checkpoint starting: time
2023-12-13_06:51:02.80703 2023-12-13 06:51:02 GMT [2416-2] LOG: checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.046 s, sync=0.021 s, total=0.608 s; sync files=2, longest=0.015 s, average=0.011 s; distance=0 kB, estimate=0 kB

I need to remember to remove postgresql 13, when I am happy with everything.

ps axjf reports for postgresql :-

PPID PID PGID SID TTY TPGID STAT UID TIME COMMAND
...
1853 1961 1961 1961 ? -1 Ss 0 0:00 \_ runsv postgresql
1961 2003 1961 1961 ? -1 S 999 0:00 | \_ svlogd -tt /var/log/runit/postgresql
1961 2004 1961 1961 ? -1 S 130 0:00 | \_ /usr/lib/postgresql/15/bin/postgres -c config_file=/etc/postgresql/15/main/postgresql.conf
2004 2350 2350 2350 ? -1 Ss 130 0:00 | \_ postgres: checkpointer
2004 2351 2351 2351 ? -1 Ss 130 0:00 | \_ postgres: background writer
2004 2368 2368 2368 ? -1 Ss 130 0:00 | \_ postgres: walwriter
2004 2369 2369 2369 ? -1 Ss 130 0:00 | \_ postgres: autovacuum launcher
2004 2370 2370 2370 ? -1 Ss 130 0:00 | \_ postgres: logical replication launcher

...

Geoff