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/21 08:52] – [Setup Patroni] 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 157: Line 174:
  
 cat > /etc/patroni/stampede.yml <<EOF cat > /etc/patroni/stampede.yml <<EOF
-scope: stampede +scope: stampede  
-name: node02 +name: ${host}  
- +  
-restapi: +restapi:  
-  listen: node02:8008 +  listen: ${host}:8008  
-  connect_address: node02:8008 +  connect_address: ${host}:8008  
- +  
-etcd: +etcd:  
-  host: node02:2379 +  host: ${host}:2379  
- +  
-bootstrap: +bootstrap:  
-  dcs: +  dcs:  
-    ttl: 30 +    ttl: 30  
-    loop_wait: 10 +    loop_wait: 10  
-    retry_timeout: 10 +    retry_timeout: 10  
-    maximum_lag_on_failover: 1048576 +    maximum_lag_on_failover: 1048576  
-    postgresql: +    postgresql:  
-      use_pg_rewind: true +      use_pg_rewind: true  
-      use_slots: true +      use_slots: true  
-      parameters: +      parameters:  
-        wal_level: logical +        wal_level: logical  
-        wal_log_hints: "on" +        wal_log_hints: "on"  
- +  
-  initdb: +  initdb:  
-  - encoding: UTF8 +  - encoding: UTF8  
-  - data-checksums +  - data-checksums  
- +  
-  pg_hba: +  pg_hba:  
-  - host replication rep_user 10.100.10.202/24 md5 +  - host replication rep_user ${ipaddr}/24 md5  
-  - host all all 10.100.10.0/24 md5 +  - host all all ${subnet}.0/24 md5  
- +  
-  users: +  users:  
-    admin: +    admin:  
-      password: adminpass +      password: adminpass  
-      options: +      options:  
-        - createrole +        - createrole  
-        - createdb +        - createdb  
- +  
-postgresql: +postgresql:  
-  listen: node02:5432 +  listen: ${host}:5432  
-  connect_address: node02:5432 +  connect_address: ${host}:5432  
-  data_dir: /db/pgdata +  data_dir: /db/pgdata  
-  bin_dir: /usr/pgsql-15/bin +  bin_dir: ${bindir} 
-  pgpass: /tmp/pgpass0 +  pgpass: /tmp/pgpass0  
-  authentication: +  authentication:  
-    replication: +    replication:  
-      username: rep_user +      username: rep_user  
-      password: newpass +      password: newpass  
-    superuser: +    superuser:  
-      username: postgres +      username: postgres  
-      password: newpass +      password: newpass  
-  parameters: +  parameters:  
-    unix_socket_directories: '/var/run/postgresql' +    unix_socket_directories: '/var/run/postgresql'  
-    external_pid_file: '/var/run/postgresql/15-main.pid' +    external_pid_file: '/var/run/postgresql/15-main.pid'  
-    logging_collector: "on" +    logging_collector: "on"  
-    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_command: 'test ! -f /db/wal_archive/%f && cp %p /db/wal_archive/%f'
     archive_mode: 'on'     archive_mode: 'on'
Line 556: 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.1708505543.txt.gz
  • Last modified: 2024/02/21 08:52
  • by andonovj