PostgresSQL从安装入门到熟练应用
在B站学习oldguo老师的视频的笔记，自己总结加上老师的讲解对postgres数据库有个相对比较全面的了解，手敲笔记，如有不对的地方烦请评论区指正。
oldguo老师视频地址：【2021最新版】Old郭带你学PostgreSQL！从入门到实战！
postgresql安装
##挂载镜像
mount /dev/sr0 /mnt
##配置本地yum源
mkdir -p /etc/yum.repos.d/bak
mv /etc/yum.repos.d/* /etc/yum.repos.d/bak/
ls -l /etc/yum.repos.d/
echo "[rhel-source]">>/etc/yum.repos.d/1.repo
echo "name=Red Hat Enterprise Linux $releasever - $basearch - Source">>/etc/yum.repos.d/1.repo
echo "baseurl=file:///mnt">>/etc/yum.repos.d/1.repo
echo "enabled=1">>/etc/yum.repos.d/1.repo
echo "gpgcheck=0">>/etc/yum.repos.d/1.repo
cat /etc/yum.repos.d/1.repo
##关闭selinux
getenforce
setenforce 0
getenforce
sed -i 's/^SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
##关闭防火墙
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
##创建数据库用户和组
useradd postgres
passwd postgres
##安装依赖包
yum groupinstall -y "Development Tools" "Legacy UNIX Compatibility"
yum install -y bison flex readline* zlib-devel gcc* gmake
##创建目录并授权
mkdir -p /usr/local/pg12
mkdir -p /pgdata/12/data
chown -R postgres. /pgdata
chown -R postgres. /usr/local/pg12
chmod -R 700 /pgdata/12/data/
##系统参数优化
# vi /etc/sysctl.conf
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 4194304
net.core.wmem_max = 1048576
# sysctl -p
# vi /etc/security/limits.conf
* soft nofile 131072
* hard nofile 131072
* soft nproc 131072
* hard nproc 131072
* soft core unlimited
* hard core unlimited
* soft memlock 50000000
* hard memlock 50000000
建议关闭numa，设置I0策略为deadline(机械)者no0p(SSD)
#开始安装
解压安装包
tar xf postgresql-12.6.tar.gz
cd postgresql-12.6/
配置并编译
./configure --prefix=/usr/local/pg12 --with-pgport=1921
（gmake world是指编译所有的可用功能，也可以单独指定编译某一个功能）
gmake world
gmake install-world
```
##设置环境变量
```
su - postgres
vim .bash_profi1e
export PATH
export PGDATA=/pgdata/12/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pg12
export LD_LIBRARY_PATH=$PGHOME/1ib:/1ib64:/usr/1ib64:/usr/loca1/1ib64:/1ib:/usr/1ib:/usr/local/1ib:$LD_LIBRARY_PATH
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
export PGUSER=postgres
source .bash_profi1e
```
##测试查看版本
```
psql --version
psql (PostgreSQL) 12.6
```
##初始化数据库
```
initdb -A md5 -D $PGDATA -E utf8 --locale=C -W
```
##启动数据库
```
pg_ctl -D /pgdata/12/data -l logfile start
```
#数据库操作
##连接数据库
```
[postgres@biggerwang ~]$ psql
Password for user postgres:
psql (12.6)
Type "help" for help.
postgres=#
```
###创建数据库
```
postgres=# create database oldguo;
CREATE DATABASE
```
###使用oldguo库
```
postgres=# \c oldguo;
You are now connected to database "oldguo" as user "postgres".
```
###创建表
```
oldguo=# create table t1(id int);
CREATE TABLE
```
###插入一条数据
```
oldguo=# insert into t1 values(1);
INSERT 0 1
oldguo=# select * from t1;
id
----
1
(1 row)
```
###查看有哪些库
```
oldguo=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
oldguo | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
oldguo=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
oldguo=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
```
###关闭数据库
```
pg_ctl stop -ms
pg_ctl stop -mf （常用）
pg_ctl stop -mi
Shutdown modes are:
smart quit after all clients have disconnected
fast quit directly, with proper shutdown (default)
immediate quit without complete shutdown; will lead to recovery on restart
```
##使用脚本启停（使用root用户）
```
[root@biggerwang start-scripts]# ls -l /opt/postgresql-12.6/contrib/start-scripts/linux
-rw-r--r-- 1 1107 1107 3552 Feb 9 2021 /opt/postgresql-12.6/contrib/start-scripts/linux
```
##远程登录
```
vi /pgdata/12/data/pg_hba.conf
host all all 192.168.153.0/24 md5
配置完成后需要重启
如果文件中有冲突条件，以上面的为准，匹配到结果后，不再往下匹配
```
##配置监听
```
vi /pgdata/12/data/postgresql.conf
listen_addresses = '192.168.153.131' # what IP address(es) to listen on;
```
##配置完成后重启
```
pg_ctl restart -mf
```
##使用远程登录
```
psql -d postgres -h 192.168.153.131 -p 1921 -U postgres
##创建用户
查看创建用户的帮助
\help create user;
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/12/sql-createuser.html
创建一个超级用户，注意密码需要单引号引起来
create user admin with superuser password '123';
create role 和create user的区别简单来说就是create role没有登录权限。
##删除用户
drop user oldguo;
###查看用户权限
admin=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
##修改用户
alter user admin with password '1234';
###查看帮助
admin=# \h alter user
#pg的进程
6.4.1 PM进程
PostMaster进程，提供监听、连接协议、验证功能，fork其他进程。监听哪个P是受到postgressql.conf影响的。默认提供socket和
TCPIP方式连接。
验证功能，通过pg_hba.conf和用户验证模块来提供，
6.4.2 SP进程
Session Processors,会话进程。用户一旦验证成功就会fork一个新的进程。
6.4.3 BGW 进程
background writer 进程。主要负责后台剧新脏页。
6.4.4 Sysloger 进程
主要负责数据库状态的信息日志记录。
6.4.5 CKPT
检查点进程。
6.4.6 WALW
wa1writer进程，WAL(Redo)日志剧写进程。
6.4.7 ARCH
WAL 日志的归档日志。
#文件管理
##查看控制文件内容
pg_controldata $PG_DATA
##查看表的段
oldguo=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
(1 row)
oldguo=# select relfilenode from pg_class where relname='t1';
relfilenode
-------------
16385
(1 row)
oldguo=# select pg_relation_filepath('t1');
pg_relation_filepath
----------------------
base/16384/16385
(1 row)
oldguo=# show data_directory;
data_directory
-----------------
/pgdata/12/data
(1 row)
oldguo=# \q
[postgres@biggerwang ~]$ cd /pgdata/12/data/base/16384
[postgres@biggerwang 16384]$ ls -l 16385
-rw------- 1 postgres postgres 8192 Jan 8 11:13 16385
[postgres@biggerwang 16384]$
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/1664418
(1 row)
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
000000010000000000000001
(1 row)
postgres=# select * from pg_ls_waldir() order by modification asc;
name | size | modification
--------------------------+----------+------------------------
000000010000000000000001 | 16777216 | 2025-01-09 11:23:35+08
(1 row)
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/1664430
(1 row)
pg_wal]$ pg_waldump 000000010000000000000002
##开启归档
vi /pgdata/12/data/postgresql.conf
# - Archiving -
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'test ! -f /pgdata/12/data/archivedir/%f && cp %p /pgdata/12/data/archivedir/%f'
###重启生效
pg_ctl restart -mf
###测试插入数据
oldguo=# insert into t1 values (generate_series(1,1000));
INSERT 0 1000
###切换归档
oldguo=# select pg_switch_wal();
pg_switch_wal
---------------
0/200FD70
(1 row)
####查看归档
[postgres@biggerwang archivedir]$ pwd
/pgdata/12/data/archivedir
[postgres@biggerwang archivedir]$ ll
total 32768
-rw------- 1 postgres postgres 16777216 Jan 14 16:23 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jan 14 16:24 000000010000000000000003
#逻辑导出和备份
##逻辑导出
[postgres@biggerwang archivedir]$ pg_dump -d oldguo >/pgdata/12/data/archivedir/oldguo.sql
Password:
[postgres@biggerwang archivedir]$ ls -lrt
total 32788
-rw------- 1 postgres postgres 16777216 Jan 14 16:23 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jan 14 16:24 000000010000000000000003
-rw-rw-r-- 1 postgres postgres 20295 Jan 14 16:29 oldguo.sql
备份
###备份报错
[postgres@biggerwang data]$ pg_basebackup -D /pgdata/pg_backup -Ft -Pv -Upostgres -h 192.168.153.131 -p 1921 -R
pg_basebackup: error: FATAL: no pg_hba.conf entry for replication connection from host "192.168.153.131", user "postgres", SSL off
报错原因是没有给replication用户
在$PG_DATA/pg_hba.conf加上如下内容：
host replication all 192.168.153.0/24 md5
重启数据库
[postgres@biggerwang data]$ pg_ctl restart -mf
waiting for server to shut down.... done
server stopped
waiting for server to start....2025-01-14 16:57:14.130 CST [27718] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2025-01-14 16:57:14.130 CST [27718] LOG: listening on IPv4 address "192.168.153.131", port 1921
2025-01-14 16:57:14.131 CST [27718] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921"
2025-01-14 16:57:14.139 CST [27719] LOG: database system was shut down at 2025-01-14 16:57:14 CST
2025-01-14 16:57:14.141 CST [27718] LOG: database system is ready to accept connections
done
server started
###开始备份
[postgres@biggerwang data]$ pg_basebackup -D /pgdata/pg_backup -Ft -Pv -Upostgres -h 192.168.153.131 -p 1921 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/6000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_27732"
106062/106062 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/6000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
###停库删除文件
pg_ctl stop
###删除数据文件和归档文件
[postgres@biggerwang pg_backup]$ rm -rf /pgdata/12/data/*
[postgres@biggerwang pg_backup]$ rm -rf /pgdata/12/data/archivedir/*
[postgres@biggerwang pg_backup]$ ls
base.tar pg_wal.tar
[postgres@biggerwang pg_backup]$ tar xf /pgdata/pg_backup/base.tar -C /pgdata/12/data/
[postgres@biggerwang archivedir]$ tar xf /pgdata/pg_backup/pg_wal.tar -C /pgdata/12/data/archivedir/
###编辑自动备份的文件
[postgres@biggerwang data]$ vi postgresql.auto.conf
增加如下两行
restore_command = 'cp /pgdata/12/data/archivedir/%f %p'
recovery_target = 'immediate'
###创建一个信号文件
[postgres@biggerwang data]$ touch /pgdata/12/data/recovery.signal
###直接启动并连接测试
[postgres@biggerwang data]$ pg_ctl start
waiting for server to start....2025-01-14 17:15:45.021 CST [28052] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2025-01-14 17:15:45.021 CST [28052] LOG: listening on IPv4 address "192.168.153.131", port 1921
2025-01-14 17:15:45.022 CST [28052] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921"
2025-01-14 17:15:45.039 CST [28053] LOG: database system was interrupted; last known up at 2025-01-14 16:57:19 CST
cp: cannot stat ‘/pgdata/12/data/archivedir/00000002.history’: No such file or directory
2025-01-14 17:15:45.050 CST [28053] LOG: entering standby mode
2025-01-14 17:15:45.070 CST [28053] LOG: restored log file "000000010000000000000006" from archive
2025-01-14 17:15:45.079 CST [28053] LOG: redo starts at 0/6000028
2025-01-14 17:15:45.080 CST [28053] LOG: consistent recovery state reached at 0/6000100
2025-01-14 17:15:45.080 CST [28053] LOG: recovery stopping after reaching consistency
2025-01-14 17:15:45.080 CST [28053] LOG: recovery has paused
2025-01-14 17:15:45.080 CST [28053] HINT: Execute pg_wal_replay_resume() to continue.
2025-01-14 17:15:45.080 CST [28052] LOG: database system is ready to accept read only connections
done
server started
[postgres@biggerwang data]$ psql
Password for user postgres:
psql (12.6)
Type "help" for help.
postgres=#
确认恢复完成
取消恢复，否则会一直恢复，并且数据库处于只读状态。
select pg_wal_replay_resume();
#备份恢复demo
创建数据
创建数据库pit
oldguo=# create database pit;
CREATE DATABASE
切换到pit数据库中，在pit数据库中创建一个t1表，并插入5条数据。
oldguo=# \c pit
You are now connected to database "pit" as user "postgres".
pit=# create table t1 (id int);
CREATE TABLE
pit=# insert into t1 values(1);
INSERT 0 1
pit=# insert into t1 values(2);
INSERT 0 1
pit=# insert into t1 values(3);
INSERT 0 1
pit=# insert into t1 values(4);
INSERT 0 1
pit=# insert into t1 values(5);
INSERT 0 1
##全备一次
进入之前备份目录，清理之前做的环境，将之前的备份删除。
[postgres@biggerwang pgdata]$ cd pg_backup/
[postgres@biggerwang pg_backup]$ ls
base.tar pg_wal.tar
[postgres@biggerwang pg_backup]$ ll
total 122452
-rw------- 1 postgres postgres 108609536 Jan 14 16:57 base.tar
-rw------- 1 postgres postgres 16779264 Jan 14 16:57 pg_wal.tar
[postgres@biggerwang pg_backup]$ rm -rf *
开始做一次全备
[postgres@biggerwang pg_backup]$ pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -Upostgres -h 192.168.153.131 -p 1921 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/7000028 on timeline 2
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_53263"
146804/146804 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/7000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
##模拟新增数据
再次创建一个表t2，再插入三条数据
pit=# create table t2 (id int);
CREATE TABLE
pit=# insert into t2 values (111);
INSERT 0 1
pit=# insert into t2 values (112);
INSERT 0 1
pit=# insert into t2 values (113);
INSERT 0 1
##模拟误操作导致数据丢失
分别查看两个表的数据
pit=# select count(*) from t1;
count
-------
5
(1 row)
pit=# select count(*) from t2;
count
-------
3
(1 row)
pit=#
切换到其他数据库，并执行删除pit数据。如果不切换会提示不能删除当前数据库。
pit=# \c oldguo
You are now connected to database "oldguo" as user "postgres".
oldguo=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
admin | admin | UTF8 | C | C |
oldguo | postgres | UTF8 | C | C |
pit | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rows)
删除数据库
oldguo=# drop database pit;
DROP DATABASE
oldguo=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
admin | admin | UTF8 | C | C |
oldguo | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
查看归档
[postgres@biggerwang archivedir]$ ls -lrt
total 180248
-rw------- 1 postgres postgres 16777216 Jan 14 16:23 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jan 14 16:24 000000010000000000000003
-rw-rw-r-- 1 postgres postgres 4721 Jan 14 16:43 oldguo.sql
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000005
drwxrwxr-x 2 postgres postgres 43 Jan 14 17:10 archive_status
-rw------- 1 postgres postgres 32 Jan 16 11:14 00000002.history
-rw------- 1 postgres postgres 16777216 Jan 16 11:18 000000020000000000000006
-rw------- 1 postgres postgres 16777216 Jan 16 11:19 000000020000000000000007
-rw------- 1 postgres postgres 337 Jan 16 11:19 000000020000000000000007.00000028.backup
-rw------- 1 postgres postgres 16777216 Jan 16 11:22 000000020000000000000008
-rw------- 1 postgres postgres 65 Jan 17 16:04 00000003.history
-rw------- 1 postgres postgres 16777216 Jan 17 16:04 000000020000000000000009.partial
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 000000030000000000000009
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 00000003000000000000000A
-rw------- 1 postgres postgres 337 Jan 17 16:15 00000003000000000000000A.00000028.backup
[postgres@biggerwang archivedir]$
[postgres@biggerwang archivedir]$
切换归档
oldguo=# select pg_switch_wal();
pg_switch_wal
---------------
0/8012250
(1 row)
再次查看归档
[postgres@biggerwang archivedir]$ ls -lrt
total 196632
-rw------- 1 postgres postgres 16777216 Jan 14 16:23 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jan 14 16:24 000000010000000000000003
-rw-rw-r-- 1 postgres postgres 4721 Jan 14 16:43 oldguo.sql
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000005
drwxrwxr-x 2 postgres postgres 43 Jan 14 17:10 archive_status
-rw------- 1 postgres postgres 32 Jan 16 11:14 00000002.history
-rw------- 1 postgres postgres 16777216 Jan 16 11:18 000000020000000000000006
-rw------- 1 postgres postgres 16777216 Jan 16 11:19 000000020000000000000007
-rw------- 1 postgres postgres 337 Jan 16 11:19 000000020000000000000007.00000028.backup
-rw------- 1 postgres postgres 16777216 Jan 16 11:22 000000020000000000000008
-rw------- 1 postgres postgres 65 Jan 17 16:04 00000003.history
-rw------- 1 postgres postgres 16777216 Jan 17 16:04 000000020000000000000009.partial
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 000000030000000000000009
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 00000003000000000000000A
-rw------- 1 postgres postgres 337 Jan 17 16:15 00000003000000000000000A.00000028.backup
-rw------- 1 postgres postgres 16777216 Jan 17 16:20 00000003000000000000000B
关闭数据库（模拟一个新库）
关闭数据库
[postgres@biggerwang archivedir]$ pg_ctl stop -mf
waiting for server to shut down.... done
server stopped
删除除归档目录以外的所有PG_DATA下文件（模拟丢失，归档之前目录创建到这个里面，但是因为在全备之后，还有业务进行，产生了归档，归档在恢复中还是要用的，故不能删除，在恢复过程中备份完成以后得归档都需要拷贝到新环境）
[postgres@biggerwang archivedir]$ cd ..
[postgres@biggerwang data]$ ls
archivedir pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal postmaster.opts
backup_label.old pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact tablespace_map.old
base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf
global pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf
[postgres@biggerwang data]$ rm -rf pg*
[postgres@biggerwang data]$ ls
archivedir backup_label.old base global PG_VERSION postgresql.auto.conf postgresql.conf postmaster.opts tablespace_map.old
[postgres@biggerwang data]$ rm -rf ba*
[postgres@biggerwang data]$ rm -rf
archivedir/ PG_VERSION postgresql.conf tablespace_map.old
global/ postgresql.auto.conf postmaster.opts
[postgres@biggerwang data]$ rm -rf global/
[postgres@biggerwang data]$ rm -rf post*
[postgres@biggerwang data]$ ls
archivedir PG_VERSION tablespace_map.old
[postgres@biggerwang data]$ rm -rf tablespace_map.old
[postgres@biggerwang data]$ rm -rf PG_VERSION
[postgres@biggerwang data]$ ls
archivedir
##解压全备
[postgres@biggerwang pg_backup]$ tar xf base.tar -C /pgdata/12/data/
##解压归档
（这里没必要做，而且目录没有指定，这里解压就是为了看一下是哪个文件，和之前预想的归档目录里面存在的是同一个）
[postgres@biggerwang pg_backup]$ tar xf pg_wal.tar
[postgres@biggerwang pg_backup]$ ls
00000003000000000000000A 00000003.history archive_status base.tar pg_wal.tar
[postgres@biggerwang pg_backup]$ ll
total 245124
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 00000003000000000000000A
-rw------- 1 postgres postgres 65 Jan 17 16:15 00000003.history
drwxrwxr-x 2 postgres postgres 35 Jan 17 16:30 archive_status
-rw------- 1 postgres postgres 217442304 Jan 17 16:15 base.tar
-rw------- 1 postgres postgres 16780288 Jan 17 16:15 pg_wal.tar
正常就做下面这个步骤
[postgres@biggerwang pg_backup]$ tar xf pg_wal.tar -C /pgdata/12/data/archivedir/
在恢复之前，确定恢复到什么时间或者什么lsn号，使用pg_waldump查看删除时的日志，找到删除操作前的lsn号。
[postgres@biggerwang archivedir]$ ls -lrt
total 213016
-rw------- 1 postgres postgres 16777216 Jan 14 16:23 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jan 14 16:24 000000010000000000000003
-rw-rw-r-- 1 postgres postgres 4721 Jan 14 16:43 oldguo.sql
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Jan 14 16:57 000000010000000000000005
drwxrwxr-x 2 postgres postgres 43 Jan 14 17:10 archive_status
-rw------- 1 postgres postgres 32 Jan 16 11:14 00000002.history
-rw------- 1 postgres postgres 16777216 Jan 16 11:18 000000020000000000000006
-rw------- 1 postgres postgres 337 Jan 16 11:19 000000020000000000000007.00000028.backup
-rw------- 1 postgres postgres 16777216 Jan 16 11:19 000000020000000000000007
-rw------- 1 postgres postgres 16777216 Jan 16 11:22 000000020000000000000008
-rw------- 1 postgres postgres 65 Jan 17 16:04 00000003.history
-rw------- 1 postgres postgres 16777216 Jan 17 16:04 000000020000000000000009.partial
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 000000030000000000000009
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 00000003000000000000000A
-rw------- 1 postgres postgres 337 Jan 17 16:15 00000003000000000000000A.00000028.backup
-rw------- 1 postgres postgres 16777216 Jan 17 16:20 00000003000000000000000B
-rw------- 1 postgres postgres 16777216 Jan 17 16:22 00000003000000000000000C
[postgres@biggerwang archivedir]$
[postgres@biggerwang archivedir]$ pg_waldump 00000003000000000000000B
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/0B000028, prev 0/0A000138, desc: RUNNING_XACTS nextXid 535 latestCompletedXid 534 oldestRunningXid 535
rmgr: Storage len (rec/tot): 42/ 42, tx: 0, lsn: 0/0B000060, prev 0/0B000028, desc: CREATE base/24591/24595
rmgr: Heap len (rec/tot): 54/ 7910, tx: 535, lsn: 0/0B000090, prev 0/0B000060, desc: INSERT off 9 flags 0x00, blkref #0: rel 1663/24591/1247 blk 7 FPW
rmgr: Btree len (rec/tot): 53/ 1033, tx: 535, lsn: 0/0B001F78, prev 0/0B000090, desc: INSERT_LEAF off 47, blkref #0: rel 1663/24591/2703 blk 2 FPW
rmgr: Btree len (rec/tot): 53/ 1901, tx: 535, lsn: 0/0B0023A0, prev 0/0B001F78, desc: INSERT_LEAF off 20, blkref #0: rel 1663/24591/2704 blk 2 FPW
rmgr: Heap len (rec/tot): 54/ 7758, tx: 535, lsn: 0/0B002B10, prev 0/0B0023A0, desc: INSERT off 128 flags 0x00, blkref #0: rel 1663/24591/2608 blk 55 FPW
rmgr: Btree len (rec/tot): 53/ 1289, tx: 535, lsn: 0/0B004978, prev 0/0B002B10, desc: INSERT_LEAF off 43, blkref #0: rel 1663/24591/2673 blk 33 FPW
rmgr: Btree len (rec/tot): 53/ 4341, tx: 535, lsn: 0/0B004E88, prev 0/0B004978, desc: INSERT_LEAF off 152, blkref #0: rel 1663/24591/2674 blk 41 FPW
rmgr: Heap len (rec/tot): 207/ 207, tx: 535, lsn: 0/0B005F80, prev 0/0B004E88, desc: INSERT off 10 flags 0x00, blkref #0: rel 1663/24591/1247 blk 7
rmgr: Btree len (rec/tot): 64/ 64, tx: 535, lsn: 0/0B006068, prev 0/0B005F80, desc: INSERT_LEAF off 47, blkref #0: rel 1663/24591/2703 blk 2
rmgr: Btree len (rec/tot): 53/ 5897, tx: 535, lsn: 0/0B0060A8, prev 0/0B006068, desc: INSERT_LEAF off 64, blkref #0: rel 1663/24591/2704 blk 1 FPW
rmgr: Heap len (rec/tot): 80/ 80, tx: 535, lsn: 0/0B0077B8, prev 0/0B0060A8, desc: INSERT off 129 flags 0x00, blkref #0: rel 1663/24591/2608 blk 55
rmgr: Btree len (rec/tot): 72/ 72, tx: 535, lsn: 0/0B007808, prev 0/0B0077B8, desc: INSERT_LEAF off 43, blkref #0: rel 1663/24591/2673 blk 33
rmgr: Btree len (rec/tot): 53/ 2241, tx: 535, lsn: 0/0B007850, prev 0/0B007808, desc: INSERT_LEAF off 77, blkref #0: rel 1663/24591/2674 blk 39 FPW
rmgr: Heap len (rec/tot): 54/ 1050, tx: 535, lsn: 0/0B008130, prev 0/0B007850, desc: INSERT off 3 flags 0x00, blkref #0: rel 1663/24591/1259 blk 0 FPW
rmgr: Btree len (rec/tot): 53/ 2413, tx: 535, lsn: 0/0B008550, prev 0/0B008130, desc: INSERT_LEAF off 116, blkref #0: rel 1663/24591/2662 blk 2 FPW
rmgr: Btree len (rec/tot): 53/ 4117, tx: 535, lsn: 0/0B008EC0, prev 0/0B008550, desc: INSERT_LEAF off 95, blkref #0: rel 1663/24591/2663 blk 2 FPW
rmgr: Btree len (rec/tot): 53/ 1093, tx: 535, lsn: 0/0B009ED8, prev 0/0B008EC0, desc: INSERT_LEAF off 50, blkref #0: rel 1663/24591/3455 blk 4 FPW
rmgr: Heap len (rec/tot): 54/ 5282, tx: 535, lsn: 0/0B00A338, prev 0/0B009ED8, desc: INSERT off 33 flags 0x00, blkref #0: rel 1663/24591/1249 blk 38 FPW
rmgr: Btree len (rec/tot): 53/ 1509, tx: 535, lsn: 0/0B00B7E0, prev 0/0B00A338, desc: INSERT_LEAF off 40, blkref #0: rel 1663/24591/2658 blk 15 FPW
rmgr: Btree len (rec/tot): 53/ 8093, tx: 535, lsn: 0/0B00BDC8, prev 0/0B00B7E0, desc: INSERT_LEAF off 400, blkref #0: rel 1663/24591/2659 blk 9 FPW
rmgr: Heap len (rec/tot): 175/ 175, tx: 535, lsn: 0/0B00DD80, prev 0/0B00BDC8, desc: INSERT off 34 flags 0x00, blkref #0: rel 1663/24591/1249 blk 38
rmgr: Btree len (rec/tot): 72/ 72, tx: 535, lsn: 0/0B00DE30, prev 0/0B00DD80, desc: INSERT_LEAF off 40, blkref #0: rel 1663/24591/2658 blk 15
rmgr: Btree len (rec/tot): 64/ 64, tx: 535, lsn: 0/0B00DE78, prev 0/0B00DE30, desc: INSERT_LEAF off 400, blkref #0: rel 1663/24591/2659 blk 9
rmgr: Heap len (rec/tot): 175/ 175, tx: 535, lsn: 0/0B00DEB8, prev 0/0B00DE78, desc: INSERT off 35 flags 0x00, blkref #0: rel 1663/24591/1249 blk 38
rmgr: Btree len (rec/tot): 72/ 72, tx: 535, lsn: 0/0B00DF68, prev 0/0B00DEB8, desc: INSERT_LEAF off 42, blkref #0: rel 1663/24591/2658 blk 15
rmgr: Btree len (rec/tot): 64/ 64, tx: 535, lsn: 0/0B00DFB0, prev 0/0B00DF68, desc: INSERT_LEAF off 400, blkref #0: rel 1663/24591/2659 blk 9
rmgr: Heap len (rec/tot): 175/ 175, tx: 535, lsn: 0/0B00DFF0, prev 0/0B00DFB0, desc: INSERT off 36 flags 0x00, blkref #0: rel 1663/24591/1249 blk 38
rmgr: Btree len (rec/tot): 72/ 72, tx: 535, lsn: 0/0B00E0B8, prev 0/0B00DFF0, desc: INSERT_LEAF off 40, blkref #0: rel 1663/24591/2658 blk 15
rmgr: Btree len (rec/tot): 64/ 64, tx: 535, lsn: 0/0B00E100, prev 0/0B00E0B8, desc: INSERT_LEAF off 400, blkref #0: rel 1663/24591/2659 blk 9
rmgr: Heap len (rec/tot): 175/ 175, tx: 535, lsn: 0/0B00E140, prev 0/0B00E100, desc: INSERT off 37 flags 0x00, blkref #0: rel 1663/24591/1249 blk 38
rmgr: Btree len (rec/tot): 72/ 72, tx: 535, lsn: 0/0B00E1F0, prev 0/0B00E140, desc: INSERT_LEAF off 43, blkref #0: rel 1663/24591/2658 blk 15
rmgr: Btree len (rec/tot): 64/ 64, tx: 535, lsn: 0/0B00E238, prev 0/0B00E1F0, desc: INSERT_LEAF off 400, blkref #0: rel 1663/24591/2659 blk 9
rmgr: Heap len (rec/tot): 175/ 175, tx: 535, lsn: 0/0B00E278, prev 0/0B00E238, desc: INSERT off 38 flags 0x00, blkref #0: rel 1663/24591/1249 blk 38
rmgr: Btree len (rec/tot): 72/ 72, tx: 535, lsn: 0/0B00E328, prev 0/0B00E278, desc: INSERT_LEAF off 40, blkref #0: rel 1663/24591/2658 blk 15
rmgr: Btree len (rec/tot): 64/ 64, tx: 535, lsn: 0/0B00E370, prev 0/0B00E328, desc: INSERT_LEAF off 400, blkref #0: rel 1663/24591/2659 blk 9
rmgr: Heap len (rec/tot): 175/ 175, tx: 535, lsn: 0/0B00E3B0, prev 0/0B00E370, desc: INSERT off 39 flags 0x00, blkref #0: rel 1663/24591/1249 blk 38
rmgr: Btree len (rec/tot): 72/ 72, tx: 535, lsn: 0/0B00E460, prev 0/0B00E3B0, desc: INSERT_LEAF off 44, blkref #0: rel 1663/24591/2658 blk 15
rmgr: Btree len (rec/tot): 64/ 64, tx: 535, lsn: 0/0B00E4A8, prev 0/0B00E460, desc: INSERT_LEAF off 400, blkref #0: rel 1663/24591/2659 blk 9
rmgr: Heap len (rec/tot): 80/ 80, tx: 535, lsn: 0/0B00E4E8, prev 0/0B00E4A8, desc: INSERT off 130 flags 0x00, blkref #0: rel 1663/24591/2608 blk 55
rmgr: Btree len (rec/tot): 53/ 6497, tx: 535, lsn: 0/0B00E538, prev 0/0B00E4E8, desc: INSERT_LEAF off 223, blkref #0: rel 1663/24591/2673 blk 32 FPW
rmgr: Btree len (rec/tot): 53/ 5993, tx: 535, lsn: 0/0B00FEA0, prev 0/0B00E538, desc: INSERT_LEAF off 126, blkref #0: rel 1663/24591/2674 blk 21 FPW
rmgr: Standby len (rec/tot): 42/ 42, tx: 535, lsn: 0/0B011628, prev 0/0B00FEA0, desc: LOCK xid 535 db 24591 rel 24595
rmgr: Transaction len (rec/tot): 405/ 405, tx: 535, lsn: 0/0B011658, prev 0/0B011628, desc: COMMIT 2025-01-17 16:16:34.935018 CST; inval msgs: catcache 75 catcache 74 catcache 75 catcache 74 catcache 50 catcache 49 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 24595
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/0B0117F0, prev 0/0B011658, desc: RUNNING_XACTS nextXid 536 latestCompletedXid 535 oldestRunningXid 536
rmgr: Heap len (rec/tot): 59/ 59, tx: 536, lsn: 0/0B011828, prev 0/0B0117F0, desc: INSERT+INIT off 1 flags 0x00, blkref #0: rel 1663/24591/24595 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 536, lsn: 0/0B011868, prev 0/0B011828, desc: COMMIT 2025-01-17 16:16:43.750725 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 537, lsn: 0/0B011890, prev 0/0B011868, desc: INSERT off 2 flags 0x00, blkref #0: rel 1663/24591/24595 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 537, lsn: 0/0B0118D0, prev 0/0B011890, desc: COMMIT 2025-01-17 16:16:46.520975 CST
rmgr: Heap len (rec/tot): 59/ 59, tx: 538, lsn: 0/0B0118F8, prev 0/0B0118D0, desc: INSERT off 3 flags 0x00, blkref #0: rel 1663/24591/24595 blk 0
rmgr: Transaction len (rec/tot): 34/ 34, tx: 538, lsn: 0/0B011938, prev 0/0B0118F8, desc: COMMIT 2025-01-17 16:16:50.037104 CST
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/0B011960, prev 0/0B011938, desc: RUNNING_XACTS nextXid 539 latestCompletedXid 538 oldestRunningXid 539
rmgr: Heap len (rec/tot): 59/ 2047, tx: 539, lsn: 0/0B011998, prev 0/0B011960, desc: DELETE off 9 flags 0x00 KEYS_UPDATED , blkref #0: rel 1664/0/1262 blk 0 FPW
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/0B0121B0, prev 0/0B011998, desc: RUNNING_XACTS nextXid 540 latestCompletedXid 538 oldestRunningXid 539; 1 xacts: 539
rmgr: Standby len (rec/tot): 54/ 54, tx: 0, lsn: 0/0B0121E8, prev 0/0B0121B0, desc: RUNNING_XACTS nextXid 540 latestCompletedXid 538 oldestRunningXid 539; 1 xacts: 539
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/0B012220, prev 0/0B0121E8, desc: CHECKPOINT_ONLINE redo 0/B0121E8; tli 3; prev tli 3; fpw true; xid 0:540; oid 32783; multi 1; offset 0; oldest xid 480 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 539; online
rmgr: Database len (rec/tot): 34/ 34, tx: 539, lsn: 0/0B012298, prev 0/0B012220, desc: DROP dir 1663/24591
rmgr: Transaction len (rec/tot): 66/ 66, tx: 539, lsn: 0/0B0122C0, prev 0/0B012298, desc: COMMIT 2025-01-17 16:18:15.153977 CST; inval msgs: catcache 21; sync
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/0B012308, prev 0/0B0122C0, desc: RUNNING_XACTS nextXid 540 latestCompletedXid 539 oldestRunningXid 540
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/0B012340, prev 0/0B012308, desc: SWITCH
在上面的日志中找到rmgr: Database len (rec/tot): 34/ 34, tx: 539, lsn: 0/0B012298, prev 0/0B012220, desc: DROP dir 1663/24591这行，执行删除操作的日志，记录xid为539，则恢复539号事物之前的538号事物。
##编辑恢复的参数文件
这里不能使用recovery_target等于immediate了，这个会导致一直恢复，恢复的结果还是会没有数据。这里使用recovery_target_xid参数，指定到删除前的事物的xid。就是上面内容查看到的。primary_conninfo这是参数是针对备库的，这里没有涉及到备库，这里就注释掉了。
recovery的参数很多，可以在 postgresql.conf参数文件找到相对应的帮助。
[postgres@biggerwang data]$ cd /pgdata/12/data
[postgres@biggerwang data]$ vi postgresql.auto.conf
restore_command = 'cp /pgdata/12/data/archivedir/%f %p'
recovery_target_xid = '538'
##启动数据库
[postgres@biggerwang data]$ pg_ctl start
waiting for server to start....2025-01-17 17:08:16.925 CST [3924] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2025-01-17 17:08:16.926 CST [3924] LOG: listening on IPv4 address "192.168.153.131", port 1921
2025-01-17 17:08:16.927 CST [3924] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921"
2025-01-17 17:08:16.936 CST [3925] LOG: database system was interrupted; last known up at 2025-01-17 16:15:36 CST
cp: cannot stat ‘/pgdata/12/data/archivedir/00000004.history’: No such file or directory
2025-01-17 17:08:16.949 CST [3925] LOG: entering standby mode
2025-01-17 17:08:16.952 CST [3925] LOG: restored log file "00000003.history" from archive
2025-01-17 17:08:16.993 CST [3925] LOG: restored log file "00000003000000000000000A" from archive
2025-01-17 17:08:17.001 CST [3925] LOG: redo starts at 0/A000028
2025-01-17 17:08:17.002 CST [3925] LOG: consistent recovery state reached at 0/A000138
2025-01-17 17:08:17.002 CST [3924] LOG: database system is ready to accept read only connections
done
server started
[postgres@biggerwang data]$ 2025-01-17 17:08:17.035 CST [3925] LOG: restored log file "00000003000000000000000B" from archive
2025-01-17 17:08:17.043 CST [3925] LOG: recovery stopping after commit of transaction 538, time 2025-01-17 16:16:50.037104+08
2025-01-17 17:08:17.043 CST [3925] LOG: recovery has paused
2025-01-17 17:08:17.043 CST [3925] HINT: Execute pg_wal_replay_resume() to continue.
##测试恢复后的数据
[postgres@biggerwang data]$ psql
Password for user postgres:
psql (12.6)
Type "help" for help.
postgres=# create database wangyong;
2025-01-17 17:08:44.594 CST [3943] ERROR: cannot execute CREATE DATABASE in a read-only transaction
2025-01-17 17:08:44.594 CST [3943] STATEMENT: create database wangyong;
ERROR: cannot execute CREATE DATABASE in a read-only transaction
此时虽然已经恢复完成，但是数据是只读，不能写入数据。故还要执行一下 pg_wal_replay_resume，才能正常写入。
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
postgres=# 2025-01-17 17:09:41.312 CST [3925] LOG: redo done at 0/B011938
2025-01-17 17:09:41.312 CST [3925] LOG: last completed transaction was at log time 2025-01-17 16:16:50.037104+08
cp: cannot stat ‘/pgdata/12/data/archivedir/00000004.history’: No such file or directory
2025-01-17 17:09:41.317 CST [3925] LOG: selected new timeline ID: 4
2025-01-17 17:09:41.379 CST [3925] LOG: archive recovery complete
2025-01-17 17:09:41.382 CST [3925] LOG: restored log file "00000003.history" from archive
2025-01-17 17:09:41.389 CST [3924] LOG: database system is ready to accept connections
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
admin | admin | UTF8 | C | C |
oldguo | postgres | UTF8 | C | C |
pit | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(6 rows)
postgres=# \c pit
You are now connected to database "pit" as user "postgres".
pit=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
(2 rows)
pit=# select count(*) from t1;
count
-------
5
(1 row)
pit=# select count(*) from t2;
count
-------
3
(1 row)
pit=# select * from t1;
id
----
1
2
3
4
5
(5 rows)
pit=# select * from t2;
id
-----
111
112
113
(3 rows)
##恢复测试技巧
在风险操作之前，做一个还原点，如果出现问题，可以直接指定这个还原点的名称，省去分析日志的步骤
select pg_create_restore_point('oldguo_before_delete')
流复制
##Master节点创建用户
postgres=# create role replica with replication login password '123456';
CREATE ROLE
postgres=# alter user replica with password '123456';
##修改pg_hba.conf
[postgres@biggerwang ~]$ cd $PGDATA
[postgres@biggerwang data]$ vi pg_hba.conf
host replication rep1ica 0.0.0.0/0 md5
##修改配置
wal_level = replica #这个是设置主为wa1的主机
max_wal_senders = 10 #这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个
wal_keep_segments = 128 #设置流复制保留的最多的x1og数目
wal_sender_timeout = 60s #设置流复制主机发送数据的超时时间
max_connections = 200 #一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的（间隔时间
hot_standby_feedback = on #如果有错误的数据复制，是否向主进行反馈
wal_log_hints = on #alsodo fullpage writesofnon-criticalupdates
##standby节点清空数据和归档
[root@biggeryong ~]# su - postgres
Last login: Fri Jan 17 16:11:26 CST 2025 on pts/3
[postgres@biggeryong ~]$ cd /pgdata/12/data/
[postgres@biggeryong data]$ ls
archivedir pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal postmaster.opts
backup_label.old pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact tablespace_map.old
base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf
global pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf
[postgres@biggeryong data]$
[postgres@biggeryong data]$
[postgres@biggeryong data]$
[postgres@biggeryong data]$ cd archivedir/
[postgres@biggeryong archivedir]$ ls
000000010000000000000002 000000020000000000000006 00000002.history 00000003000000000000000C
000000010000000000000003 000000020000000000000007 000000030000000000000009 00000003.history
000000010000000000000004 000000020000000000000007.00000028.backup 00000003000000000000000A 00000004.history
000000010000000000000005 000000020000000000000008 00000003000000000000000A.00000028.backup archive_status
000000010000000000000006 000000020000000000000009.partial 00000003000000000000000B oldguo.sql
[postgres@biggeryong archivedir]$ rm -rf *
[postgres@biggeryong archivedir]$ ls
[postgres@biggeryong archivedir]$ cd ..
[postgres@biggeryong data]$ ls
archivedir pg_commit_ts pg_logical pg_serial pg_subtrans pg_wal postmaster.opts
backup_label.old pg_dynshmem pg_multixact pg_snapshots pg_tblspc pg_xact tablespace_map.old
base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.auto.conf
global pg_ident.conf pg_replslot pg_stat_tmp PG_VERSION postgresql.conf
[postgres@biggeryong data]$ rm -rf *
[postgres@biggeryong data]$ ls
[postgres@biggeryong data]$ mkdir archivedir
[postgres@biggeryong data]$ ls
archivedir
[postgres@biggeryong data]$ ll
total 0
drwxrwxr-x 2 postgres postgres 6 Jan 20 11:06 archivedir
删除历史备份
[postgres@biggeryong pgdata]$ cd pg_backup/
[postgres@biggeryong pg_backup]$ ls
00000003000000000000000A 00000003.history archive_status base.tar pg_wal.tar
[postgres@biggeryong pg_backup]$ ll
total 245124
-rw------- 1 postgres postgres 16777216 Jan 17 16:15 00000003000000000000000A
-rw------- 1 postgres postgres 65 Jan 17 16:15 00000003.history
drwxrwxr-x 2 postgres postgres 35 Jan 17 16:30 archive_status
-rw------- 1 postgres postgres 217442304 Jan 17 16:15 base.tar
-rw------- 1 postgres postgres 16780288 Jan 17 16:15 pg_wal.tar
[postgres@biggeryong pg_backup]$ rm -rf *
[postgres@biggeryong pg_backup]$ ls
##备份主库数据到备库
[postgres@biggeryong pg_backup]$ pg_basebackup -D /pgdata/pg_backup/ -Ft -Pv -Upostgres -h 192.168.153.131 -p 1921 -R
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/E000028 on timeline 4
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3427"
310663/310663 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/E000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
##解压数据
[postgres@biggeryong pg_backup]$ tar xf base.tar -C /pgdata/12/data/
[postgres@biggeryong pg_backup]$ tar xf pg_wal.tar -C /pgdata/12/data/archivedir/
[postgres@biggeryong pg_backup]$
修改standby.signa文件：
[postgres@biggeryong data]$ cd /pgdata/12/data/
[postgres@biggeryong data]$ vi standby.signal
standby_mode = 'on'
修改postgresql.auto.conf文件
[postgres@biggeryong pg_backup]$ cd /pgdata/12/data/
[postgres@biggeryong data]$ vi postgresql.auto.conf
restore_command = 'cp /pgdata/12/data/archivedir/%f %p'
primary_conninfo = 'user=postgres password=123456 host=192.168.153.131 port=1921 sslmode=disable sslcompression=0 gssencmode=disable kr
bsrvname=postgres target_session_attrs=any'
##修改postgresql.conf文件
[postgres@biggeryong data]$ cd /pgdata/12/data/
[postgres@biggeryong data]$ vi postgresql.conf
修改一下监听IP，这个是从主库克隆的，所以在启动的时候提示冲突
listen_addresses = '192.168.153.132' # what IP address(es) to listen on;
以下是笔记中的参数
recovery_target_timeline = 'latest' #默认
max_connections = 200 #大于等于主节点，正式环境境应当重新考虑此值的大小
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on
max_wal_senders = 10
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
##启动备库
[postgres@biggeryong data]$ pg_ctl start
waiting for server to start....2025-01-20 11:48:24.176 CST [4666] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2025-01-20 11:48:24.176 CST [4666] LOG: could not bind IPv4 address "192.168.153.131": Cannot assign requested address
2025-01-20 11:48:24.176 CST [4666] HINT: Is another postmaster already running on port 1921? If not, wait a few seconds and retry.
2025-01-20 11:48:24.176 CST [4666] WARNING: could not create listen socket for "192.168.153.131"
2025-01-20 11:48:24.176 CST [4666] FATAL: could not create any TCP/IP sockets
2025-01-20 11:48:24.176 CST [4666] LOG: database system is shut down
stopped waiting
pg_ctl: could not start server
Examine the log output.
报错了，提示有一个这个监听地址了，才想起了克隆的机器，没有修改配置文件里的IP信息
[postgres@biggeryong data]$ vi postgresql.conf
listen_addresses = '192.168.153.132'
[postgres@biggeryong data]$ pg_ctl start
waiting for server to start....2025-01-20 11:51:23.590 CST [4719] LOG: starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2025-01-20 11:51:23.590 CST [4719] LOG: listening on IPv4 address "192.168.153.132", port 1921
2025-01-20 11:51:23.592 CST [4719] LOG: listening on Unix socket "/tmp/.s.PGSQL.1921"
2025-01-20 11:51:23.721 CST [4719] LOG: redirecting log output to logging collector process
2025-01-20 11:51:23.721 CST [4719] HINT: Future log output will appear in directory "pg_log".
done
server started
##监控数据库同步情况
###主库监控
postgres=# select pid,client_addr,sync_priority,sync_state from pg_catalog.pg_stat_replication;
pid | client_addr | sync_priority | sync_state
------+-----------------+---------------+------------
3973 | 192.168.153.132 | 0 | async
(1 row)
###备库监控
可以使用非交互式的方式
[postgres@biggeryong data]$ psql -c "\x" -c "select * from pg_stat_wal_receiver;"
Password for user postgres:
Expanded display is on.
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 4762
status | streaming
receive_start_lsn | 0/10000000
receive_start_tli | 4
received_lsn | 0/10002088
received_tli | 4
last_msg_send_time | 2025-01-20 14:24:54.217509+08
last_msg_receipt_time | 2025-01-20 14:24:53.869824+08
latest_end_lsn | 0/10002088
latest_end_time | 2025-01-20 12:06:07.538569+08
slot_name |
sender_host | 192.168.153.131
sender_port | 1921
conninfo | user=postgres password=******** dbname=replication host=192.168.153.131 port=1921 fallback_application_name=walreceiver sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any
#同步验证
主库建库建表并插数据
postgres=# create database wy222;
CREATE DATABASE
postgres=#
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
admin | admin | UTF8 | C | C |
oldguo | postgres | UTF8 | C | C |
pit | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
wy222 | postgres | UTF8 | C | C |
(7 rows)
postgres=# \c wy222
You are now connected to database "wy222" as user "postgres".
wy222=# create table ttt(id int);
CREATE TABLE
wy222=# insert into ttt values(222);
INSERT 0 1
wy222=# insert into ttt values(333);
INSERT 0 1
备库验证
[postgres@biggeryong data]$ psql
Password for user postgres:
psql (12.6)
Type "help" for help.
postgres=# \c
You are now connected to database "postgres" as user "postgres".
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
admin | admin | UTF8 | C | C |
oldguo | postgres | UTF8 | C | C |
pit | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
wy222 | postgres | UTF8 | C | C |
(7 rows)
postgres=# \c wy222
You are now connected to database "wy222" as user "postgres".
wy222=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | ttt | table | postgres
(1 row)
wy222=# select count(*) from ttt;
count
-------
2
(1 row)
wy222=# select * from ttt;
id
-----
222
333
(2 rows)
wy222=# insert into ttt values(444);
ERROR: cannot execute INSERT in a read-only transaction
wy222=#
文章转载自bigger，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
