kostiantyn-nemchenko / ansible-role-patroni

:elephant: Ansible Role for Patroni

Ansible Role for Patroni

An Ansible role which installs and configures Patroni - HA solution for PostgreSQL.

Help Wanted! If you encountered any difficulties with deploying the role into your environment, noticed a bug or a missing feature or just have an idea of how this project could be enhanced, please feel free to file an issue.

Requirements

This role requires root privileges, so tell ansible to use become: true in any convenient way for you.

Role Variables

Coming soon.

Dependencies

There are no dependencies for the role, but Patroni itself needs a DCS (Etcd, Consul, ZooKeeper or Exhibitor) to be installed and configured properly and it's your responsibility to make it up and running before using this role. Currently, it is supposed that a DCS is prepared. Otherwise, you can try one of the following roles (just uncomment respective section here and set patroni_dcs_exists variable to false):

• andrewrothstein.etcd-cluster
• brianshumate.consul
• AnsibleShipyard.ansible-zookeeper

Example Playbook

- hosts: postgresql-servers
  become: yes
  roles:
    - kostiantyn-nemchenko.patroni

License

MIT

Author Information

Kostiantyn Nemchenko [email protected]

ansible-role-patroni's Issues

Support of etcdv3

Add support for etcdv3 with an etcd3 section in the Patroni configuration file. The configuration parameters for the etcd3 section are the same as for etcd. https://patroni.readthedocs.io/en/latest/SETTINGS.html#etcdv3

Manage pg_ident.conf

If the postgresql.pg_ident is defined in the config file or DCS, Patroni will write its value to pg_ident.conf, however, if postgresql.parameters.ident_file is defined, Patroni will assume that pg_ident is managed from outside and not update the file.

Install patroni from pgdg

Hello,

Thank you for this role.

I would like to suggest that this role provide a way to install patroni from pgdg repository (or simply replace pip installation).
This would save the work from creating users, directory, service file and so on. You already have this repository installed for both Debian and Red-Hat:

ansible-role-patroni/vars/Debian.yml

Line 4 in 2fceaeb

postgresql_apt_repo: "deb http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main"

ansible-role-patroni/vars/RedHat.yml

Line 3 in 2fceaeb

postgresql_yum_repo_url: "https://yum.postgresql.org/{{ patroni_postgresql_version }}/{{ 'fedora' if ansible_distribution|lower == 'fedora' else 'redhat' }}/{{ 'fedora' if ansible_distribution|lower == 'fedora' else 'rhel' }}-{{ ansible_distribution_major_version }}-{{ ansible_architecture }}/{{ postgresql_yum_repo_pkg_name }}"

That way we would be able to leverage pgdg contribution, make this role usable for those working behind a strict firewall where allowing pypi is not possible.

I think it would be doable by removing pip usage and adding patroni to patroni_postgresql_packages:

ansible-role-patroni/vars/Debian.yml

Lines 12 to 16 in 2fceaeb

	patroni_postgresql_packages:
	- { name: "postgresql-{{ patroni_postgresql_version }}", state: "present" }
	- { name: "postgresql-client-{{ patroni_postgresql_version }}", state: "present" }
	- { name: "postgresql-contrib-{{ patroni_postgresql_version }}", state: "present" }
	- { name: "postgresql-server-dev-{{ patroni_postgresql_version }}", state: "present" }

Let me know what you think about this.
Cheers,
W.

Standby cluster support

The feature was introduced in version 1.5.0 of patroni.
More detailed description of this feature and some configuration examples can be found at here.

Callback scripts override empty files

Hi! Thank you for this perfect role.

We faced with a problem that after second run of playbook our callback scripts are empty, because they are overridden by this part of code - https://github.com/kostiantyn-nemchenko/ansible-role-patroni/blob/master/tasks/configure.yml#L20.

It would be nice to have ability to change path for callback scripts. What do you think about this?

Extend the list of pip extras to support AWS callbacks

In order to install Patroni together with dependencies for specific DCS (defined by patroni_dcs variable) and AWS callbacks, we should come up with a way to extend the list of pip "extras" to something like patroni[{{ patroni_dcs }},aws].

An option to disable backup on copy tasks

Hi @kostiantyn-nemchenko !

Thanks a lot for this, it is very useful....

I've played with the role to deploy callbacks scripts on various hosts and in the long I find that the backup: true option on the copy task is a bit annoying:

https://github.com/kostiantyn-nemchenko/ansible-role-patroni/blob/master/tasks/configure.yml#L27

