使用pg_dump命令将表中的记录导出为insert into语句

 

导出命令如下：
 

pg_dump -h 192.168.80.1 -d postgres -U postgres  -W -p 5901 -t pg_database --inserts -a --column-inserts  --encoding=UTF8 -f d:\test1.sql

生成的sql文件内容如下：
下面的sql文件中没有建表语句，是因为上面带了-a参数。去掉-a参数之后，sql文件会带有建表语句和insert into语句。

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.5  --->>>pg_dump工具连接到的数据库的版本号
-- Dumped by pg_dump version 11.5     --->>>pg_dump工具的版本号

SET 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;

--
-- Data for Name: pg_database; Type: TABLE DATA; Schema: pg_catalog; Owner: postgres
--

INSERT INTO pg_catalog.pg_database (datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl) VALUES ('postgres', 10, 6, 'Chinese (Simplified)_China.936', 'Chinese (Simplified)_China.936', false, true, -1, 13011, '562', '1', 1663, NULL);
INSERT INTO pg_catalog.pg_database (datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl) VALUES ('template1', 10, 6, 'Chinese (Simplified)_China.936', 'Chinese (Simplified)_China.936', true, true, -1, 13011, '562', '1', 1663, '{=c/postgres,postgres=CTc/postgres}');
INSERT INTO pg_catalog.pg_database (datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl) VALUES ('template0', 10, 6, 'Chinese (Simplified)_China.936', 'Chinese (Simplified)_China.936', true, false, -1, 13011, '562', '1', 1663, '{=c/postgres,postgres=CTc/postgres}');


--
-- PostgreSQL database dump complete
--

如下语句将 建表语句、建constraint语句、insert into语句、建立索引依次进行输出：

pg_dump -h 192.168.80.1 -d postgres -U postgres  -W -p 5901 -t t_lei --inserts   --encoding=UTF8 -Fp -f d:\lei_2.sql

输出的内容如下：
 

--
-- PostgreSQL database dump
--

-- Dumped from database version 11.5
-- Dumped by pg_dump version 11.5

SET 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_with_oids = false;

--
-- Name: t_lei; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t_lei (
    c1 integer,
    CONSTRAINT c1_chk CHECK ((c1 <> 0))
);


ALTER TABLE public.t_lei OWNER TO postgres;

--
-- Data for Name: t_lei; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.t_lei VALUES (1);
INSERT INTO public.t_lei VALUES (2);
INSERT INTO public.t_lei VALUES (3);
INSERT INTO public.t_lei VALUES (4);
INSERT INTO public.t_lei VALUES (5);


--
-- Name: idx_t_lei_1; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX idx_t_lei_1 ON public.t_lei USING btree (c1);


--
-- PostgreSQL database dump complete
--