Skip to main content

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

Project description

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

Production

$ pip install pg-sequence-increaser

Development

Using virtualenv,

$ pip install -r requirements.txt

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


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

pg-sequence-increaser-0.1.3.tar.gz (4.5 kB view details)

Uploaded Source

Built Distribution

pg_sequence_increaser-0.1.3-py3-none-any.whl (6.2 kB view details)

Uploaded Python 3

File details

Details for the file pg-sequence-increaser-0.1.3.tar.gz.

File metadata

  • Download URL: pg-sequence-increaser-0.1.3.tar.gz
  • Upload date:
  • Size: 4.5 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.22.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.41.0 CPython/3.7.5

File hashes

Hashes for pg-sequence-increaser-0.1.3.tar.gz
Algorithm Hash digest
SHA256 658a4d5de61be0405b5c16ef9dc6785b2d664d276e80a18b09eee7ed8027655f
MD5 7f5c20cecdfcda50c96700614a0e347b
BLAKE2b-256 496942a3a8536933c8fa4360f6fd675c0ac093b82824c4c5e4f7a59411f12b79

See more details on using hashes here.

File details

Details for the file pg_sequence_increaser-0.1.3-py3-none-any.whl.

File metadata

  • Download URL: pg_sequence_increaser-0.1.3-py3-none-any.whl
  • Upload date:
  • Size: 6.2 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.22.0 setuptools/42.0.2 requests-toolbelt/0.9.1 tqdm/4.41.0 CPython/3.7.5

File hashes

Hashes for pg_sequence_increaser-0.1.3-py3-none-any.whl
Algorithm Hash digest
SHA256 ffb53d918f24976a740c199009d4e122a57087a6043f02f449227753714c1a7f
MD5 380c709fd2385a6d13d5df167ece10af
BLAKE2b-256 4791cd08e325305ef57bf4be767f559c502dd61edb6eea35cec03501770f0d9e

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page