Pg-数据同步详解配置

初次接触po s t g re s q l, 因海外服务器打算上p g 数据库， 为何这样，因为钱，你说国内用用ora c le, 没什么。 国外，就有点涉及到版权的问题的。

其实国内，国外都有这一块， 只是。。。。 此处省略 n 课字， 为了环保吧。

postgres hot_standby 主从配置部署: (hot_standy)

版本: postgresql 9.6.2

Centos 6.8 x86 64bit

配置文件: pg_hba.conf 网管控制(配置参数)

postgres.conf 数据库参数配置文件

recovery.conf 备库需手动创建的 配置同步参数文件

主库: 192.168.41.212 端口: 5438

备库1: 10.0.1.82 端口: 5438

备库2: 192.168.41.230 端口: 5438

## Postgresql 流复制是异步的, 其缺点,显而易见. 如果使用做读写分离, 就会存在数据一致性的问题.

9.1 之后,提供了同步流复制. 同步复制要求数据在写入standby 数据库后, 事务的commit 才返回. 所以standby 出现问题会导致主库 hang 朱(所以一般使用1主2从)

## 实现同步复制功能主要在主库上配置 参数synchronous_standby_names , 这个参数指定多个 standby 的名称.各个名称通过逗号分隔.

## 而standby 名称是在 standby 连接主库时, 由连接参数 application_name 指定.

## 要使用同步复制, 在standby 数据库中, recovery.conf 里的primary_conninfo 一定要指定连接参数 application_name.

eg : recovery.conf 配置

standby_mode = on

primary_conninfo = 'application_name=standby01 user=replica password=xxxx host=xxx.xxx.xxx.xx port=5438 ssmode=disable sslcompression=1'

## 配置如下

主库上 进行配置

1, 编辑pg_hba.conf 配置文件,

host replication replica xxx.xxx.xxx.xx(iP) trust

2,主库 postgresql.conf 设置参数　

　max_wal_senders = x (并行度)

wal_level = hot_standby

指定同步复制 standby 的名称:

