Major online upgrades¶

In the following, we illustrate how to perform an online upgrade of a database from PostgreSQL version 12 to version 15 with Ansible and pglift. The source database app lives in instance 12/old:

$ pglift instance create old --version=12 --port=5499
INFO     initializing PostgreSQL
INFO     configuring PostgreSQL authentication
INFO     configuring PostgreSQL
INFO     starting PostgreSQL 12-old
$ pglift role -i old create app --password
Role password:
Repeat for confirmation:
INFO     creating role 'app'
$ pglift database -i old create app --owner app
INFO     creating 'app' database

and we’re filling it with some data:

$ pglift database -i old run -d app 'CREATE TABLE books (title TEXT)'
INFO     running "CREATE TABLE books (title TEXT)" on app database of 12/old
INFO     CREATE TABLE
$ pglift database -i old run -d app "ALTER TABLE books OWNER TO app"
INFO     running "ALTER TABLE books OWNER TO app" on app database of 12/old
INFO     ALTER TABLE
$ pglift database -i old run -d app "INSERT INTO books VALUES ('On the road'), ('Big Sur')"
INFO     running "INSERT INTO books VALUES ('On the road'), ('Big Sur')" on app
         database of 12/old
INFO     INSERT 0 2

Let’s prepare it for logical replication by:

adjusting the app role with LOGIN and REPLICATION options, and,
adding a publication to the source database.

We’ll be doing this through the following Ansible playbook which also sets up the target instance named new (in PostgreSQL version 15):

online-upgrade.yml¶

---
- name: Major online upgrade of 'app' database
  hosts: localhost
  tasks:
    - name: old instance
      dalibo.pglift.instance:
        name: old
        version: 12
        port: 5499
        settings:
          wal_level: logical
        restart_on_changes: true
        roles:
          - name: app
            password: "{{ app_password }}"
            login: true
            replication: true
        databases:
          - name: app
            owner: app
            publications:
              - name: migrate

    - name: new instance
      dalibo.pglift.instance:
        name: new
        version: 15
        port: 5500
        roles:
          - name: app
            password: "{{ app_password }}"
        databases:
          - name: app
            owner: app
            clone:
              dsn: "postgresql://app:{{ app_password }}@localhost:5499/app"
              schema_only: true
            subscriptions:
              - name: migrate
                connection:
                  conninfo: "host=localhost user=app port=5499"
                  password: "{{ app_password }}"
                publications:
                  - migrate

Note that the target database app is:

initialized as a clone of the source database, but only for data definitions (the clone.schema_only: true option),
configured with a subscription matching the publication on the source database.

Also note that the source instance needs wal_level=logical for logical replication to work.

Once this playbook applied, we should get two instances locally:

$ ps xf
pts/2    S      0:00 /usr/lib/postgresql/15/bin/postgres -D /srv/pgsql/15/new/data
?        Ss     0:00  \_ postgres: new: logger
?        Ss     0:00  \_ postgres: new: checkpointer
?        Ss     0:00  \_ postgres: new: background writer
?        Ss     0:00  \_ postgres: new: walwriter
?        Ss     0:00  \_ postgres: new: autovacuum launcher
?        Ss     0:00  \_ postgres: new: logical replication launcher
?        Ss     0:00  \_ postgres: new: logical replication worker for subscription 16387
pts/2    S      0:00 /usr/lib/postgresql/12/bin/postgres -D /srv/pgsql/12/old/data
?        Ss     0:00  \_ postgres: old: logger
?        Ss     0:00  \_ postgres: old: checkpointer
?        Ss     0:00  \_ postgres: old: background writer
?        Ss     0:00  \_ postgres: old: walwriter
?        Ss     0:00  \_ postgres: old: autovacuum launcher
?        Ss     0:00  \_ postgres: old: stats collector
?        Ss     0:00  \_ postgres: old: logical replication launcher
?        Ss     0:00  \_ postgres: old: walsender app ::1(46192) idle

with logical replication in place:

$ pglift database -i new run -d app 'TABLE books' -o json
INFO     running "TABLE books" on app database of 15/new
INFO     SELECT 2
{
  "app": [
    {
      "title": "On the road"
    },
    {
      "title": "Big Sur"
    }
  ]
}

Once the replication is done and everything is in sync, the subscription on the target database can be disabled (or it can be dropped):

 - name: new instance
   dalibo.pglift.instance:
     ...
     databases:
       - name: app
         owner: app
         ...
         subscriptions:
           - name: migrate
             enabled: false
             connection:
               conninfo: "host=localhost user=app port=5499"
               password: "{{ app_password }}"
             publications:
               - migrate