1.全自动备份
需要在备份机上也安装postgres(最好同一个版本)
在postgres目录下建立密码保存文件(明码保存,所以保密很重要),如果不建立,则因为每次备份都要输入密码,不能进行自动备份
#创建.pgpass文件 touch .pgpass #在文件中写入以下内容,可多行 #127.0.0.1:5432:dbName:dbUser:dbPassword #127.0.0.1:5432:dbName2:dbUser2:dbPassword2
创建自动脚本,自动按月分文件夹
#!/bin/sh . /etc/profile . ~/.bash_profile dirname=$(date +%Y%m) filename=$(date +%Y%m%d%H%M%S) mkdir -p /home/postgres/backup/$dirname/ pg_dump -h [ipAddr] -U [dbUser] [dbName]| gzip>/home/postgres/backup/$dirname/bk_$filename.gz
创建crontab自动执行即可
#去除限制导出 #--no-tablespaces 代表去除表空间限制(不导出表空间信息) #-O 代表不导出对象/库的所有者信息 #-x 代表不导出ACL权限信息和表的权限信息 pg_dump -h 127.0.0.1 -U postgres --no-tablespaces -O -x -f sqlname.sql dbname
2.恢复数据库
解压gz
#直接解压,不保留原gz文件 gunzip xxx.gz #解压保留源gz文件 gunzip -c xxx.gz > xxx.sql
进去pgsql控制台
psql -U dbUser
#切换到待导入的数据库 \c dbName; #导入sql文件 \i xxx.sql
#退出控制台
\q
3.PG查看当前进程
#查看连接数和详情 select * from pg_stat_activity; #查看最大连接数 show max_connections;
4.批量删除空闲进程
#使用PG账户在数据库客户端上删除 SELECT pg_terminate_backend(p.pid) FROM ( SELECT pid FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state = 'idle' UNION ALL SELECT pid FROM pg_stat_activity WHERE pid <> pg_backend_pid() AND state = 'idle in transaction' AND backend_xid IS NULL ) p #直接到服务器上删除 ps -ef|grep postgres|grep idle |awk '{print $2}' | xargs kill
kill有两种方式,第一种是:
SELECT pg_cancel_backend(PID);
这种方式只能kill select查询,对update、delete 及DML不生效)
第二种是:
SELECT pg_terminate_backend(PID);
这种可以kill掉各种操作(select、update、delete、drop等)操作
XX .在线热备份(归档) -- 待研究
(一)备份
1,配置归档模式
配置归档需要编辑postgresql.conf文件,默认为与/usr/local/pgsql/data/目录下
vim /usr/local/pgsql/data/postgesql.conf
archive_mode = on
archive_command = ‘cp %p /usr/local/pgsql/backup/archived_log/%f’
注:%p要被归档的日志文件的路径,%f是要被归档的日志文件的文件名
2,启动数据库
pg_ctl –D /usr/local/pgsql/data start
3,创建数据库arch
createdb arch
4,创建表并插入记录
psql arch
arch=# create table tb(a int);
arch=# insert into tb(a) values(1);
5,创建备份
arch=# select pg_start_backup(‘baseline’);
6,备份整个data目录
tar –jcv –f /usr/local/pgsql/backup/baseline.tar.bz2 /usr/local/pgsql/data/
7,停止备份
psql arch
arch=# select pg_stop_backup();
8,插入新记录,然后切换日志,重复3次
arch=# insert into tb(a) values(2);
arch=# select pg_switch_xlog();
arch=# insert into tb(a) values(3);
arch=# select pg_switch_xlog();
arch=# insert into tb(a) values(4);
arch=# select pg_switch_xlog();
9,把/data/pg_xlog/下的WAL日志文件复制到预设的归档目录下,保证产生的WAL日志都已归档。
(二)恢复
1,停止数据库
pg_ctl –D /usr/local/pgsql/data/ stop
2,删除/data/
rm –r /usr/local/pgsql/data/
3,恢复备份
tar –jxv –f /usr/local/pgsql/backup/baseline.tar.bz2 –C /
4,清空/data/pg_xlog/目录下所有文件
rm –r /usr/local/pgsql/data/pg_xlog/
5,创建/pg_xlog/及其下面的archive_status目录
mkdir /usr/local/pgsql/data/pg_xlog/
mkdir /usr/local/pgsql/data/pg_xlog/archive_status
6,在/data/目录下创建recovery.conf
vim /usr/local/pgsql/data/recovery.conf
restore_command = ‘cp /usr/local/pgsql/backup/archived_log/%f “%p”’
7,启动数据库
pg_ctl –D /usr/local/pgsql/data/ start
一切正常的话数据库就会自动应用WAL日志进行恢复
8,查看数据库arch是否恢复
psql arch
arch=# select * from tb;
a
---
1
2
3
4
(4 rows)
至此,数据已经成功恢复!