首页 > 数据库 >postgresql从入门到精通教程 - 第36讲:postgresql逻辑备份

postgresql从入门到精通教程 - 第36讲:postgresql逻辑备份

时间:2023-12-01 10:14:01浏览次数:45  
标签:postgresql postgres dump 备份 36 testdb pg tab 数据库

 

 

 

PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUG PG技术大讲堂。

 

第36讲:PostgreSQL逻辑备份

 

第36讲:12月02日(周六)19:30-20:30,往期公开课的文档及视频,联系CUUG

 

内容1:逻辑备份概述

内容2:pg_dump使用

内容3:pg_dumpall使用

内容4:copy使用

 

PG导出导入工具

 

PG导出导入概述

· 可以使用这些实用程序执行以下操作:

归档历史数据

保存表定义以防止用户出错

在计算机和数据库之间或PostgreSQL服务器的不同版本之间移动数据

在数据库之间传输数据

 

调用导出和导入程序的方法

命令行接口

其它管理工具

 

导出模式

· pg_dump只能备份单个数据库,不会导出角色和表空间相关的信息,而且恢复的时候需要创建空数据库。

· pg_dumpall可以备份所有数据库,并且备份角色、表空间。

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

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

 

调用pg_dump

语法:

pg_dump [选项]... [数据库名字]

示例:

pg_dump testdb > testdb.sql

pg_dump -F c -f testdb.dmp -C -E UTF8 -h 127.0.0.1 -U postgres testdb

pg_dump -F p -f testdb.sql -C -E UTF8 -h 127.0.0.1 -U postgres testdb

 

逻辑恢复

· 恢复顺序

1.创建新表

2.导入数据

3.建立索引

4.触发器已导入

5.在新表上启用完整性约束

6.创建函数

 

· 恢复数据

文本格式备份恢复方式:

1、创建新数据库

CREATE DATABASE new_db1;

2、恢复数据

psql new_db1 < testdb.sql

二进制格式备份恢复方式:

1、创建新数据库

CREATE DATABASE new_db1;

2、恢复数据

pg_restore -d new_db1 testdb.dmp

 

二进制备份实现精细恢复

· 利用toc文件实现精细恢复

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

pg_restore -l -f testdb.toc testdb.dmp

2、修改 toc 文件,用‘;’号注释掉不用还原的内容

203; 1259 40996 TABLE public dept postgres

204; 1259 41001 TABLE public emp postgres

;202; 1259 16391 TABLE public t1 postgres

2840; 0 40996 TABLE DATA public dept postgres

2841; 0 41001 TABLE DATA public emp postgres

;2839; 0 16391 TABLE DATA public t1 postgres

3、以toc文件做恢复

pg_restore -F c -L testdb.toc -d testdb testdb.dmp

 

其它导出导入技巧

· 利用管道符实现各种导出导入

导出并且压缩

pg_dump testdb |gzip testdb.sql

解压并且导入,压缩文件不变,,导入到testdb数据库:

gunzip -c testdb.sql.gz |psql testdb

导出后直接进行恢复,文件不落地,把testdb数据库的表导入到testdb1:

pg_dump testdb| psql testdb1

并行导出与导入

pg_dump -Fd -j4 -f testdb.p.dump testdb

pg_restore -d testdb1 -j4 testdb.p.dump

 

其它备份恢复技巧

· 备份指定/排除的表

如果只想导出指定的表数据,可以使用-t参数,表名需要用两个双引号概括起来。

pg_dump -t ""departments"" -t ""employees"" testdb > testdb.sql

导出排除指定的表的数据库

pg_dump -d testdb -T ""emp"" > testdb_ex_emp.sql

 

使用pg_dump实现主机间数据库间迁移数据

· 文件不落地,实现数据库迁移

1、配置两个数据库之间的信任关系(必须)

host all all 192.168.18.0/24 trust

2、在数据库之间进行某个数据库迁移

pg_dump -h pg1 -p 1922 -U postgres testdb|psql -h pg2 -p 5432 -U postgres testdb

3、迁移所有的数据库到目标数据库

pg_dumpall -h pg1 -p 1922 -U postgres |psql -h pg2 -p 5432 -U postgres

注意:

1、PG数据库之间迁移无需配置dblink,只要指定主机名和端口即可。

2、由于用的是管道符,所以必须配置两个数据库远程登录信任关系。

3、迁移时两个数据库必须要有相同的表空间,需要提前创建。

 

copy概述

· 实现表与文件之间的交互

copy 命令用于数据库中表与操作系统文件(和标准输出、标准输入)之间的相互拷贝,可以由表至文件,也可以由文件至表。

使用copy实现数据库表和文本文件之间数据的迁移,非常方便,比Oracle的sqlloader易用性好。

 

copy语法

· 语法

文件到表

COPY table_name [ ( column ...] ) ]

FROM { 'filename' | command' | STDIN }

[ [ WITH ] ( option [, ] ) ]

表到文件

COPY { table_name column_name [, ...] ) ] | ( query ) }

TO { 'filename' | PROGRAM 'command' | STDOUT }

[ [ WITH ] ( option [, ...] ) ]

 

应用案例

· 文件到表

