postgre双机热备（流复制）-最简操作

postgre双机热备（流复制）

主机ip:10.0.2.5

从机ip:10.0.2.6

安装postgre

1、先安装依赖包

rpm -ivh libicu-50.2-4.el7_7.x86_64.rpm
rpm -ivh libzstd-1.5.5-1.el7.x86_64.rpm

2、再安装postgre

rpm -ivh postgresql15-libs-15.4-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql15-15.4-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql15-server-15.4-1PGDG.rhel7.x86_64.rpm

Master主机上操作

1、初始化数据库

[root@localhost ~]# su postgres
bash-4.2$ cd /usr/pgsql-15/bin/ bash-4.2$ ./initdb -D /var/lib/pgsql/15/data/
bash-4.2$ exit
exit

2、修改配置文件

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

listen_addresses = '*'
wal_level = replica      

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

host    replication     repl            10.0.2.6/32             md5
host    all             all             0.0.0.0/0               md5

3、启动服务

[root@localhost ~]# systemctl enable postgresql-15 && systemctl restart postgresql-15
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-15.service to /usr/lib/systemd/system/postgresql-15.service.

4、创建同步账户

[root@localhost ~]# su - postgres
-bash-4.2$ psql

postgres=# create user repl login replication encrypted password ‘repl’;
CREATE ROLE
postgres=# exit
-bash-4.2$ exit

slave从机操作

1、初始化数据库

[root@localhost ~]# pg_basebackup -h 10.0.2.5 -p 5432 -U repl -Xs -v -Fp -P -R -D /var/lib/pgsql/15/data

2、启动服务

[root@localhost ~]# chown -R postgres:postgres /var/lib/pgsql/15/data

[root@localhost ~]# systemctl start postgresql-15

查看双机热备状态

1、主机上查看

[root@localhost ~]# su postgres

bash-4.2$ psql

postgres=# select pid,state,client_addr,sync_priority,sync_state,sent_lsn,write_lsn from pg_stat_replication;

pid | state | client_addr | sync_priority | sync_state | sent_lsn | write_lsn

------±----------±------------±--------------±-----------±----------±----------

2020 | streaming | 10.0.2.6 | 0 | async | 0/4000148 | 0/4000148

(1 row)

2、从机上查看

[root@localhost ~]# su postgres

bash-4.2$ psql

postgres=# select pid,status,sender_host,sender_port from pg_stat_wal_receiver;

pid | status | sender_host | sender_port

------±----------±------------±------------

4025 | streaming | 10.0.2.5 | 5432

(1 row)

其他操作

查看服务

[root@localhost ~]# systemctl status postgresql-15

修改密码

[root@localhost ~]# su postgres

bash-4.2$ psql -U postgres

postgres=# ALTER USER postgres with encrypted password ‘Aa@123456’;