Q, dev: Create Pgbackrest stanza

[chlordk]

Hi

I'm trying to let Ansible creating the pgbackrest stanza.

I have moved the role in deploy_cluster.yml to a position where the database should have been created and running.
I can see the database is created so it must be running. When I run the command manually after db is started, it works fine.

Or am I doing something which is already there?

Any hint will be appreciated.

TASK [pgbackrest : Create stanza if not exist] ***** fatal: [10.42.69.51]: FAILED! => {"changed": true, "cmd": ["/usr/bin/pgbackrest", "--stanza=val_pasx", "stanza-create"], "delta": "0:00:00.013117", "end": "2022-11-29 11:17:59.340805", "msg": "non-zero return code", "rc": 56, "start": "2022-11-29 11:17:59.327688", "stderr": "WARN: unable to check pg1: [DbConnectError] unable to connect to 'dbname='postgres' port=5432': connection to server on socket \"/var/run/postgresql/.s.PGSQL.5432\

diff --git a/val_postgresql_cluster/deploy_pgcluster.yml b/val_postgresql_cluster/deploy_pgcluster.yml
index 6699f0e..c8a0b05 100644
--- a/val_postgresql_cluster/deploy_pgcluster.yml
+++ b/val_postgresql_cluster/deploy_pgcluster.yml
@@ -136,9 +136,9 @@
       include_vars: "vars/RedHat.yml"
       when: ansible_os_family == 'Rocky' or ansible_os_family == 'AlmaLinux'
       tags: always
-  roles:
-    - role: pgbackrest
-      when: pgbackrest_install|bool
+#  roles:
+#  - role: pgbackrest
+#    when: pgbackrest_install|bool

 - hosts: postgres_cluster
   become: true
@@ -200,6 +200,9 @@
     - role: netdata
       when: netdata_install is defined and netdata_install|bool

+    - role: pgbackrest
+      when: pgbackrest_install|bool
+
     # finish (info)
     - role: deploy-finish

diff --git a/val_postgresql_cluster/roles/pgbackrest/tasks/main.yml b/val_postgresql_cluster/roles/pgbackrest/tasks/main.yml
index 0ffc6e8..6596931 100644
--- a/val_postgresql_cluster/roles/pgbackrest/tasks/main.yml
+++ b/val_postgresql_cluster/roles/pgbackrest/tasks/main.yml
@@ -106,6 +106,10 @@
         owner: postgres
         group: postgres
         mode: 0644
+    - name: Create stanza if not exist
+      command: "/usr/bin/pgbackrest --stanza={{ pgbackrest_stanza }} stanza-create"
+      args:
+        creates: "{{ pgbackrest_repo_path }}/backup/{{ pgbackrest_stanza }}/backup.info"
   when: "'postgres_cluster' in group_names"
   tags: pgbackrest, pgbackrest_conf

[vitabaks]

WARN: unable to check pg1: [DbConnectError] unable to connect to 'dbname='postgres' port=5432': connection to server on socket "/var/run/postgresql/.s.PGSQL.5432

try adding a database availability check on the socket before executing the stanza-create command.

[vitabaks]

@chlordk

1. If you use playbook deploy_pgcluster.yml without tags, it will stop with the error "Whoops! data directory {{ postgresql_data_dir }} is already initialized"

2. If you specify the variable postgresql_exists='true' in inventory You will no longer get this error since the playbook will switch to the mode of converting a basic PostgreSQL installation to a Cluster mode. To do this, the database will be restarted.

You can convert your basic PostgreSQL installation to a high availability cluster. Just specify the variable postgresql_exists='true' in the inventory file. Attention! Your PostgreSQL will be stopped before running in cluster mode (please plan for a short downtime of databases).

We could change the logic a bit so that the playbook does not restart Postgresql if it is already part of the Patroni cluster. \ But we went a slightly different way, now we are developing a playbook "config_pgcluster.yml" designed to manage an already deployed cluster.

Summing up:

if you want to use a playbook designed only for the initial deployment of "deploy_pgcluster.yml", then specify the tag you need, for example, "ansible-playbook deploy_pgcluster.yml --tags pgbackrest". \ Or use the playbook to "config_pgcluster.yml" (tags are also supported).

[vitabaks]

@chlordk How's it going? It would be a great addition to https://github.com/vitabaks/postgresql_cluster/pull/215

[chlordk]

I have to setup another test environment so it will take some more days.

My plan is to look more into config_cluster.yml and hopefully understand it so well that I can write documentation for it.

[vitabaks]

@chlordk here is a short description of the playbooks:

• etcd_cluster.yml - designed for deploying etcd clusters.
• balancers.yml- designed for deploying Haproxy load balancers.
• deploy_pgcluster.yml- designed for deploying new Patroni clusters.
  • includes playbooks: etcd_cluster.yml and balancers.yml
• config_pgcluster.yml - it is intended for configuring parameters for an already deployed Patroni cluster. Can be used for integration with git for cluster configuration management (infrastructure as code). GitHub Action (example), GitLab CI/CD (example)
  • includes the patroni/config role, which keeps the patroni configuration up to date, including Postgres parameters stored in DCS.
  • includes a maintenance variable that can be used in to exclude part of the code logic or add new logic for an already deployed cluster. example.
• add_pgnode.yml - designed to add a new PostgreSQL node to an already deployed Patroni cluster. Cluster Scaling
  • During the run this playbook, the new nodes will be prepared in the same way as when first deployment the cluster. But unlike the initial deployment, all the necessary configuration files will be copied from the master server.
• add_balancer.yml- designed to add a new Haproxy balancer node.
  • During the run this playbook, the new balancer node will be prepared in the same way as when first deployment the cluster. But unlike the initial deployment, all necessary configuration files will be copied from the server specified in the [master] group.

[chlordk]

I've been a bit busy with work. Will get back.

[oxycash]

@chlordk

1. If you use playbook deploy_pgcluster.yml without tags, it will stop with the error "Whoops! data directory {{ postgresql_data_dir }} is already initialized"
2. If you specify the variable postgresql_exists='true' in inventory You will no longer get this error since the playbook will switch to the mode of converting a basic PostgreSQL installation to a Cluster mode. To do this, the database will be restarted.

You can convert your basic PostgreSQL installation to a high availability cluster. Just specify the variable postgresql_exists='true' in the inventory file. Attention! Your PostgreSQL will be stopped before running in cluster mode (please plan for a short downtime of databases).

We could change the logic a bit so that the playbook does not restart Postgresql if it is already part of the Patroni cluster. But we went a slightly different way, now we are developing a playbook "config_pgcluster.yml" designed to manage an already deployed cluster.

Summing up:

if you want to use a playbook designed only for the initial deployment of "deploy_pgcluster.yml", then specify the tag you need, for example, "ansible-playbook deploy_pgcluster.yml --tags pgbackrest". Or use the playbook to "config_pgcluster.yml" (tags are also supported).

I have pg installed but not init. So I am getting this error

fatal: [host]: FAILED! => {"changed": false, "msg": "Whoops! data directory /var/lib/pgsql/12/data is not initialized"}

[vitabaks]

@oxycash You are writing a little bit in the wrong issue. Development issues related to backup support are discussed here.

"Whoops! data directory /var/lib/pgsql/12/data is not initialized"

Specify the variable postgresql_exists='false' in the inventory file for this server.

If you still have questions, please create a new issue.

[vitabaks]

@chlordk Hi, how are you? Let me know if you are ready to implement this functionality, or I will do it myself.

A few details on the implementation:

I see this as a sub-role "stanza-create" to the role "pgbackrest" which is executed after the role "patroni", and performs the following tasks:

1) checks that such a stanza no exists ("Make sure stanza {{ pgbackrest_stanza }} exists"

• command: pgbackrest --stanza={{ pgbackrest_stanza }} check
• if there is no exists, then perform the next task 2) checks the availability of the database on all nodes of the cluster
• it can 'select 1' or Patroni Rest API check
• error if the database is unavailable
• if available, perform the next task 3) create stanza
• command: pgbackrest --stanza={{ pgbackrest_stanza }} stanza-create

Notes:

1. Executed if the variable pgbackrest_cron is is defined and length > 0 (which tells us that we want to backup the cluster and not just restore it).
2. Executed locally on database servers if pgbackrest_repo_type is not "posix"
3. Executed on the dedicated pgbackrest host if pgbackrest_repo_type is "posix" and pgbackrest_repo_host is defined and length > 0

[chlordk]

Hi

I've tried several way of placing the code so the stanza-create is executed after the db but it runs to early. As it has to be tested without having a db created the full deployment has to be run which take some time (6 min). So it is a bit hard to debug I think.

Your numbered plan looks right but I'm not sure how to implement it in ansible.

[vitabaks]

@chlordk Okay, I'll do it. Thanks!

[vitabaks]

Done https://github.com/vitabaks/postgresql_cluster/commit/392dc34acf3099458df9e555cc514a1d1f9e1436