postgresql流复制配置

2023-08-01 20:43:16

一、配置环境：

示例环境
主机名	IP	角色	系统版本	数据目录	pg版本
db1	192.168.128.128	主库	RedHat5.3	/app/postgreSQL/data	9.1.7
db2	192.168.129.129	备库	RedHat5.3	/app/postgreSQL/data	9.1.7

二、postgresql安装（略）

主库完全安装。备库安装只需要到make install即可不需要initdb。

三、主库配置（在192.168.128.128操作配置）

1、创建复制角色

postgres=# create user rep replication login connection limit 100 encrypted password 'rep123';

CREATE ROLE

2、配置pg_hba.conf

host replication rep 192.168.128.129/32 md5

host all all 192.168.128.1/32 trust

3、配置postgresql.conf

wal_level = hot_standby

archive_mode = on

archive_command = '/bin/date'

wal_keep_segments = 256

max_wal_senders = 32

max_standby_archive_delay = 300s

max_standby_streaming_delay = 300s

wal_receiver_status_interval = 10s

hot_standby_feedback = on

hot_standby = on

/app/postgreSQL/bin/pg_ctl restart

/app/postgreSQL/bin/pg_ctl reload -D /app/postgreSQL/data

四、备库配置（在192.168.128.129）上进行操作

1、创建目录

创建于主库相同的目录并授权如data目录以及后来创建的表空间目录

2、创建密码文件

[postgres@db2 ~]$ vi .pgpass

192.168.128.128:5432:replication:rep:rep123

[postgres@db2 ~]$ chmod 0600 .pgpass

3、pg_basebackup

[postgres@db2 ~]$ /app/postgreSQL/bin/pg_basebackup -F p -D /app/postgreSQL/data -h 192.168.128.128 -p 5432 -U rep

[postgres@db2 data]$ cp /app/postgreSQL/share/postgresql/recovery.conf.sample recovery.conf

4、修改recovery.conf配置

standby_mode = on

primary_conninfo = 'host=192.168.128.128 port=5432 user=rep'

trigger_file = '/app/postgreSQL/data/postgresql.trigger.5432'

五、启动服务

/app/postgreSQL/bin/pg_ctl start -D /app/postgreSQL/data

1、主库进程

[postgres@db1 ~]$ ps -ef|grep postgres

postgres 11174 13893 0 04:13 ? 00:00:00 postgres: wal sender process rep 192.168.128.129(49028) streaming 0/30001D0

postgres 11187 13984 0 04:16 pts/1 00:00:00 ps -ef

postgres 11188 13984 0 04:16 pts/1 00:00:00 grep postgres

postgres 13893 1 0 03:46 pts/1 00:00:00 /app/postgreSQL/bin/postgres

postgres 13895 13893 0 03:46 ? 00:00:00 postgres: writer process

postgres 13896 13893 0 03:46 ? 00:00:00 postgres: wal writer process

postgres 13897 13893 0 03:46 ? 00:00:00 postgres: autovacuum launcher process

postgres 13898 13893 0 03:46 ? 00:00:00 postgres: archiver process last was 000000010000000000000002.00000020.backup

postgres 13899 13893 0 03:46 ? 00:00:00 postgres: stats collector process

root 13921 13890 0 02:10 pts/1 00:00:00 su - postgres

postgres 13922 13921 0 02:10 pts/1 00:00:00 -bash

root 13983 13951 0 02:11 pts/1 00:00:00 su - postgres

postgres 13984 13983 0 02:11 pts/1 00:00:00 -bash

2、备库进程

[postgres@db2 ~]$ ps -ef|grep postgres

postgres 10855 1 0 04:10 pts/1 00:00:00 /app/postgreSQL/bin/postgres -D /app/postgreSQL/data

postgres 10856 10855 0 04:10 ? 00:00:00 postgres: startup process recovering 000000010000000000000003

postgres 10857 10855 0 04:10 ? 00:00:01 postgres: wal receiver process streaming 0/3000260

postgres 10858 10855 0 04:10 ? 00:00:00 postgres: writer process

postgres 10859 10855 0 04:10 ? 00:00:00 postgres: stats collector process

postgres 10869 13863 0 04:15 pts/1 00:00:00 ps -ef

postgres 10870 13863 0 04:15 pts/1 00:00:00 grep postgres

root 13862 3671 0 02:14 pts/1 00:00:00 su - postgres

postgres 13863 13862 0 02:14 pts/1 00:00:00 -bash

六、测试

1、主库建表插入数据

[postgres@db1 ~]$ /app/postgreSQL/bin/psql

psql (9.1.7)

Type "help" for help.

postgres=# create table test (id integer);

CREATE TABLE

postgres=# insert into test values (1);

INSERT 0 1

postgres=# select * from test;

----

(1 row)

2、备库查看数据是否传输。

[postgres@db2 ~]$ /app/postgreSQL/bin/psql