- 浏览: 239323 次
最新评论
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日志进行恢复了
发表评论
-
pg 锁
2016-01-14 16:26 0pg 锁 ... -
postgresql 的三类日志
2016-01-14 15:59 18406一、PostgreSQL有3种日志: 1)pg_log(数据 ... -
pg存储过程--创建分区表
2016-01-13 15:46 01)将普通表改成按时间字段分区表 调用select fun_c ... -
pg常用自制shell脚本-tina
2016-01-13 15:30 48481)小型监控: 1.在pg库主机上部署,每5分钟执行一次,插入 ... -
postgresql 时间类型和相关函数
2016-01-13 10:41 5339今天来好好学习一下postgresql涉及时间的字段类型和一些 ... -
pg 表空间
2016-01-07 16:28 3088一、说明 在数据库运维工作中,经常会有数据目录使用率较高 ... -
pg 定期vacuum和reindex
2016-01-07 14:56 8565定期vacuum和reindex: 一 ... -
pg 序列
2016-01-06 16:58 1578一、简介 一个序列对象通常用于为行或者表生成唯一的标识符。 ... -
ERROR: invalid page header in block 27073 of relation base/21078/45300926
2016-01-06 15:12 2115突然断网,检查后通知我们UPS断电,db所在主机重启 1、连上 ... -
pg_cancel_backend()和pg_terminate_backend()
2016-01-05 17:42 3518pg_cancel_backend()和pg_terminat ... -
canceling statement due to conflict with recovery
2016-01-05 17:12 1622报错: canceling statement due to ... -
postgresql dblink 使用
2015-12-31 14:33 2011dblink的使用 pg的跨库查询工具 select dbli ... -
root用户不能使用psql或者pg_dump等pg命令
2015-12-24 14:40 6956root用户不能使用psql或者pg_dump等pg命令 [ ... -
postgresql新建库2个常见报错
2015-12-22 16:43 6160今天使用pg建库发现两个报错: ERROR: new c ... -
安装postgresql 9.1.1
2015-12-22 16:25 611安装postgresql 9.1.1 ---版本自选,步骤相同 ... -
pgbadger监控安装和使用
2015-12-21 10:01 2001pgbadger监控安装和使用 https://github ... -
oracle,postgresql,mysql一些使用上的区别记录
2015-12-16 11:38 01.限制行数: select * from ta where ... -
postgresql存储过程实例:已审核证书存入临时表
2015-12-14 16:44 622存储过程实例: 需求: 思路:建立存储过程 代码逻辑: 1 ... -
pg 函数sfa_tmp_sleep()执行越来越慢-sql分析
2015-12-11 09:48 646pg 函数sfa_tmp_sleep()执行越来越慢 ... -
pgpool 主从流复制模式下的安装使用
2015-12-11 09:50 4092pgpool-II 是一个位于 PostgreSQL 服务器和 ...
相关推荐
Window 下的PostgreSQL 数据库备份和恢复工具[GUI],供大家一起共同分享使用。
postgresql备份与恢复pgadmin3工具.docx
PGConf.CN2019大会资料 培训PPT--9-石勇虎--遇见时光机-浅谈平安云上PG备份与恢复-final
最好用的PG备份工具PG_PROBACKUP 本地+异地备份恢复部署文档
数据恢复的方式跟备份的方式有关。比如物理备份还是逻辑备份,是否开启了日志备份。所谓日志备份其实就是日志,如果开启了日志...把本地备份恢复到云上 ppas或oracle 恢复备份或者恢复到时间点 mariadb 恢复mariadb备
pg_probackup是一个实用程序,用于管理PostgreSQL数据库集群的备份和恢复。 它旨在执行PostgreSQL实例的定期备份,使您能够在发生故障时还原服务器。 该实用程序兼容: PostgreSQL 9.5、9.6、10、11、12、13; 与...
2.1 创建需要备份的数据库3 2.2 执行备份数据库的命令3 2.3 查看备份出来的数据格式3 3.1 创建需要恢复的数据库4 3.2 导入备份数据4 4.1
pg_probackup官方英文文档+中文翻译,pg_probackup管理PostgreSQL数据库集群的备份和恢复.pg_probackup是一个实用程序,用于管理PostgreSQL数据库集群的备份和恢复。它旨在执行PostgreSQL实例的定期备份,使您能够在...
PostgreSQL数据库备份脚本,结合系统计划任务可以实现数据库的自动化备份。密码直接设置于脚本当中。
postgresql数据库定时备份脚本(linux),亲测可用总结
如果你只做全备份,那么受限于全备份的大小和备份时间,不可能常做。而且只有 全备份,不能将数据库恢复至某个时间点。所以,我们需要全备份+日志备份。比如每天一个全备份,每隔1小时或若干分钟一个日志备份。说到...
11、HGDB数据库备份和恢复 12、Implementation of Global Temp Table 13、PG 可以用来干什么? 14、PG数据库生态选型思路与最佳实践 15、PostgreSQL CDC最佳实践 16、PostgreSQL VACUUM 运行机制探究 17、PostgreSQL...
对许多企业来说,数据库信息代表着网站内容和其它重要的数据,因此,保持数据备份是非常重要的操作。庆幸的是,MySQL和PostgreSQL都提供将数据库转化成flat-text文件的工具,这样你就可以将数据库信息输入到其它系统...
PostgreSQL PG&Oracle-备份技术完美攻略
pg_rman:PostgreSQL备份和还原管理工具
pg_dump和pg_restore案例,其中简单罗列了一些应用过程中可能用到的sql语句。
1PG和FX2n程序 1PG和FX2n程序 1PG和FX2n程序 1PG和FX2n程序
1. Docker 中 Postgres数据库备份 命令: docker exec it db_13 pg_dumpall -c -U mrp > mrp217 解释: docker exec it [docker中镜像数据库名称] pg_dumpall -c -U [用户名称] > [导处的数据库名称] 2. ...
Navicat MySQL和PG都可用 永久免费 分享一下啊啊啊啊啊