1 Fresh. . . |
# pg_createcluster 9.6 -p 5433 -d /databases/db_01
# pg_lsclusters
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
data_directory = '/db/disk_database_db_01/db_01'
hba_file = '/etc/postgresql/9.6/db_01/pg_hba.conf'
ident_file = '/etc/postgresql/9.6/db_01/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.6-db_01.pid'
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'
port = 5433
max_connections = 100
unix_socket_directories = '/var/run/postgresql'
ssl = true
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
shared_buffers = 1536MB
work_mem = 7864kB
maintenance_work_mem = 384MB
dynamic_shared_memory_type = posix
shared_preload_libraries = 'online_analyze, plantuner,pg_stat_statements'
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
wal_level = replica
wal_buffers = 16MB
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9
#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
max_wal_senders = 2
wal_keep_segments = 32
#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
effective_cache_size = 4608MB
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
stats_temp_directory = '/var/run/postgresql/9.6-db_01.pg_stat_tmp'
#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
datestyle = 'iso, dmy'
timezone = 'localtime'
lc_messages = 'ru_RU.UTF-8' # locale for system error message
# strings
lc_monetary = 'ru_RU.UTF-8' # locale for monetary formatting
lc_numeric = 'ru_RU.UTF-8' # locale for number formatting
lc_time = 'ru_RU.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.russian'
#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
max_locks_per_transaction = 300 # min 10
#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
escape_string_warning = off
standard_conforming_strings = off
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
online_analyze.threshold = 50
online_analyze.scale_factor = 0.1
online_analyze.enable = on
online_analyze.verbose = off
online_analyze.min_interval = 10000
online_analyze.table_type = 'temporary'
plantuner.fix_empty_table = false
# pg_ctlcluster 9.6 db_01 start
hostname port=5433
listen_addresses = '*'
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 128
postgres=# CREATE ROLE replica WITH REPLICATION PASSWORD 'MyBestPassword' LOGIN;
# TYPE DATABASE USER ADDRESS METHOD
host replication replica 192.168.0.0/24 md5
# pg_ctlcluster 9.6 db_01 restart
# pg_ctlcluster 9.6 db_01 stop
hot_standby = on
# cd /databases/db_01
# rm -Rf /databases/db_01
# su postgres -c "pg_basebackup -h master.domain.local -p 5433 -U replica -D /databases/db_01 -R -P --xlog-method=stream"
standby_mode = 'on'
primary_conninfo = 'user=replica password=MyBestPassword host=master.domain.local port=5433 sslmode=prefer sslcompression=1 krbsrvname=postgres'
# pg_ctlcluster 9.6 db_01 start
SELECT *,pg_xlog_location_diff(s.sent_location,s.replay_location) byte_lag FROM pg_stat_replication s;
SELECT now()-pg_last_xact_replay_timestamp();
archive_command = 'test ! -f /wal_backup/db_01/%f && cp %p /wal_backup/db_01/%f'
#!/bin/bash
db="db_01"
wal_arch="/wal_backup"
datenow=`date '+%Y-%m-%d %H:%M:%S'`
mkdir /tmp/pg_backup_$db
su postgres -c "/usr/bin/pg_basebackup --port=5433 -D /tmp/pg_backup_$db -Ft -z -Xf -R -P"
test -e ${wal_arch}/$db/base.${datenow}.tar.gz && rm ${wal_arch}/$db/base.${datenow}.tar.gz
cp /tmp/pg_backup_$db/base.tar.gz ${wal_arch}/$db/base.${datenow}.tar.gz
# pg_ctlcluster 9.6 db_01 stop
# rm -Rf /databases/db_01
restore_command = 'cp /wal_backup/db_01/%f %p'
recovery_target_time = '2017-06-12 21:33:00 MSK'
recovery_target_inclusive = true