当前位置: article > 正文
Postgresql 异步流复制详解及配置切换_error: cannot execute drop database in a read-only

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

Postgresql 异步流复制详解及配置切换_error: cannot execute drop database in a read-only
