Fixing all sequences in PostgreSQL 15 after migrating with logical replication from PostgreSQL 11

Question

I've run into a problem recently during migration of PostgreSQL 11 to PostgreSQL 15 with logical replication.

As one of the cons of using logical replication, sequence data does not replicate between the two instances. So if the database has any sequences inside, then it is necessary to fix them, before using the new database.

To fix the sequences I've been using a script with a select which takes all sequences present in the database and then it assembles a SETVAL command which is then printed in the database.

Unfortunately, every select I found to perform this, only selects the sequences which are owned by a column.

Is there any automated way to fix sequences which aren't owned by a column? Or is there other way entirely to fix sequences?

Answer 1

It seems i was able to nearly replicate what the pg_dump --data-only does when it exports sequences.

#!/bin/bash
port=$1
db=$2

query="select schemaname as schema,
       sequencename as sequence,
       start_value,
       last_value
from pg_sequences order by sequencename asc;
"

while read schema sequence start_value last_value
do
        if [ -z "$last_value" ]
        then
        echo "SELECT pg_catalog.SETVAL('${schema}.\"${sequence}\"', $start_value, true);"
        else
                echo "SELECT pg_catalog.SETVAL('${schema}.\"${sequence}\"', $last_value, true);"
        fi

done < <(psql -t -A -F" " -p ${port} ${db} -c "${query}")

This bash code takes all sequences present in the pg_sequences table and it assembles a setval command with either the start_value or last_value. Which one gets selected depends on if the last_value value is empty, therefore it should be the same as start_value. Only thing missing is figuring out whether the setval is gonna have a "true" or "false" which considering what it does, I figured for my purposes, there isn't much of a point in trying to figure this out too.