导出表定义
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');