postgresql 10.5 主从复制--搭建测试

前提环境

ｒｏｌｅ	ｍａｓｔｅｒ	ｓｌａｖｅ
ｈｏｓｔ	Ｐｇ１	Ｐｇ２
ｉｐ	192.168.1.11	192.168.1.12
Ｐｇ－ｖｅｒｓｉｏｎ	１０．５	１０．５

 

搭建主机

 

１．二进制安装postgresql10.5数据库

 

[root@pg1 ~]# tar -zxvf postgresql-10.5.tar.gz

[root@pg1 ~]# cd postgresql-10.5/

[root@pg1 postgresql-10.5]# mkdir /usr/local/postgresql

[root@pg1 postgresql-10.5]# yum install -y zlib-devel readline-devel gcc python-devel perl-ExtUtils-Embed

[root@pg1 postgresql-10.5]# ./configure --prefix=/usr/local/postgresql --with-python --with-perl

[root@pg1 postgresql-10.5]# make && make install

 

 

为postgres单独创建用户和文件夹，降权使得用户更加安全

[root@pg1 postgresql-10.5]# useradd postgres

[root@pg1 postgresql-10.5]# mkdir -p /home/pgdata

[root@pg1 postgresql-10.5]# chown -R postgres.postgres /home/pgdata

[root@pg1 postgresql-10.5]# vim /etc/profile

export PATH=/usr/local/postgresql/bin:$PATH

export LD_LIBRARY_PATH=/usr/local/postgresql/lib

[root@pg1 postgresql-10.5]# source /etc/profile

[root@pg1 postgresql-10.5]# vim /etc/hosts

192.168.1.11 pg1

192.168.1.12 pg2

[root@pg1 postgresql-10.5]# chown -R postgres.postgres /usr/local/postgresql/

 

 

 

[root@pg1 postgresql-10.5]# su postgres

[postgres@pg1 postgresql-10.5]$ vim /home/postgres/.bash_profile

export PGHOME=/usr/local/postgresql

export PGDATA=/home/pgdata

export PGHOST=/tmp

export PATH="$HOME/bin:$HOME/.local/bin:$PATH:$PGHOME/bin"

export MANPATH=$PGHOME/share/man:$MANPATH

export LANG=en_US.utf8

export DATE=`date +"%Y-%m-%d %H:%M:%S"`

export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

[postgres@pg1 postgresql-10.5]$ source /home/postgres/.bash_profile

 

 

 

[postgres@pg1 postgresql-10.5]$ /usr/local/postgresql/bin/initdb -D /home/pgdata/

could not change directory to "/root/postgresql-10.5": Permission denied

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

 

The database cluster will be initialized with locale "en_US.utf8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

 

Data page checksums are disabled.

 

fixing permissions on existing directory /home/pgdata ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting dynamic shared memory implementation ... posix

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

 

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

 

Success. You can now start the database server using:

 

/usr/local/postgresql/bin/pg_ctl -D /home/pgdata/ -l logfile start

 

[postgres@pg1 pgdata]$ vim postgresql.conf

listen_addresses = '*'

max_connections = 1000

shared_buffer = 10240MB

wal_level = hot_standby

synchronous_commit = on

checkpoint_timeout = 5min

archive_mode = on

archive_command = '/bin/date'

max_wal_senders = 10

wal_keep_segments = 16

hot_standby = on

max_standby_archive_delay = 30s

max_standby_streaming_delay = 30s

wal_receiver_status_interval = 1s

hot_standby_feedback = on

wal_receiver_timeout = 60s

[postgres@pg1 pgdata]$ vim pg_hba.conf     

host    replication     all             ::1/128                 trust　　　－－注释掉

host     replication     repuser          192.168.1.0/24          md5　　　－－－增加行

host     all             all              192.168.1.0/24     trust　　　－－增加行

 

[postgres@pg1 pgdata]$ cd /usr/local/postgresql/bin

[postgres@pg1 bin]$ ./pg_ctl start -D /home/pgdata/

waiting for server to start....2019-12-10 10:43:14.244 CST [961] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2019-12-10 10:43:14.245 CST [961] LOG:  listening on IPv6 address "::", port 5432

2019-12-10 10:43:14.283 CST [961] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2019-12-10 10:43:14.473 CST [962] LOG:  database system was shut down at 2019-11-22 20:59:10 CST

