Postgresql - Backup and Restore

备份
一般来说备份分为两种,备份表,或者备份库

备份表,采用sql模式。
pg_dump -U postgres -d postgres -t table_name -f /data/backup/table_name.sql
如果单表太大,我们采用-Fc的模式,
pg_dump -U postgres -d postgres -Fc -t table_name -f /data/backup/table_name.dmp

看一下导出的sql文件
**********************************************************************************************
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.4
-- Dumped by pg_dump version 10.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET row_security = off;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test01; Type: TABLE; Schema: public; Owner: mytest
--

CREATE TABLE public.test01 (
id integer DEFAULT nextval('public.seq_test01_id'::regclass) NOT NULL,
col1 character varying(10)
);


ALTER TABLE public.test01 OWNER TO mytest;

--
-- Data for Name: test01; Type: TABLE DATA; Schema: public; Owner: mytest
--

COPY public.test01 (id, col1) FROM stdin;
1 aaa
2 bbb
\.


--
-- PostgreSQL database dump complete
--
**********************************************************************************************
## 说明:
## 在建表语句中存在序列,但是导出文件中没有创建序列的语句,说明需要自己创建相关序列


# 备份数据库
pg_dump -U postgres -d mytest -f /data/mytest.sql
pg_dump -U postgres -d mytest -f /data/mytest.dmp -Fc

# 看一下备份数据库的sql文件(通过-Fc压缩过的导出dmp文件无法打开)。
**********************************************************************************************
--
-- PostgreSQL database dump
--

-- Dumped from database version 10.4
-- Dumped by pg_dump version 10.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: seq_mytest_id; Type: SEQUENCE; Schema: public; Owner: mytest
--

CREATE SEQUENCE public.seq_mytest_id
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;


ALTER TABLE public.seq_mytest_id OWNER TO mytest;

--
-- Name: seq_test01_id; Type: SEQUENCE; Schema: public; Owner: mytest
--

CREATE SEQUENCE public.seq_test01_id
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;


ALTER TABLE public.seq_test01_id OWNER TO mytest;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: test01; Type: TABLE; Schema: public; Owner: mytest
--

CREATE TABLE public.test01 (
id integer DEFAULT nextval('public.seq_test01_id'::regclass) NOT NULL,
col1 character varying(10)
);


ALTER TABLE public.test01 OWNER TO mytest;

--
-- Data for Name: test01; Type: TABLE DATA; Schema: public; Owner: mytest
--

COPY public.test01 (id, col1) FROM stdin;
1 aaa
2 bbb
\.


--
-- Name: seq_mytest_id; Type: SEQUENCE SET; Schema: public; Owner: mytest
--

SELECT pg_catalog.setval('public.seq_mytest_id', 1, false);


--
-- Name: seq_test01_id; Type: SEQUENCE SET; Schema: public; Owner: mytest
--

SELECT pg_catalog.setval('public.seq_test01_id', 2, true);


--
-- PostgreSQL database dump complete
--
**********************************************************************************************
# 注意:
# 备份数据库中没有说明数据库名称,所以在恢复的时候需要首先创建数据库,并在恢复命令中指明恢复到哪个数据库

恢复
# 如果备份的时候没有加-Fc参数,备份成sql文件的时候,恢复就要使用psql去执行。

阅读更多

没有更多推荐了,返回首页