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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
外国CTO也感兴趣的开源数据库项目——openHalo
小满未满、
621次阅读
2025-04-21 16:58:09
9.9 分高危漏洞，尽快升级到 pgAdmin 4 v9.2 进行修复
严少安
360次阅读
2025-04-11 10:43:23
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
345次阅读
2025-04-15 14:48:05
openHalo问世，全球首款基于PostgreSQL兼容MySQL协议的国产开源数据库
严少安
318次阅读
2025-04-07 12:14:29
转发有奖 | PostgreSQL 16 PGCM高级认证课程直播班招生中！
墨天轮小教习
155次阅读
2025-04-14 15:58:34
墨天轮PostgreSQL认证证书快递已发（2025年3月批）
墨天轮小教习
134次阅读
2025-04-03 11:43:25
SQL 优化之 OR 子句改写
xiongcc
100次阅读
2025-04-21 00:08:06
融合Redis缓存的PostgreSQL高可用架构
梧桐
91次阅读
2025-04-08 06:35:40
PostgreSQL拓展PGQ实现解析
chirpyli
89次阅读
2025-04-07 11:23:17
Mysql/Oracle/Postgresql快速批量生成百万级测试数据sql
hongg
78次阅读
2025-04-07 15:32:54