2019-12-10 10:43:14.562 CST [961] LOG:  database system is ready to accept connections

 done

server started

[postgres@pg1 bin]$ netstat -lnt|grep 5432

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     

tcp6       0      0 :::5432                 :::*                    LISTEN   

[postgres@pg1 bin]$ psql

psql (10.5)

Type "help" for help.

 

postgres=# create role repuser login replication encrypted password '123456';

CREATE ROLE

postgres=# \q

 

从库配置

--salve配置 ，在初始化db前的操作都一样，slave上不需要初始化

从库安装完成后，不初始化，若已经初始化，删除其data目录

 

[postgres@pg2 postgresql-10.5]$ cd

[postgres@pg2 ~]$ pg_basebackup -D /home/pgdata -F p -X stream  -R -v -P -h 192.168.1.11 -p 5432 -U repuser

Password:

pg_basebackup: initiating base backup, waiting for checkpoint to complete

pg_basebackup: checkpoint completed

pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1

pg_basebackup: starting background WAL receiver

23716/23716 kB (100%), 1/1 tablespace                                         

pg_basebackup: write-ahead log end point: 0/20000F8

pg_basebackup: waiting for background process to finish streaming ...

pg_basebackup: base backup completed

 

 

 

配置备库参数postgresql.conf

 

[postgres@pg1 pgdata]$ vim postgresql.conf

#在基础备份时，初始化文件是从主库复制来的，所以配置文件一致，注释掉

#wal_level，

#max_wal_senders

#wal_keep_segments等参数

打开如下参数：

hot_standby = on #在备份的同时允许查询

max_standby_streaming_delay = 30s    #可选，流复制最大延迟

wal_receiver_status_interval = 10s    #可选，从向主报告状态的最大间隔时间

hot_standby_feedback = on    #可选，查询冲突时向主反馈

max_connections = 1100    #默认参数，非主从配置相关参数，表示到数据库的连接数，一般从库做主要的读服务时，设置值需要高于主库

 

 

[postgres@pg2 pgdata]$ vim pg_hba.conf

 

host     replication     repuser       192.168.1.11/24         md5

--追加

[postgres@pg2 pgdata]# vim recovery.conf 

standby_mode = 'on'

primary_conninfo = 'user=repuser password=123456 host=192.168.1.１1 port=5432 sslmode=prefer sslcompression=1 target_session_attrs=any'

 

 

[postgres@pg2 bin]$ ./pg_ctl start -D /home/pgdata

pg_ctl: another server might be running; trying to start server anyway

waiting for server to start....2019-12-11 11:53:34.217 CST [1014] LOG:  listening on IPv4 address "0.0.0.0", port 5432

2019-12-11 11:53:34.217 CST [1014] LOG:  listening on IPv6 address "::", port 5432

2019-12-11 11:53:34.271 CST [1014] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

2019-12-11 11:53:34.550 CST [1015] LOG:  database system was interrupted while in recovery at log time 2019-11-18 16:01:30 CST

2019-12-11 11:53:34.550 CST [1015] HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.

..2019-12-11 11:53:36.384 CST [1015] LOG:  entering standby mode

2019-12-11 11:53:36.459 CST [1015] LOG:  redo starts at 0/60198C0

2019-12-11 11:53:36.460 CST [1015] LOG:  consistent recovery state reached at 0/6019968

2019-12-11 11:53:36.460 CST [1014] LOG:  database system is ready to accept read only connections

2019-12-11 11:53:36.460 CST [1015] LOG:  invalid record length at 0/60199A0: wanted 24, got 0

2019-12-11 11:53:36.552 CST [1019] LOG:  started streaming WAL from primary at 0/6000000 on timeline 1

 done

server started

 

 

 

 

 

 

 

 

 

 

 

 

 

进程查看

 

--master

 

[postgres@pg1 bin]$ ps -ef|grep postgres

root       920   878  0 11:39 pts/0    00:00:00 su postgres

postgres   921   920  0 11:39 pts/0    00:00:00 bash

postgres  1001     1  0 11:52 pts/0    00:00:00 /usr/local/postgresql/bin/postgres -D /home/pgdata

postgres  1003  1001  0 11:52 ?        00:00:00 postgres: checkpointer process   

