Postgres的流复制最佳实践

initdb -D /exp2/pgdata -E UTF8 --locale=C -U postgres 

listen_addresses = '*'			
port = 5433				
max_connections = 700			
unix_socket_directories = '/exp2/pgdata/data_salon/socket'	
shared_buffers = 1024MB			
work_mem = 10MB				
dynamic_shared_memory_type = posix	
wal_level = hot_standby			
wal_buffers = 1024kB			
checkpoint_segments = 64		
max_wal_senders = 3		
wal_keep_segments = 32		
random_page_cost = 2.0			
effective_cache_size = 3072MB
logging_collector = on			
log_directory = 'pg_log'		
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	
log_rotation_age = 1d			
log_rotation_size = 20MB		
log_min_duration_statement = 5000	
log_line_prefix = '[%d %p %t %c %l]'	
log_timezone = 'Japan'
autovacuum_vacuum_cost_limit = 200	
datestyle = 'iso, ymd'
timezone = 'Japan'
client_encoding = UTF8						
default_text_search_config = 'pg_catalog.simple'
deadlock_timeout = 100s

# "local" is for Unix domain socket connections only  
local   all             all                                     trust  
# IPv4 local connections:  
host    all             all             127.0.0.1/32            trust  
# IPv6 local connections:  
host    all             all             ::1/128                 trust  
# Allow replication connections from localhost, by a user with the  
# replication privilege.  
local   replication     all                                     trust  
host    replication     all             127.0.0.1/32            trust  
host    replication     all             ::1/128                 trust  
host replication all 0.0.0.0/0 md5  

vi recovery.done  
  
recovery_target_timeline = 'latest'  
standby_mode = on  
primary_conninfo = 'host=127.0.0.1 port=5433 user=app_user password=app123'  
# recovery_min_apply_delay = 0   #延迟多少分钟应用，用户可以配置延迟的备库，例如给一点误操作的缓冲时间。在备库不会这么早被应用。  

psql 
Type "help" for help.  
  
postgres=# set synchronous_commit =off;  
SET  
postgres=# create role rep login replication encrypted password 'pwd';  
CREATE ROLE

pg_basebackup -D /exp2/pgdata -F p -X stream -h 127.0.0.1 -p 5433 -U rep  

mv recovery.done recovery.conf  
pg_ctl start -D /exp2/pgdata

pgbench -i -s 100 -h 127.0.0.1 -p 5433 -U postgres  
  
pgbench -n -r -P 1 -h 127.0.0.1 -p 5433 -U postgres -c 32 -j 32 -T 120  

transaction type: <builtin: TPC-B (sort of)>
scaling factor: 100
query mode: simple
number of clients: 32
number of threads: 32
duration: 120 s
number of transactions actually processed: 1326066
latency average = 2.896 ms
latency stddev = 2.030 ms
tps = 11050.199659 (including connections establishing)
tps = 11051.140876 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         0.002  \set aid random(1, 100000 * :scale)
         0.001  \set bid random(1, 1 * :scale)
         0.001  \set tid random(1, 10 * :scale)
         0.000  \set delta random(-5000, 5000)
         0.043  BEGIN;
         0.154  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
         0.112  SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
         0.159  UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
         0.423  UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
         0.092  INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
         1.910  END;