Postgres数据库，导入导出时遇到的坑

最新推荐文章于 2025-02-28 09:30:19 发布

金金金金金大叔

最新推荐文章于 2025-02-28 09:30:19 发布

阅读量7.4k

点赞数 8

文章标签： postgresql

本文链接：https://blog.csdn.net/weixin_45244610/article/details/112680603

版权

本文档介绍了在Navicat无法连接远程数据库时，如何通过命令行导出和导入PostgreSQL数据库数据。问题在于命令行导出的数据使用COPY语句，而Navicat不支持此格式。解决方法是使用pg_dump命令指定--inserts选项，导出INSERT语句格式的数据，然后使用psql命令导入。此外，分享了如何高效地导出全库、特定数据库和表格的命令。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言
为了方便本地测试，我想要将服务端的数据拉到本地，当navicat无法直接连接远端数据库时，我们只能通过命令行操作。

导出数据
通过搜索，获取到知识点：

导出指定表

\pg_dump -h your_host -U username databasename  -t tablename > ./dum.sql

数据成功导出！

使用Navicat导入数据，失败，报错：

[SQL] Query cqh1 start
[ERR] 错误:  语法错误 在 "165513" 或附近的
LINE 80: 165513 1 3560.000000000000000000 3560.000000000000000000 356...
         ^

而出现错误的地方，正式数据的第一条。然后，我仔细对比了命令行导出文件和使用navicat 导出.sql 文件之间的区别，发现了问题所在：

命令行导出sql文件	navicat导出的sql文件
`COPY public.contract_quotehour_1 (id, coin_code, low, high, open, close, last_price, avg_price, volume, "timestamp", rise_fall_rate, rise_fall_value, updated_at, created_at) FROM stdin; 165513 1 3560.000000000000000000 3560.000000000000000000 .......`	INSERT INTO “public”.“contract_servers” (“contract_id”,“approval_se”,“matching_se”,“scan_server”,“liquidate_s”,“adl_server_”,“settlement_”,“datacenter_”,“index_serve”,“plan_order_”,“clear_group”) VALUES (1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1),…;COMMIT;

命令行导出来的文件，数据部分使用了COPY来写数据（难道这是Postgres默认的模式？）；而在一般使用的，用的是INSERT的方式，插入数据。

这个问题怎么解决呢？

解决办法
使用命令行方式导入。
本机是win10，使用Powershell，用命令行方式执行。

跳转到bin目录
cd 'C:\Program Files\PostgreSQL\10\bin\'
导入
psql -h localhost -U postgres -d contract -f C:\work\dum.sql

数据成功导入！

但是这样的导入方式显然是很麻烦的，那有没有更简单的方法呢？有！

导出数据时，可以指定数据导入的方式！

pg_dump -h your_host -U username -O -x -c --inserts --if-exists --quote-all-identifiers -f 路径/db.sql -t tablename databasename

这时候，导出的文件就是：

INSERT INTO "public"."contract_quotehour_1" VALUES (165513, '1', 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 0.000000000000000000, 1545975001, 0.000000000000000000, 0.000000000000000000, '2018-12-28 05:15:02.547135', '2018-12-20 02:43:00.663257');
INSERT INTO "public"."contract_quotehour_1" VALUES (165549, '1', 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 3560.000000000000000000, 0.000000000000000000, 1546039801, 0.000000000000000000, 0.000000000000000000, '2018-12-28 11:40:02.105429', '2018-12-20 02:43:00.663257');
......

就是一条一条插入的语句了！！！！

经验教训：
一定要看文档，就算是help指令的文档！！

附上help指令

[test@gateway ~]$ pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -c, --clean                  clean (drop) database objects before recreating
  -C, --create                 include commands to create database in dump
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=SCHEMA          dump the named schema(s) only
  -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)
  -o, --oids                   include OIDs in dump
  -O, --no-owner               skip restoration of object ownership in
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=TABLE            dump the named table(s) only
  -T, --exclude-table=TABLE    do NOT dump the named table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=TABLE   do NOT dump data for the named table(s)
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY	//就是这个！！！！
  --no-security-labels         do not dump security label assignments
  --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
  --no-tablespaces             do not dump tablespace assignments
  --no-unlogged-table-data     do not dump unlogged table data
  --quote-all-identifiers      quote all identifiers, even if not key words
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@postgresql.org>.

英文不好，就不献丑翻译了。
这里附上一些其他的指令：

导出全库
su postgres -c 'pg_dumpall -O -x -c --inserts --if-exists --quote-all-identifiers -f db.sql
导出指定库
pg_dump -h your_host -U username -O -x -c --inserts --if-exists --quote-all-identifiers -f 路径/db.sql  databasename
导出指定表
pg_dump -h your_host -U username -O -x -c --inserts --if-exists --quote-all-identifiers -f 路径/db.sql -t tablename databasename