PostgreSQL之pgdump备份恢复操作

2024-11-29 18:53:47

逻辑备份在恢复时，介于逻辑备份与故障时间点之间的数据难以恢复，故一般不采取逻辑备份方式进行数据库备份，但逻辑适用于跨平台跨版本的数据迁移；

逻辑备份恢复主要以下三种：

pg_dump

pg_dumpall

copy

本小节主要讲解pg_dump

pg_dump备份

只能备份单个数据库，不会导出角色和表空间相关的信息

-F c 备份为二进制格式,压缩存储.并且可被pg_restore用于精细还原

-F p 备份为文本,大库不推荐

pg_dump恢复

psql dbname -U username < bakfile

或

pg_restore
– pg_restore [option] ... [filename]
– pg_restore -d dbname bakfile

二进制格式的备份只能使用pg_restore来还原,可以指定还原的表,编辑TOC文件,定制还原的顺序,表, 索引等。

文本格式的备份还原, 直接使用用户连接到对应的数据库执行备份文本即可,例如psql dbname -f bak.sql

pg_dump备份恢复示例

1）创建数据库

createdb testdb

2）连入数据库testdb

psql testdb

3）创建测试表，插入数据

testdb=# create table tt(a int) tablespace tbls_t;
testdb=# insert into tt(a) values(1);
testdb=# insert into tt(a) values(2);

4）查看数据

testdb=# select * from tt;

5）备份

pg_dump testdb>/dbbak/testdb.sql #简单语法,可结合选项灵活备份

6）删除数据库testdb

dropdb testdb

7）创建新数据库（恢复之前需创建数据库）

createdb testdb

8）恢复数据

psql testdb </dbbak/testdb.sql

9）查看数据是否回复

psql testdb

testdb=# select * from tt;

至此，数据已成功恢复！

pg_restore -d postgres /dbbak/pgdumpbak/p.dmp

pg_dump备份恢复命令扩展练习

pg_dump -F c -f /dbbak/pgdumpbak/c.dmp -C -E UTF8 -h 127.0.0.1 -U postgres testdb  #二进制格式备份文件
pg_dump -F p -f /dbbak/pgdumpbak/p.dmp -C -E UTF8 -h 127.0.0.1 -U postgres testdb #文本格式备份文件，”-C” 表示包含创建语句
pg_restore /dbbak/c.dmp|less 可以解析二进制格式的备份文件
pg_restore -l /dbbak/c.dmp
pg_restore -d testdb /dbbak/pgdumpbak/c.dmp #需要先创建目标库
pg_restore -d postgres /dbbak/pgdumpbak/p.dmp #文件中包含创建数据库的命令，不需要创建目标库

toc文件选择性备份恢复

1）根据二进制备份文件生成toc文件

pg_restore -l -f /dbbak/pgdumpbak/toc /dbbak/pgdumpbak/c.dmp

2）修改 toc文件，以首行加分号“;”的方式注释掉不用还原的内容

3）以toc文件列表做恢复

pg_restore -F c -L /dbbak/pgdumpbak/toc -d testdb /dbbak/pgdumpbak/c.dmp

补充：Postgresql备份与还原命令pg_dump

postgresql数据库的备份和还原命令pg_dump

常用命令：

备份：

pg_dump -U postgres -d myDBname -f dump.sql

其中

postgres是用户名

myDBname是数据库名

dump.sql是文件名

还原：

createdb newDBname

psql -d newDBname -U postgres -f dump.sql

其中

postgres是用户名

newDBname是数据库名

dump.sql是文件名

参考：

pg_dump 把一个数据库转储为纯文本文件或者是其它格式.
用法:
 pg_dump [选项]... [数据库名字]
一般选项:
 -f, --file=FILENAME   输出文件或目录名
 -F, --format=c|d|t|p   输出文件格式 (定制, 目录, tar)
        明文 (默认值))
 -j, --jobs=NUM    执行多个并行任务进行备份转储工作
 -v, --verbose    详细模式
 -V, --version    输出版本信息，然后退出
 -Z, --compress=0-9   被压缩格式的压缩级别
 --lock-wait-timeout=TIMEOUT 在等待表锁超时后操作失败
 -?, --help     显示此帮助, 然后退出
控制输出内容选项:
 -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   在只恢复数据的过程中禁用触发器
 --enable-row-security  启用行安全性（只转储用户能够访问的内容）
 --exclude-table-data=TABLE 不转储指定名称的表中的数据
 --if-exists    当删除对象时使用IF EXISTS
 --inserts     以INSERT命令，而不是COPY命令的形式转储数据
 --no-security-labels   不转储安全标签的分配
 --no-synchronized-snapshots 在并行工作集中不使用同步快照
 --no-tablespaces    不转储表空间分配信息
 --no-unlogged-table-data  不转储没有日志的表数据
 --quote-all-identifiers  所有标识符加引号，即使不是关键字
 --section=SECTION   备份命名的节 (数据前, 数据, 及 数据后)
 --serializable-deferrable 等到备份可以无异常运行
 --snapshot=SNAPSHOT   为转储使用给定的快照
 --strict-names    要求每个表和/或schema包括模式以匹配至少一个实体
 --use-set-session-authorization
        使用 SESSION AUTHORIZATION 命令代替
    ALTER OWNER 命令来设置所有权
