Postgresql role

Mandatory parameter

You need to define the postgresql version that you want to install:

pg_version: 17

Important note about ZFS

This role does some optimization if it determines that the filesystem containing the cluster is ZFS:

full_page_writes = off
wal_compression = off

wal_recycle = off
wal_init_zero = off

logbias: throughput
recordsize: 16K

Important about the speed optimization that comes from synchronous_commit = off

By default, this role will set the postgres parameter synchronous_commit = off.

This means that postgresql will tell the system that a commit is done before it is persisted to disk, speeding a great deal the write operations. The commit happens at worst 600ms after that point, and thus allow the grouping of many commit operations in one sync.

This also means that in case of a crash, you can lose up to 600ms worth of commits. If this is unacceptable, use this variable to change the behavior:

pg_synchronous_commit: "on"

This will force a sync after every commit, which is the default postgresql behavior.

Optional parameters

Default encoding

By default, the encoding is set to en_US.UTF-8, you can change it by setting this variable:

pg_default_encoding: "en_US.UTF-8"

Change port and cluster name

Previously those parameters were mandatory, we now define those as the default, which you can change:

pg_port: 5432
pg_cluster_name: main

Listen to a different IP address than localhost

By default, this role makes postgres only listen to localhost with this variable:

pg_listen_addresses: localhost

If you need remote access, either enter a list of addresses to listen to:

pg_listen_addresses: "localhost,a.b.c.d"

Or make postgresql listen on all addresses (limit this to internal servers only):

pg_listen_addresses: "*"

Deactivate ssl

