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日志进行恢复了
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日志进行恢复了
