PostgreSQL的pg_dump中 --inserts参数测试

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