@H_419_2@ postgres hot_standby 主从配置部署: (hot_standy) @H_419_2@ 版本: postgresql 9.6.2 @H_419_2@ Centos 6.8 x86 64bit @H_419_2@ 目录: /usr/local/pgsql* --安装目录 @H_419_2@ /data/postgres/posdb --数据目录 @H_419_2@ 配置文件: pg_hba.conf 网管控制(配置参数) @H_419_2@ postgres.conf 数据库参数配置文件 @H_419_2@ recovery.conf 备库需手动创建的 配置同步参数文件 @H_419_2@ 主库: 10.0.1.82 端口: 5438 @H_419_2@ 备库: 192.168.41.212 端口: 5438 @H_419_2@
@H_419_2@ 流复制: (原理详解) @H_419_2@ 9.0开始提供的一种新的传递WAL 日志的方式,只要primary 数据库 一产生日志,就会传递到standby 数据库. (1,异步,2,同步) 两种方式. (9.2 增加了级联复制功能 ), 9.0之前,主从需第三方同步拷贝 @H_419_2@ 数据同步方式时 : 在primary 提交事务时,一定会等到WAL 日志传递到standby 后才返回,这样可以得到standby 数据完全和 primary 数据库同步. 没有一点落后.(自动切换,可以达到零丢失) @H_419_2@ 数据异步同步方式 : Primary 提交事务后,不必等日志传递到standby 就即可返回. 所以standby 数据库通常 比primary 落后很少. @H_419_2@
@H_419_2@ standby 运行原理: @H_419_2@ postgresql 数据库异常中止后,数据库重启,会重放停机前最后一个checkpoint 点之后的 WAL 日志. 再把数据库恢复到停机时的状态. @H_419_2@ 创建standby 的过程分为俩大步骤: @H_419_2@ a,生成一个基础备份,通过pg_start_backup('db_name'),pg_stop_backup(); @H_419_2@ select pg_start_backup('target') @H_419_2@ --Ps: 可以使用pg_basebackup 来 完成基础备份的步骤. @H_419_2@ b,拷贝备份过程中的所有WAL 日志文件. @H_419_2@ @H_419_2@
@H_419_2@ pg_basebackup 命令简介: @H_419_2@ 这个工具会把整个数据库实例都拷贝出来,而 不只是 把实例中的部分(某个数据库或某张表) 单独备份出来. 该工具需要配置 pg_hba.conf 参数文件 @H_419_2@
@H_419_2@ 参数: @H_419_2@ -D directory 或 --pgdata=directory 指定把备份写到那个目录.(如果目录不存在,会自动创建) @H_419_2@ -F format 或 --format=format : 指定输出的格式 目前支持两种格式: @H_419_2@ @H_419_2@ @H_419_2@ -x 或 --xlog : 备份时会把备份中产生的xlog 文件也自动备份出来. 这样在数据库恢复时,应用这些xlog 文件,把数据库推到一个一致点. 设置这个选项需要设置 wal_keep_segments 参数(以抱枕在备份过程中,需要的WAL日志文件不会被覆盖 ). 与选项 -X fetch 完全一样. @H_419_2@ -X method 或 xlog-method=method: 可以取的值为"f","fetch","s","stream" ,当为f时,与fetch意义相同,与-x 参数一样. 当为"s",stream 表示意思也相同,表示备份开始后,启动另一个流复制从主库接收WAL日志 .(这种方式避免了,使用-X f时,主库上的WAL日志有可能被覆盖从而导致失败的问题.),使用这种连接,主库需要配置 max_wal_senders 至少需要大于2 @H_419_2@ -z 或 --gzip,仅能与tar 输出模式配合使用. 表示tar 备份是经过gzip 压缩的. 生成tar.gz 备份包. @H_419_2@ -Z level 或 --compress=level,指定gzip 压缩级别(1-9),级别越高,耗cpu 越厉害. @H_419_2@ -c fast | spread 或 --checkpoint=fast | spread : 设置checkpoint 的模式是fast还是spread . @H_419_2@ -l label 或 --label=label,指定备份的一个标示. 备份的标识是一个任意字符串,便于今后维护识别. @H_419_2@ -P 或 --progress,允许在备份过程中实时打印备份进度. @H_419_2@ -v 或--verbose,详细模式,在使用-P 参数后,会打印出正在备份的具体的信息. @H_419_2@ -V 或 --version,打印pg_basebackup 的版本后,退出. @H_419_2@ ---------------------------- @H_419_2@ -h host 或 --host =host,执行连接数据库的IP地址或主机名 @H_419_2@ -p port 或 --port=port 指定连接端口 @H_419_2@ -s interval 或 --status-interval = interval: 指定向服务器周期反馈状态的秒数,如果服务器配置了 流复制的超时,在使用--xlog=stream 选项时,这需要设置这个参数. 默认为10s,如果设置为0,表示不向服务器反馈状态. @H_419_2@
@H_419_2@ eg: pg_basebackup -F p --progress -D /data/postgresql/postdb -h 10.0.1.82 -p 5438 -U replica --password (基础备份) @H_419_2@ ############# 我是华丽分界线 ################ @H_419_2@
@H_419_2@ 安装postgresql : @H_419_2@ groupadd -g 1200 postgres @H_419_2@ useradd -m -u 1100 -g postgres postgres @H_419_2@ mkdir -pv /data/Postgresql/{postgresdb,postgreslog} @H_419_2@
@H_419_2@ gunzip postgresql-9.6.2-3-linux-x64-binarIEs.tar.gz @H_419_2@ tar -xvf postgresql-9.6.2-3-linux-x64-binarIEs.tar -C /usr/local/ @H_419_2@
@H_419_2@ 初始化postgressql 数据库: @H_419_2@ cd /usr/local/pgsql-9.6.2/bin @H_419_2@ ./initdb -E utf8 -D /data/Postgresql/postgresdb @H_419_2@
@H_419_2@ 主库: postgres.conf 配置 (4GB-Memory) @H_419_2@ ## authentic setting ## @H_419_2@ port = 5438 @H_419_2@ max_connections = 500 @H_419_2@ unix_socket_directorIEs = '/tmp' @H_419_2@ Listen_addresses = '*' @H_419_2@ superuser_reserved_connections = 5 @H_419_2@ #uthentication_timeout = 60 @H_419_2@ deadlock_timeout =2000 @H_419_2@ max_locks_per_transaction = 64 @H_419_2@
@H_419_2@ ## base setting ## @H_419_2@ datestyle = 'iso,mdy' @H_419_2@ timezone = 'PRC' @H_419_2@ lc_messages = 'en_US.UTF-8' @H_419_2@ lc_monetary = 'en_US.UTF-8' @H_419_2@ lc_numeric = 'en_US.UTF-8' @H_419_2@ lc_time = 'en_US.UTF-8' @H_419_2@ default_text_search_config = 'pg_catalog.english' @H_419_2@
@H_419_2@ ## log setting ## @H_419_2@ logging_collector = on @H_419_2@ log_destination = 'csvlog' @H_419_2@ log_directory = '/data/postgres/poslog' @H_419_2@ # -- every day on logs -- # @H_419_2@ log_filename = 'pglog5438-%Y-%m-%d_%H%M%s.log' @H_419_2@ log_file_mode = 0600 @H_419_2@ log_connections = on @H_419_2@ log_disconnections = off @H_419_2@ log_checkpoints = on @H_419_2@ log_rotation_age = 1d @H_419_2@ log_rotation_size = 200MB @H_419_2@ log_truncate_on_rotation = off @H_419_2@ log_timezone = 'PRC' @H_419_2@ log_lock_waits = on @H_419_2@
@H_419_2@ log_min_duration_statement = 3s @H_419_2@ log_lock_waits = on @H_419_2@ log_min_messages = info @H_419_2@ log_min_error_statement = info @H_419_2@
@H_419_2@ ## memory setting ## @H_419_2@ shared_buffers = 512MB @H_419_2@ temp_buffers = 16MB @H_419_2@ work_mem = 32MB @H_419_2@ effective_cache_size = 2GB @H_419_2@ maintenance_work_mem = 128MB @H_419_2@ #max_stack_depth = 2MB @H_419_2@ dynamic_shared_memory_type = posix @H_419_2@
@H_419_2@ ## WAL setting ## @H_419_2@ fsync = on @H_419_2@ synchronous_commit = on @H_419_2@ wal_sync_method = fdatasync @H_419_2@ # -- yixia is defaults -- # @H_419_2@ full_page_writes = on @H_419_2@ wal_buffers = 16MB @H_419_2@ wal_writer_delay = 200ms @H_419_2@ commit_delay = 0 @H_419_2@ commit_siblings = 5 @H_419_2@ #wal_level = archive @H_419_2@ #wal_level = hot_standby @H_419_2@ archive_mode = on @H_419_2@ archive_command = 'cp %p /data/postgres/pos_archive/%f' @H_419_2@
@H_419_2@ ## statistic setting ## @H_419_2@ track_activitIEs = on @H_419_2@ log_statement_stats = on @H_419_2@ # log_parser_stats = @H_419_2@ # log_planner_stats = @H_419_2@ # log_executor_stats = @H_419_2@ autovacuum = on @H_419_2@ track_counts = on @H_419_2@
@H_419_2@ ## async standby setting ## @H_419_2@ max_wal_senders = 5 @H_419_2@ wal_level = hot_standby @H_419_2@ #Listen_addresses = '*' @H_419_2@ wal_keep_segments = 10240 @H_419_2@ wal_sender_timeout = 120s @H_419_2@
@H_419_2@ ## 初次需要修改参数 ## @H_419_2@ Listener_addresses = '*' --可以是IP,也可以是 ' * ' 替代. @H_419_2@ wal_level = hot_standby --热备模式开启 @H_419_2@ max_wal_sender = 5 --可以并行设置几个流复制连接进程.( 几个从,设置几个) @H_419_2@ wal_keep_segments = 10240 #重要配置 流复制 @H_419_2@ wal_send_timeout = 60s --可防止逻辑错误,延缓同步时间. @H_419_2@ max_connections = 512 --standby此参数设置最好比primary 设置的要大. @H_419_2@ archive_mode = on --允许归档 @H_419_2@ archive_command = 'cp % /data/postgreslog/archivelog/%f' --归档路径 @H_419_2@ 关于wal 日志归档描述: @H_419_2@ @H_419_2@ --以上是主库设置. @H_419_2@
@H_419_2@ 编辑环境变量: @H_419_2@ [postgres@martin.lee-test ~]$ cat .bash_profile @H_419_2@ # .bash_profile @H_419_2@
@H_419_2@ # Get the aliases and functions @H_419_2@ if [ -f ~/.bashrc ]; then @H_419_2@ . ~/.bashrc @H_419_2@ fi @H_419_2@ # User specific environment and startup programs @H_419_2@ PATH=$PATH:$HOME/bin @H_419_2@ export PATH @H_419_2@ export DATADIR=/data/postgres/posdb @H_419_2@ export BASEDIR=/usr/local/pgsql-9.6.2 @H_419_2@ export PATH=$BASEDIR/bin:$PATH:$HOME/bin:$PATH @H_419_2@
@H_419_2@ 编辑 pg_hba.conf 配置参数文件: @H_419_2@ # TYPE DATABASE USER ADDRESS METHOD @H_419_2@
@H_419_2@ # "local" is for Unix domain socket connections only @H_419_2@ local all all trust @H_419_2@ # IPv4 local connections: @H_419_2@ host all all 127.0.0.1/32 trust @H_419_2@ # IPv6 local connections: @H_419_2@ host all all ::1/128 trust @H_419_2@ host all all 0.0.0.0/0 trust #因本地测试环境,我全放开 @H_419_2@ # Allow replication connections from localhost,by a user with the @H_419_2@ # replication privilege. @H_419_2@ #local replication postgres trust @H_419_2@ #host replication postgres 127.0.0.1/32 trust @H_419_2@ host replication postgres ::1/128 trust @H_419_2@ host replication replica 192.168.41.0/24 trust #流复制IP target 端 (网段) @H_419_2@ host replication replica 10.0.1.0/24 trust #流复制IP target 端 (网段) @H_419_2@ --replication 表示支持流复制. 使用的是replica 用户 @H_419_2@
@H_419_2@
@H_419_2@ 启动主库,postgresql 数据库 @H_419_2@ a,通过 @H_419_2@ /usr/local/pgsql-9.6.2/bin/postgres -D /data/postgres/posdb @H_419_2@ b,pg_ctl start -D /data/postgres/posdb @H_419_2@ 检测 启动进程 @H_419_2@ [root@martin.lee-test posdb]# lsof -i:5438 @H_419_2@ COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE name @H_419_2@ postgres 11734 postgres 3u IPv4 19736071 0t0 TCP *:5438 (ListEN) @H_419_2@ postgres 11734 postgres 4u IPv6 19736072 0t0 TCP *:5438 (ListEN) @H_419_2@ postgres 11960 postgres 10u IPv4 19738713 0t0 TCP limin-test.novalocal:5438->192.168.41.212:56398 (ESTABliSHED) @H_419_2@
@H_419_2@
@H_419_2@ # 备库设置: @H_419_2@ a,如果为开始的初始话数据库,不需要删除 /data/目录下的文件. 如果不是,最好删除. @H_419_2@ b,备份复制: pg_basebackup -F p --progress -D /data/postgresql/postdb -h 10.0.1.82 -p 5438 -U replica --password @H_419_2@ c,手动创建recovery.conf 配置文件: @H_419_2@ [postgres@newmachine postdb]$ cat recovery.conf @H_419_2@ standby_mode = on @H_419_2@ trigger_file = '/data/postgresql/postlog/trigger.kenyou' @H_419_2@ recovery_target_timeline = 'latest' @H_419_2@ restore_command = 'cp %p /data/postgresql/postdb/%f' @H_419_2@ primary_conninfo = 'host=10.0.1.82 port=5438 user=replica password=replica' @H_419_2@ d,根据主库的postgresql.conf 配置文件 修改参数配置: @H_419_2@ 1,Listen_addresses = '* ' @H_419_2@ 2,wal_level = hot_standby @H_419_2@ 3,max_connections = xxx,# 一般比主库大一些. @H_419_2@ 4,hot_standby = on @H_419_2@ 5,max_standby_streaming_delay = 30s @H_419_2@ 6. wal_receiver_status_interval = 10s @H_419_2@ 7,hot_standby_Feedback = on # 如果有错误的数据复制,是否向 主进行范例. @H_419_2@
@H_419_2@ ### 备库 postgresql.conf 配置文件 #### @H_419_2@ [postgres@newmachine postdb]$ cat recovery.conf @H_419_2@ standby_mode = on @H_419_2@ trigger_file = '/data/postgresql/postlog/trigger.kenyou' @H_419_2@ recovery_target_timeline = 'latest' @H_419_2@ restore_command = 'cp %p /data/postgresql/postdb/%f' @H_419_2@ primary_conninfo = 'host=10.0.1.82 port=5438 user=replica password=replica' @H_419_2@
@H_419_2@ [postgres@newmachine postdb]$ cat postgresql.conf @H_419_2@ ## authentic setting ## @H_419_2@ port = 5438 @H_419_2@ max_connections = 550 @H_419_2@ unix_socket_directorIEs = '/tmp' @H_419_2@ Listen_addresses = '*' @H_419_2@ superuser_reserved_connections = 5 @H_419_2@ #uthentication_timeout = 60 @H_419_2@ deadlock_timeout =2000 @H_419_2@ max_locks_per_transaction = 64 @H_419_2@
@H_419_2@
@H_419_2@ ## base setting ## @H_419_2@ datestyle = 'iso,mdy' @H_419_2@ timezone = 'PRC' @H_419_2@ lc_messages = 'en_US.UTF-8' @H_419_2@ lc_monetary = 'en_US.UTF-8' @H_419_2@ lc_numeric = 'en_US.UTF-8' @H_419_2@ lc_time = 'en_US.UTF-8' @H_419_2@ default_text_search_config = 'pg_catalog.english' @H_419_2@
@H_419_2@ ## log setting ## @H_419_2@ logging_collector = on @H_419_2@ log_destination = 'csvlog' @H_419_2@ log_directory = '/data/postgresql/postlog' @H_419_2@ # -- every day on logs -- # @H_419_2@ log_filename = 'pglog5438-%Y-%m-%d_%H%M%s.log' @H_419_2@ log_file_mode = 0600 @H_419_2@ log_connections = on @H_419_2@ log_disconnections = off @H_419_2@ log_checkpoints = on @H_419_2@ log_rotation_age = 1d @H_419_2@ log_rotation_size = 200MB @H_419_2@ log_truncate_on_rotation = off @H_419_2@ log_timezone = 'PRC' @H_419_2@ log_lock_waits = on @H_419_2@
@H_419_2@ log_min_duration_statement = 3s @H_419_2@ log_lock_waits = on @H_419_2@ log_min_messages = info @H_419_2@ log_min_error_statement = info @H_419_2@
@H_419_2@ ## memory setting ## @H_419_2@ shared_buffers = 512MB @H_419_2@ temp_buffers = 16MB @H_419_2@ work_mem = 32MB @H_419_2@ effective_cache_size = 2GB @H_419_2@ maintenance_work_mem = 128MB @H_419_2@ #max_stack_depth = 2MB @H_419_2@ dynamic_shared_memory_type = posix @H_419_2@
@H_419_2@ ## WAL setting ## @H_419_2@ fsync = on @H_419_2@ synchronous_commit = on @H_419_2@ wal_sync_method = fdatasync @H_419_2@ # -- yixia is defaults -- # @H_419_2@ full_page_writes = on @H_419_2@ wal_buffers = 16MB @H_419_2@ wal_writer_delay = 200ms @H_419_2@ commit_delay = 0 @H_419_2@ commit_siblings = 5 @H_419_2@ #wal_level = archive @H_419_2@ #wal_level = hot_standby @H_419_2@ archive_mode = on @H_419_2@ archive_command = 'cp %p /data/postgresql/postdb/%f' @H_419_2@ #restore_command = 'cp %p /data/postgresql/postdb/%f' @H_419_2@
@H_419_2@ ## statistic setting ## @H_419_2@ track_activitIEs = on @H_419_2@ log_statement_stats = on @H_419_2@ autovacuum = on @H_419_2@ track_counts = on @H_419_2@
@H_419_2@ ## async standby setting ## @H_419_2@ max_wal_senders = 5 @H_419_2@ wal_level = hot_standby @H_419_2@ #Listen_addresses = '*' @H_419_2@ wal_keep_segments = 10240 @H_419_2@ wal_sender_timeout = 120s @H_419_2@ hot_standby = on @H_419_2@ hot_standby_Feedback = on @H_419_2@ max_standby_streaming_delay = 20s @H_419_2@ wal_receiver_status_interval = 1s @H_419_2@
@H_419_2@ ## 启动备库: @H_419_2@ pg_ctl start -D /data..... @H_419_2@
@H_419_2@ ##检测主从 复制状态: (主库上查看) @H_419_2@ select clIEnt_addr,sync_state from pg_stat_replication; @H_419_2@ postgres=# select clIEnt_addr,sync_state from pg_stat_replication; @H_419_2@ clIEnt_addr | sync_state @H_419_2@ ----------------+------------ @H_419_2@ 192.168.41.212 | async @H_419_2@ (1 row) @H_419_2@
@H_419_2@ postgres=# @H_419_2@ postgres=# select * from pg_stat_replication; @H_419_2@ pID | usesysID | usename | application_name | clIEnt_addr | clIEnt_hostname | clIEnt_port | ba @H_419_2@ ckend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_lo @H_419_2@ cation | sync_priority | sync_state @H_419_2@ -------+----------+---------+------------------+----------------+-----------------+-------------+----------- @H_419_2@ --------------------+--------------+-----------+---------------+----------------+----------------+---------- @H_419_2@ -------+---------------+------------ @H_419_2@ 11960 | 16568 | replica | walreceiver | 192.168.41.212 | | 56398 | 2017-08-16 @H_419_2@ 17:37:45.876011+08 | 1761 | streaming | 0/801DB70 | 0/801DB70 | 0/801DB70 | 0/801DB70 @H_419_2@ | 0 | async @H_419_2@ (1 row) @H_419_2@
@H_419_2@ ## 数据 ddl,dml *** 作测试: @H_419_2@
@H_419_2@ ##standby 只读,所以任何 *** 作,都会告警失败: @H_419_2@ postgres=# drop database martinli; @H_419_2@ ERROR: cannot execute DROP DATABASE in a read-only transaction @H_419_2@
@H_419_2@ [postgres@limin-test ~]$ netstat -lntup|grep 5438 && ps -ef|grep postmaster @H_419_2@ (Not all processes Could be IDentifIEd,non-owned process info @H_419_2@ will not be shown,you would have to be root to see it all.) @H_419_2@ tcp 0 0 0.0.0.0:5438 0.0.0.0:* ListEN 11734/postgres @H_419_2@ tcp 0 0 :::5438 :::* ListEN 11734/postgres @H_419_2@
@H_419_2@ 主库 sender : @H_419_2@ [postgres@limin-test ~]$ ps -ef |grep postgres |grep sender @H_419_2@ postgres 11960 11734 0 Aug16 ? 00:00:19 postgres: wal sender process replica 192.168.41.212(56398) streaming 0/801F168 @H_419_2@ postgres 24621 20934 0 16:56 pts/0 00:00:00 grep sender @H_419_2@
@H_419_2@ 备库 recover : @H_419_2@ [postgres@newmachine postdb]$ ps -ef |grep postgres |grep recover @H_419_2@ postgres 27798 27796 0 Aug16 ? 00:00:00 postgres: startup process recovering 000000010000000000000008 @H_419_2@ postgres 28948 28759 0 08:53 pts/4 00:00:00 grep recover @H_419_2@ ----------------- 异步 同步部署完成 ------------ @H_419_2@
@H_419_2@ 异步postgresql 主备切换: @H_419_2@ 1,停止主库: @H_419_2@ [postgres@limin-test ~]$ pg_ctl stop -D /data/postgres/posdb/ @H_419_2@ waiting for server to shut down....... done @H_419_2@ server stopped @H_419_2@ ------ @H_419_2@ 主库 log 日志: @H_419_2@ 2017-08-17 17:00:46.522 CST,11734,59940c60.2dd6,5,2017-08-16 17:12:00 CST,LOG,00000,"database system is shut down","" @H_419_2@ -------- @H_419_2@ 此时备库的日志: @H_419_2@ ","" @H_419_2@ 2017-08-17 08:58:19.789 CST,28962,5994ea2b.7122,1,2017-08-17 08:58:19 CST,FATAL,XX000," Could not connect to the primary server: Could not connect to server: Connection refused @H_419_2@ Is the server running on host ""10.0.1.82"" and accepting @H_419_2@ TCP/IP connections on port 5438? @H_419_2@ ","" @H_419_2@ ---------- @H_419_2@ 2,查看备库的 recovery.conf 参数文件， 在对应的目录下创建 trigger 文件. @H_419_2@ [postgres@newmachine postdb]$ cat recovery.conf @H_419_2@ standby_mode = on @H_419_2@ trigger_file = '/data/postgresql/postlog/trigger.kenyou' @H_419_2@ recovery_target_timeline = 'latest' @H_419_2@ restore_command = 'cp %p /data/postgresql/postdb/%f' @H_419_2@ primary_conninfo = 'host=10.0.1.82 port=5438 user=replica password=replica' @H_419_2@
@H_419_2@ cd /data/postgresql/postlog/ @H_419_2@ touch trigger.kenyou # 这一步很重要. 创建后,ll 查看,并没有什么,但是,日志及recovery.conf 文件变了: @H_419_2@ --------------- 数据状态查看------ @H_419_2@ 通过pg_controldata : @H_419_2@ 未创建 ( touch trigger.kenyou ) 触发文件之前的 状态信息: @H_419_2@ 备库: @H_419_2@ [postgres@newmachine postlog]$ pg_controldata -D /data/postgresql/postdb/ @H_419_2@ pg_control version number: 960 @H_419_2@ Catalog version number: 201608131 @H_419_2@ Database system IDentifIEr: 6410192200887248642 @H_419_2@ Database cluster state: in archive recovery # 提示恢复状态. @H_419_2@ pg_control last modifIEd: Thu 17 Aug 2017 08:59:15 AM CST @H_419_2@ Latest checkpoint location: 0/9000028 @H_419_2@ Prior checkpoint location: 0/801F1A0 @H_419_2@ Latest checkpoint's REDO location: 0/9000028 @H_419_2@ Latest checkpoint's REDO WAL file: 000000010000000000000009 @H_419_2@ Latest checkpoint's TimelineID: 1 @H_419_2@ Latest checkpoint's PrevTimelineID: 1 @H_419_2@ Latest checkpoint's full_page_writes: on @H_419_2@ Latest checkpoint's NextXID: 0:1761 @H_419_2@ Latest checkpoint's NextOID: 16571 @H_419_2@ Latest checkpoint's NextMultixactID: 1 @H_419_2@ Latest checkpoint's NextMultiOffset: 0 @H_419_2@ Latest checkpoint's oldestXID: 1668 @H_419_2@ Latest checkpoint's oldestXID's DB: 1 @H_419_2@ Latest checkpoint's oldestActiveXID: 0 @H_419_2@ Latest checkpoint's oldestMultixID: 1 @H_419_2@ Latest checkpoint's oldestMulti's DB: 1 @H_419_2@ Latest checkpoint's oldestCommitTsXID:0 @H_419_2@ Latest checkpoint's newestCommitTsXID:0 @H_419_2@ Time of latest checkpoint: Thu 17 Aug 2017 05:00:44 PM CST @H_419_2@ Fake LSN counter for unlogged rels: 0/1 @H_419_2@ Minimum recovery ending location: 0/9000098 @H_419_2@ Min recovery ending loc's timeline: 1 @H_419_2@ Backup start location: 0/0 @H_419_2@ Backup end location: 0/0 @H_419_2@ End-of-backup record required: no @H_419_2@ wal_level setting: replica @H_419_2@ wal_log_hints setting: off @H_419_2@ max_connections setting: 500 @H_419_2@ max_worker_processes setting: 8 @H_419_2@ max_prepared_xacts setting: 0 @H_419_2@ max_locks_per_xact setting: 64 @H_419_2@ track_commit_timestamp setting: off @H_419_2@ Maximum data alignment: 8 @H_419_2@ Database block size: 8192 @H_419_2@ Blocks per segment of large relation: 131072 @H_419_2@ WAL block size: 8192 @H_419_2@ Bytes per WAL segment: 16777216 @H_419_2@
@H_419_2@ 创建 ( ) 触发文件之后的 状态信息: @H_419_2@ Database cluster state: in production # 变法了. @H_419_2@ pg_control last modifIEd: Thu 17 Aug 2017 09:10:30 AM CST @H_419_2@ Latest checkpoint location: 0/9000138 @H_419_2@ Prior checkpoint location: 0/9000028 @H_419_2@ Latest checkpoint's REDO location: 0/9000100 @H_419_2@ Latest checkpoint's REDO WAL file: 000000020000000000000009 @H_419_2@ Latest checkpoint's TimelineID: 2 @H_419_2@ Latest checkpoint's PrevTimelineID: 2 @H_419_2@ Latest checkpoint's full_page_writes: on @H_419_2@ Latest checkpoint's NextXID: 0:1761 @H_419_2@ Latest checkpoint's NextOID: 16571 @H_419_2@ Latest checkpoint's NextMultixactID: 1 @H_419_2@ Latest checkpoint's NextMultiOffset: 0 @H_419_2@ Latest checkpoint's oldestXID: 1668 @H_419_2@ Latest checkpoint's oldestXID's DB: 1 @H_419_2@ Latest checkpoint's oldestActiveXID: 1761 @H_419_2@ Latest checkpoint's oldestMultixID: 1 @H_419_2@ Latest checkpoint's oldestMulti's DB: 1 @H_419_2@ Latest checkpoint's oldestCommitTsXID:0 @H_419_2@ Latest checkpoint's newestCommitTsXID:0 @H_419_2@ Time of latest checkpoint: Thu 17 Aug 2017 09:10:30 AM CST @H_419_2@ Fake LSN counter for unlogged rels: 0/1 @H_419_2@ Minimum recovery ending location: 0/0 @H_419_2@ Min recovery ending loc's timeline: 0 @H_419_2@ Backup start location: 0/0 @H_419_2@ Backup end location: 0/0 @H_419_2@ End-of-backup record required: no @H_419_2@ wal_level setting: replica @H_419_2@ wal_log_hints setting: off @H_419_2@ max_connections setting: 550 @H_419_2@ max_worker_processes setting: 8 @H_419_2@ max_prepared_xacts setting: 0 @H_419_2@ max_locks_per_xact setting: 64 @H_419_2@ track_commit_timestamp setting: off @H_419_2@ Maximum data alignment: 8 @H_419_2@ Database block size: 8192 @H_419_2@ Blocks per segment of large relation: 131072 @H_419_2@ WAL block size: 8192 @H_419_2@ Bytes per WAL segment: 16777216 @H_419_2@ Maximum length of IDentifIErs: 64 @H_419_2@ Maximum columns in an index: 32 @H_419_2@ Maximum size of a TOAST chunk: 1996 @H_419_2@ Size of a large-object chunk: 2048 @H_419_2@ Date/time type storage: 64-bit integers @H_419_2@ float4 argument passing: by value @H_419_2@ float8 argument passing: by value @H_419_2@ Data page checksum version: 0 @H_419_2@
@H_419_2@ 同时,恢复配置文件由 recovery.conf 变为了 recovery.done @H_419_2@ [postgres@newmachine postdb]$ cat recovery.done @H_419_2@ standby_mode = on @H_419_2@ trigger_file = '/data/postgresql/postlog/trigger.kenyou' @H_419_2@ recovery_target_timeline = 'latest' @H_419_2@ restore_command = 'cp %p /data/postgresql/postdb/%f' @H_419_2@ primary_conninfo = 'host=10.0.1.82 port=5438 user=replica password=replica' @H_419_2@ ------log 日志状态 ------ @H_419_2@ ","" @H_419_2@ 2017-08-17 09:10:25.520 CST,29138,5994ed01.71d2,2017-08-17 09:10:25 CST,"Could not connect to the primary server: Could not connect to server: Connection refused @H_419_2@ Is the server running on host ""10.0.1.82"" and accepting @H_419_2@ TCP/IP connections on port 5438? @H_419_2@ @H_419_2@ 2017-08-17 09:10:30.522 CST,27798,5993a108.6c96,6,2017-08-16 09:34:00 CST,1/0,"trigger file found: /data/postgresql/postlog/trigger.kenyou",42)">## 这里找到了一个trigger 文件,(touch trigger.kenyou 后) @H_419_2@ 2017-08-17 09:10:30.522 CST,7,2017-08-16 09:34:00 CST,"redo done at 0/9000028","" @H_419_2@ 2017-08-17 09:10:30.522 CST,8,"last completed transaction was at log time 2017-08-16 17:57:07.604087+08","" @H_419_2@ 2017-08-17 09:10:30.587 CST,9,"selected new timeline ID: 2","" @H_419_2@ 2017-08-17 09:10:30.788 CST,10,"archive recovery complete","" @H_419_2@ 2017-08-17 09:10:30.844 CST,11,"Multixact member wraparound protections are Now enabled","" @H_419_2@ 2017-08-17 09:10:30.868 CST,27800,5993a109.6c98,844,2017-08-16 09:34:01 " @H_419_2@ 2017-08-17 09:10:30.870 CST,27796,5993a108.6c94,3,"database system is ready to accept connections","" @H_419_2@ 2017-08-17 09:10:30.870 CST,29142,5994ed06.71d6,2017-08-17 09:10:30 CST,"autovacuum launcher started","" @H_419_2@ 2017-08-17 09:10:30.940 CST,845,2017-08-16 09:34:01 CST,"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.072 s; sync files=0,longest=0.000 s,average=0.000 s; distance=0 kB,estimate=14633 kB","" @H_419_2@ 2017-08-17 09:15:30.946 CST,846,"checkpoint starting: time","" @H_419_2@ 2017-08-17 09:15:31.091 CST,847,"checkpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added,0 recycled; write=0.102 s,sync=0.018 s,total=0.144 s; sync files=1,longest=0.018 s,average=0.018 s; distance=0 kB,estimate=13170 kB","" @H_419_2@ -------------- @H_419_2@
@H_419_2@ 主库: @H_419_2@ [postgres@martin.lee-test posdb]$ pg_controldata -D /data/postgres/posdb/ @H_419_2@ pg_control version number: 960 @H_419_2@ Catalog version number: 201608131 @H_419_2@ Database system IDentifIEr: 6410192200887248642 @H_419_2@ Database cluster state: shut down ## 因关闭了,所以主库的状态不会改变. @H_419_2@ pg_control last modifIEd: Thu 17 Aug 2017 05:00:46 PM CST @H_419_2@ Latest checkpoint location: 0/9000028 @H_419_2@ Prior checkpoint location: 0/801F1A0 @H_419_2@ Latest checkpoint's REDO location: 0/9000028 @H_419_2@ Latest checkpoint's REDO WAL file: 000000010000000000000009 @H_419_2@ Latest checkpoint's TimelineID: 1 @H_419_2@ Latest checkpoint's PrevTimelineID: 1 @H_419_2@ Latest checkpoint's full_page_writes: on @H_419_2@ Latest checkpoint's NextXID: 0:1761 @H_419_2@ Latest checkpoint's NextOID: 16571 @H_419_2@ Latest checkpoint's NextMultixactID: 1 @H_419_2@ Latest checkpoint's NextMultiOffset: 0 @H_419_2@ Latest checkpoint's oldestXID: 1668 @H_419_2@ Latest checkpoint's oldestXID's DB: 1 @H_419_2@ Latest checkpoint's oldestActiveXID: 0 @H_419_2@ Latest checkpoint's oldestMultixID: 1 @H_419_2@ Latest checkpoint's oldestMulti's DB: 1 @H_419_2@ Latest checkpoint's oldestCommitTsXID:0 @H_419_2@ Latest checkpoint's newestCommitTsXID:0 @H_419_2@ Time of latest checkpoint: Thu 17 Aug 2017 05:00:44 PM CST @H_419_2@ Fake LSN counter for unlogged rels: 0/1 @H_419_2@ Minimum recovery ending location: 0/0 @H_419_2@ Min recovery ending loc's timeline: 0 @H_419_2@ Backup start location: 0/0 @H_419_2@ Backup end location: 0/0 @H_419_2@ End-of-backup record required: no @H_419_2@ wal_level setting: replica @H_419_2@ wal_log_hints setting: off @H_419_2@ max_connections setting: 500 @H_419_2@ max_worker_processes setting: 8 @H_419_2@ max_prepared_xacts setting: 0 @H_419_2@ max_locks_per_xact setting: 64 @H_419_2@ track_commit_timestamp setting: off @H_419_2@ Maximum data alignment: 8 @H_419_2@ Database block size: 8192 @H_419_2@ Blocks per segment of large relation: 131072 @H_419_2@ WAL block size: 8192 @H_419_2@ Bytes per WAL segment: 16777216 @H_419_2@ Maximum length of IDentifIErs: 64 @H_419_2@ Maximum columns in an index: 32 @H_419_2@ Maximum size of a TOAST chunk: 1996 @H_419_2@ Size of a large-object chunk: 2048 @H_419_2@ Date/time type storage: 64-bit integers @H_419_2@ float4 argument passing: by value @H_419_2@ float8 argument passing: by value @H_419_2@ Data page checksum version: 0 @H_419_2@
@H_419_2@ 新主库监听检测: @H_419_2@ [postgres@newmachine postlog]$ ps -ef |grep pos |grep sender @H_419_2@ postgres 29186 27796 0 09:27 ? 00:00:00 postgres: wal sender process replica 10.0.1.82(16143) streaming 0/9000480 @H_419_2@ postgres 29200 28408 0 09:30 pts/3 00:00:00 grep sender @H_419_2@
@H_419_2@
@H_419_2@ 3,把 原备库的 recover.conf 文件 拷贝至原主库,修改对应信息: @H_419_2@ [postgres@limin-test posdb]$ cat recovery.conf @H_419_2@ standby_mode = on @H_419_2@ trigger_file = '/data/postgres/posdb/trigger.kenyou' ## 这里创建需要注意,与原主对应. @H_419_2@ recovery_target_timeline = 'latest' @H_419_2@ restore_command = 'cp %p /data/postgres/poslog/%f' @H_419_2@ #primary_conninfo = 'host=10.0.1.82 port=5438 user=replica password=replica' @H_419_2@ primary_conninfo = 'host=192.168.41.212 port=5438 user=replica password=replica' @H_419_2@
@H_419_2@ 4,启动原主库 (现在的备库) @H_419_2@ pg_ctl start -D /data/postgres/posdb/ @H_419_2@ --------------- @H_419_2@ [postgres@martin.lee-test posdb]$ ps -ef |grep pos |grep recover @H_419_2@ postgres 24823 24821 0 17:31 ? 00:00:00 postgres: startup process recovering 000000020000000000000009 @H_419_2@ postgres 24834 21610 0 17:31 pts/2 00:00:00 grep recover @H_419_2@
@H_419_2@ --状态检测(新备库) @H_419_2@ [postgres@limin-test posdb]$ pg_controldata -D /data/postgres/posdb/ @H_419_2@ pg_control version number: 960 @H_419_2@ Catalog version number: 201608131 @H_419_2@ Database system IDentifIEr: 6410192200887248642 @H_419_2@ Database cluster state: in archive recovery @H_419_2@ pg_control last modifIEd: Thu 17 Aug 2017 05:29:05 PM CST @H_419_2@ Latest checkpoint location: 0/9000028 @H_419_2@ Prior checkpoint location: 0/9000028 @H_419_2@ Latest checkpoint's REDO location: 0/9000028 @H_419_2@ Latest checkpoint's REDO WAL file: 000000010000000000000009 @H_419_2@ Latest checkpoint's TimelineID: 1 @H_419_2@ Latest checkpoint's PrevTimelineID: 1 @H_419_2@ Latest checkpoint's full_page_writes: on @H_419_2@ Latest checkpoint's NextXID: 0:1761 @H_419_2@ Latest checkpoint's NextOID: 16571 @H_419_2@
@H_419_2@ 验证: 在新主库(原主库上) @H_419_2@ martinli=# select * from pg_stat_replication @H_419_2@ martinli-# ; @H_419_2@ pID | usesysID | usename | application_name | clIEnt_addr | clIEnt_hostname | clIEnt_port | backend_start | b @H_419_2@ ackend_xmin | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state @H_419_2@ -------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-- @H_419_2@ ------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------ @H_419_2@ 29278 | 16568 | replica | walreceiver | 10.0.1.82 | | 17444 | 2017-08-17 09:51:12.822522+08 | @H_419_2@ 1762 | streaming | 0/90164E0 | 0/90164E0 | 0/90164E0 | 0/90164E0 | 0 | async @H_419_2@ (1 row) @H_419_2@
@H_419_2@ martinli=# select clIEnt_addr,sync_state from pg_stat_replication ; @H_419_2@ clIEnt_addr | sync_state @H_419_2@ -------------+------------ @H_419_2@ 10.0.1.82 | async @H_419_2@ (1 row) @H_419_2@ # 数据 dml,ddl *** 作测试: .此处省略 N 颗 字........ @H_419_2@ # 新备库 做 ddl,dml *** 作,查看 是否报错 @H_419_2@ martinli=# \dt test @H_419_2@ List of relations @H_419_2@ Schema | name | Type | Owner @H_419_2@ --------+------+-------+---------- @H_419_2@ public | test | table | postgres @H_419_2@ (1 row) @H_419_2@
@H_419_2@ martinli=# drop table test; @H_419_2@ ERROR: cannot execute DROP table in a read-only transaction @H_419_2@ -------------- 我是华丽分割线 --------------END ---------- @H_419_2@ ################## 参考 ############ @H_419_2@ 原从库 *** 作（原主库宕机情况下将其作为主库 *** 作）： @H_419_2@ 在之前备机上的recovery.conf中配置trigger_file = ‘/data/pgdata/pg_stand_by/trigger.unl’ @H_419_2@ touch /data/pgdata/pg_stand_by/trigger.unl @H_419_2@ 修改 pg_hba.conf： @H_419_2@ 增加 @H_419_2@ host replication replica 172.18.18.99/32 md5 @H_419_2@ host replication replica 172.18.18.100/32 md5 @H_419_2@ 重启从库: /usr/pgsql9.3.4/bin/pg_ctl restart -D /data/pgdata/pg_stand_by @H_419_2@ 查看是否切换成功：/usr/pgsql9.3.4/bin/pg_controldata /data/pgdata/pg_stand_by -》Database cluster state: in production 表示是主库 @H_419_2@ recovery.conf文件名字变成了recovery.done @H_419_2@ 3》原主库 *** 作（恢复原主库为从库）： @H_419_2@ cp /usr/pgsql9.3.4/share/recovery.conf.sample /data/pgdata/pg_primary/recovery.conf @H_419_2@ 修改recovery.conf： @H_419_2@ recovery_target_timeline = ‘latest’ @H_419_2@ standby_mode = on @H_419_2@ primary_conninfo = ‘host=172.18.18.101 port=3121 user=replica password=replica’ @H_419_2@ 修改postgresql.conf文件： @H_419_2@ hot_standby = on @H_419_2@ 启动原主库（当前从库）：/usr/pgsql9.3.4/bin/pg_ctl start -D /data/pgdata/pg_primary @H_419_2@ 4》修改100机器从库对应的主库信息： @H_419_2@ 修改recovery.conf ： @H_419_2@ 重启从库：/usr/pgsql9.3.4/bin/pg_ctl restart -D /data/pgdata/pg_stand_by -m fast @H_419_2@ 5》检查主从是否切换成功： @H_419_2@ 在新的主库上执行： @H_419_2@ postgres=# select * from pg_stat_replication; @H_419_2@ ################## @H_419_2@ @H_419_2@ 总结
以上是内存溢出为你收集整理的Postgresql 异步流复制 详解及配置切换全部内容，希望文章能够帮你解决Postgresql 异步流复制 详解及配置切换所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）