postgres  1004  1001  0 11:52 ?        00:00:00 postgres: writer process   

postgres  1005  1001  0 11:52 ?        00:00:00 postgres: wal writer process   

postgres  1006  1001  0 11:52 ?        00:00:00 postgres: autovacuum launcher process   

postgres  1007  1001  0 11:52 ?        00:00:00 postgres: archiver process   

postgres  1008  1001  0 11:52 ?        00:00:00 postgres: stats collector process   

postgres  1009  1001  0 11:52 ?        00:00:00 postgres: bgworker: logical replication launcher  

postgres  1010  1001  0 11:53 ?        00:00:00 postgres: wal sender process repuser 192.168.1.82(44008) streaming 0/6019A48

postgres  1011   921  0 11:54 pts/0    00:00:00 ps -ef

postgres  1012   921  0 11:54 pts/0    00:00:00 grep --color=auto postgres

从库

[postgres@pg2 bin]$ ps -ef|grep postgres

root       876   853  0 11:43 pts/0    00:00:00 su postgres

postgres   877   876  0 11:43 pts/0    00:00:00 bash

postgres  1014     1  0 11:53 pts/0    00:00:00 /usr/local/postgresql/bin/postgres -D /home/pgdata

postgres  1015  1014  0 11:53 ?        00:00:00 postgres: startup process   recovering 000000010000000000000006

postgres  1016  1014  0 11:53 ?        00:00:00 postgres: checkpointer process   

postgres  1017  1014  0 11:53 ?        00:00:00 postgres: writer process   

postgres  1018  1014  0 11:53 ?        00:00:00 postgres: stats collector process   

postgres  1019  1014  0 11:53 ?        00:00:00 postgres: wal receiver process   streaming 0/6019A48

postgres  1020   877  0 11:55 pts/0    00:00:00 ps -ef

postgres  1021   877  0 11:55 pts/0    00:00:00 grep --color=auto postgres

 

 

 

 

状态查看

 

 

 

 

--master

 

postgres=# select client_addr,sync_state from pg_stat_replication;

 client_addr  | sync_state

--------------+------------

 192.168.1.82 | async

(1 row)

 

 

postgres=# select * from pg_stat_replication;

 pid | usesysid | usename | application_name | client_addr  | client_hostname | client_port |         backend_start       

  | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_

priority | sync_state

-----+----------+---------+------------------+--------------+-----------------+-------------+-----------------------------

--+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+------

---------+------------

 923 |    16384 | repuser | walreceiver      | 192.168.1.82 |                 |       46516 | 2019-12-12 10:34:40.850709+0

8 |          562 | streaming | 0/6019AF0 | 0/6019AF0 | 0/6019AF0 | 0/6019AF0  |           |           |            |      

       0 | async

(1 row)

 

 

postgres=# create user zhuhaiyan with password '123456';

CREATE ROLE

postgres=# create database mydatabase owner zhuhaiyan;

CREATE DATABASE

postgres=# grant all privileges on database mydatabase to zhuhaiyan;

GRANT

postgres=# \c mydatabase

You are now connected to database "mydatabase" as user "postgres".

mydatabase=# create table mytable(name varchar(20),signup_data DATE);

CREATE TABLE

mydatabase=# insert into mytable(name,signup_data) values('zhu','2019-12-12');

INSERT 0 1

mydatabase=# select * from mytable;

 name | signup_data

------+-------------

 zhu  | 2019-12-12

(1 row)

 

 

可以在从库显示

[postgres@pg2 bin]$ ./psql

psql (10.5)

Type "help" for help.

 

postgres=# \l

                                    List of databases

    Name    |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges    

------------+-----------+----------+-------------+-------------+-------------------------

 mydatabase | zhuhaiyan | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/zhuhaiyan          +

            |           |          |             |             | zhuhaiyan=CTc/zhuhaiyan

 postgres   | postgres  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 repuser    | postgres  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |

 template0  | postgres  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres            +

            |           |          |             |             | postgres=CTc/postgres

 template1  | postgres  | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres            +

            |           |          |             |             | postgres=CTc/postgres

 test1      | zhy       | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/zhy                +

            |           |          |             |             | zhy=CTc/zhy

(6 rows)

 

 

mydatabase=# select * from mytable;

 name | signup_data

------+-------------

 zhu  | 2019-12-12

(1 row)