When I run the playbook several times, the destination folders is fillled with backup files which are not really useful...

Do you think we could had an option called backup_on_copy and let users decide if they want to keeps old version or not ?

If you agree with this idea, I can submit a PR for that.

Add support for EL 8

To support EL 8 the installation of PostgreSQL has to be modified. There has to be the postgres module disabled and the installation has to be executed by dnf. Also, the configuration of the repository GPG is required.

More information here: https://www.postgresql.org/download/linux/redhat/

How to generate haproxy configuration using patronictl

Hi Sir,

We have successfully installed the patroni, its working fine as expected. We are planning to generate HAPROXY configuration using patroni, when I have checked only below commands available

NOTE:
Patroni provides an HAProxy configuration, which will give your application a single endpoint for connecting to the cluster’s leader

Commands:
configure Create configuration file
dsn Generate a dsn for the provided member, defaults to a dsn of...
edit-config Edit cluster configuration
failover Failover to a replica
flush Flush scheduled events
list List the Patroni members for a given Patroni
pause Disable auto failover
query Query a Patroni PostgreSQL member
reinit Reinitialize cluster member
reload Reload cluster member configuration
remove Remove cluster from DCS
restart Restart cluster member
resume Resume auto failover
scaffold Create a structure for the cluster in DCS
show-config Show cluster configuration
switchover Switchover to a replica
version Output version of patronictl command or a running Patroni...

when I checked configuration command like below, even this command also not able to find regarding HAPROXY .

patronictl configure

