PostgreSQL12 主從安裝

1.官网下载:

https://yum.postgresql.org/12/redhat/rhel-7-x86_64/repoview/postgresqldbserver12.group.html

-rw-r--r--. 1 root root 1566860 Oct 17  2019 postgresql12-12.0-1PGDG.rhel7.x86_64.rpm

-rw-r--r--. 1 root root  639572 Oct 17  2019 postgresql12-contrib-12.0-1PGDG.rhel7.x86_64.rpm

-rw-r--r--. 1 root root  375852 Oct 17  2019 postgresql12-libs-12.0-1PGDG.rhel7.x86_64.rpm

-rw-r--r--. 1 root root 5180216 Oct 17  2019 postgresql12-server-12.0-1PGDG.rhel7.x86_64.rpm

2.master安装

[root@localhost ~]# rpm -ivh  postgresql12*

warning: postgresql12-12.0-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

error: Failed dependencies:

        libicu is needed by postgresql12-12.0-1PGDG.rhel7.x86_64

        python2-libs is needed by postgresql12-12.0-1PGDG.rhel7.x86_64

        python36-libs is needed by postgresql12-12.0-1PGDG.rhel7.x86_64

        libperl.so()(64bit) is needed by postgresql12-contrib-12.0-1PGDG.rhel7.x86_64

        libpython3.6m.so.1.0()(64bit) is needed by postgresql12-contrib-12.0-1PGDG.rhel7.x86_64

        libxslt.so.1()(64bit) is needed by postgresql12-contrib-12.0-1PGDG.rhel7.x86_64

        libxslt.so.1(LIBXML2_1.0.11)(64bit) is needed by postgresql12-contrib-12.0-1PGDG.rhel7.x86_64

        libxslt.so.1(LIBXML2_1.0.18)(64bit) is needed by postgresql12-contrib-12.0-1PGDG.rhel7.x86_64

        libxslt.so.1(LIBXML2_1.0.22)(64bit) is needed by postgresql12-contrib-12.0-1PGDG.rhel7.x86_64

        libicui18n.so.50()(64bit) is needed by postgresql12-server-12.0-1PGDG.rhel7.x86_64

        libicuuc.so.50()(64bit) is needed by postgresql12-server-12.0-1PGDG.rhel7.x86_64

安装依赖套件

[root@localhost ~]# yum install libxslt libicu python36-libs python2-libs libperl.so

[root@localhost ~]# rpm -ivh  postgresql12*

warning: postgresql12-12.0-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY

Preparing...                          ################################# [100%]

Updating / installing...

   1:postgresql12-libs-12.0-1PGDG.rhel################################# [ 25%]

   2:postgresql12-12.0-1PGDG.rhel7    ################################# [ 50%]

   3:postgresql12-contrib-12.0-1PGDG.r################################# [ 75%]

   4:postgresql12-server-12.0-1PGDG.rh################################# [100%]

切换postgres用户, 创建数据目录

[root@localhost ~]# su - postgres

-bash-4.2$ cat  ~/.bash_profile

-bash-4.2$ mkdir /var/lib/pgsql/data

初始化数据库(/usr/pgsql-12/bin不在PATH中)

-bash-4.2$ /usr/pgsql-12/bin/pg_ctl init -D /var/lib/pgsql/data

(or -bash-4.2$ /usr/pgsql-12/bin/initdb -D /var/lib/pgsql/data)

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.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.

fixing permissions on existing directory /var/lib/pgsql/data ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting default time zone ... Asia/Taipei

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

initdb: 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-12/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start

修改联机授权配置文件

[root@localhost bin]# vi /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only本地登录簿用密码

local   all             all                                     trust

# IPv4 local connections:

host    all             all             10.0.0.0/8              password

# IPv6 local connections:

host    all             all             ::1/128                 password

# replication privilege.

host    replication     repl            10.0.0.0/8              md5

修改完pg_hba.conf文件之后，需要重新加载配置，可以不用重启数据库, 执行pg_reload_conf()

[root@localhost postgresql_package]# su – postgres

-bash-4.2$ psql

psql (12.3)

Type "help" for help.

postgres=# select pg_reload_conf();

主库参数配置

[root@localhost bin]# vi /var/lib/pgsql/data/postgresql.conf

listen_addresses = '*'

wal_level = replica

max_wal_senders = 10     #最多可以连10台slave

wal_keep_segments = 64   #保留WAL文件数, 每一个档案16M

启动数据库(有报错)

[root@localhost bin]# systemctl enable postgresql-12

[root@localhost bin]# systemctl start postgresql-12

-- Unit postgresql-12.service has begun starting up.

Jul 08 18:25:35 localhost.localdomain postgresql-12-check-db-dir[1648]: "/var/lib/pgsql/12/data/" is missing or empty.

Jul 08 18:25:35 localhost.localdomain postgresql-12-check-db-dir[1648]: Use "/usr/pgsql-12/bin/postgresql-12-setup initdb" to in

Jul 08 18:25:35 localhost.localdomain postgresql-12-check-db-dir[1648]: See /usr/share/doc/postgresql12-12.0/README.rpm-dist for

