postgres备份/恢复，导出/导入（包含\copy，pg_blukload批量导入）_postgres用psql方式只导出表数据并还原表数据

血的教训，千万不要用第三方工具！千万不要用第三方工具！千万不要用第三方工具！
就用sql语句导出导入，工具各种坑多

说明：

--clean                      在重新创建之前，先清除（删除）数据库对象
--create                     在转储中包括命令,以便创建数据库
--inserts                    以INSERT命令，而不是COPY命令的形式转储数据
--no-owner                   在明文格式中, 忽略恢复对象所属者
--data-only                  只转储数据,不包括模式
--schema-only                只转储模式, 不包括数据
--table=TABLE                只转储指定名称的表
--exclude-table=TABLE        排除指定名称的表
1
2
3
4
5
6
7
8

1.只导出库，表结构

 /opt/pgsql/bin/pg_dump -h localhost -U postgres --dbname="test" --file="test.sql" --schema-only --clean --create --if-exists --no-owner
1

2.只导出表数据

 /opt/pgsql/bin/pg_dump -h localhost -U postgres --dbname="test" --file="test.sql" --data-only --clean --create --if-exists --no-owner
1

3.导出表结构和表数据

建议此方式，因为使用微服务，postgres数据库在有连接时不能断开重建

 /opt/pgsql/bin/pg_dump -h localhost -U postgres --dbname="test" --file="test.sql" --inserts ---create  --no-owner
1

导出表结构和表数据库后：

sed -i "1iSELECT pg_terminate_backend(pid) \
FROM pg_stat_activity \
WHERE datname = 'testdb'  \
  AND pid <> pg_backend_pid(); \
drop database if exists testdb; \
CREATE DATABASE testdbWITH TEMPLATE = template0 ENCODING = 'UTF8';;; \
ALTER DATABASE testdbOWNER TO postgres;;; \
COMMENT ON DATABASE alarm IS '告警服务数据库';;;"  \
./dump.testdb.sql
###有5个斜杠，也不知为啥，多试几个吧
sed -i "2i\\\\\connect testdb;;;" ./dump.testdb.sql
1
2
3
4
5
6
7
8
9
10
11

4.恢复库，表结构

/opt/pgsql/bin/psql -h localhost -U postgres < test.sql
# 指定数据库test
/opt/pgsql/bin/psql -h localhost -U postgres -d test < test.sql

1
2
3
4

---

使用\copy导入csv文件（可用于导入大批量的数据）

• 创建预置数据表结构

create table xxxx();
1

• 导入数据（以csv为例），也支持sql的导入

\COPY postgres_log FROM '/home/pglog/postgresql-log.Tue.csv' with CSV;
1

当有NULL字段值时：
从Postgres 9.4开始，您现在可以使用FORCE NULL。这会导致将空字符串转换为NULL。非常方便，尤其是CSV文件。
语法如下：COPY table FROM stdin WITH DELIMITER’;’ CSV FORCE NULL integerfieldname;

使用pg_blukload批量导入（无wal日志，更快）

补充

pg_bulkload安装

[root@Postgres201 ~]# unzip pg_bulkload-VERSION3_1_10.zip 
[root@Postgres201 ~]# cd pg_bulkload-VERSION3_1_10
[root@Postgres201 pg_bulkload-VERSION3_1_10]# make
[root@Postgres201 pg_bulkload-VERSION3_1_10]# make install

安装完成；要使用它需要建extension

[postgres@Postgres201 ~]$ psql lottu lottu
psql (9.6.0)
Type "help" for help.
lottu=# create extension pg_bulkload;
CREATE EXTENSION

4. pg_bulkload参数

[postgres@Postgres201 ~]$ pg_bulkload --help
pg_bulkload is a bulk data loading tool for PostgreSQL
Usage:
  Dataload: pg_bulkload [dataload options] control_file_path
  Recovery: pg_bulkload -r [-D DATADIR]
Dataload options:
  -i, --input=INPUT         INPUT path or function
  -O, --output=OUTPUT       OUTPUT path or table
  -l, --logfile=LOGFILE     LOGFILE path
  -P, --parse-badfile=*     PARSE_BADFILE path
  -u, --duplicate-badfile=* DUPLICATE_BADFILE path
  -o, --option="key=val"    additional option
Recovery options:
  -r, --recovery            execute recovery
  -D, --pgdata=DATADIR      database directory
Connection options:
  -d, --dbname=DBNAME       database to connect
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
Generic options:
  -e, --echo                echo queries
  -E, --elevel=LEVEL        set output message level
  --help                    show this help, then exit
  --version                 output version information, then exit

5. pg_bulkload的使用

　　创建测试表tbl_lottu和测试文件tbl_lottu_output.txt

[postgres@Postgres201 ~]$ psql lottu lottu
psql (9.6.0)
Type "help" for help.
lottu=# create table tbl_lottu(id int,name text);
CREATE TABLE
[postgres@Postgres201 ~]$  seq 100000| awk '{print $0"|lottu"}' > tbl_lottu_output.txt

1.不使用控制文件使用参数

[postgres@Postgres201 ~]$ pg_bulkload -i /home/postgres/tbl_lottu_output.txt -O tbl_lottu -l /home/postgres/tbl_lottu_output.log -P /home/postgres/tbl_lottu_bad.txt  -o "TYPE=CSV" -o "DELIMITER=|" -d lottu -U lottu
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59