qldump 备份所有表_PostgreSQL学习笔记（9）之逻辑备份还原

PostgreSQL对于数据备份，提供了物理备份和逻辑备份2种解决方案

先看逻辑备份

pg_dump(备份) 

把PostgreSQL数据库抽取为一个脚本文件或其他归档文件

常用参数

-F c 

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

-F p 

备份为文本,大库不推荐

pg_dump恢复(还原)

方法1：

psql dbname -U username < bakfile

方法2：

pg_restore

– pg_restore [option] ... [filename]

– pg_restore -d dbname bakfile 

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

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

演示

先准备数据

create database db1;\c db1CREATE TABLE TAB_TABLEDUMP (ID INT );INSERT INTO TAB_TABLEDUMP VALUES (1);INSERT INTO TAB_TABLEDUMP VALUES (2);INSERT INTO TAB_TABLEDUMP VALUES (3);

查看数据

备份数据为sql文件

#把一个数据库db1转储到一个SQL脚本文件， #简单语法,可结合选项灵活备份pg_dump db1  -f db1.sql

查看备份的脚本文件

---- PostgreSQL database dump---- Dumped from database version 13.0-- Dumped by pg_dump version 13.0SET statement_timeout = 0;SET lock_timeout = 0;SET idle_in_transaction_session_timeout = 0;SET client_encoding = 'UTF8';SET standard_conforming_strings = on;SELECT pg_catalog.set_config('search_path', '', false);SET check_function_bodies = false;SET xmloption = content;SET client_min_messages = warning;SET row_security = off;SET default_tablespace = '';SET default_table_access_method = heap;---- Name: tab_tabledump; Type: TABLE; Schema: public; Owner: postgres--CREATE TABLE public.tab_tabledump (id integer);ALTER TABLE public.tab_tabledump OWNER TO postgres;---- Data for Name: tab_tabledump; Type: TABLE DATA; Schema: public; Owner: postgres--COPY public.tab_tabledump (id) FROM stdin;123\.---- PostgreSQL database dump complete--                     

还原数据

将备份的脚本数据还原到数据库db2

create database db2;psql -Upostgres -d db2 -f db1.sql

其他用法

备份数据为二进制文件pg_dump -Fc db1  -f db1.dump还原数据pg_restore -d db3  db1.dump要转储一个数据库到一个目录格式的归档：$ pg_dump -Fd mydb -f dumpdir要用 5 个并行的工作者任务转储一个数据库到一个目录格式的归档：$ pg_dump -Fd mydb -j 5 -f dumpdir把一个归档文件重新装载到同一个数据库(该归档正是从这个数据库中转储得来)中，丢掉那个数据库中的当前内容：$ pg_restore -d postgres --clean --create db.dump要转储一个名为mytab的表：$ pg_dump -t mytab mydb > db.sql要转储detroit模式中名称以emp开始的所有表，排除名为employee_log的表：$ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql要转储名称以east或者west开始并且以gsm结束的所有模式，排除名称包含词test的任何模式：$ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql同样，用正则表达式记号法来合并开关：$ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql要转储除了名称以ts_开头的表之外的所有数据库对象：1752pg_dump$ pg_dump -T 'ts_*' mydb > db.sql要在-t和相关开关中指定一个大写形式或混合大小写形式的名称，你需要双引用该名称，否则它会被折叠到小写形式(见模式(Pattern))。但是双引号对于 shell 是特殊的，所以反过来它们必须被引用。因此，要转储一个有混合大小写名称的表，你需要类似这样的东西：$ pg_dump -t "\"MixedCaseName\"" mydb > mytab.sql

生产环境通常会采取自动定时备份的方式去进行数据备份