Jul 08 18:25:35 localhost.localdomain systemd[1]: postgresql-12.service: control process exited, code=exited status=1

Jul 08 18:25:35 localhost.localdomain systemd[1]: Failed to start PostgreSQL 12 database server.

启动文件已设定默认数据库目录(/var/lib/pgsql/12/data)与自建数据目录(/var/lib/pgsql/data)不同,

修改postgresql-12.service, 将默认data目录改成/var/lib/pgsql/data

[root@localhost]# vi /usr/lib/systemd/system/postgresql-12.service

# Location of database directory

Environment=PGDATA=/var/lib/pgsql/data/

[root@localhost]# systemctl daemon-reload

[root@localhost]# systemctl start postgresql-12

[root@localhost]# systemctl status postgresql-12

3.安装slave

确认是否正常访问主机DB：

[root@localhost ~]# psql -h 192.168.100.104 -U postgres

Password for user postgres:

psql (12.3)

Type "help" for help.

postgres=# \q

[root@localhost]# rpm -ivh  postgresql12*

建立数据目录

[root@localhost]# su - postgres

-bash-4.1$ mkdir /var/lib/pgsql/data

-bash-4.1$ chmod 700 /var/lib/pgsql/data

用pg_basebackup在线复制数据, 数据目录下必须为空, 如果有数据必须先移除

-bash-4.2$ rm –fr /var/lib/pgsql/data/*

bash-4.2$ pg_basebackup -h 192.168.100.104 -p 5432 -U replica -Fp -Xs -Pv -R -D /var/lib/pgsql/data/

could not change directory to "/root": Permission denied

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

pg_basebackup: created temporary replication slot "pg_basebackup_15302"

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

pg_basebackup: write-ahead log end point: 0/2000100

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

pg_basebackup: syncing data to disk ...

pg_basebackup: base backup completed

简单做一下参数说明（可以通过pg_basebackup --help进行查看），

-h指定连接的数据库的主机名或IP地址，这里就是主库的ip。

-U指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户。

-F指定了输出的格式，支持p（原样输出）或者t（tar格式输出）。

-X表示备份开始后，启动另一个流复制连接从主库接收WAL日志 --wal-method=none|fetch|stream。

-P表示允许在备份过程中实时的打印备份的进度。

-R表示会在备份结束后自动生成standby.signal文件(postgresql1之前为recovery.conf)，这样也就避免了手动创建。

-D指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（/var/lib/pgsql/data/）目录需要手动清空。

修改standby.signal参数：

-bash-4.2$ vi /var/lib/pgsql/data/standby.signal

standby_mode = on

修改postgresql.conf配置文文件

-bash-4.2$ vi /var/lib/pgsql/data/postgresql.conf

listen_addresses = '*'

port = 5432

primary_conninfo = 'host=192.168.100.104 port=5432 user=replica password=123456' 

recovery_target_timeline = latest

max_connections = 120    #必须大于master的设定

wal_level = replica

hot_standby = on

hot_standby_feedback = on

修改启动文件数据目录

[root@localhost]# vi /usr/lib/systemd/system/postgresql-12.service

# Location of database directory

Environment=PGDATA=/var/lib/pgsql/data/

启动slave

[root@localhost]# systemctl enable postgresql-12

[root@localhost]# systemctl start postgresql-12

到master查看主从同步状况

[root@localhost]# su - postgres

-bash-4.2$ psql

postgres=# select client_addr,sync_state from pg_stat_replication;

 client_addr | sync_state

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

 10.0.2.2    | async

(1 row)

4.主备切换

先在slave作业：

停止slave

[root@localhost]# systemctl stop postgresql-12

删除/var/lib/pgsql/data/standby.signal文件

[root@localhost]# rm -f /var/lib/pgsql/data/standby.signal

修改postgresql.conf配置文件, 屏蔽掉slave的设置

[root@localhost]# vi /var/lib/pgsql/data/postgresql.conf

listen_addresses = '*'

#primary_conninfo = 'host=192.168.100.104 port=5432 user=replica password=123456'

#recovery_target_timeline = latest

max_connections = 120

wal_level = replica

#hot_standby = on

max_wal_senders = 10

wal_keep_segments = 64

#hot_standby_feedback = on

启动服务, 此时已成为主库

[root@localhost]# systemctl start postgresql-12

因之前用pg_basebackup 复制时, pg_hba.conf已复制过来, 主从都是一样, 不用修改

在master作业：

停止服务

[root@localhost]# systemctl stop postgresql-12

以postgres用户创建/app/pgsql/data/standby.signal

[root@localhost]# su - postgres

-bash-4.2$ vi /var/lib/pgsql/data/standby.signal

standby_mode = on

修改postgresql.conf配置文件

[root@localhost]# vi /var/lib/pgsql/data/postgresql.conf

listen_addresses = '*'

primary_conninfo = 'host=192.168.100.103 port=5432 user=replica password=123456'

recovery_target_timeline = latest

max_connections = 150

wal_level = replica

hot_standby = on

#max_wal_senders = 10

#wal_keep_segments = 64

hot_standby_feedback = on

启动服务, 此时已成为从库

[root@localhost]# systemctl start postgresql-12