postgresql表的导入导出

导出表定义

pg_dump -s -t tablename test_db > /pgsql/tablename.sql

其中-s表示只导出表定义，-t指定表名，test_db是database名。
如果pg_hba.conf中认证方式不是trust,则需要加入连接选项。连接选项参考如下：

  -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

导出表数据

COPY (SELECT * FROM tablename WHERE xxx ) TO '/usr/tablename.csv' WITH(FORMAT 'csv',ENCODING 'UTF8');

导入表数据

\copy tablename from '/usr/tablename.csv' with(format csv,encoding 'UTF8');