联接选项:
 -d, --dbname=DBNAME  对数据库 DBNAME备份
 -h, --host=主机名  数据库服务器的主机名或套接字目录
 -p, --port=端口号  数据库服务器的端口号
 -U, --username=名字  以指定的数据库用户联接
 -w, --no-password  永远不提示输入口令
 -W, --password   强制口令提示 (自动)
 --role=ROLENAME   在转储前运行SET ROLE
如果没有提供数据库名字, 那么使用 PGDATABASE 环境变量
的数值.
报告错误至 <pgsql-bugs@postgresql.org>.

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

postgresql 实现sql多行语句合并一行

多行语句合并一行三个表关联查询的结果集为: SELECT users.name,users.age,users.birthday,roles.name FROM users,users_roles,roles WHERE users_roles.userid = users.id and users_roles.roleid = roles.id name age birthday role 张三 23 1993-1-1 role1 张三 23 1993-1-1 role2 张三 23 199
PostgreSQL 实现将多行合并转为列

需求将下列表格相同id的name拼接起来输出成一列 id Name 1 peter 1 lily 2 john 转化后效果: id Name 1 peter:lily 2 john: 实现方式使用 array_to_string 和 array_agg 函数,具体语句如下: string_agg(expression, delimiter) 把表达式变成一个数组 string_agg(expression, delimiter) 直接把一个表达式变成字符串 select id, array_to
在postgresql数据库中判断是否是数字和日期时间格式函数操作

在编写GreenPlum函数的过程中,遇到要判断字符串是否是数字和日期格式的情况,基于GreenPlum和postgresql的亲缘关系,找到了下面两个函数. --1.判断字符串是否是数字 CREATE OR REPLACE FUNCTION isnumeric(txtStr VARCHAR) RETURNS BOOLEAN AS $$ BEGIN RETURN txtStr ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'; END; $$ LANGUAGE 'plpgsq
PostgreSQL 分页查询时间的2种比较方法小结

数据库中存了3000W条数据,两种分页查询测试时间第一种 SELECT * FROM test_table WHERE i_id> 1000 limit 100; Time: 0.016s 第二种 SELECT * FROM test_table limit 100 OFFSET 1000; Time: 0.003s 第一种 SELECT * FROM test_table WHERE i_id> 10000 limit 100; Time: 0.004s 第二种 SELECT * FROM
postgresql rank() over, dense_rank(), row_number()用法区别

如下学生表student,学生表中有姓名.分数.课程编号,需要按照课程对学生的成绩进行排序 select * from jinbo.student; id | name | score | course ----+-------+-------+-------- 5 | elic | 70 | 1 4 | dock | 100 | 1 3 | cark | 80 | 1 2 | bob | 90 | 1 1 | alice | 60 | 1 10 | jacky | 80 | 2 9 | iri
PostgreSQL删除更新优化操作

1. 先说删除吧,因为刚搞了. 删除缓慢的原因:主要是约束的问题.(数据库在有约束的时候,进行操作,会根据约束对相关表进行验证,可想而知,20W的数据验证要耗费多久的时间).其次就是sql的编写.(sql如果查询中包含子查询等的可以优化的where会影响匹配的速度<查询的话就不多逼逼了>).索引的问题请看下面的补充部分具体解决方法: ALTER TABLE tableName DISABLE TRIGGER ALL; delete 目标语句 ALTER TABLE tableName E
Postgresql自定义函数详解

PostgreSQL函数也称为PostgreSQL存储过程. PostgreSQL函数或存储过程是存储在数据库服务器上并可以使用SQL界面调用的一组SQL和过程语句(声明,分配,循环,控制流程等). 语法: CREATE [OR REPLACE] FUNCTION function_name (arguments) RETURNS return_datatype AS $variable_name$ DECLARE declaration; [...] BEGIN < function_body
PostgreSQL之pgdump备份恢复操作

逻辑备份在恢复时,介于逻辑备份与故障时间点之间的数据难以恢复,故一般不采取逻辑备份方式进行数据库备份,但逻辑适用于跨平台跨版本的数据迁移: 逻辑备份恢复主要以下三种: pg_dump pg_dumpall copy 本小节主要讲解pg_dump pg_dump备份只能备份单个数据库,不会导出角色和表空间相关的信息 -F c 备份为二进制格式,压缩存储.并且可被pg_restore用于精细还原 -F p 备份为文本,大库不推荐 pg_dump恢复 psql dbname -U username
IIS备份恢复一键搞定

