数据库备份表语句、数据库备份数据语句 收藏 0

SQL语句之备份表(数据库备份操作之指南)

备份乃不死之王！

一、全表结构备份：

SELECT INTO 语句：表示从一个表中选取数据，然后把数据插入另一个表中，常用来备份一张表

示例：备份student表，备份表取名为student_backup

则会生成一张与student表结构及数据一样的备份表。

先需要建好表和表结构；

二、如果只备份表中的某些列：

示例：只备份student表中的sno,name列入新表student_backup

三、如果需要将表中满足一定条件的记录进行备份，则可以使用where字句配套使用

示例：将所有性别为男的学生记录备份到新表student_backup

注：但是在mysql中使用SELECT INTO语句是无法进行备份操作，执行命令时会提示新表未定义

所以，我们应该使用下列语句进行数据表的备份操作。

1.只复制表结构到新表 :（只有结构无数据）

或

此两种方法的区别：

使用第一条语句，备份的新表并没有旧表的primary key 、auto_increment等属性，需要重新对新表进行设置

示例：

或者

2.复制表结构及数据到新表

这种方法会将oldtable中所有的内容都拷贝过来，同时也存在备份的新表不具备旧表 primary key、auto_increment等属性，需要对新表再次设置。

示例：复制student表中所有数据到新表student_backup1;

超详细的pg12.2数据库五种备份恢复机制总结，值得收藏

备份重于一切，今天主要介绍PG的五种备份方式，仅供参考。

ps:前四种重点掌握

1、语法

可以在本地及远程进行备份，只需要表的读权限即可备份。pg_dump创建的备份是一致的，在pg_dump运行时数据库产生快照，不阻塞数据库的DML操作，但是会阻塞需要排他锁的操作，如alter table等。特别注意的是，pg_dump一次只能备份一个单独的数据库，且不能备份角色和表空间信息（因为这些信息是cluster-wide，而不是在某个数据库中（per-database））。

使用pg_dump的自定义转储格式。. 如果PostgreSQL所在的系统上安装了zlib压缩库，自定义转储格式将在写出数据到输出文件时对其压缩。这将产生和使用gzip时差不多大小的转储文件，但是这种方式的一个优势是其中的表可以被有选择地恢复。

下面的命令使用自定义转储格式来转储一个数据库：pg_dump -Fc dbname > filename自定义格式的转储不是psql的脚本，只能通过pg_restore恢复，例如：pg_restore -d dbname filename

2、常见用法

pg_dump只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息。pg_dumpall则可以导出整个数据库集群中所有的数据库中的数据，同时也会导出角色、用户和表空间的定义信息。

执行pg_dumpall需要超级用户权限。

1、语法

2、常用用法

COPY在PostgreSQL表和文件之间交换数据。 COPY TO把一个表的所有内容都拷贝到一个文件，而COPY FROM从一个文件里拷贝数据到一个表里(把数据附加到表中已经存在的内容里)。 COPY TO还能拷贝SELECT查询的结果。

如果声明了一个字段列表，COPY将只在文件和表之间拷贝已声明字段的数据。 如果表中有任何不在字段列表里的字段，那么COPY FROM将为那些字段插入缺省值。

带文件名的COPY指示PostgreSQL服务器直接从文件中读写数据。 如果声明了文件名，那么服务器必须可以访问该文件，而且文件名必须从服务器的角度声明。 如果使用了PROGRAM选项，则服务器会从指定的这个程序进行输入或是写入该程序作为输出。 如果使用了STDIN 或STDOUT选项，那么数据将通过客户端和服务器之间的连接来传输。

注意：copy命令必须在plsql命令行执行，执行用户必须为superuser，普通用户进行执行，需要在copy前面加入 “\\”，即 \\copy。

COPY只能用于表，不能用于视图，不过可以用于COPY (SELECT * FROM viewname) TO …

1、语法

copy to的导出速度非常之快，经测试10W的数据量只需要3秒左右的时间。

COPY FROM能够识别下列特殊反斜杠字符：

2、常见用法

1、基础备份

2、恢复

