PostgreSQL的pg_dump中 --inserts参数测试
1 准备测试数据
创建表yewu1.t1，并插入1000000条数据。
white=# create table yewu1.t1 (id int,name varchar(20));
CREATE TABLE
white=# DO $$
white$# DECLARE aa INTEGER;
white$# BEGIN
white$# FOR aa IN 1..1000000 LOOP
white$# INSERT INTO yewu1.t1 VALUES (aa,'white_' || aa);
white$# END LOOP;
white$# COMMIT;
white$# END $$;
DO
white=#
white=# select count(*) from yewu1.t1;
count
---------
1000000
(1 row)
white=#
white=# SELECT
white-# pg_size_pretty(pg_relation_size('yewu1.t1')) AS base_size,
white-# pg_size_pretty(pg_table_size('yewu1.t1')) AS table_size,
white-# pg_size_pretty(pg_indexes_size('yewu1.t1')) AS indexes_size,
white-# pg_size_pretty(pg_total_relation_size('yewu1.t1')) AS total_size;
base_size | table_size | indexes_size | total_size
-----------+------------+--------------+------------
49 MB | 49 MB | 0 bytes | 49 MB
(1 row)
2 pg_dump 导出测试
2.1 加 --inserts 参数
导出
pg_dump -h 192.168.10.99 -U postgres -p 5777 -W white -t yewu1.t1 --inserts > /home/pg16/tmp_20240819/bak_0821_1.sql
查看导出文件内容
[pg16@test tmp_20240819]$ more bak_0821_1.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 16.2
-- Dumped by pg_dump version 16.2
-- Started on 2024-08-20 18:20:20 PDT
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_table_access_method = heap;
--
-- TOC entry 230 (class 1259 OID 17716)
-- Name: t1; Type: TABLE; Schema: yewu1; Owner: postgres
--
CREATE TABLE yewu1.t1 (
id integer,
name character varying(20)
);
ALTER TABLE yewu1.t1 OWNER TO postgres;
--
-- TOC entry 4118 (class 0 OID 17716)
-- Dependencies: 230
-- Data for Name: t1; Type: TABLE DATA; Schema: yewu1; Owner: postgres
--
INSERT INTO yewu1.t1 VALUES (1, 'white_1');
INSERT INTO yewu1.t1 VALUES (2, 'white_2');
INSERT INTO yewu1.t1 VALUES (3, 'white_3');
INSERT INTO yewu1.t1 VALUES (4, 'white_4');
INSERT INTO yewu1.t1 VALUES (5, 'white_5');
INSERT INTO yewu1.t1 VALUES (6, 'white_6');
INSERT INTO yewu1.t1 VALUES (7, 'white_7');
INSERT INTO yewu1.t1 VALUES (8, 'white_8');
INSERT INTO yewu1.t1 VALUES (9, 'white_9');
INSERT INTO yewu1.t1 VALUES (10, 'white_10');
INSERT INTO yewu1.t1 VALUES (11, 'white_11');
INSERT INTO yewu1.t1 VALUES (12, 'white_12');
INSERT INTO yewu1.t1 VALUES (13, 'white_13');
。。。。。省略
导入
psql -U postgres -W -v -d white -f /home/pg16/tmp_20240819/bak_0821_1.sql
查看输出日志：
[pg16@test tmp_20240819]$ more bak_0821_1.log
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
。。。。。省略
2.2 不加 --inserts 参数
导出
pg_dump -h 192.168.10.99 -U postgres -p 5777 -W white -t yewu1.t1 > /home/pg16/tmp_20240819/bak_0821_2.sql
查看导出文件内容
[pg16@test tmp_20240819]$ more bak_0821_2.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 16.2
-- Dumped by pg_dump version 16.2
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_table_access_method = heap;
--
-- Name: t1; Type: TABLE; Schema: yewu1; Owner: postgres
--
CREATE TABLE yewu1.t1 (
id integer,
name character varying(20)
);
ALTER TABLE yewu1.t1 OWNER TO postgres;
--
-- Data for Name: t1; Type: TABLE DATA; Schema: yewu1; Owner: postgres
--
COPY yewu1.t1 (id, name) FROM stdin;
1 white_1
2 white_2
3 white_3
4 white_4
5 white_5
6 white_6
7 white_7
8 white_8
9 white_9
10 white_10
11 white_11
12 white_12
13 white_13
14 white_14
15 white_15
。。。。省略
导入
psql -U postgres -W -d white -f /home/pg16/tmp_20240819/bak_0821_2.sql
查看输出日志
[pg16@test tmp_20240819]$ more bak_0821_2.log
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 1000000