Set this variable to `false to deactivate ssl with the snakeoil certificate:

pg_ssl: False

Main config

The following variables can be defined to override the default from postgres, the ansible variable are the same as the postgres variables, only idfference is the suffix "pg_" in front:

Note that the default pg_shared_buffers is set to 128MB in this role, which should be the default since postgres 9.3 (up to postgres 12) but is strangely set to 8MB on Debian.

Special note about the fsync parameter

The fsync parameter is special because if set to off, postgres runs in insecure mode that will generate database corruption in case of postgres or OS crash. So this parameter should always remains untouched to the default and safe value of on.

But it can be very interesting to deactivate sync BEFORE putting the cluster into production, for example to load a database dump more quickly on a new cluster.

The best way to not forget this parameter is to not commit it to git and use an extra-vars to temporary change it instead:

ansible-playbook someplaybook --limit somehost --extra-vars "{ 'pg_fsync' : false }"

If you really know what you are doing, you can instead change the default variable which is:

pg_fsync: True

Set it to False to deactivate fsync but don't forget to remove this setting before putting the cluster in production.

When you switch fsync back to on, you must ensure that all data have been written to the system, follow instructions on the official documentation for the fsync parameter to do the right thing: https://www.postgresql.org/docs/current/runtime-config-wal.html

SSD config

A default ssd config can be activated by setting the following variable to true:

pg_ssd: True

The config that will be added is:

seq_page_cost = 1.0
random_page_cost = {{ pg_random_page_cost }}
effective_io_concurrency = '{{ pg_effective_io_concurrency }}'

You can adjust the pg_random_page_cost and pg_effective_io_concurrency if you want, the default are:

pg_random_page_cost: 1.2
pg_effective_io_concurrency: 20

Logging

By default no logging is active, you can activate it by setting pg_logging: True:

pg_logging: True

You can also change the log directory, bellow is the default:

pg_log_directory: "/var/log/postgresql"

Currently this will activate a basic logging configuration which logs everything, you can look at the template to see exactly what it does.

The default suffix for log files is:

pg_log_filename_suffix: '%a_%H'

This will keep 1 week worth of logs (%a is the day of the week: "Mon", "Tue", etc). You can change this value if needed, for example if you want to only keep 1 day worth of logs:

pg_log_filename_suffix: '%H'

The default parameter is to log everything (value = 0), you can change that by setting a value in milliseconds on this variable, if you do so, only query that takes longer than that will be logged, this has a positive performance impact:

pg_log_min_duration_statement: '0'

Archive mode and replication as master

You can activate either the replication or archive+replication.

To activate only the replication, which allow the creation of a replica and streaming replication, use this variable:

pg_replication: True

You should also create some physical replication slots with this variable:

pg_replication_slot_list:
  - some_slot
  - an_other_slot

If you need to archive the WAL, you need to prepare in advance a server where you will send the WAL files. Set up an account on the remote server and copy the postgres ssh pubkey from the origin server to the archive one. Make sure that the ssh connection is working without any prompt, else the archive command will fail.

You then need to set pg_archive_command:

pg_archive_command: 'chmod 640 %p && rsync -e "ssh -o StrictHostKeyChecking=yes -o BatchMode=yes" -a %p {{ ansible_hostname }}@srv-227.cosium.com:/srv/pg/center/pitr/{{ ansible_hostname }}/{{ pg_port }}/wal/%f'

The pg_archive_command example above is a typical example. You need to set the ssh option so that the rsync command will fail immediately if for any reason the authentication via pubkey doesn't work, else it may get stuck with a password prompt.

Keep in mind that setting the archive mode will also activate the replication mode because it is needed to have correct WAL files.

Replication as slave

To create a replication slot on the slave you must use this variable:

pg_replication_slot_list_replica:
  - some_slot
  - an_other_slot

If you want to make sure some replication slots are ABSENT, you can use this list:

pg_replication_slot_to_remove:
  - some_slot
  - an_other_slot

Disable backup service

This script was created to put the postgresql in backup mode to ensure coherency in the filesystem layer during snapshot. But this was a misunderstanding and this is in fact not useful at all with a ZFS backed database. It's still there for now and can be activated by setting this variable but may be removed in the future:

pg_backup_service: True

user handling

The password encryption use scram-sha-256 by default if md5 is needed set the following "pg_md5" var to true.

It's important to set the method of authentifcation in pg_hba to md5 after this.

Define any number of user to create them like this (attrib are the rights of the user), look at:

for all possible values, all values are optional:

pg_users:
  - name: alfred
    password: ALFRED
    attrib: LOGIN,CREATEDB
  - name: boris
    password: BORIS
    attrib: LOGIN
  - name: clair
    password: CLAIR
    attrib: LOGIN
    db: somedb
    conn_limit: 10
    groups: somegroup
    priv: somepriv
  role-that-will-be-used-as-a-group:
    attrib: NOLOGIN

The password can be generated automatically by setting it to "auto":

pg_users:
  - name: "matrix_synapse"
    password: "auto"

You can find the password in hashicorpvault in this way: postgres_user_{{ user.name }}_password

database handling

Define databases that you want to create like this:

pg_databases:
  - name: alpha
    owner: alfred
  - name: beta
    owner: boris
  - name: gamma
    owner: clair
    conn_limit: 50 # optional, the default is 80
    extension_list: [ 'fuzzystrmatch' ] # optional, remember that this will be added to the default extensions from the pg_extensions list if defined

This will create each database with the correct owner and make sure it stays like that on successive ansible runs.

shared librairies handling

Define shared librairies that you want to to preload like this: It requires postgres to restart; however, it will fail if the library is not yet installed.

pg_shared_preload_libraries:
  - timescaledb

pg_hba.conf

pg_hba_config:
  - user: foo
    type: host
    database: all
    address: 10.12.0.0/16
    method: "scram-sha-256"
  - user: bar
    type: hostssl
    database: mydb
    address: 10.12.1.1/32
    method: "scram-sha-256"

With the previous values, the result in the pg_hba.conf file will look like this:

host all foo 10.12.0.0/16 scram-sha-256 # description
hostssl mydb bar 10.12.1.1/32 scram-sha-256 # hello world

local all all scram-sha-256

Which you can change by setting this variable:

pg_local_auth_method: "peer" # you can use any value accepted by postgres, see https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Extensions

You can add any number of extension to the template1 database. This database is used to create all other databases by default, so any database created will have the same extensions.

To define the extensions you need, simply create this list:

pg_extensions:
  - pg_trgm
  - btree_gist

You can also define extensions per database, see database handling. Per database is useful because it can add extension to an existing database while pg_extensions only apply to databases created latter.

setting up a replica

To set up a replica, you must first make sure that all the pg_ variables from the master are used as well for the future replica (set them up in a group_vars or make a symlink between the master and the replica).

Then install a new standalone server without specifying a pg_replication_role variable.

After that, you need to connect yourself to the future replica server and:

  1. stop postgresql
  2. delete the postgres data directory
  3. initiate the slave with pg_basebackup, the command will look like pg_basebackup --write-recovery-conf --checkpoint=fast --label="IT-XXXXXX" --progress --slot=replication_slot_name --host=primary_fqdn --username=replication_username --pgdata=/var/lib/postgresql/11/main (look at the man page for options)

At this point, you need to define the following variables (you can define them at the same level as the other pg_ variables, they will be ignored by the master):

postgres_primary_conninfo_host: "master fqdn"
postgres_primary_conninfo_user: "the replication user"
postgres_primary_conninfo_password: "the replication user password"
postgres_primary_slot_name: "the replication slot name"

You can also change the default for the following variable if needed:

postgres_primary_conninfo_port: 5432
postgres_primary_conninfo_sslmode: "require"

If you have a WAL repository, you can also define a restore_command:

pg_restore_command: 'rsync -e "ssh -o StrictHostKeyChecking=yes -o BatchMode=yes" -a login@wal_repo_fqdn:/path_to_wall_repo/%f %p'

You can now change the pg_replication_role only for the replica:

pg_replication_role: "replica"

Then rerun the role. This will create the recovery.conf or the standby.signal file depending on the postgresql version.

postgresql connection reaper

This role includes a script for Zabbix that is intended to be called as a remote command to purge the majority of active connections when the number of active connection is 2 times the amount of threads.

The usage of this script is controlled by Zabbix's Actions.

Don't forget that if you want to use it, you need to add the following variable to enable remote command execution by zabbix:

zabbix_remote_command: True

restart_required extra location

Sometimes, you need to have restart_required in a second location (different than pg_configuration_file_restart_required), you can use pg_configuration_file_restart_required_extra_location for this.

pg_upgrade

In this example, we will upgrade a server running pg 14 to pg 17, adjust the versions for your use case:

  1. apt update
  2. manually download the latest postgresql version: apt install postgresql-17
  3. if a new cluster has been created, stop it pg_ctlcluster stop 17 main, then remove the directories /var/lib/postgresql/17 and /etc/postgresql/17
  4. if no new cluster has been created, create a new one using pg_createcluster 17 main
  5. remove the directory /var/lib/postgresql/17/main and as the postgresql user, run /usr/lib/postgresql/17/bin/initdb /var/lib/postgresql/17/main
  6. check pg_lsclusters, you should have the current cluster running and the new one down
  7. Stop the current cluster
  8. perform a ZFS or Incus/LXD snapshot if necessary
  9. switch to the postgres user
  10. run this command: adjust the job count depending on the available memory and cpu count: /usr/lib/postgresql/17/bin/pg_upgrade --old-bindir=/usr/lib/postgresql/14/bin --new-bindir=/usr/lib/postgresql/17/bin --old-datadir=/var/lib/postgresql/14/main --new-datadir=/var/lib/postgresql/17/main --jobs=2 --link --verbose
  11. adjust the ansible variable for the new cluster version: pg_version: 17
  12. run the postgres role
  13. look at the sh scripts in the folder where you did the upgrade, you may have some post-upgrade bash script created by pg_upgrade, look at them and run them accordingly
  14. remove the old cluster: rm -r /var/lib/postgresql/14 /etc/postgresql/14