Ubuntu环境下PostgreSQL大数据库本地导出至CSV方案咨询

直接在Ubuntu 14.04服务器本地操作完全能避开网络传输的瓶颈，对于1TB规模的数据库来说，这绝对是效率最高的方案。下面给你几种实用的本地导出CSV的方法，以及一些优化建议：

一、用PostgreSQL原生COPY命令（最快最推荐） #

COPY是PostgreSQL专门为批量数据导入导出设计的工具，本地执行时速度远超网络传输的方式，而且语法简单。

步骤： #

1. 首先切换到postgres用户（PostgreSQL进程默认用这个用户，权限足够避免文件写入问题）：

   sudo su - postgres
   

2. 进入目标数据库的psql命令行：

   psql -d 你的数据库名称
   

3. 导出单张表到CSV：

   COPY 你的表名 TO '/path/to/save/file.csv' WITH (FORMAT csv, HEADER, DELIMITER ',', ENCODING 'UTF8');
   

   • HEADER：导出文件包含表头（字段名），方便后续查看
   • DELIMITER ','：指定逗号为分隔符，可根据需求修改
   • ENCODING 'UTF8'：统一编码避免乱码问题

批量导出所有表 #

如果要导出数据库里的所有用户表，可以写个简单的shell脚本批量处理：

#!/bin/bash
# 替换成你的数据库名和导出目录
DB_NAME="your_database"
OUTPUT_DIR="/var/postgres_csv_export"

# 创建导出目录并设置权限（确保postgres用户能写入）
mkdir -p $OUTPUT_DIR
chown postgres:postgres $OUTPUT_DIR

# 获取public schema下的所有用户表名（如果你的表在其他schema，修改WHERE条件即可）
TABLES=$(psql -d $DB_NAME -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';")

# 循环导出每张表
for TABLE in $TABLES; do
  echo "正在导出表: $TABLE"
  psql -d $DB_NAME -c "COPY $TABLE TO '$OUTPUT_DIR/$TABLE.csv' WITH (FORMAT csv, HEADER, DELIMITER ',', ENCODING 'UTF8');"
done

保存为export_all_tables.sh，然后赋予执行权限并运行：

chmod +x export_all_tables.sh
./export_all_tables.sh

二、针对超大表的优化建议 #

如果你的数据库里有几十GB甚至上百GB的超大表，一次性导出可能会占用过多内存或IO资源，可以分批次导出：

-- 按id分段导出大表，比如每100万条数据一个文件
COPY (SELECT * FROM 超大表 WHERE id BETWEEN 1 AND 1000000) TO '/path/to/large_table_part1.csv' WITH (FORMAT csv, HEADER);
COPY (SELECT * FROM 超大表 WHERE id BETWEEN 1000001 AND 2000000) TO '/path/to/large_table_part2.csv' WITH (FORMAT csv, HEADER);
-- 以此类推，直到导出所有数据

三、其他注意事项 #

• 存储选择：尽量把导出文件存到SSD磁盘上，机械硬盘的IO速度会成为导出瓶颈
• 权限问题：确保导出目录的权限足够，postgres用户必须有读写权限，否则会报错
• 临时优化：导出期间可以暂时关闭autovacuum（导出完成后记得重新开启），避免后台进程占用资源：

  ALTER SYSTEM SET autovacuum = off;
  SELECT pg_reload_conf();
  -- 导出完成后恢复
  ALTER SYSTEM SET autovacuum = on;
  SELECT pg_reload_conf();
  

内容的提问来源于stack exchange，提问作者BallerNacken