Configuration file [/root/.config/patroni/patronictl.yaml]:
DCS connect url [etcd://localhost:2379]:
Namespace [/service/]:

Can you please guid me how to generate haproxy configuration using patroni.

Custom bootstrap methods

See http://patroni.readthedocs.io/en/latest/replica_bootstrap.html#bootstrap for details.

how to change postgres password

My Client is asking if we want to change the postgress password via this role?. how can we change the password. while the the first time, then only we can set the password, If I change the password and re run the this role, we are not able to change the potgress password.

Check that variable is defined and is not empty

An ugly condition (e.g. if not( (patroni_etcd_url is none) or (patroni_etcd_url | trim == '') )) for checking that variable is defined and is not empty should be improved somehow.
Perhaps, a custom Jinja filter will do the trick

Patroni is not running

Hi Sir,

I am trying to setup patroni on 2 servers. ETCD cluster is showing healthy. Even Patroni play book also not shown any issues. But Patroni is not up and running. I am facing below issues.
With the same play book when I have setup with 3 nodes/servers, its working fine. Can you please help me to fix this issue.

2018-12-25T23:13:24-08:00 daemon:info [1e] patroni (31544): return self.post_bootstrap()
2018-12-25T23:13:24-08:00 daemon:info [1e] patroni (31544): File "/usr/local/lib/python2.7/dist-packages/patroni/ha.py", line 1079, in post_bootstrap
2018-12-25T23:13:24-08:00 daemon:info [1e] patroni (31544): self.cancel_initialization()
2018-12-25T23:13:24-08:00 daemon:info [1e] patroni (31544): File "/usr/local/lib/python2.7/dist-packages/patroni/ha.py", line 1074, in cancel_initialization
2018-12-25T23:13:24-08:00 daemon:info [1e] patroni (31544): raise PatroniException('Failed to bootstrap cluster')
2018-12-25T23:13:24-08:00 daemon:info [1e] patroni (31544): patroni.exceptions.PatroniException: 'Failed to bootstrap cluster'
2018-12-25T23:13:24-08:00 daemon:notice [1d] systemd (1): patroni.service: Main process exited, code=exited, status=1/FAILURE
2018-12-25T23:13:24-08:00 daemon:notice [1d] systemd (1): patroni.service: Unit entered failed state.
2018-12-25T23:13:24-08:00 daemon:warning [1c] systemd (1): patroni.service: Failed with result 'exit-code'.

Optimize variables for new Postgres YUM repo structure

Hi,

Wanted to upgrade installable version of Postgres to 11. It turned out that for Yum they have changed the approach and now they have single rpm that contains repository for all the packages.

postgresql_yum_repo_url: "https://yum.postgresql.org/{{ patroni_postgresql_version }}/{{ 'fedora' if ansible_distribution|lower == 'fedora' else 'redhat' }}/{{ 'fedora' if ansible_distribution|lower == 'fedora' else 'rhel' }}-{{ ansible_distribution_major_version }}-{{ ansible_architecture }}/{{ postgresql_yum_repo_pkg_name }}"

postgresql_yum_repo_pkg_name: pgdg-redhat-repo-latest.noarch.rpm
postgresql_yum_repo_pkg_version: 11.4-1PGDG.rhel7

patroni_postgresql_packages:
  - { name: "postgresql{{ patroni_postgresql_version|replace('.','') }}-{{ postgresql_yum_repo_pkg_version }}",         state: "present" }
  - { name: "postgresql{{ patroni_postgresql_version|replace('.','') }}-server-{{ postgresql_yum_repo_pkg_version }}",  state: "present" }
  - { name: "postgresql{{ patroni_postgresql_version|replace('.','') }}-contrib-{{ postgresql_yum_repo_pkg_version }}", state: "present" }
  - { name: "postgresql{{ patroni_postgresql_version|replace('.','') }}-devel-{{ postgresql_yum_repo_pkg_version }}",   state: "present" }

I've introduced postgresql_yum_repo_pkg_version variable to specify specific version which will be passed to package names. It would be good to have the same approach built-in and define it on default level so that it is overridable by inventory variables

ERROR: failed to bootstrap from leader

After executed the this role ETCD cluster health is good and

etcdctl cluster-health
member 66070623373786c7 is healthy: got healthy result from http://192.168.33.10:2379
member a5bc874f58c33150 is healthy: got healthy result from http://192.168.33.13:2379
member beea9366978d9511 is healthy: got healthy result from http://192.168.33.14:2379
cluster is healthy

and we facing the issue at patroni like ERROR: failed to bootstrap . even Patroni status is fine can you please help us to fix this issue.

sudo systemctl status patroni
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
Loaded: loaded (/etc/systemd/system/patroni.service; enabled; vendor preset: enabled)
Active: active (running) since Thu 2018-11-15 15:45:52 UTC; 18h ago
Main PID: 20451 (patroni)
Tasks: 4
Memory: 24.7M
CPU: 2min 47.226s
CGroup: /system.slice/patroni.service
└─20451 /usr/bin/python /usr/local/bin/patroni /etc/patroni/192.168.33.10.yml

Nov 16 09:47:49 server10 patroni[20451]: 2018-11-16 09:47:49,753 INFO: trying to bootstrap from leader '192.168.33.13'
Nov 16 09:47:49 server10 patroni[20451]: pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.33.10", user "replicator", SSL off
Nov 16 09:47:49 server10 patroni[20451]: pg_basebackup: removing data directory "/var/lib/postgresql/10/main"
Nov 16 09:47:49 server10 patroni[20451]: 2018-11-16 09:47:49,764 ERROR: Error when fetching backup: pg_basebackup exited with code=1
Nov 16 09:47:49 server10 patroni[20451]: 2018-11-16 09:47:49,764 WARNING: Trying again in 5 seconds
Nov 16 09:47:54 server10 patroni[20451]: pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host "192.168.33.10", user "replicator", SSL off
Nov 16 09:47:54 server10 patroni[20451]: pg_basebackup: removing data directory "/var/lib/postgresql/10/main"
Nov 16 09:47:54 server10 patroni[20451]: 2018-11-16 09:47:54,780 ERROR: Error when fetching backup: pg_basebackup exited with code=1
Nov 16 09:47:54 server10 patroni[20451]: 2018-11-16 09:47:54,780 ERROR: failed to bootstrap from leader '192.168.33.13'
Nov 16 09:47:54 server10 patroni[20451]: 2018-11-16 09:47:54,780 INFO: Removing data directory: /var/lib/postgresql/10/main

Please add support install using proxy

Please add support install using proxy

Support of post_init/post_bootstrap scripts

Add support of post_init/post_bootstrap scripts - an additional scripts that will be executed after initializing the cluster using initdb or custom bootstrap method respectively.

Deployment failure on ubuntu 18.04 with python3

Hello

Would you consider a pull request which switches patroni_system_packages to python3 (python3-psycopg2 python3-pip) for ubuntu 18.04 but keeps them as they are for other versions (including Debian itself)?

Regards
Kristjan

AnsibleUndefinedVariable dict object has no attribute etcd

I dont know how fix it.

TASK [retr0h.etcd : Add etcd systemd service] ***************************************************************************************************
fatal: [ubuntu1]: FAILED! => {"changed": false, "msg": "AnsibleUndefinedVariable: 'dict object' has no attribute u'etcd'"}
fatal: [ubuntu2]: FAILED! => {"changed": false, "msg": "AnsibleUndefinedVariable: 'dict object' has no attribute u'etcd'"}
fatal: [ubuntu3]: FAILED! => {"changed": false, "msg": "AnsibleUndefinedVariable: 'dict object' has no attribute u'etcd'"}

cat patroni.yml 
- hosts: patroni
  become: yes
  roles:
    - kostiantyn-nemchenko.patroni

Take into account the current timeline during leader election

Could be achieved by setting bootstrap.dcs.check_timeline to true.

pg_rewind related features

Starting from 1.5.5 it is possible to automatically reinitialize the former master using by setting postgresql.remove_data_directory_on_diverged_timelines to true.
Additionally, use_pg_rewind shoud be changed to false as this is the default value in patroni.

Issue with non -alphanumeric Postgresql configuration value

The Patroni cannot start when it use non-alphanumeric value for value in patroni_postgresql_parameters and patroni_bootstrap_dcs_postgresql_parameters.

e.g:

patroni_postgresql_parameters:
  - { option: "listen_addresses", value: "*" }
  - { option: "log_line_prefix", value: "%t [%p]: [%l-1] user=%u,db=%d,client=%h" }

Bootstrap vs Dynamic configuration settings

Could you explain the rationale behind setting configuration values on the bootstrap.dcs level?
Values such as maximum_lag_on_failover or slots can often change over the time. With bootstrap configuration it seems that it is impossible to change them afterwards, wouldn't it be better to set them using dynamic configuration approach?

Allow to specify consul consistency mode within yaml config

Versioning Content

One of the best practice recommended in ansible galaxy documentation is to use versioning content. Doing so allow users to pin the version of the role in order to avoid surprise in production during the next ansible-galaxy install.
This require only to add tags to commit which is not done at the moment in this repository.

Here is an example of my side project requirements.yml

---
roles:
  - name: brianshumate.consul
    version: v2.5.4
  - name: kostiantyn-nemchenko.patroni

collections: []

As you can see I can pin the version I want yo use for brianshumate.consul but not for kostiantyn-nemchenko.patroni due to missing tags.

What do you think about adding 1.0.0 tag to the latest commit?

Don't download dcs roles that I don't want

How do I ignore the roles that I don't want from the role meta/main.yml? I am not going to use zookeeper (and that role is broken due to version number scheme change), nor etcd. I want to use only consul, but all three roles get downloaded when I install this role with ansible-galaxy

Kubernetes support

Kubernetes is supported as a DCS starting from Patroni 1.4

Add reload support for patroni.service unit file

Define ExecReload option and respective command to be executed when the unit is reloaded.

Support work with etcd cluster via set of proxies

By setting etcd.use_proxies to true, Patroni will consider hosts as a list of proxies and will not perform a topology discovery of etcd cluster.

Enable "check-ssl" and "verify none" in HAProxy configuration if certfile is false

Changes are required to make HAProxy work if certfile is specified:
"certfile: (optional): Specifies the file with the certificate in the PEM format. If the certfile is not specified or is left empty, the API server will work without SSL."

There also should be considered if verify_client is set to "required", that HAProxy probably needs certificates to communicate with the Patroni REST-API for safe requests:
"verify_client: (optional): none (default), optional or required. When none REST API will not check client certificates. When required client certificates are required for all REST API calls. When optional client certificates are required for all unsafe REST API endpoints. When required is used, then client authentication succeeds if the certificate signature verification succeeds. "

Add support for SSL options under authentication section in patroni.yml.j2

It would be good if it was possible to configure ssl related options for users in the postgressql.authentication section of patroni.yml.j2, i.e: sslmode,sslkey and so on.

Check defined environment variables

Patroni supports defining some of the configuration parameters by setting the system environment variables. We should check existing environment variables (e.g. {{ ansible_env.SOME_VARIABLE }}) before tweaking the Patroni configuration file because those variables always take precedence over the ones set in the Patroni configuration file.

How to connect postgres user from remote

Hi Sir,
May I know how can I assign password for postgres user, like below.

Inside the psql shell you can give the DB user postgres a password:

ALTER USER postgres PASSWORD 'newPassword';

Via patroni role how can I do that ?

Support of Exhibitor as a DCS

Watchdog device does not get correct permissions on initial deployment

Hello

This is happening on Ubuntu 18.04

I noticed that patroni systemd service logs the following warning on initial deployment:

Nov 05 07:05:15 node01 patroni[18221]: 2019-11-05 07:05:15,494 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 13] Permission denied: '/dev/watchdog'"

