Support only one replication type for PostgreSQL (with repmgr)

[atsikham]

It seems that native replication is not supported and doesn't work for PostgreSQL. This task is about:

• Review of PostgreSQL role
• Check if PostgreSQL native replication works (seems that no)
• Check if someone uses that native replication
• Decide if related parts should be just removed or this should be handled by upgrade

Additional context:

We may need a separate task for clean-up in develop since this is not directly related to ARM.

Originally posted by @to-bar in #2231 (comment)

There was also another discussion previously. Highlights:

• In this commit there is a note that native replication feature is deprecated:
  653b7e4

• It's not clear whether with automatic migration on upgrade or not.

• Repmgr was introduced in v0.6.0 but native replication was in v0.3.0. So we may need a crystal ball in order to find out whether someone is still using this feature.

[atsikham]

Note: before any modification, ARM changes should be applied as they affect PostgreSQL part.

[to-bar]

Not groomed yet, since waiting for information whether the native replication is used by DE project. That information will help us decide about the scope.

[plirglo]

According to my talks with some of clients i've got some thoughts regarding this topic

• We've got some clients which use old not supported Epiphany version (<0.6), and there is possibility that they use native replication, but those clients will rather go path of setting up newer version of cluster instead upgrading old one.
• Native replication stopped working and is not needed since we introduce Postgres HA config - v0.6
• We should rather focus on configuration implemented in 0.6 and stop supporting native replication.

[przemyslavic]

✔️Successfully deployed 1 PostgreSQL node on ARM

• epicli apply: AWS x CentOS

✔️Successfully deployed a PostgreSQL cluster with 2 nodes.

• epicli apply: Azure/AWS x RHEL/Ubuntu

✔️Backup has been successfully created for 1 node on ARM

• epicli backup: AWS x CentOS

✔️Backup has been successfully created for 2 nodes cluster

• epicli backup: Azure/AWS x RHEL/Ubuntu

✔️Backup has been successfully restored for 1 node on ARM

• epicli recovery: AWS x CentOS

❌ Recovery does not work for 2 nodes cluster

• epicli recovery: it failed on RHEL:

2021-05-19T08:13:04.2192159Z [38;21m08:13:04 INFO cli.engine.ansible.AnsibleCommand - TASK [recovery : Rejoin secondary node to repmgr cluster] **********************
2021-05-19T08:13:04.2642909Z [38;21m08:13:04 INFO cli.engine.ansible.AnsibleCommand - skipping: [ci-devazurrhelflannel-postgresql-vm-0]
2021-05-19T08:18:06.7793073Z [31;21m08:18:06 ERROR cli.engine.ansible.AnsibleCommand - fatal: [ci-devazurrhelflannel-postgresql-vm-1]: FAILED! => {"changed": true, "cmd": "/usr/pgsql-10/bin/repmgr -f /etc/repmgr/10/repmgr.conf standby follow -F", "delta": "0:05:01.869687", "end": "2021-05-19 08:18:06.695615", "msg": "non-zero return code", "rc": 4, "start": "2021-05-19 08:13:04.825928", "stderr": "NOTICE: setting node 2's primary to node 1\nNOTICE: restarting server using \"sudo /bin/systemctl restart postgresql-10\"\n\nWe trust you have received the usual lecture from the local System\nAdministrator. It usually boils down to these three things:\n\n    #1) Respect the privacy of others.\n    #2) Think before you type.\n    #3) With great power comes great responsibility.\n\nsudo: timed out reading password\nERROR: unable to restart server\nNOTICE: STANDBY FOLLOW failed", "stderr_lines": ["NOTICE: setting node 2's primary to node 1", "NOTICE: restarting server using \"sudo /bin/systemctl restart postgresql-10\"", "", "We trust you have received the usual lecture from the local System", "Administrator. It usually boils down to these three things:", "", "    #1) Respect the privacy of others.", "    #2) Think before you type.", "    #3) With great power comes great responsibility.", "", "sudo: timed out reading password", "ERROR: unable to restart server", "NOTICE: STANDBY FOLLOW failed"], "stdout": "", "stdout_lines": []}

and it's been hanging for over an hour on Ubuntu

[38;21m08:12:50 INFO cli.engine.ansible.AnsibleCommand - TASK [recovery : Start repmgr service] *****************************************
[38;21m08:12:50 INFO cli.engine.ansible.AnsibleCommand - skipping: [ci-devazurubuflannel-postgresql-vm-0]
[38;21m08:12:51 INFO cli.engine.ansible.AnsibleCommand - changed: [ci-devazurubuflannel-postgresql-vm-1]
[38;21m08:12:51 INFO cli.engine.ansible.AnsibleCommand - 
[38;21m08:12:51 INFO cli.engine.ansible.AnsibleCommand - TASK [recovery : Rejoin secondary node to repmgr cluster] **********************
[38;21m08:12:51 INFO cli.engine.ansible.AnsibleCommand - skipping: [ci-devazurubuflannel-postgresql-vm-0]

[przemyslavic]

✔️ Fixed in #2331.