# Postgresql role

<!-- TOC -->
* [Postgresql role](#postgresql-role)
  * [Mandatory parameter](#mandatory-parameter)
  * [Important note about ZFS](#important-note-about-zfs)
  * [Important about the speed optimization that comes from `synchronous_commit = off`](#important-about-the-speed-optimization-that-comes-from-synchronous_commit--off)
  * [Optional parameters](#optional-parameters)
    * [Change port and cluster name](#change-port-and-cluster-name)
    * [Listen to a different IP address than localhost](#listen-to-a-different-ip-address-than-localhost)
    * [Deactivate ssl](#deactivate-ssl)
    * [Main config](#main-config)
    * [Special note about the `fsync` parameter](#special-note-about-the-fsync-parameter)
    * [SSD config](#ssd-config)
    * [Logging](#logging)
    * [Archive mode and replication as master](#archive-mode-and-replication-as-master)
    * [Replication as slave](#replication-as-slave)
    * [Disable backup service](#disable-backup-service)
  * [user handling](#user-handling)
  * [database handling](#database-handling)
  * [pg_hba.conf](#pg_hbaconf)
<!-- TOC -->

## 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:
- For postgres:
```
full_page_writes = off
wal_compression = off
```
- On postgresql version >= 12, you also have:
```
wal_recycle = off
wal_init_zero = off
```
- If you are on bare metal, it will also tune the postgres' dataset:
```
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:
- pg_autovacuum_freeze_max_age
- pg_autovacuum_max_workers
- pg_autovacuum_vacuum_scale_factor
- pg_autovacuum_work_mem
- pg_autovacuum_vacuum_cost_delay
- pg_autovacuum_vacuum_cost_limit
- pg_checkpoint_timeout
- pg_effective_cache_size
- pg_huge_pages
- pg_maintenance_work_mem
- pg_max_connections
- pg_shared_buffers
- pg_timezone
- pg_work_mem
- pg_max_wal_size
- pg_min_wal_size
- pg_idle_in_transaction_session_timeout (pg 9.6+ required)
- pg_max_parallel_workers_per_gather (pg 9.6+ required)
- pg_max_worker_processes (pg 9.6+ required)


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.talas.com:/srv/pg/veza/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:
* https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_user_module.html
* https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_membership_module.html
* https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_privs_module.html

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

- Define this varibale to create entries in 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
```
- The default local access via unix socket is this line:
```
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`
1. manually download the latest postgresql version: `apt install postgresql-17`
1. 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`
1. if no new cluster has been created, create a new one using `pg_createcluster 17 main`
1. 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`
1. check `pg_lsclusters`, you should have the current cluster running and the new one down
1. Stop the current cluster
1. perform a ZFS or Incus/LXD snapshot if necessary
1. switch to the `postgres` user
1. 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`
1. adjust the ansible variable for the new cluster version: `pg_version: 17`
1. run the postgres role
1. 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
1. remove the old cluster: `rm -r /var/lib/postgresql/14 /etc/postgresql/14`
