一键难忘520

这个屌丝很懒，什么也没留下！

热门标签

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

作者：一键难忘520 | 2024-07-02 17:30:45

踩

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

[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博客

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/一键难忘520/article/detail/780374

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

一.postgresql数据库安装

1.下载软件包

2.解压安装

安装环境

3.编译

4.安装

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

6、创建目录 data、log

7、加入系统环境变量

8、增加用户 postgres 并赋权

9、初始化数据库

10.配置文件

11、启动服务

12.查看版本

13.登录数据库

13.查看有几个库

14.进库

15.建表

16 .查看库里面的表

17.切换库

18.建表插入数据

19.查询数据条数

二.pgpool安装

1.下载地址

2.解压

3.编译

4.安装

5.更改

6.重启数据库

8.创建测试表tb_pgpool

9.pgpool配置

10.查看文件 postgresql.conf

11.启停数据库

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

三.主从配置

■■ 主节点

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

2.修改 pg_hba.conf 配置

3.修改 postgresql.conf 配置

■■ 从节点

1.从主库同步数据

2.远程拉取主节点数据

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

4.修改 postgresql.conf 配置

5.声明从库

6.启动 从库

7.在从机测试主机

8.查看主节点复制插槽

9.主节点信息

10.从节点信息

四.主备切换

1.停止主库

2.启动备库成为新主库对外提供服务

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

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

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

6.测试同步状态

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

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

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

6.启动从库