postgresql数据库主备安装，备份还原

一.postgresql数据库安装

1.下载软件包

地址：PostgreSQL: File Browser

2.解压安装

[root@postgresql u01]# tar -zxf postgresql-14.2.tar.gz


安装环境 

yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake


 3.编译

[root@postgresql u01]# cd postgresql-14.2[root@postgresql postgresql-14.2]# ./configure --prefix=/usr/local/postgresql


4.安装

[root@postgresql postgresql-14.2]# make && make install


5.进入安装后的目录，查看目录结构

[root@VM-8-15-centos ~]# cd /usr/local/postgresql


6、创建目录 data、log

[root@postgresql bin]# mkdir /usr/local/postgresql/data
[root@postgresql bin]# mkdir /usr/local/postgresql/log


7、加入系统环境变量

[root@postgresql bin]#vim /etc/profile

添加如下内容

export PGHOME=/usr/local/postgresql
export PGDATA=/usr/local/postgresql/dataexport JAVA_HOME=/u01/jdk1.8.0_201
export CLASSPATH=$:CLASSPATH:$JAVA_HOME/lib/
export PATH=$PATH:$JAVA_HOME/bin
export PATH=$PATH:$JAVA_HOME/bin:$HOME/.local/bin:$HOME/bin:$PGHOME/bin


使配置文件生效

[root@postgresql bin]# source /etc/profile


 8、增加用户 postgres 并赋权

[root@postgresql postgresql]# useradd postgres
[root@postgresql postgresql]# chown -R postgres:root /usr/local/postgresql


9、初始化数据库

[root@postgresql ~]# su postgres
[postgres@postgresql root]$ /usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data/


10.配置文件

[postgres@postgresql root]$ vim /usr/local/postgresql/data/postgresql.conf


[postgres@postgresql root]$ vim /usr/local/postgresql/data/pg_hba.conf

 添加

host all all 0.0.0.0/0 trust


 11、启动服务

[postgres@postgresql root]$ pg_ctl start -l /usr/local/postgresql/log/pg_server.log


 12.查看版本

[postgres@postgresql root]$ psql -V


 13.登录数据库

[postgres@postgresql root]$ psql -U postgres -d postgres


 13.查看有几个库

postgres=# \l


14.进库

postgres=# \c template1

15.建表

template1=# create table test(name varchar(20),age int);


 16 .查看库里面的表

template1=# \d


 17.切换库

template1=# \c postgres


18.建表插入数据

postgres=# create table class(id bigint,name varchar(20),age int,sex char(2));
CREATE TABLE
postgres=# \dList of relationsSchema | Name | Type | Owner
--------+-------+-------+----------public | class | table | postgres
(1 row)


postgres=# insert into class values(001,&＃39;赵华&＃39;,16,&＃39;男&＃39;);
INSERT 0 1


 19.查询数据条数

postgres=# select count(*) from class;

 二.pgpool安装

1.下载地址

Downloads - pgpool Wiki

2.解压

[root@sqlserver u01]# tar -zxvf pgpool-II-4.2.9.tar.gz


创建目录

mkdir -p /postgres/pgpool

 3.编译

[root@sqlserver pgpool-II-4.2.9]# ./configure --with-pgsql=/postgres/pgpool


若报错configure: error: libpq is not installed or libpq is old

[root@sqlserver ~]# yum install -y postgresql* gcc*


4.安装

进入目录

[root@sqlserver pgpool-recovery]# cd /u01/pgpool-II-4.2.9/src/sql/pgpool-recovery

安装 

[root@sqlserver pgpool-recovery]# make && make install


5.更改 

[root@sqlserver data]# vim postgresql.conf

 更改内容

archive_mode = on
archive_command = &＃39;cp "%p" "/postgres/archivedir" &＃39;
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica

创建目录

[root@sqlserver pgpool-recovery]# mkdir -p /postgres/archivedir

6.重启数据库 

[postgres@postgresql data]$ pg_ctl restart


7.主库修改postgres的密码、创建流复制用户repl

ALTER USER postgres WITH PASSWORD &＃39;123456&＃39;;
CREATE ROLE pgpool WITH PASSWORD &＃39;123456&＃39; LOGIN;
CREATE ROLE repl WITH PASSWORD &＃39;123456&＃39; REPLICATION LOGIN;

postgres=#
postgres=# ALTER USER postgres WITH PASSWORD &＃39;123456&＃39;;
ALTER ROLE
postgres=# CREATE ROLE pgpool WITH PASSWORD &＃39;123456&＃39; LOGIN;
CREATE ROLE
postgres=# CREATE ROLE repl WITH PASSWORD &＃39;123456&＃39; REPLICATION LOGIN;
CREATE ROLE


 8.创建测试表tb_pgpool

