PostgreSQL学习第十六篇 异步流复制Hot Standby的示例

配置环境：主机名IP地址角色数据目录pg186.168.100.14主库/PostgreSQL/9.6.1/datanpghs186.168.100.24standby/PostgreSQL/9.6.1/datahs主数据库的配置：允许主库接受流复制的连接pg_hba.conf中：host    replication     postgres       186.168.100.0/24                  trustpostgresql.conf设置：listen_addresses = '*'max_wal_senders = 5wal_level = hot_standby重启数据库standby上的操作：在备库生成基础备份：[postgres@pghs data]$ pg_basebackup -h 186.168.100.14 -U postgres -F p -P -x -R -D /PostgreSQL/9.6.1/datahs -l  postgresbackup2017020945089/45089 kB (100%), 2/2 tablespaces那么在/PostgreSQL/9.6.1/datahs 路径下就看到了拷贝过来的文件等，因为使用了-R，所以有recovery.conf文件，内容：、standby_mode = 'on'primary_conninfo = 'user=postgres host=186.168.100.14 port=5432 sslmode=disable sslcompression=1'在启动standby数据库之前，需要修改postgresql.conf文件：hot_standby = on启动standby：[postgres@pghs datahs]$ pg_ctl start -D /PostgreSQL/9.6.1/datahs/server starting[postgres@pghs datahs]$ FATAL:  data directory "/PostgreSQL/9.6.1/datahs" has group or world accessDETAIL:  Permissions should be u=rwx (0700).[postgres@pghs 9.6.1]$ chmod 700 datahs/[postgres@pghs 9.6.1]$ LOG:  redirecting log output to logging collector processHINT:  Future log output will appear in directory "pg_log".在主库建一个表，然后插入几条数据：postgres=# create table testhsb(id int,name varchar(10));CREATE TABLEpostgres=# insert into testhsb values (1,'test');INSERT 0 1备库查询：postgres=# select * from testhsb; id | name----+------  1 | test(1 row)操作马上就同步了在备库尝试修改：postgres=# delete from testhsb where id=1;ERROR:  cannot execute DELETE in a read-only transaction