postgresql 最简主从配置

实验目的

配置一个最简的主从环境，了解基本的主从配置。

环境参数

操作系统	CentOS Linux release 7.9.2009 (Core)
数据库版本	PostgreSQL 10.23
主库端口	15431
备库端口	15432

• 因为只是做实验，所以主备库放在同一台机器上，仅通过端口区分主备
• 操作的系统用户为pg，没有特殊说明的情况下操作命令都由pg用户执行

搭个环境

初始化主库

[pg@localhost ~]$ pg_ctl -D /data/db1 init
The files belonging to this database system will be owned by user "pg".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
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.

creating directory /data/db1 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
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/pgsql-10/bin/pg_ctl -D /data/db1 -l logfile start

配置主库参数

[pg@localhost ~]$ vi /data/db1/postgresql.conf
修改如下参数：
---------------------------------------
port = 15431	#主库端口
unix_socket_directories = '/tmp'  #本地连接socket文件目录pg访问/var/run/postgresql会有权限制问题，反正不打算用本地socket方式连接所以直接改成/tmp

[pg@localhost ~]$ vi /data/db1/pg_hba.conf
确认已开启本地复制的权限，本地连接的所有用户都有备机的复制权限
---------------------------------------
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

初始化备库

备库的基准文件必须来自于主库，直接复制主库文件到备库位置即可。

[pg@localhost ~]$ rsync -avz /data/db1/* /data/db2

#修改主备库目录的权限为0700，否则会导致数据库启动异常
[pg@localhost ~]$ chmod 0700 /data/db[1,2]

配置备库参数

备库需求配置一个特定的参数文件recovery.conf，可以从安装目录中复制文件模板过来修改。

[pg@localhost ~]$ cp /usr/pgsql-10/share/recovery.conf.sample /data/db2/recovery.conf

[pg@localhost ~]$ vi /data/db2/recovery.conf
开启备库模式
---------------------------------------
standby_mode = on

[pg@localhost ~]$ vi /data/db2/postgresql.conf
修改如下参数：
---------------------------------------
port = 15432
unix_socket_directories = '/tmp'

验证主备功能

#启动主库
[pg@localhost ~]$ pg_ctl -D /data/db1/ -l /data/db1/server.log start
waiting for server to start.... done
server started

#启动备库
[pg@localhost ~]$ pg_ctl -D /data/db2/ -l /data/db2/server.log start
waiting for server to start.... done
server started

#查看可用账号和库
[pg@localhost ~]$ psql -l -h localhost -p 15431
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg            +
           |       |          |             |             | pg=CTc/pg
 template1 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg            +
           |       |          |             |             | pg=CTc/pg
(3 rows)

[pg@localhost ~]$ psql -l -h localhost -p 15432
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg            +
           |       |          |             |             | pg=CTc/pg
 template1 | pg    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/pg            +
           |       |          |             |             | pg=CTc/pg
(3 rows)

#连接主库，建表
[pg@localhost ~]$ psql -h localhost -p 15431 -U pg postgres
psql (10.23)
Type "help" for help.

postgres=# create table t3(a int);
CREATE TABLE
postgres=# insert into t3(a) values (1),(2),(3);
INSERT 0 3

#连接备库查看看同步情况
[pg@localhost ~]$ psql -h localhost -p 15432 -U pg postgres
psql (10.23)
Type "help" for help.

postgres=# \dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | t3   | table | pg
(1 row)

postgres=# select * from t3;
 a
---
 1
 2
 3
(3 rows)

postgres=#