This comes from the fact that /dev/watchdog is not owned by postgres:postgres (though it should).

If goes away after node reboot, for which ever reason (/dev/watchdog is now owned by postgres:postgres).

Investigated this a bit and noticed that the following one liner does not work as expected (I suspect specifing multiple ExecStart in patroni-watchdog.service work in similar fashion):

root@node01:~# modprobe softdog; chown postgres:postgres /dev/watchdog
root@node01:~# ls -l /dev/watchdog
crw------- 1 root root 10, 130 Nov  5 07:10 /dev/watchdog

In the same time, adding sleep 1 does whats expected (same goes for patroni-watchdog.service when adding ExecStart=/bin/sleep 1 between modprobe and chown):

root@node01:~# modprobe softdog; sleep 1; chown postgres:postgres /dev/watchdog
root@node01:~# ls -l /dev/watchdog
crw------- 1 postgres postgres 10, 130 Nov  5 07:11 /dev/watchdog

I personally don't like adding sleep, so I worked around it by defining udev rule as /etc/udev/rules.d/60-watchdog.rules:

KERNEL=="watchdog", OWNER="postgres", GROUP="postgres", MODE="0600"

Which solution would you prefer?

Required dependencies changed for CentOS 7.6

While running role on CentOS 7.6 i've identified that the set of built-in dependencies is insufficient to get Patroni installed.

