Set all sequences to a higher value when promoting a PostgreSQL logical replica

# pg-sequence-increaser

Set all sequences to a higher value when promoting a PostgreSQL logical replica.

## Requirements

1. Python3
2. libpq-dev (for psycopg2). On Mac OS, brew install postgresql, on Ubuntu, install libpq-dev.

## Installation



### Usage

This tool implements two strategies:

1. Getting the sequences and their desired values from the logical replica alone (called "max-id-on-replica"),
2. getting the sequences and their desired values from the primary database, i.e. the publisher; this is called "primary-sequences".

#### Max Id on Replica

We get the sequences from information_schema.default_value. Any default_value that has nextval('some_sequence_name'), we assume that's a sequence and extract the table name, the associated column and the sequence name. Then, we run MAX(column_name) to get the last value inserted into that table, likely from the sequence; we then move the sequence forward to the MAX(column_name) + increment_by. This strategy has the advantage of using the replica as the source of truth and not requiring the publisher to be online.

$pgseqmover --replica-url=postgres://localhost:5432/my_db --queries-only  will print the queries you can then run manually. Those queries will increase all sequences by 1000. #### Primary Sequences We get the sequences from the primary (publisher). After all, that's where we are replicating from in the first place. We use pg_sequences view and get both the name and the last_value from there as well. We then set the sequences on the replica to what they are on the primary + increment_by. This strategy has the advantage of using the primary database as the source of truth in case replica lag is non-zero during promotion which is bad, but possible. This requires the primary to be available during promotion of the replica. $ pgseqmover --replica-url=postgres://replica-db:5432/my_db --primary-url=postgres://primary-db:5432/my_db --queries-only --strategy=primary-sequences


will print the queries you can then run manually. Those queries will increase all sequences by 1000.

#### Arguments

This also accepts optional arguments:

1. --dry-run which will show the queries it will run but not actually do anything; this is the default behavior,
2. --debug will show all queries it's running; can be combined with --dry-run,
3. --increment-by overrides the default increase by value of 1000 to any value (even negative ones).
4. --strategy picks the strategy to use for increasing sequences. Your options are: primary-sequences and max-id-on-replica, both explained above.
5. --queries-only will only print the queries that will move the sequences and not do anything else. Less pretty version of --dry-run, but good for pgseqmover --queries-only > ~/Desktop/queries_to_run.sql use case.

## Project details

Uploaded source
Uploaded py3