.. SPDX-FileCopyrightText: 2021 Dalibo .. .. SPDX-License-Identifier: GPL-3.0-or-later Standby setup ============= .. highlight:: console Command-line interface ---------------------- From the command-line interface, creating a standby instance is done through the ``instance create`` command: :: $ pglift instance create standby --standby-for --standby-password Password for the replication user: Repeat for confirmation: The ``--standby-for`` option should be a `connection string`_ to the primary server (e.g. ``host=primary port=5433``). If the primary is also a pglift instance, you must use the dedicated ``replication`` user, set ``user=replication`` in the dsn. A replication slot can be specified with ``--standby-slot ``. The slot has to be first created on the primary instance, either at instance creation with the ``--slot `` option or through ``instance alter --create-slot `` if the primary already exists. pglift will call `pg_basebackup`_ utility to create a standby by default. However, if pgBackRest is used on site, and a backup is available in the repository, pglift will create the standby from this backup, which can be significantly more efficient than a *basebackup*. .. note:: If the primary instance has a password set for the super-user role, and is needed for local authentication through the password file in particular, it might be useful to provide the same password through ``--surole-password`` option when creating the standby. .. note:: If Prometheus postgres_exporter was set up on the primary instance and is wanted on the standby, don't forget to provide ``--prometheus-password`` option to the above command with the same password as on the primary instance. Promoting a standby instance: :: $ pglift instance promote standby .. note:: If pgBackRest was set up on the instance, a ``pgbackrest check`` command may be automatically executed (after confirmation) upon promotion of the standby. If the promoted instance is meant for testing only and further intended to be demoted, this "check" step may be avoided. Demotion of a former primary instance is possible through ``instance demote`` command: :: $ pglift instance demote instance_b \ --from="host=instance_a.dbserver role=rewinduser" --password This operation first runs ``pg_rewind`` using the `connection string`_ of the "source server" specified through ``--from`` option along with the ``--password`` option for the role used for rewinding. Then it restores the original configuration of the instance as it was before "demotion" but with standby replication set up. .. _`connection string`: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING .. _pg_basebackup: https://www.postgresql.org/docs/current/app-pgbasebackup.html Ansible ------- Standby instance management is also possible from the Ansible interface. Please refer to the :ref:`tutorial ` for starting up with this. The ``dalibo.pglift.instance`` module exposes a ``standby`` field to define connection to the primary, password and replication slot. First, we will define a password for the replication user on the ``control`` node and store that in Ansible vault: :: user@control:~$ cat << EOF | ansible-vault encrypt > pglift-vars replication_role_password: $(openssl rand -base64 9) EOF The following playbook will create 2 instances on the same host: - ``pg1`` as a primary, port ``5432`` - ``pg2`` as a standby, port ``5433`` .. literalinclude:: ../ansible/standby-setup.yml :language: yaml :caption: standby-setup.yml And finally, run the playbook: :: user@control:~$ ansible-playbook --extra-vars @pglift-vars --ask-vault-password standby-setup.yml Vault password: PLAY [standby setup] *********************************************************************************************************************** TASK [Gathering Facts] ********************************************************************************************************************* ok: [localhost] TASK [primary instance 14/main] ************************************************************************************************************ changed: [localhost] TASK [replica instance 14/main] ************************************************************************************************************ changed: [localhost] PLAY RECAP ********************************************************************************************************************************* localhost : ok=3 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 We can find our instances running on ``managed`` node : :: user@managed:~$ pglift instance list ┏━━━━━━┳━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┓ ┃ name ┃ version ┃ port ┃ datadir ┃ status ┃ ┡━━━━━━╇━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━┩ │ pg1 │ 14 │ 5432 │ /srv/pgsql/14/pg1/data │ running │ │ pg2 │ 14 │ 5433 │ /srv/pgsql/14/pg2/data │ running │ └──────┴─────────┴──────┴────────────────────────┴─────────┘ The following playbook will promote ``pg2`` and rebuild ``pg1`` as a standby: .. literalinclude:: ../ansible/standby-promote.yml :language: yaml :caption: standby-promote.yml Run the playbook: :: user@control:~$ ansible-playbook --extra-vars @pglift-vars --ask-vault-password standby-promote.yml Vault password: PLAY [promote standby] ************************************************************************************************** TASK [Gathering Facts] ************************************************************************************************** ok: [localhost] TASK [promote standby instance 14/pg2] ********************************************************************************** ok: [localhost] TASK [delete 14/pg1 instance] ******************************************************************************************* changed: [localhost] TASK [rebuild 14/pg1 instance as a standby] ***************************************************************************** changed: [localhost] PLAY RECAP ************************************************************************************************************** localhost : ok=5 changed=2 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0 Finally we can see ``pg1`` as a standby and both running : :: user@managed:~$ pglift instance get pg1 name version port data_checksums locale encoding extensions pending_restart standby pg1 14 5432 False C UTF8 False primary_conninfo: user=replication channel_binding=prefer host=127.0.0.1 port=5433 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any status: demoted slot: replication_lag: 0 user@managed:~$ pglift instance list ┏━━━━━━┳━━━━━━━━━┳━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┓ ┃ name ┃ version ┃ port ┃ datadir ┃ status ┃ ┡━━━━━━╇━━━━━━━━━╇━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━┩ │ pg1 │ 14 │ 5432 │ /srv/pgsql/14/pg1/data │ running │ │ pg2 │ 14 │ 5433 │ /srv/pgsql/14/pg2/data │ running │ └──────┴─────────┴──────┴────────────────────────┴─────────┘