血的教训,千万不要用第三方工具!千万不要用第三方工具!千万不要用第三方工具!
就用sql语句导出导入,工具各种坑多
说明:
--clean 在重新创建之前,先清除(删除)数据库对象
--create 在转储中包括命令,以便创建数据库
--inserts 以INSERT命令,而不是COPY命令的形式转储数据
--no-owner 在明文格式中, 忽略恢复对象所属者
--data-only 只转储数据,不包括模式
--schema-only 只转储模式, 不包括数据
--table=TABLE 只转储指定名称的表
--exclude-table=TABLE 排除指定名称的表
1.只导出库,表结构
/opt/pgsql/bin/pg_dump -h localhost -U postgres --dbname="test" --file="test.sql" --schema-only --clean --create --if-exists --no-owner
2.只导出表数据
/opt/pgsql/bin/pg_dump -h localhost -U postgres --dbname="test" --file="test.sql" --data-only --clean --create --if-exists --no-owner
3.导出表结构和表数据
建议此方式,因为使用微服务,postgres数据库在有连接时不能断开重建
/opt/pgsql/bin/pg_dump -h localhost -U postgres --dbname="test" --file="test.sql" --inserts ---create --no-owner
导出表结构和表数据库后:
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
4.恢复库,表结构
/opt/pgsql/bin/psql -h localhost -U postgres < test.sql
# 指定数据库test
/opt/pgsql/bin/psql -h localhost -U postgres -d test < test.sql
使用\copy导入csv文件(可用于导入大批量的数据)
- 创建预置数据表结构
create table xxxx();
- 导入数据(以csv为例),也支持sql的导入
\COPY postgres_log FROM '/home/pglog/postgresql-log.Tue.csv' with CSV;
当有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