postgresql_patroni

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
postgresql_patroni [2024/02/20 19:26] – [Reinitialize a node] andonovjpostgresql_patroni [2025/05/13 05:14] (current) – andonovj
Line 128: Line 128:
  
 So we have our key-value propagated among the cluster, nice.... So we have our key-value propagated among the cluster, nice....
 +
 +
 +We can also check the health of the cluster endpoints as follow:
 +
 +<Code:Bash|Check cluster endpoints>
 +[root@etcd02 ~]# etcdctl endpoint status --write-out=table --endpoints=http://etcd01:2379,http://etcd00:2379,http://etcd02:2379
 ++--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
 +|      ENDPOINT      |        ID        | VERSION | DB SIZE | IS LEADER | IS LEARNER | RAFT TERM | RAFT INDEX | RAFT APPLIED INDEX | ERRORS |
 ++--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
 +| http://etcd01:2379 | 32552f25bf46ac89 |  3.5.21 |   20 kB |     false |      false |         2 |         12 |                 12 |        |
 +| http://etcd00:2379 | 5dd4414f709cd700 |  3.5.21 |   20 kB |      true |      false |         2 |         12 |                 12 |        |
 +| http://etcd02:2379 | e1e9e5d83c377f01 |  3.5.21 |   20 kB |     false |      false |         2 |         12 |                 12 |        |
 ++--------------------+------------------+---------+---------+-----------+------------+-----------+------------+--------------------+--------+
 +[root@etcd02 ~]# 
 +
 +</Code>
 +
  
 =====Setup Patroni===== =====Setup Patroni=====
Line 214: Line 231:
     log_directory: "/var/log/postgresql"      log_directory: "/var/log/postgresql" 
     log_filename: "postgresql-15-main.log"      log_filename: "postgresql-15-main.log" 
 +    archive_command: 'test ! -f /db/wal_archive/%f && cp %p /db/wal_archive/%f'
 +    archive_mode: 'on'
 EOF EOF
  
 +chown -R postgres:postgres /etc/patroni
 +chown postgres:postgres /db/pgdata
 +chown -R postgres:postgres /var/log/postgresql/
 +chown postgres:postgres /db
 chmod 600 /etc/patroni/stampede.yml chmod 600 /etc/patroni/stampede.yml
 chmod 750 /db/pgdata chmod 750 /db/pgdata
 chown postgres:postgres /var/log/postgresql/patroni.log chown postgres:postgres /var/log/postgresql/patroni.log
 +
 patroni /etc/patroni/stampede.yml &> /var/log/postgresql/patroni.log & patroni /etc/patroni/stampede.yml &> /var/log/postgresql/patroni.log &
 </Code> </Code>
Line 286: Line 310:
 Well, I had many issues, but one of the most important ones was that Node02 was ahead of the Node00 for some reason, so I had to reinitialize Node02: Well, I had many issues, but one of the most important ones was that Node02 was ahead of the Node00 for some reason, so I had to reinitialize Node02:
  
 +**IT IS IMPORTANT TO NOTE THAT, IF YOU UPDATE THE PATRONI YML FILE, YOU NEED TO RELOAD THE CLUSTER AND RESTART IT!!!**
  
 ====Reinitialize a node==== ====Reinitialize a node====
Line 548: Line 573:
  
 That code will create 3 node cluster with everything. That code will create 3 node cluster with everything.
 +
 +
 +=====Backup=====
 +Below you can see a method to backup, encrypt and upload a backup to OCI. The backup uses the replica for a backup source:
 +
 +<Code:bash | Backup >
 +#!/bin/bash
 +
 +switch_logfiles(){
 +    v_leader=$1
 +
 +    while :
 +    do
 +        echo "Switch logfile"
 +        psql -Ubarman -h ${v_leader} -c "select pg_switch_wal()" -d datamine
 +        sleep 10
 +    done
 +}
 +
 +backup() {
 +
 +environment=$1
 +echo "Backup start"
 +
 +echo "Obtaining the first replica in the cluster"
 +REPLICA_HOST=postgres-${environment}
 +
 +v_first_replica=$(curl -sk https://${REPLICA_HOST}:8008/cluster | jq -r '.members[] | select(.role == "replica") | .host' | head -n 1)
 +v_leader=$(curl -sk https://${REPLICA_HOST}:8008/cluster | jq -r '.members[] | select(.role == "leader") | .host')
 +
 +echo "Replacing placeholder"
 +sed -i "s/REPLICA_IP_PLACEHOLDER/${v_first_replica}/g" /etc/barman.d/${environment}/streaming-server.conf
 +
 +echo "Creating replication slot"
 +barman -c /etc/barman_${environment}.conf receive-wal --create-slot ${environment}
 +
 +echo "Starting cron and receive-wal"
 +barman -c /etc/barman_${environment}.conf cron
 +
 +echo "Backup process start"
 +
 +switch_logfiles ${v_leader} &
 +
 +barman -c /etc/barman_${environment}.conf backup ${environment} --wait
 +
 +kill $!
 +
 +echo "Return placeholder"
 +sed -i "s/${v_first_replica}/REPLICA_IP_PLACEHOLDER/g" /etc/barman.d/${environment}/streaming-server.conf
 +}
 +
 +clean_old_backups(){
 +    find /var/log/mbackups -depth -mtime +7 -type f -print
 +}
 +
 +encrypt_backup(){
 +        date
 +        environment=$1
 +        backup_dir=$(ls -rtd /data/backup/${environment}/base/* | tail -1)
 +        cd $(dirname ${backup_dir})
 +        backup_file=$(echo ${backup_dir} | cut -d '/' -f 6)
 +        enc_key=$(cat /var/lib/barman/enckey.enc | base64 -d)
 +        tar zcvf - ${backup_file}/ | openssl enc -e -pbkdf2 -aes-256-ctr -k ${enc_key} -out ${backup_file}.tar.gz.enc
 +        date
 +}
 +
 +copy_files_to_os(){
 +        date
 +        environment=$1
 +        echo "Set BUCKET_NAME and OCI config file variable"
 +        case ${environment} in
 +            "dev")
 +                BUCKET_NAME=postgres-dev-backup
 +                OCI_CONFIG_FILE=/var/lib/barman/ocivault/dev.conf
 +            ;;
 +            "stg")
 +                BUCKET_NAME=postgres-stg-backup
 +                OCI_CONFIG_FILE=/var/lib/barman/ocivault/stg.conf
 +            ;;
 +            "prod")
 +                BUCKET_NAME=postgres-prod-backup
 +                OCI_CONFIG_FILE=/var/lib/barman/ocivault/prod.conf
 +            ;;
 +            *)
 +        esac
 +
 +        export BUCKET_NAME
 +        export OCI_CONFIG_FILE
 +
 +        encrypted_file=$(ls -rtd /data/backup/${environment}/base/* | grep "tar.gz.enc" | tail -1)
 +        oci os object put --bucket-name ${BUCKET_NAME} --file ${encrypted_file}
 +        rm -f ${encrypted_file}
 +        date
 +}
 +
 +backup $1
 +encrypt_backup $1
 +copy_files_to_os $1
 +
 +</Code>
  • postgresql_patroni.1708457178.txt.gz
  • Last modified: 2024/02/20 19:26
  • by andonovj