synchronous_standby_names ='standby01,standby02' (或主机名) (

-- 这里需要和 standby 中的 recovery.conf 的　primary_conninfo 指定保持一致

--synchronous_standby_names 修改只需加载 生效.

3, 主库重启动, 因为这些参数为静态参数, 重启生效.

-- 在生产时, 对应的可以线配置好.

备库1,2 上分别配置recover.conf文件:

1,recovery.conf 配置

standby_mode = on

primary_conninfo = 'application_name=standby01（standby02）user=replica password=xxxx host=xxx.xxx.xxx.xx(主库IP) port=5438 ssmode=disable sslcompression=1'

2, 分别启动备库.

数据测试:

主库： 配置： postgres.conf

[postgres@newmachine postdb]$ cat postgresql.conf

## authentic setting ##

port = 5438

max_connections = 550

unix_socket_directories = '/tmp'

listen_addresses = '*'

superuser_reserved_connections = 5

#uthentication_timeout = 60

deadlock_timeout =2000

max_locks_per_transaction = 64

## base setting ##

datestyle = 'iso, mdy'

timezone = 'PRC'

lc_messages = 'en_US.UTF-8'

lc_monetary = 'en_US.UTF-8'

lc_numeric = 'en_US.UTF-8'

lc_time = 'en_US.UTF-8'

default_text_search_config = 'pg_catalog.english'

## log setting ##

logging_collector = on

log_destination = 'csvlog'

log_directory = '/data/postgresql/postlog'

# -- every day on logs -- #

log_filename = 'pglog5438-%Y-%m-%d_%H%M%S.log'

log_file_mode = 0600

log_connections = on

log_disconnections = off

log_checkpoints = on

log_rotation_age = 1d

log_rotation_size = 200MB

log_truncate_on_rotation = off

log_timezone = 'PRC'

log_lock_waits = on

log_min_duration_statement = 3s

log_lock_waits = on

log_min_messages = info

log_min_error_statement = info

## memory setting ##

shared_buffers = 512MB

temp_buffers = 16MB

work_mem = 32MB

effective_cache_size = 2GB

maintenance_work_mem = 128MB

#max_stack_depth = 2MB

dynamic_shared_memory_type = posix

## WAL setting ##

fsync = on

synchronous_commit = on

wal_sync_method = fdatasync

# -- yixia is defaults -- #

full_page_writes = on

wal_buffers = 16MB

wal_writer_delay = 200ms

commit_delay = 0

commit_siblings = 5

#wal_level = archive

#wal_level = hot_standby

archive_mode = on

archive_command = 'cp %p /data/postgresql/postdb/%f'

#restore_command = 'cp %p /data/postgresql/postdb/%f'

## statistic setting ##

track_activities = on

log_statement_stats = on

autovacuum = on

track_counts = on

## async standby setting ##

max_wal_senders = 5

wal_level = hot_standby

#listen_addresses = '*'

wal_keep_segments = 10240

wal_sender_timeout = 120s

hot_standby = on

hot_standby_feedback = on

max_standby_streaming_delay = 20s

wal_receiver_status_interval = 1s

synchronous_standby_names ='standby82,standby230'

-- 注意这里

备库 1.82 postgres.conf

[postgres@limin-test posdb]$ cat postgresql.conf

## authentic setting ##

port = 5438

max_connections = 600

unix_socket_directories = '/tmp'

listen_addresses = '*'

superuser_reserved_connections = 5

#uthentication_timeout = 60

deadlock_timeout =2000

max_locks_per_transaction = 64

## base setting ##

datestyle = 'iso, mdy'

timezone = 'PRC'

lc_messages = 'en_US.UTF-8'

lc_monetary = 'en_US.UTF-8'

lc_numeric = 'en_US.UTF-8'

lc_time = 'en_US.UTF-8'

default_text_search_config = 'pg_catalog.english'

## log setting ##

logging_collector = on

log_destination = 'csvlog'

log_directory = '/data/postgres/poslog'

# -- every day on logs -- #

log_filename = 'pglog5438-%Y-%m-%d_%H%M%S.log'

log_file_mode = 0600

log_connections = on

log_disconnections = off

log_checkpoints = on

log_rotation_age = 1d

log_rotation_size = 200MB

log_truncate_on_rotation = off

log_timezone = 'PRC'

log_lock_waits = on

log_min_duration_statement = 3s

log_lock_waits = on

log_min_messages = info

log_min_error_statement = info

## memory setting ##

shared_buffers = 512MB

temp_buffers = 16MB

work_mem = 32MB

effective_cache_size = 2GB

maintenance_work_mem = 128MB

#max_stack_depth = 2MB

dynamic_shared_memory_type = posix

## WAL setting ##

fsync = on

synchronous_commit = on

wal_sync_method = fdatasync

# -- yixia is defaults -- #

full_page_writes = on

wal_buffers = 16MB

wal_writer_delay = 200ms

commit_delay = 0

commit_siblings = 5

#wal_level = archive

#wal_level = hot_standby

archive_mode = on

archive_command = 'cp %p /data/postgres/pos_archive/%f'

## statistic setting ##

track_activities = on

log_statement_stats = on

# log_parser_stats =

# log_planner_stats =

# log_executor_stats =

autovacuum = on

track_counts = on

## async standby setting ##

max_wal_senders = 5

wal_level = hot_standby

#listen_addresses = '*'

wal_keep_segments = 10240

wal_sender_timeout = 120s

hot_standby = on

hot_standby_feedback = on

max_standby_streaming_delay = 20s

wal_receiver_status_interval = 1s

recover.conf 配置参数

[postgres@limin-test posdb]$ cat recovery.conf

standby_mode = on

trigger_file = '/data/postgres/poslog/trigger.kenyou'

recovery_target_timeline = 'latest'

restore_command = 'cp %p /data/postgres/pos_archive/%f'

#primary_conninfo = 'host=10.0.1.82 port=5438 user=replica password=replica'

primary_conninfo = 'application_name=standby82 host=192.168.41.212 port=5438 user=replica password=replica'

备库 230 postgres.conf 配置参数

[postgres@sit-db postdb]$ cat postgresql.conf

## authentic setting ##

port = 5438

max_connections = 550

unix_socket_directories = '/tmp'

listen_addresses = '*'

superuser_reserved_connections = 5

#uthentication_timeout = 60

deadlock_timeout =2000

max_locks_per_transaction = 64

## base setting ##

datestyle = 'iso, mdy'

timezone = 'PRC'

lc_messages = 'en_US.UTF-8'

lc_monetary = 'en_US.UTF-8'

lc_numeric = 'en_US.UTF-8'

lc_time = 'en_US.UTF-8'

default_text_search_config = 'pg_catalog.english'

## log setting ##

logging_collector = on

log_destination = 'csvlog'

log_directory = '/home/postgres/postlog'

# -- every day on logs -- #

log_filename = 'pglog5438-%Y-%m-%d_%H%M%S.log'

log_file_mode = 0600

log_connections = on

log_disconnections = off

log_checkpoints = on

log_rotation_age = 1d

log_rotation_size = 200MB

log_truncate_on_rotation = off

log_timezone = 'PRC'

log_lock_waits = on

log_min_duration_statement = 3s

log_lock_waits = on

log_min_messages = info

log_min_error_statement = info

## memory setting ##

shared_buffers = 512MB

temp_buffers = 16MB

work_mem = 32MB

effective_cache_size = 2GB

maintenance_work_mem = 128MB

#max_stack_depth = 2MB

dynamic_shared_memory_type = posix

## WAL setting ##

fsync = on

synchronous_commit = on

wal_sync_method = fdatasync

# -- yixia is defaults -- #

full_page_writes = on

wal_buffers = 16MB

wal_writer_delay = 200ms

commit_delay = 0

commit_siblings = 5

#wal_level = archive

#wal_level = hot_standby

archive_mode = on

archive_command = 'cp %p /home/postgres/pos_archive/%f'

#restore_command = 'cp %p /data/postgresql/postdb/%f'

## statistic setting ##

track_activities = on

log_statement_stats = on

autovacuum = on

track_counts = on

## async standby setting ##

max_wal_senders = 5

wal_level = hot_standby

#listen_addresses = '*'

wal_keep_segments = 10240

wal_sender_timeout = 120s

hot_standby = on

hot_standby_feedback = on

max_standby_streaming_delay = 20s

wal_receiver_status_interval = 1s

recovery.conf 配置参数

[postgres@sit-db postdb]$ cat recovery.conf

standby_mode = on

trigger_file = '/home/postgres/postlog/trigger.kenyou'

recovery_target_timeline = 'latest'

restore_command = 'cp %p /home/postgres/pos_archive/%f'

#primary_conninfo = 'host=10.0.1.82 port=5438 user=replica password=replica'

primary_conninfo = 'application_name=standby230 host=192.168.41.212 port=5438 user=replica password=replica'

备注， 启动备库230时， 一定要做基础备份， 通过 pg_basebackup

[postgres@sit-db postdb]$ /home/postgres/pgsql-9.6/bin/pg_basebackup -F p --progress -D /home/postgres/postdb/ -h 192.168.41.212 -p 5438 -U replica --password

Password:

116720/116720 kB (100%), 1/1 tablespace

NOTICE: pg_stop_backup complete, all required WAL segments have been archived

启动 主-备-备

验证测试：

postgres=# select pid,usename,client_addr,client_hostname,client_port from pg_stat_replication ;

pid | usename | client_addr | client_hostname | client_port

-------+---------+----------------+-----------------+-------------

2433 | replica | 10.0.1.82 | | 38339

18510 | replica | 192.168.41.230 | | 57943

(2 rows)

数据DML 测试：

主库：

! 0/0 [49/1] voluntary/involuntary context switches",,,,,"rollback;",,,"psql.bin"

2017-08-21 10:04:23.274 CST,,,2424,,59964992.978,1731,,2017-08-18 09:57:38 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""

2017-08-21 10:04:23.542 CST,,,2424,,59964992.978,1732,,2017-08-18 09:57:38 CST,,0,LOG,00000,"checkpoint complete: wrote 2 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.202 s, sync=0.024 s, total=0.267 s; sync files=2, longest=0.024 s, average=0.012 s; distance=1 kB, estimate=10749 kB",,,,,,,,,""

2017-08-21 10:09:23.642 CST,,,2424,,59964992.978,1733,,2017-08-18 09:57:38 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,,""

2017-08-21 10:09:23.665 CST,,,2424,,59964992.978,1734,,2017-08-18 09:57:38 CST,,0,LOG,00000,"checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.000 s, total=0.023 s; sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=9674 kB",,,,,,,,,""

备库 82

017-08-21 18:08:01.155 CST,,,30885,,5996bb0e.78a5,2594,,2017-08-18 18:01:50 CST,,0,LOG,00000,"restartpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.107 s, sync=0.015 s, total=0.162 s; sync files=1, longest=0.015 s, average=0.015 s; distance=0 kB, estimate=11943 kB",,,,,,,,,""

2017-08-21 18:08:01.155 CST,,,30885,,5996bb0e.78a5,2595,,2017-08-18 18:01:50 CST,,0,LOG,00000,"recovery restart point at 0/C000300","last completed transaction was at log time 2017-08-21 10:02:59.179756+08",,,,,,,,""

2017-08-21 18:13:00.256 CST,,,30885,,5996bb0e.78a5,2596,,2017-08-18 18:01:50 CST,,0,LOG,00000,"restartpoint starting: time",,,,,,,,,""

备库230 ：

! 0/0 [2/3] voluntary/involuntary context switches",,,,,"select * from test;",,,"psql.bin"

2017-08-21 18:04:50.050 CST,,,15251,,599aade9.3b93,4,,2017-08-21 17:54:49 CST,,0,LOG,00000,"restartpoint starting: time",,,,,,,,,""

2017-08-21 18:04:50.425 CST,,,15251,,599aade9.3b93,5,,2017-08-21 17:54:49 CST,,0,LOG,00000,"restartpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.102 s, sync=0.038 s, total=0.375 s; sync files=1, longest=0.038 s, average=0.038 s; distance=0 kB, estimate=14746 kB",,,,,,,,,""

2017-08-21 18:04:50.425 CST,,,15251,,599aade9.3b93,6,,2017-08-21 17:54:49 CST,,0,LOG,00000,"recovery restart point at 0/C000300","last completed transaction was at log time 2017-08-21 10:02:59.179756+08",,,,,,,,""

2017-08-21 18:09:50.525 CST,,,15251,,599aade9.3b93,7,,2017-08-21 17:54:49 CST,,0,LOG,00000,"restartpoint starting: time",,,,,,,,,""

2017-08-21 18:09:50.574 CST,,,15251,,599aade9.3b93,8,,2017-08-21 17:54:49 CST,,0,LOG,00000,"restartpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.003 s, sync=0.000 s, total=0.048 s; sync files=0, longest=0.000 s, average=0.000 s; distance=1 kB, estimate=13271 kB",,,,,,,,,""

2017-08-21 18:09:50.574 CST,,,15251,,599aade9.3b93,9,,2017-08-21 17:54:49 CST,,0,LOG,00000,"recovery restart point at 0/C000A70","last completed transaction was at log time 2017-08-21 10:02:59.179756+08",,,,,,,,""

------------------------ 完 ---------------------------