pg数据库备份

 1. pg_dumpall 备份还原

--备份脚本
vim pgall.sh

#!/bin/bash
source /etc/profile
source ~/.bashrc
DATE=`date +%Y%m%d%H%M%S`

newdir=/4T/postgresql/pg127/pgdir
dbname=postgres

#备份内容详细输出到日志文件里
 pg_dumpall > $newdir/all_database-$DATE.sql 
 

# 删除旧的备份文件
find /4T/postgresql/pg127/pgdir -name "*.sql" -mtime +2 -exec rm -rf  {} \;

-------------------------------------------------------------------
crontab -e 
0 5 * * *  su - pg127 -c /4T/postgresql/pg127/scripts/pgall.sh


--------------------------------------------------------------------
还原数据库

[pg12@db02 pgdir]$ psql
psql (12.7)
Type "help" for help.
postgres=# create database test;                   /* 创建中间库，用于删除postgres */
CREATE DATABASE
postgres=# exit
[pg12@db02 pgdir]$ psql -d test                    /* 中间库登录*/              
psql (12.7)
Type "help" for help.
test=# drop database postgres;                     /* 删除postgres */                 
DROP DATABASE
test=# create database postgres;                   /* 创建空库postgres */
CREATE DATABASE
postgres=# exit

[pg12@db02 pgdir]$ psql -f all_database-20241107001001.sql     /* 还原数据库postgres */

 2. pg_dump备份还原

vim pgbak.sh

#!/bin/bash
source /etc/profile
source ~/.bashrc
DATE=`date +%Y%m%d%H%M%S`

newdir=/4T/postgresql/pg127/pgdir
dbname=postgres

#备份内容详细输出到日志文件里
 pg_dump -U postgres -v -Fc -d $dbname --no-owner -f $newdir/postgres-$DATE.dump  2>$newdir/postgres-$DATE.log

#过滤所有备份日志文件里面的error信息，合并一个文件
grep error:*.log > 0error-$DATE.log

# 删除旧的备份文件
find /4T/postgresql/pg127/pgdir -name "postgres*" -mtime +1 -exec rm -rf  {} \;

-------------------------------------------------------------------
crontab -e 
0 15 * * *  su - pg127 -c /4T/postgresql/pg127/scripts/pgbak.sh
----------------------------------------------------------------------
数据库还原

还原数据库
[pg12@db02 pgdir]$ psql
psql (12.7)
Type "help" for help.
postgres=# create database test;                   /* 创建中间库，用于删除postgres */
CREATE DATABASE
postgres=# exit
[pg12@db02 pgdir]$ psql -d test                    /* 中间库登录*/              
psql (12.7)
Type "help" for help.
test=# drop database postgres;                     /* 删除postgres */                 
DROP DATABASE
test=# create database postgres;                   /* 创建空库postgres */
CREATE DATABASE
postgres=# exit

[pg12@db02 pgdir]$ pg_restore -U postgres -h localhost -p 15434 -d postgres   -F c postgres-20241111001001.dump   2> postgres-20241111001001.log   /* 还原数据库postgres */

2.自动删除原库还原（-c）
[pg12@db02 pgdir]$ pg_restore -U postgres -h localhost -p 15434 -d postgres  -c -F c postgres-20241111001001.dump   2> postgres-20241111001002.log   

3. pg_basebackup备份还原
 

vim /4T/postgresql/pg12/scripts/pg_basebackup.sh

  
source /home/pg12/.bash_profile
DATE=`date "+%Y%m%d%H%M%S"`
PGDIR=/4T/postgresql/pg12/pgdir
PGARCH=/4T/postgresql/pg12/archive

#输人密码
export PGPASSWORD='！@#Bds#pg￥%……'


#备份内容输出到备用服务器上
#pg_basebackup -h 192.168.18.101  -U postgres -l bk-${DATE} -F p -P -R -D $PGDIR/bak-${DATE}
pg_basebackup -h 192.168.18.101  -U postgres -l bk-${DATE} -F p -P -R -D $PGDIR/bak-${DATE}  2>&1 | tee $PGDIR/bak-${DATE}.log


# 删除旧的备份文件
find $PGDIR/  -name "bak*" -mtime +7 -exec rm -rf  {} \;
find $PGARCH/ -name "*"    -mtime +7 -exec rm -rf  {} \;

-------------------------------------------------------------------
crontab -e 
10 0  * * *  su - pg12 -c /4T/postgresql/pg12/scripts/pg_basebackup.sh
----------------------------------------------------------------------


数据库还原
[pg12@db02 pgdir]$ pg_ctl stop

[pg12@db02 pgdir]$ rm -rf /4T/postgresql/pg12/pgdata/*
[pg12@db02 pgdir]$ mv  /4T/postgresql/pg12/pgdir/bak-20250307150045/*   /4T/postgresql/pg12/pgdata/
（#*/ 括号里里面的不要管）
[pg12@db02 pgdir]$ rm -rf /4T/postgresql/pg12/pgdata/standby.signal

[pg12@db02 pgdir]$ pg_ctl start

4. 定时truncate表里数据

vim truncate.sh

source /home/pg12/.bash_profile

#输人密码
export PGPASSWORD='！@#Bds#pg￥%……'

#清空表数据
psql -h 192.168.18.1 -U postgres -d postgres -c "TRUNCATE TABLE smp.smp_disk_status_info;"
psql -h 192.168.18.1 -U postgres -d postgres -c "TRUNCATE TABLE smp.smp_memory_status_info;"
psql -h 192.168.18.1 -U postgres -d postgres -c "TRUNCATE TABLE smp.smp_network_status_info;"
psql -h 192.168.18.1 -U postgres -d postgres -c "TRUNCATE TABLE smp.smp_processor_status_info;"
psql -h 192.168.18.1 -U postgres -d postgres -c "TRUNCATE TABLE smp.smp_task_log_info;"
psql -h 192.168.18.1 -U postgres -d postgres -c "TRUNCATE TABLE public.dps_position ;"


-------------------------------------------------------------------
crontab -e 
20 0  * * *  su - pg12 -c /4T/postgresql/pg12/scripts/truncate.sh
----------------------------------------------------------------------