Cluster

🔹 Master primary

pg_hba.conf

# Allow local connections with password (md5)
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    all         all         172.18.0.0/24                   trust  


# Replica user access from any host (with password)
host    replication     replicator             0.0.0.0/0              md5   # Change origin

postgresql.conf

# PGDATA
data_directory = '/data' 

# Impostazioni di ascolto e porte
listen_addresses = '*' 
port = 5432  
max_connections = 100

# Impostazioni per la replica
wal_level = replica  
max_wal_senders = 10
wal_keep_size = 128MB    # WAL precedenti conservati nella pg_wal
wal_sender_timeout = 40000   # Timeout Termina le connessioni di replica inattive

#archivio Wal
archive_mode = on  
archive_command = 'cp %p /archive/%f'

# Sincronia della replica
synchronous_commit = local
synchronous_standby_names = '*'  


# Configurazione dei log
log_destination = 'stderr' 
logging_collector = off  
log_statement = 'none'  

# File di configurazione di accesso
hba_file = '/config/pg_hba.conf' 

Docker-compose

services:
  postgres-master:
    image: postgres:17
    container_name: postgres-master
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGDATA: "/data"
    volumes:
      - ./data:/data
      - ./config:/config
      - archive:/archive
    networks:
      - portainer
    command: >
      postgres -c config_file=/config/postgresql.conf

networks:
  portainer:
    external: true

volumes:
  archive:
    name: archive

🔹 Replica Standby

postgresql.conf

# PGDATA
data_directory = '/data'

# Impostazioni di ascolto e porte
listen_addresses = '*'
port = 5432

# Impostazioni per la replica
wal_receiver_timeout = 50000  # Termina le connessioni di replica inattive 
wal_keep_size = 16MB
max_connections = 100
max_wal_senders = 20

# Log
log_destination = 'stderr'
logging_collector = off
log_statement = 'none'

hba_file = '/data/pg_hba.conf'
hot_standby = 'on'
primary_conninfo = 'host=postgres-master port=5432 user=replicator password=password'

# Comando per ripristinare WAL archiviati (modifica path e comando secondo il tuo sistema)
#restore_command = 'cp /archive/%f %p'
#recovery_target_timeline = 'latest'

Docker-compose

services:
  postgres-slave:
    image: postgres:17
    container_name: postgres-slave
    ports:
      - "5433:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      PGDATA: "/data"
    volumes:
      - ./data:/data
      - ./postgresql.conf:/config/postgres.conf
      - archive:/archive
    networks:
      - portainer
    command: >
      postgres -c config_file=/config/postgres.conf
networks:
  portainer:
    external: true

volumes:
  archive:
    external: true

🔹 Comandi

Comandi Utili

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'password';

pg_basebackup -h postgres-master -U replicator -D restore/  -Fp -Xs -P -R