postgres=# CREATE TABLE tb_pgpool ( id serial,age bigint,insertTime timestamp default now());^
postgres=# insert into tb_pgpool(age) values(1);


查询

postgres=# select * from tb_pgpool;id | age | inserttime
----+-----+----------------------------1 | 1 | 2023-01-01 18:52:26.506927
(1 行记录)


9.pgpool配置 

 查找pgpool.conf.sample-stream

[postgres@postgresql pgpool-II-4.2.9]$ find ./ -name pgpool.conf.sample-stream


创建目录 

[postgres@sqlserver postgresql]$ mkdir pgpool/etc -p


找到pgpool所在位置

[postgres@sqlserver pgpool]$ pwd
/usr/local/postgresql/pgpool


执行下面代码

[postgres@sqlserver pgpool-II-4.2.9]$ cp ./src/sample/pgpool.conf.sample-stream /usr/local/postgresql/pgpool/etc/pgpool.conf


10.查看文件 postgresql.conf

[postgres@postgresql data]$ grep -Ev &＃39;^#|^$&＃39; $PGDATA/postgresql.conf


查看pgpool.conf 文件

[postgres@postgresql etc]$ grep -Ev &＃39;^#|^$&＃39; /usr/local/postgresql/pgpool/etc/pgpool.conf


只查看有用的参数

[postgres@postgresql etc]$ grep -Ev &＃39;^#|^$&＃39; /usr/local/postgresql/pgpool/etc/pgpool.conf | grep -v &＃39;^\s.*&＃39;


11.启停数据库 

pg_ctl status
pg_ctl start
pg_ctl stop

 12.客户端工具使用开源的pgAdmin

Set Master Password: postgres

三.主从配置 

■■ 主节点

1.创建用于主从访问的用户， 修改postgres用户的密码，用于远程登录

su postgres
psql
# 创建 postgres 密码
ALTER USER postgres WITH PASSWORD &＃39;123456&＃39;;
# 创建 从库 replica 用户密码
CREATE ROLE replica login replication encrypted password &＃39;123456&＃39;;
# 检查账号
SELECT usename from pg_user;
SELECT rolname from pg_roles;

执行结果

postgres=# ALTER USER postgres WITH PASSWORD &＃39;123456&＃39;;
ALTER ROLE
postgres=# CREATE ROLE replica login replication encrypted password &＃39;123456&＃39;;
CREATE ROLE
postgres=# SELECT usename from pg_user;usename
----------pgpoolreplpostgresreplica
(4 rows)postgres=# SELECT rolname from pg_roles;rolname
---------------------------pg_database_ownerpg_read_all_datapg_write_all_datapg_monitorpg_read_all_settingspg_read_all_statspg_stat_scan_tablespg_read_server_filespg_write_server_filespg_execute_server_programpg_signal_backendpgpoolreplpostgresreplica
(15 rows)postgres=#


2.修改 pg_hba.conf 配置 

# 添加从库网段
host all all 0.0.0.0/0 trust
# replication privilege.
local replication all peer
host replication replica 192.168.222.12/24 md5
#注意此处 192.168.222.12/24 需修改为从库的 IP 段


3.修改 postgresql.conf 配置 

[postgres@postgresql data]$ vim $PGDATA/postgresql.conf

listen_addresses = &＃39;*&＃39;
wal_level = hot_standby
synchronous_commit = remote_write
# synchronous_commit 参考文档可选其他 on
max_wal_senders = 32 #同步最大的进程数量
wal_sender_timeout = 60s #流复制主机发送数据的超时时间
max_cOnnections= 100 #最大连接数，从库的max_connections必须要大于主库的


 ■■ 从节点

1.从主库同步数据

清除从库数据