PostgreSQL有一个导出和导入事务快照的功能，这个功能在9.2版本开始支持，允许事务共享它当时的snapshot给其他的事务使用。SET TRANSACTION SNAPSHOT命令允许新的事务使用与一个现有事务相同的快照运行。已经存 在的事务必须已经把它的快照用pg_export_snapshot函数导出。该函数会返回一个快照标识符，SET TRANSACTION SNAPSHOT需要被给定一个快照标识符来指定要导入的快照。需要注意的是：只有事务是SERIALIZABLE以及 repeatable read时，DEFERRABLE 事务属性才会有效。

PostGreSQL采用“快照”方式来实现MVCC。具体地说，这意味着每一个事务中的查询仅能看到：

1)该事务启动之前已经提交的事务所作出的数据更改。

2)当前事务中该查询之前的查询所作出的更改。

下面基于事务隔离级别repeatable read进行测试

1、建表

2、session1:

3、session2(插入一条新数据并提交)：

4、session3(能查看到会话2插入的数据):

5、session4 (导入s1的第一个snapshot, 因此看不到s2提交的数据) :

6、session5 (导入s1的第二个snapshot, 因此看不到s2提交的数据, 同时验证了看不到s1修改过的数据):

7、session1(提交)：

8、session4 (s1提交后, 这个snapshot还存在, 只要还有导入了这个snapshot的事务存在着) :

9、session5 (s1提交后, 这个snapshot还存在, 只要还有导入了这个snapshot的事务存在着)

篇幅有限，基于时间点恢复的内容后面单独介绍吧，感兴趣的朋友可以关注下！

史上最全Oracle数据泵常用命令

导读：expdp和impdp是oracle数据库之间移动数据的工具，本文简单总结了数据泵的常用命令，希望对大家有帮助。

前言

expdp和impdp是oracle数据库之间移动数据的工具。expdp和impdp只能在数据库服务端使用，不能在客户端使用。本文简单总结了expdp和impdp常用的命令，详细信息参考oracle官方文档Utilities。

directory相关SQL语句:

EXPDP导出

注意：

1、导数的数据库用户需要拥有对directory_object的读写权限。

2、操作系统中需要已经存在directory_object指定的路径。

3、oracle用户拥有对directory_object指定路径的读写权限。

4、system用户导出用户，会将创建用户和授予系统权限的元数据也导出，普通用户不能导出这些元数据。

expdp命令示例

以导出一个用户为例

expdp参数说明:

活动预告

2019 数据技术嘉年华,数据英雄聚京华 – 诚邀论道

IMPDP导入

注意：

1、expdp导出的文件不能使用imp导入，只能通过impdp导入数据库。

2、导入时遇到已存在的对象，默认会跳过这个对象，继续导入其他对象。

3、导入时应确认dmp文件和目标数据库的tablespace、schema是否对应。

4、导入dmp文件时，应确定dmp文件导出时的命令，以便顺利导入数据。

拿到一个dmp文件，如果忘记了导出命令，可以通过以下方法确认(非官方，生产数据勿使用)：

确认dmp文件是exp导出还是expdp导出

1）xxd test.dmp | more

2）strings test.dmp | more

impdp命令示例

以导入dmp文件中的所有数据为例

impdp参数说明

交互模式

进入交互可以操作导入导出作业。

进入交互模式的方法：

1、导入导出命令行执行期间按Ctrl + c

2、expdp attach=jobname或impdp attach=jobnam

查看导入导出日志可以看到jobname，也可以通过查询dba_datapump_jobs找到jobname。

报错总结

系统目录未建立，报错：

impdp导入exp导出的dmp文件，报错：

如果导出的数据库版本比导入的数据版本高，需要在导出时加上参数version=要导入的数据库版本。否则报错：

文末再附加一条导出用户元数据的sqlfile命令，这个是之前一个朋友问我的，因为之前做的db2的工作，最近才开始和oracle打交道，数据泵了解的很少，所以被问到这块的时候犹豫了片刻，第一个想到的自然是使用oracle自带的数据泵工具expdp：

但是问题来了，朋友要的是sqlfile不是dumpfile，经过百度得知可以通过plsql developer工具把元数据导出成sqlfile的形式，但是导出的sqlfile还是不符合朋友的需求，后经指点得知，在导出元数据之后，只需要在导入的时候加上sqlfile参数，就可以生成sqlfile文件，具体命令如下：

impdp工具里对sqlfile的描述如下

将所有的 SQL DDL 写入指定的文件。

活动预告：2019数据技术嘉年华于2019年11月15日-16日在北京举办，扫描下方二维码了解详情，欢迎大家携好友一同参会！

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com