arjunavinfra
commented
1 year ago posgres-cluster.yaml
apiVersion: acid.zalan.do/v1
kind: postgresql
metadata:
annotations:
meta.helm.sh/release-name: infra-postgres
generation: 3
labels:
app: infra-postgres-db
app.kubernetes.io/managed-by: Helm
infra: "true"
k8s-app: infra-postgres-db
name: infra-postgres-db
spec:
additionalVolumes:
- mountPath: /config
name: postgres-exporter-config
targetContainers:
- postgres-exporter
volumeSource:
configMap:
name: postgres-exporter-config
allowedSourceRanges:
- 0.0.0.0/0
databases:
netseva: netseva
enableMasterLoadBalancer: true
enableReplicaLoadBalancer: true
numberOfInstances: 2
patroni:
synchronous_mode: false
podAnnotations:
prometheus: infra-postgres-db
prometheus.io/path: /metrics
prometheus.io/port: "9187"
prometheus.io/scrape: "true"
podPriorityClassName: high-priority
postgresql:
parameters:
checkpoint_completion_target: "0.9"
default_statistics_target: "100"
effective_cache_size: 1536MB
effective_io_concurrency: "200"
log_connections: "off"
log_destination: stderr
log_disconnections: "off"
log_statement: none
logging_collector: "off"
maintenance_work_mem: 128MB
max_connections: "150"
max_parallel_maintenance_workers: "1"
max_parallel_workers: "2"
max_parallel_workers_per_gather: "1"
max_wal_size: 4GB
max_worker_processes: "2"
min_wal_size: 1GB
random_page_cost: "1.1"
shared_buffers: 512MB
tcp_keepalives_count: "0"
tcp_keepalives_idle: "0"
tcp_keepalives_interval: "0"
wal_buffers: 16MB
work_mem: 3495kB
version: "11"
resources:
limits:
cpu: "2"
memory: 16Gi
requests:
cpu: 200m
memory: 4Gi
sidecars:
- env:
- name: DATA_SOURCE_URI
value: localhost/netseva?sslmode=disable
- name: DATA_SOURCE_USER
valueFrom:
secretKeyRef:
key: username
name: postgres.infra-postgres-db.credentials
- name: DATA_SOURCE_PASS
valueFrom:
secretKeyRef:
key: password
name: postgres.infra-postgres-db.credentials
- name: PG_EXPORTER_EXTEND_QUERY_PATH
value: /config/queries.yaml
image: quay.io/anuta/postgres_exporter:v0.8.0
name: postgres-exporter
ports:
- containerPort: 9187
name: pg-metrics
protocol: TCP
teamId: infra
users:
netseva:
- login
volume:
size: 100Gi
storageClass: postgres-storage
patronictl show-config
loop_wait: 10
maximum_lag_on_failover: 33554432
postgresql:
parameters:
archive_mode: 'on'
archive_timeout: 1800s
autovacuum_analyze_scale_factor: 0.02
autovacuum_max_workers: 5
autovacuum_vacuum_scale_factor: 0.05
checkpoint_completion_target: '0.9'
default_statistics_target: '100'
effective_cache_size: 1536MB
effective_io_concurrency: '200'
hot_standby: 'on'
log_autovacuum_min_duration: 0
log_checkpoints: 'on'
log_connections: 'off'
log_disconnections: 'off'
log_line_prefix: '%t [%p]: [%l-1] %c %x %d %u %a %h '
log_lock_waits: 'on'
log_min_duration_statement: 500
log_statement: none
log_temp_files: 0
maintenance_work_mem: 128MB
max_connections: '150'
max_parallel_maintenance_workers: '1'
max_parallel_workers: '2'
max_parallel_workers_per_gather: '1'
max_replication_slots: 10
max_wal_senders: 10
max_wal_size: 4GB
max_worker_processes: '2'
min_wal_size: 1GB
random_page_cost: '1.1'
tcp_keepalives_count: '0'
tcp_keepalives_idle: '0'
tcp_keepalives_interval: '0'
track_functions: all
wal_buffers: 16MB
wal_compression: 'on'
wal_level: hot_standby
wal_log_hints: 'on'
work_mem: 3495kB
use_pg_rewind: true
use_slots: true
retry_timeout: 10
synchronous_mode: false
ttl: 30
postgres.yml
bootstrap:
dcs:
loop_wait: 10
maximum_lag_on_failover: 33554432
postgresql:
parameters:
archive_mode: 'on'
archive_timeout: 1800s
autovacuum_analyze_scale_factor: 0.02
autovacuum_max_workers: 5
autovacuum_vacuum_scale_factor: 0.05
checkpoint_completion_target: '0.9'
default_statistics_target: '100'
effective_cache_size: 1536MB
effective_io_concurrency: '200'
hot_standby: 'on'
log_autovacuum_min_duration: 0
log_checkpoints: 'on'
log_connections: 'off'
log_disconnections: 'off'
log_line_prefix: '%t [%p]: [%l-1] %c %x %d %u %a %h '
log_lock_waits: 'on'
log_min_duration_statement: 500
log_statement: none
log_temp_files: 0
maintenance_work_mem: 128MB
max_connections: '150'
max_parallel_maintenance_workers: '1'
max_parallel_workers: '2'
max_parallel_workers_per_gather: '1'
max_replication_slots: 10
max_wal_senders: 10
max_wal_size: 4GB
max_worker_processes: '2'
min_wal_size: 1GB
random_page_cost: '1.1'
tcp_keepalives_count: '0'
tcp_keepalives_idle: '0'
tcp_keepalives_interval: '0'
track_functions: all
wal_buffers: 16MB
wal_compression: 'on'
wal_level: hot_standby
wal_log_hints: 'on'
work_mem: 3495kB
use_pg_rewind: true
use_slots: true
retry_timeout: 10
synchronous_mode: true
ttl: 30
initdb:
- auth-host: md5
- auth-local: trust
post_init: /scripts/post_init.sh "zalandos"
users:
zalandos:
options:
- CREATEDB
- NOLOGIN
password: ''
kubernetes:
bypass_api_service: true
labels:
application: spilo
pod_ip: 10.200.14.8
ports:
- name: postgresql
port: 5432
role_label: spilo-role
scope_label: pg-cluster-name
use_endpoints: true
namespace: atom
postgresql:
authentication:
replication:
password: <>
username: standby
superuser:
password: <>
username: postgres
basebackup_fast_xlog:
command: /scripts/basebackup.sh
retries: 2
bin_dir: /usr/lib/postgresql/11/bin
callbacks:
on_role_change: /scripts/on_role_change.sh zalandos true
connect_address: 10.200.14.8:5432
create_replica_method:
- basebackup_fast_xlog
data_dir: /home/postgres/pgdata/pgroot/data
listen: '*:5432'
name: infra-postgres-db-0
parameters:
archive_command: /bin/true
bg_mon.history_buckets: 120
bg_mon.listen_address: '::'
extwlist.custom_path: /scripts
extwlist.extensions: btree_gin,btree_gist,citext,extra_window_functions,first_last_agg,hll,hstore,hypopg,intarray,ltree,pgcrypto,pgq,pgq_node,pg_trgm,postgres_fdw,tablefunc,uuid-ossp,timescaledb,pg_partman
log_destination: stderr
log_directory: ../pg_log
log_file_mode: '0644'
log_filename: postgresql-%u.log
log_rotation_age: 1d
log_truncate_on_rotation: 'on'
logging_collector: 'off'
pg_stat_statements.track_utility: 'off'
shared_buffers: 512MB
shared_preload_libraries: bg_mon,pg_stat_statements,pgextwlist,pg_auth_mon,set_user,timescaledb,pg_cron,pg_stat_kcache
ssl: 'on'
ssl_cert_file: /run/certs/server.crt
ssl_key_file: /run/certs/server.key
pg_hba:
- local all all trust
- hostssl all +zalandos 127.0.0.1/32 pam
- host all all 127.0.0.1/32 md5
- hostssl all +zalandos ::1/128 pam
- host all all ::1/128 md5
- local replication standby trust
- hostssl replication standby all md5
- hostnossl all all all reject
- hostssl all +zalandos all pam
- hostssl all all all md5
pgpass: /run/postgresql/pgpass
use_unix_socket: true
use_unix_socket_repl: true
restapi:
connect_address: 10.200.14.8:8008
listen: :8008
scope: infra-postgres-db
Please, answer some short questions which should help us to understand your problem / question better?
Type of issue? question
I have a requirement to set up disaster recovery on postgresql running as a operator, I have used patroni CRD for the same for setting this up. I need to setup asynchronous communication between the primary cluster and the standby cluster , which has been done using the below attribute .
Also, I need the replication between the master and read-replica synchronously in each cluster like in the below diagram, What are the modifications that I need to do in configuration files to set the primary replica communication as synchronous?