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

Java钉子户

已于 2023-11-24 13:54:10 修改

阅读量3.1k

点赞数

文章标签：数据库 postgresql

于 2022-12-31 09:50:43 首次发布

本文链接：https://blog.csdn.net/weixin_44895829/article/details/128502792

版权

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

[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/data

export 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:/usr/local/postgresql/bin

[root@kylin postgresql]# vim ~/.bash_profile

PATH=$PATH:$HOME/bin:$JAVA_HOME/bin:$HOME/.local/bin:$HOME/bin:$PGHOME/bin:/usr/local/postgresql/bin
export PGHOME=/usr/local/postgresql
export PGDATA=/usr/local/postgresql/data

export 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:/usr/local/postgresql/bin

[root@kylin postgresql]# source  ~/.bash_profile

切换postgres用户

[postgres@kylin postgresql]$ vim ~/.bash_profile

加入

export PGHOME=/usr/local/postgresql
export PGDATA=/usr/local/postgresql/data

export 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:/usr/local/postgresql/bin

[postgres@kylin postgresql]$ source  ~/.bash_profile

使配置文件生效

[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=# \d
         List of relations
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | class | table | postgres
(1 row)

postgres=# insert into  class values(001,'赵华',16,'男');
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 = 'cp "%p" "/postgres/archivedir" '
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  '123456';
CREATE ROLE pgpool WITH PASSWORD  '123456' LOGIN;
CREATE ROLE repl WITH PASSWORD '123456'  REPLICATION LOGIN;

postgres=# 
postgres=# ALTER USER postgres WITH PASSWORD  '123456';
ALTER ROLE
postgres=# CREATE ROLE pgpool WITH PASSWORD  '123456' LOGIN;
CREATE ROLE
postgres=# CREATE ROLE repl WITH PASSWORD '123456'  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 '^#|^$' $PGDATA/postgresql.conf

查看pgpool.conf 文件

[postgres@postgresql etc]$ grep -Ev '^#|^$' /usr/local/postgresql/pgpool/etc/pgpool.conf

只查看有用的参数

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

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 '123456';
# 创建 从库 replica 用户密码
CREATE ROLE replica login replication encrypted password '123456';
# 检查账号
SELECT usename from pg_user;
SELECT rolname from pg_roles;

执行结果

postgres=# ALTER USER postgres WITH PASSWORD '123456';
ALTER ROLE
postgres=# CREATE ROLE replica login replication encrypted password '123456';
CREATE ROLE
postgres=# SELECT usename from pg_user;
 usename  
----------
 pgpool
 repl
 postgres
 replica
(4 rows)

postgres=# SELECT rolname from pg_roles;
          rolname          
---------------------------
 pg_database_owner
 pg_read_all_data
 pg_write_all_data
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 pgpool
 repl
 postgres
 replica
(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 = '*'
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 = 'host=192.168.153.129 port=5432 user=replica password=123456'
recovery_target_timeline = 'latest'

5.声明从库

[postgres@sqlserver data]$ vim standby.signal

# 声明从库
standby_mode = on

6.启动从库

[postgres@sqlserver postgresql]$ pg_ctl start

7.在从机测试主机

6. 在从机上测试主机

su - postgres

psql -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.启动备库成为新主库对外提供服务

select pg_promote(true,60);

3. 验证

select pg_is_in_recovery();   #f代表主库 t代表备库

4.新主库修改新主库修改pg_hba.conf文件

原主库IP 192.168.153.133

host    replication     all             192.168.153.133/24           md5

3.将旧主库改为新备库，重新同步，原主库新建$PGDATA/standby.signal文件

[postgres@postgresql cz]$ cd $PGDATA
[postgres@postgresql data]$ vim standby.signal

加入

standby_mode = 'on'

4.原主库修改$PGDATA/postgresql.auto.conf文件

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

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

5.启动原主库，变为新备库

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

6.测试同步状态

select pg_is_in_recovery();   #f代表主库 t代表备库

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 'psql'
/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=#

基于Postgre 14的主备复制和主备切换

基于Postgre 14的主备复制和主备切换_qq_36606793的博客-CSDN博客