如何在bash脚本中通过psql连接AWS PostgreSQL导出CSV数据

原脚本的核心错误 #

• 命令换行语法错误：bash中单条命令的参数换行时，必须在行尾加反斜杠\，否则会被识别为独立命令执行
• psql参数误用：-p是指定数据库端口的参数，不是传密码的参数，直接在脚本里明文传密码本身也有安全风险
• COPY命令使用错误：普通COPY是服务端执行的命令，会尝试将结果写入AWS PostgreSQL服务器的指定路径，根本不可能访问你本地电脑的下载目录，需要改用psql客户端侧的\copy命令
• SQL路径错误：指定的导出路径是缺少根路径前缀/的相对路径，且只写了目录没有指定最终CSV文件名，执行会报错
• SQL和psql命令无关联：你直接把SQL语句写在psql命令之后，二者没有绑定关系，psql不会识别后面的内容为要执行的SQL
• 不需要加sudo/su：连接远程AWS PostgreSQL不需要切换本地系统用户，--U的参数写法也是错误的，单字母参数统一用单横杠-U

正确可执行脚本示例 #

#!/bin/bash

# 配置数据库连接信息，替换为你自己的实际值
PG_HOST="你的AWS数据库域名"
PG_PORT="5432" # 默认端口，你改了的话就替换
PG_USER="你的用户名"
PG_PWD="你的密码"
PG_DB="你的数据库名"
# 导出路径，要写绝对路径加CSV文件名
EXPORT_PATH="/Users/name/Downloads/query_result.csv"

# 执行导出
PGPASSWORD=$PG_PWD psql -h $PG_HOST -p $PG_PORT -U $PG_USER -d $PG_DB << EOF
\copy (SELECT data FROM table LIMIT 100) TO '$EXPORT_PATH' WITH CSV HEADER;
EOF

# 清除环境变量里的密码，避免泄露
unset PGPASSWORD

echo "导出完成，文件路径：$EXPORT_PATH"

补充说明 #

• 如果要更高的安全性，不要把密码写在脚本里，可以在你本地用户目录下创建.pgpass文件，写入内容：你的AWS数据库域名:5432:你的数据库名:你的用户名:你的密码，然后执行chmod 600 ~/.pgpass设置权限，之后脚本里就可以删掉PGPASSWORD=$PG_PWD和密码相关的配置，执行时会自动读取验证
• 如果需要导出CSV带表头，就保留上面命令里的HEADER参数，不需要的话删掉即可

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