Setting values as follows helped to solve the issue. Please consider adjusting role to reflect those changes

patroni_system_packages:
  - { name: "gcc", state: "present" }
  - { name: "python-devel", state: "present" }
  - { name: "epel-release", state: "present" }
  - { name: "python2-pip", state: "present" }
  - { name: "python-psycopg2", state: "present" }
  - { name: "jq",         state: "present" }

patroni_pip_packages:
  - { name: "pip",                        state: "latest",  umask: "0022" }
  - { name: "setuptools",                 state: "latest",  umask: "0022" }
  - { name: "patroni[{{ patroni_dcs }}]", state: "present", umask: "0022" }

Custom scripts to clone a new replica

See http://patroni.readthedocs.io/en/latest/replica_bootstrap.html#building-replicas for details.

Error watchdog.service canceled on installation

Hi,
When we use the role to install patroni on our servers, it fails at the step "restart watchdog" with the following message:

fatal: [kilogram]: FAILED! => {"changed": false, "msg": "Unable to restart service watchdog: Job for watchdog.service canceled.\n"}
fatal: [pound]: FAILED! => {"changed": false, "msg": "Unable to restart service watchdog: Job for watchdog.service canceled.\n"}

When we run the playbook again it works however

watchdog device permissions.

Haven't tested it with this playbook, but when I've been working with patroni and watchdog I've encountered situation that after system reboots /dev/watchdog ownership is switched back to the root user. And if you are running patroni with a different user (which you should) it fails to start because of /dev/watchdog permissions. So I had to add a udev rule to chown it back to patroni user on system startup.

Might be a good idea for You to check this scenario.
Looks like softdog module is not persistent with your setup also. So it will not load on startup.

Next release / patroni rest api & ssl

Hi,

We re using your playbook to deploy patroni and need to secure the rest api.
We have seen this commit which solves our problem and would like to use it.

Do you have a planned date to release (/ tag) this modification ?

thank you.
Benoit

Optional dependencies are still required

Hi,

When running role with patroni_dcs_exists: true set on inventory level execution fails with missing dependencies error (below). This should not happen as dependencies are not needed in this case

The error appears to have been in '/ansible-role-patroni/meta/main.yml': line 29, column 5, but may
be elsewhere in the file depending on the exact syntax problem.

The offending line appears to be:

dependencies:
  - role: brianshumate.consul
    ^ here

First run get error in Add etcd systemd service

I get error in Add etcd systemd service if first run playbook

TASK [retr0h.etcd : Add etcd systemd service] ***************************************************************************************************
fatal: [patroni1]: FAILED! => {
    "changed": false
}
MSG:
AnsibleUndefinedVariable: 'ansible.vars.hostvars.HostVarsVars object' has no attribute u'ansible_lo'
fatal: [patroni2]: FAILED! => {
    "changed": false
}
MSG:
AnsibleUndefinedVariable: 'ansible.vars.hostvars.HostVarsVars object' has no attribute u'ansible_lo'

Add support for pg_hba configuration in postgresql config section

We need support of the postgresql.pg_hba, additionally to bootstrap.pg_hba to manage the file.