pg 简单备份和恢复

pg的备份和恢复
pg_dump 把一个数据库转储为纯文本文件或者是其它格式.
用法:  pg_dump [选项]... [数据库名字]
一般选项:
  -f, --file=FILENAME         output file or directory name
  -F, --format=c|d|t|p        output file format (custom, directory, tar, plain text)
  -v, --verbose            详细模式
  -Z, --compress=0-9       被压缩格式的压缩级别
--lock-wait-timeout=TIMEOUT 在等待表锁超时后操作失败
  --help                       显示此帮助信息, 然后退出
  --versoin                    输出版本信息, 然后退出
控制输出内容选项:
  -a, --data-only          只转储数据,不包括模式
  -b, --blobs              在转储中包括大对象
  -c, --clean              在重新创建之前，先清除（删除）数据库对象
  -C, --create             在转储中包括命令,以便创建数据库
  -E, --encoding=ENCODING     转储以ENCODING形式编码的数据
  -n, --schema=SCHEMA      只转储指定名称的模式
-N, --exclude-schema=SCHEMA     不转储已命名的模式
  -o, --oids               在转储中包括 OID
  -O, --no-owner           在明文格式中, 忽略恢复对象所属者
  -s, --schema-only        只转储模式, 不包括数据
  -S, --superuser=NAME     在转储中, 指定的超级用户名
  -t, --table=TABLE        只转储指定名称的表
  -T, --exclude-table=TABLE       只转储指定名称的表
  -x, --no-privileges      不要转储权限 (grant/revoke)
  --binary-upgrade         只能由升级工具使用
  --column-inserts          以带有列名的INSERT命令形式转储数据
  --disable-dollar-quoting     取消美元 (符号) 引号, 使用 SQL 标准引号
  --disable-triggers         在只恢复数据的过程中禁用触发器
  --inserts                 以INSERT命令，而不是COPY命令的形式转储数据
  --no-security-labels        do not dump security label assignments
  --no-tablespaces           不转储表空间分配信息
  --no-unlogged-table-data    do not dump unlogged table data
  --quote-all-identifiers     quote all identifiers, even if not key words
  --serializable-deferrable   wait until the dump can run without anomalies
--use-set-session-authorization
   使用 SESSION AUTHORIZATION 命令代替ALTER OWNER 命令来设置所有权
联接选项:
  -h, --host=主机名        数据库服务器的主机名或套接字目录
  -p, --port=端口号        数据库服务器的端口号
  -U, --username=名字      以指定的数据库用户联接
  -w, --no-password        永远不提示输入口令
  -W, --password           强制口令提示 (自动)
  --role=ROLENAME          do SET ROLE before dump
如果没有提供数据库名字, 那么使用 PGDATA环境变量的数值.

1、pg_dump
导出整个库
pg_dump -U postgres -Fc  tina >tina.dump
pg_dump --host localhost --port 5432 --username postgres -Fc --encoding=UTF8 --verbos  --file /opt/db_backup/tmp_bk/devops.bk  devops

对多个表导出，每个表都需要加参数-t
pg_dump tina -t test -t test1 >filename

导出表结构：-s -t
pg_dump -s -t tina.tbtest testdb > tbtest.out

导出表结构和内容：-t
pg_dump -h testhost -t tina.tbtest testdb > tbtest.sql

只导出某个表的数据：-a
pg_dump -h testhost -t tina.tbtest -a testdb > tbtest.sql
pg_dump --host localhost --port 5432 --username postgres -Fc --encoding=UTF8 --verbose --data-only --file=/opt/db_backup/tmp_bk/t_sfa_sample_tmp1230.bk -t t_sfa_sample_tmp tm_samples

导数时压缩：                          
pg_dump dbname|gzip >filename.gz

2、pg_dumpall
pg_dump只能备份单个数据库，而且恢复的时候需要创建空数据库
pg_dumpall 可以备份所有数据库，并且备份角色、表空间
pg_dumpall > /pg/data/pg_all.dmp

3、恢复
恢复压缩的备份片
gunzip -c filename.gz |psql dbname

恢复备份片
cat filename*|psql dbname

恢复数据库
psql -f  /pg/data/pg_all.dmp  postgres   ---其实是直接执行dump里面的sql命令
pg_restore -U postgres -d devops  /tmp_bk/devops.bk  ---要先创建好空的同名数据库

恢复某个表的数据
pg_restore -U postgres -d tm_samples /tmp/t_sfa_sample_tmp1230.bk
pg_restore -U postgres -d tm_samples /tmp/t_sfa_sample1230.bk

恢复某个表
pg_restore -U postgres -d test /tmp_bk/t_task_file_info.bk  
pg_restore -U postgres -d rename_check  /tmp_bk/t_analyst_list.bk  2>&1 &


4、冷备份，需要停止数据库--直接拷贝数据目录 $PGDATA，要保证两边安装了同样版本的pg
原主机：
pg_ctl -D /pg/data stop
tar -zcvf /pg/data/data.tar.gz  /pg/data

新主机：
cd /pg
mv data data_old
恢复
tar -zxvf data.tar.gz -C /pg/

启动数据库
pg_ctl -D /pg/data start
验证

5、热备份，需要配置归档模式
vi /pg/data/postgresql.conf
archive_mode = on
archive_command = 'cp %p /pg/data/backup/archived_log/%f'
%p 要被归档的日志文件的路径，%f要归档的日志文件的文件名

pg_ctl -D /pg/data  start

创建备份：
testdb=# select pg_start_backup('baseline');
ERROR:  WAL level not sufficient for making an online backup
HINT:  wal_level must be set to "archive" or "hot_standby" at server start.
STATEMENT:  select pg_start_backup('baseline');

开启了归档，可能需要重启生效
[postgres@localhost data]$ pg_ctl -D /pg/data stop
LOG:  received smart shutdown request
LOG:  autovacuum launcher shutting down
LOG:  shutting down
waiting for server to shut down....LOG:  database system is shut down
done
server stopped
[postgres@localhost data]$ pg_ctl -D /pg/data start
FATAL:  WAL archival (archive_mode=on) requires wal_level "archive" or "hot_standby"
server starting

修改参数
wal_level = archive

testdb=# select pg_start_backup('baseline');
pg_start_backup
-----------------
0/4000020
(1 row)

如果遇到报错
可以将备份停下来
select pg_stop_backup();

备份整个data目录
tar -jcv -f /pg/data/backup/baseline.tar.bz2  /pg/data/backup

停止备份
select pg_stop_backup();

插入新记录，然后切换日志，重复三次
insert into test values(1);
select pg_switch_xlog();
insert into test values(2);
select pg_switch_xlog();
insert into test values(3);
select pg_switch_xlog();

把/data/pg_xlog下的wal日志文件复制到预设的归档目录下，保证产生的wal日志都已经归档

恢复
停止数据库
pg_ctl -D  /pg/data stop
删除/data
rm -r /pg/data
恢复备份
tar -jxv -f /pg/data/backup/baseline.tar.bz2 -C /
清空/data/pg_xlog 目录下所有文件
rm -r /pg/data/pg_xlog
创建/pg/xlog 及下面的archive_status目录
mkdir /pg/data/pg_xlog
mkdir /pg/data/pg_xlog/archive_status
在/data目录下创建recovery.conf
vim  /pg/data/recovery.conf
restore_command='cp /pg/data/backup/archived_log/%f"%p"'

启动数据库
pg_ctl -D /pg/data/start

一切正常的话数据库就会自动应用wal日志进行恢复了