postgresql-备份恢复

https://www.hyouit.com?aff=418

1、逻辑备份恢复

pg_dump/pg_restore 命令备份恢复

对数据库或表备份恢复

## 备份指定的数据库 test
$ pg_dump -U postgres -W -h 192.168.3.122 -p 1921 test > /pgdata/dumpbak/test.sql

## 备份指定库中的某个表 t1
$ pg_dump -U postgres -W -h 192.168.3.122 -p 1921 test -t t1 > /pgdata/dumpbak/t1.sql

## 备份文件以加密形式存储，该备份出来的文件可以通过pg_restore恢复
$ pg_dump -U postgres -W test -Fc > /pgdata/dumpbak/test.dump

## 数据恢复
$ pg_restore -d test /pgdata/dumpbak/test.dump 
$ psql -U postgres -W test < /pgdata/dumpbak/test.sql 

备份所有的库

##　文件内容格式为　hostname:port:database:username:password
$ more .pgpass 
*:*:*:postgres:123456
$ chmod 600 .pgpass
$ pg_dumpall > all.sql

2、物理备份恢复

pg_basebackup 工具备份

选项参数含义：

-D ，保存备份的路径

-Ft , 备份内容保存格式为tar包形式，有 p|t 两种形式，p为普通文本形式

-z , 压缩备份文件

-Pv , 显示进度和输出信息

$ pg_basebackup -D /pgdata/pg_backup/ -Ft -z -Pv -U postgres -h 192.168.3.122 -p 1921 -R

模拟数据丢失，进行恢复

$ pg_ctl stop -mf
$ rm -rf $PGDATA/*
$ rm -rf /archive/*

## 将备份文件解压到指定路径中
$ tar -zxvf base.tar.gz -C /pgdata/
$ tar -zxvf pg_wal.tar.gz -C /archive/

## 编辑文件
$ vim postgresql.auto.conf
restore_command = 'cp /archive/%f %p'
recovery_target = 'immediate'

## 启动数据库
$ pg_ctl start
$ psql
## 查看当前数据库处于恢复中
$ pg_controldata
## 此时数据还只是只读状态，无法进行写操作，执行以下函数恢复读写
> postgres=# select pg_wal_replay_resume();

3、故障恢复模拟

创建表 t2 ，并插入几行数据

test=# create table t2(id int);
test=# insert into t2 values(1),(2),(3);

数据全备

$ pg_basebackup -D backup/ -Ft -z -Pv -U postgres  -p 1921 -R

再创建表 t3 ，并插入几行数据

test=# create table t2(id int);
test=# insert into t2 values(1),(2),(3);

删除数据库 test

postgres=# drop database test;

查看当前正在写的wal日志文件

postgres=# select pg_walfile_name(pg_current_wal_lsn());

通过分析当前 wal 日志，找到drop 前的事务id

$ pg_waldump 00000003000000000000000C 
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C000028, prev 0/0B000138, desc: RUNNING_XACTS nextXid 512 latestCompletedXid 511 oldestRunningXid 512
rmgr: XLOG        len (rec/tot):     30/    30, tx:          0, lsn: 0/0C000060, prev 0/0C000028, desc: NEXTOID 40973

rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/0C012A30, prev 0/0C012338, desc: RUNNING_XACTS nextXid 518 latestCompletedXid 516 oldestRunningXid 517; 1 xacts: 517
rmgr: Standby     len (rec/tot):     54/    54, tx:          0, lsn: 0/0C012A68, prev 0/0C012A30, desc: RUNNING_XACTS nextXid 518 latestCompletedXid 516 oldestRunningXid 517; 1 xacts: 517
rmgr: XLOG        len (rec/tot):    114/   114, tx:          0, lsn: 0/0C012AA0, prev 0/0C012A68, desc: CHECKPOINT_ONLINE redo 0/C012A68; tli 3; prev tli 3; fpw true; xid 0:518; oid 40973; 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 517; online
rmgr: Database    len (rec/tot):     34/    34, tx:        517, lsn: 0/0C012B18, prev 0/0C012AA0, desc: DROP dir 1663/16390
rmgr: Transaction len (rec/tot):     66/    66, tx:        517, lsn: 0/0C012B40, prev 0/0C012B18, desc: COMMIT 2023-07-25 20:49:12.425741 CST; inval msgs: catcache 21; sync
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/0C012B88, prev 0/0C012B40, desc: RUNNING_XACTS nextXid 518 latestCompletedXid 517 oldestRunningXid 518
pg_waldump: fatal: error in WAL record at 0/C012B88: invalid record length at 0/C012BC0: wanted 24, got 0

找到drop事务id是517，所以恢复到事务id 516

切换当前wal日志文件至下一个，拷贝上一个wal日志文件到归档目录中

进行数据恢复，将备份文件解压至对应目录下

编辑文件

$ vim postgresql.auto.conf
restore_command = 'cp /archive/%f %p'
recovery_target_xid = '516'

启动数据库，恢复读写，检查数据恢复至drop 前。