[postgres@sqlserver bin]$ rm -rf $PGDATA/*


 2.远程拉取主节点数据

[postgres@sqlserver data]$ pg_basebackup -h 192.168.153.129 -D $PGDATA -U replica -P -v -R -X stream -C -S pgstandby1


复制后从节点的配置文件会和主节点的一样，并且一并复制的还有数据库文件，里面的数据也一样，意味着主节点有什么用户数据从节点也有。

-h –指定作为主服务器的主机。
-D –指定数据目录。
-U –指定连接用户。
-P –启用进度报告。
-v –启用详细模式。
-R–启用恢复配置的创建：创建一个standby.signal文件，并将连接设置附加到数据目录下的postgresql.auto.conf。
-X–用于在备份中包括所需的预写日志文件（WAL文件）。流的值表示在创建备份时流式传输WAL。
-C –在开始备份之前，允许创建由-S选项命名的复制插槽。
-S –指定复制插槽名称。

3.备份过程完成后，会在data目录下创建了一个standby.signal，并将primary_conninfo写入postgresql.auto.conf

[postgres@sqlserver data]$ vim postgresql.auto.conf


4.修改 postgresql.conf 配置

[postgres@sqlserver data]$ vim $PGDATA/postgresql.conf

# 移除或注释 wal_level
wal_level = xxx
# 修改或添加以下
primary_cOnninfo= &＃39;host=192.168.153.129 port=5432 user=replica password=123456&＃39;
recovery_target_timeline = &＃39;latest&＃39;


 5.声明从库

[postgres@sqlserver data]$ vim standby.signal

# 声明从库
standby_mode = on


6.启动 从库

[postgres@sqlserver postgresql]$ pg_ctl start


7.在从机测试主机

6. 在从机上测试主机su - postgrespsql -h 192.168.153.129 -U postgres验证主备同步状态：ps aux | grep wal主机上有 wal sender process 进程从机上有 wal receiver process 进程

8.查看主节点复制插槽 

SELECT * FROM pg_replication_slots;

postgres=# \x
扩展显示已打开。
postgres=# SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]-------+-----------
slot_name | pgstandby1
plugin |
slot_type | physical
datoid |
database |
temporary | f
active | t
active_pid | 3346
xmin |
catalog_xmin |
restart_lsn | 0/60001C0
confirmed_flush_lsn |
wal_status | reserved
safe_wal_size |
two_phase | f


 9.主节点信息

[postgres@postgresql log]$ psql -c "\x" -c "SELECT * FROM pg_stat_replication;"


[postgres@postgresql log]$ psql -c \x -c "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_ls
| write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time
------+----------+---------+------------------+-----------------+-----------------+-------------+---
--------------------------+--------------+-----------+-----------+-----------+-----------+----------
+-----------+-----------+------------+---------------+------------+-------------------------------3346 | 24582 | replica | walreceiver | 192.168.153.131 | | 57212 | 20
-01-02 17:03:28.827758+08 | | streaming | 0/60001C0 | 0/60001C0 | 0/60001C0 | 0/60001C0
| | | | 0 | async | 2023-01-02 17:06:38.903732+08
(1 行记录)


10.从节点信息

[postgres@sqlserver postgresql]$ psql -c "\x" -c "SELECT * FROM pg_stat_wal_receiver;"


3177 | streaming | 0/5000000 | 1 | 0/60001C0 | 0/60001C0 | 1 | 20
23-01-02 17:15:11.650372+08 | 2023-01-02 17:15:09.816532+08 | 0/60001C0 | 2023-01-02 17:04:10.182778
+08 | pgstandby1 | 192.168.153.129 | 5432 | user=replica passfile=/home/postgres/.pgpass channel_b
inding=disable dbname=replication host=192.168.153.129 port=5432 fallback_application_name=walreceiver ss
lmode=disable sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=po
stgres target_session_attrs=any
(1 row)

四.主备切换 

1.停止主库

su - postgres
pg_ctl stop -m fast


2.启动备库对外提供服务 

su - postgres
pg_ctl promotepg_controldata #查看状态为in production对外提供服务，另外standby.signal文件自动删除


3.将旧主改为新备库，重新同步

#重建standby.signal文件
su - postgres
cd $PGDATA
touch standby.signal#修改postgresql.auto.conf
vim postgresql.auto.conf# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_cOnninfo= &＃39;user=repl password=repl channel_binding=prefer host=192.168.9.114 port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any&＃39;
primary_slot_name = &＃39;pgstandby1&＃39;#新主库若没有配置复制槽，需要配置下
SELECT * FROM pg_create_physical_replication_slot(‘pgstandby1’);
SELECT slot_name, slot_type, active FROM pg_replication_slots;#启动备库
pg_ctl startpg_controldata #查看状态为in archive recovery


4.警告:psql 版本8.4, 服务器版本14.2. 

解决办法

su root
[root@sqlserver bin]# mv /usr/bin/psql /usr/bin/psql-bk
[root@sqlserver bin]# cd
[root@sqlserver ~]# find / -name &＃39;psql&＃39;
/usr/local/postgresql/bin/psql
/u01/postgresql-14.2/src/bin/psql
/u01/postgresql-14.2/src/bin/psql/psql
[root@sqlserver ~]# ln -s /usr/local/postgresql/bin/psql /usr/bin/psql
[root@sqlserver ~]# su postgres
[postgres@sqlserver root]$ psql
could not change directory to "/root": 权限不够
psql (14.2)
Type "help" for help.postgres=#