文件到表时,支持的文本内容格式有两种,一种是tab制表符作为分隔符(不是空格);一种是逗号做为分隔符。

tab键作为分隔符:

1 a

2 b

3 c

逗号作为分隔符:

1,a,"I am students"

2,b,"I am teacher"

3,c,"I am boss"

· 文件到表

文件到表时,支持的文本内容格式有两种,一种是tab制表符作为分隔符(不是空格);一种是逗号做为分隔符。

tab键作为分隔符:

\copy tab_name from /home/postgres/tab.txt;

逗号做为分隔符:

\copy tab_name from /home/postgres/tab.csv with csv;

· 表到文件

表到文件时,支持的文本内容格式有两种,一种是tab制表符作为分隔符(不是空格);一种是逗号做为分隔符。

tab键作为分隔符:

\copy tab_name to /home/postgres/tab.txt;

逗号做为分隔符:

 

\copy tab_name to /home/postgres/tab.csv with csv;

 

CUUG PostgreSQL技术大讲堂系列公开课,往期视频及文档,请联系CUUG客服。

标签:postgresql,postgres,dump,备份,36,testdb,pg,tab,数据库
From: https://www.cnblogs.com/cnblogs5359/p/17869096.html

相关文章

  • RAC备份(12CR2)->异机恢复(单机)
    OS:CentOS7源库:2节点组成的RAC,SID:slnngk1,slnngk2,db_name:slnngk目的库:单机,sid:slnngkb,db_name:slnngkb数据库版本:12.2.0.1注意:目的端的db_name必须与源端保持一致 1.异地机器安装相同版本的数据库软件和启动监听安装步骤省略,注意只安装软件和启动监听器(没有监听......
  • mysqlsh备份恢复小记
    参考文档:https://mp.weixin.qq.com/s/SGWgecxrpLNhnQzQMwh_Ewhttps://opensource.actionsky.com/20221012-mysql/https://blog.csdn.net/m0_67261762/article/details/125046721?spm=1001.2101.3001.6661.1&utm_medium=distribute.pc_relevant_t0.none-task-blog-2~......
  • postgreSql使用postgis实现空间聚簇
    聚合前 聚合后 具体sql/*使用ST_ClusterDBSCAN函数,ST_ClusterDBSCAN是窗口函数用来对数据进行聚簇第一个参数是字段,第二个参数是距离,他是用的是度,这里写0.000179大约就是20米,minpoints表示多少个聚合才算是一个簇,比如两台车就算是聚集,那就设置为2*/selectST......
  • 解决VS编译C++时,该文件包含不能在当前代码页(936)中表示的字符。请将该文件保存为 Uni
    使用VS编译C++时,报错: warningC4819:该文件包含不能在当前代码页(936)中表示的字符。请将该文件保存为Unicode格式以防止数据丢失。利用VS的高级保存选项,修改合适的编码规则即可解决,最新版VS需要手动添加高级保存选线的命令,方法如下:打开工具-->自定义 选择命令-->选择添......
  • sqlsever单表改名及备份
    --改名EXECsp_rename'old_table_name','new_table_name';--备份select*intodbo.t_scs_0304fromdbo.t_scs;对表结构字段进行修改:添加列:altertable表名add列名varchar(55)删除列:altertable表名dropcolumn列名改列类型:altertable表名altercolumn列名varc......
  • 其他备份
    @router.post('/openai',response_class=EventSourceResponse)asyncdefsimple_chat(user_input=Body(...,embed=True,alias='input'),chat_history=Body(...,embed=True,alias='chat_history')):app_input......
  • P1036 [NOIP2002 普及组] 选数(递归)
    [P1036[NOIP2002普及组]选数]我的思路是运用递归实现一个树状分支例如3712194选3,每个情况为3-7-123-12-197-12-19注意我们用递归时在传参时要以和的形式传参。如果先求和再传参就会发生错误.#include<iostream>#include<string>#include<math.h>#include<......
  • PostgreSQL - Change Timezone
    ThedefaulttimezoneofanewlycreateddatabaseisUTC. Youcansetthetimezonetoanewvaluesessionlyorglobally:zzh@ZZHPC:~$dockerexec-itpostgres16psql-Urootzimple_bankpsql(16.1)Type"help"forhelp.zimple_bank=#SELECTc......
  • 360°全方位体验华为云开放技术能力——华为云开发者日苏州站成功举办
    本文分享自华为云社区《360°全方位体验华为云开放技术能力——华为云开发者日苏州站成功举办》,作者:华为云社区精选。近日,华为云开发者日HDC.Cloud Day苏州站成功举行,开发者不仅聆听了华为云技术专家在IoT与鸿蒙的结合,软件开发工具,大模型等前沿的技术分享,还在KooLabs工作坊、开......
  • 360°全方位体验华为云开放技术能力——华为云开发者日苏州站成功举办
    近日,华为云开发者日HDC.Cloud Day苏州站成功举行,开发者不仅聆听了华为云技术专家带来的IoT与鸿蒙的结合,软件开发工具,大模型等前沿的技术分享,还在KooLabs工作坊、开发者体验区,展台亲身体验华为云产品的技术魅力。华为云开发者日是面向全球开发者的旗舰活动,汇聚来自千行百业、高校、......