- 配置环境:
-
- 主机名
- IP地址
- 角色
- 数据目录
- pg
- 186.168.100.14
- 主库
- /Postgresql/9.6.1/datan
- pghs
- 186.168.100.24
- standby
- /Postgresql/9.6.1/datahs
-
- 主数据库的配置:
- 允许主库接受流复制的连接pg_hba.conf中:
- host replication postgres 186.168.100.0/24 trust
-
- postgresql.conf设置:
- listen_addresses = '*'
- max_wal_senders = 5
- wal_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 postgresbackup20170209
- 45089/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 access
- DETAIL: 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 process
- HINT: Future log output will appear in directory "pg_log".
-
-
- 在主库建一个表,然后插入几条数据:
- postgres=# create table testhsb(id int,name varchar(10));
- CREATE TABLE
- postgres=# 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