备份的过程很简单:在Windows 2000 Server中找到"开始"菜单中的"管理工具",点击其中的"Internet服务管理器",在服务器名称上右击,在弹出的菜单中点击"备份/还原配置",会出现如图1所示的窗口.点击[创建备份]按钮,会出现输入IIS配置备份名称的对话框,这里我们输入"黄迎2004415".点击[确定]按钮,IIS的备份就完成了. 图1 我就是这样做的,自从教了那个网管后,客户再也没有
oracle备份恢复的具体方法

1.1 数据库数据全库备份注:此操作需要启动数据库. 切换至oracle用户,在OS侧创建备份使用目录: mkdir /oracle/backup 登录oracle数据库,在数据库中创建备份使用目录 sqlplus / as sysdba create directory backupdir as '/oracle/backup'; 退出sqlplus后,用oracle用户执行全库备份,备份文件生成在/oracle/backup下: expdp system/System123 DIR
如何备份恢复IIS？详细备份恢复IIS的步骤

Windows的IIS有一个非常"坑爹"的备份还原功能,和大部分备份还原不同的是,这个服务默认不支持不同电脑之间的备份还原,如果用户重装Windows的话,即使备份了IIS的相关文件,还原的时候还是提示"无效签名",导致这个IIS无法恢复. 实际上,如果要在不同电脑之间(或者重装Windows)备份恢复IIS,需要在备份的时候输入密码,在还原的时候也输入密码,这样恢复才能正常,而默认是不输入密码则永远也无法成功恢复IIS配置,相信很多网站管理员都被微软这个坑爹的功能
使用centos系统中的crontab命令对mongodb定时备份恢复

通过centos脚本来执行备份操作,使用crontab命令实现定时备份与恢复功能,并删除指定天数前的备份具体操作: 1.创建Mongodb数据库备份目录 mkdir -p /home/backup/mongod_bak/mongod_bak_now mkdir -p /home/backup/mongod_bak/mongod_bak_list 2.新建Mongodb数据库备份脚本 vi /home/crontab/mongod_bak.sh #新建文件,输入以下代码 #!/bin/sh DU
MySQL数据库入门之备份数据库操作详解

本文实例讲述了MySQL数据库入门之备份数据库操作.分享给大家供大家参考,具体如下: 接上一次:MySQL数据库入门多实例配置一提到数据,大家神经都会很紧张,数据的类型有很多种,但是总归一点,数据很重要,非常重要,因此,日常的数据备份工作就成了运维工作的重点中的重点的重点................. 首先来看看数据库中的数据 mysql> select * from test; +-----+------+ | id | name | +-----+------+ | 1 | 1 | |
mysql8.0.20配合binlog2sql的配置和简单备份恢复的步骤详解

第一步安装 1.安装MySQL 2.安装Python3 [root@localhost /]#yum install python3 3.下载binlog2sql文件到本地(文件在百度云盘) [root@localhost /]#mkdir tools [root@localhost /]#cd tools [root@localhost tools]# ll total 317440 -rw-r--r--. 1 root root 317440 Sep 21 23:55 binlog2sql
mysql 中的备份恢复,分区分表,主从复制,读写分离

目录 mysql 的备份和恢复 mysql 的分区分表 mysql 的主从复制读写分离 mysql 的备份和恢复创建备份管理员创建备份管理员,并授予管理员相应的权限备份所需权限:select,reload,lock tables,replication client,show view,event,process # 创建管理员 create user 'backup'@'localhost' identified by '123456'; # 给管理员授权 grant select,re
php实现mysql备份恢复分卷处理的方法

本文实例讲述了php实现mysql备份恢复分卷处理的方法.分享给大家供大家参考.具体分析如下: 分卷处理就是把握们要处理的数据分成一个个小文件进行处理了,这里我来给大家介绍一个php mysql备份恢复分卷处理类,实现mysql数据库分卷备份,选择表进行备份,实现单个sql文件及分卷sql导入. 分卷导入类及思路详解数据库导入导出是一个后台必要拥有的功能,网上一搜,有很多关于数据库导入导出的,但基本上一个大的系统,包含了许多我们并不需要的,而且他们都是自己的后台的形式,我并不喜欢的是拿人家的东
mysql 5.6 从陌生到熟练之_数据库备份恢复的实现方法

MySQL数据库使用命令行备份|MySQL数据库备份命令例如: 数据库地址:127.0.0.1 数据库用户名:root 数据库密码:root 数据库名称: szldb 备份数据库到D盘跟目录 mysqldump -h127.0.0.1 -uroot -proot szldb > d:/backupfile.sql 备份到当前目录备份MySQL数据库为带删除表的格式,能够让该备份覆盖已有数据库而不需要手动删除原有数据库 mysqldump --add-drop-table -h127.0.0.