PostgreSQL COPY导出备份bat脚本时输出多余双引号如何解决

问题背景 #

需要动态生成bat格式的PostgreSQL全量备份脚本，通过Windows任务计划配置每日定时执行，调用pg_dump完成所有数据库备份。
原有实现生成的备份命令未给备份路径包裹双引号，路径包含空格、特殊字符时会执行失败；直接在SQL拼接逻辑中添加双引号时，会因COPY命令的CSV格式默认转义规则输出大量冗余双引号，无法得到预期命令格式。
预期正确的备份命令格式如下：

pg_dump postgres > "e:\postgresbackups\FULL\postgres_%date:~4,2%-%date:~7,2%-%date:~10,4%_%time:~0,2%_%time:~3,2%_%time:~6,2%.dump"

原有问题代码 #

COPY (SELECT 'pg_dump '|| datname || ' > e:\postgresbackups\FULL\' || datname || '_%date:~4,2%-%date:~7,2%-%date:~10,4%_%time:~0,2%_%time:~3,2%_%time:~6,2%.dump' FROM pg_database) TO 'E:\PostgresBackups\Script\FULL_Postgres_Backup_Job_TEST.bat' (format csv, delimiter ';');

可行修改方案 #

核心修改逻辑 #

多余双引号的根源是COPY ... TO使用CSV格式输出时，默认会对包含特殊字符的字段自动包裹外层双引号，同时对字段内的双引号做双写转义。生成逐行执行的BAT脚本不需要CSV格式，直接切换为TEXT格式输出即可完全规避转义问题，直接在拼接的命令字符串中加入需要的双引号即可。
修改后的SQL代码：

COPY (
    SELECT 
        'pg_dump ' || datname || ' > "e:\postgresbackups\FULL\' || datname || '_%date:~4,2%-%date:~7,2%-%date:~10,4%_%time:~0,2%_%time:~3,2%_%time:~6,2%.dump"' 
    FROM pg_database
) TO 'E:\PostgresBackups\Script\FULL_Postgres_Backup_Job_TEST.bat' (FORMAT text, ENCODING 'UTF8');

兼容性优化 #

Windows默认时间变量在凌晨0-9点时段，%time:~0,2%会返回带前置空格的小时值（例如 8），会导致生成的文件名包含空格，可将小时部分的变量替换为%time:~0,2: =0%，自动将空格替换为0，文件名更规范。优化后代码：

COPY (
    SELECT 
        'pg_dump ' || datname || ' > "e:\postgresbackups\FULL\' || datname || '_%date:~4,2%-%date:~7,2%-%date:~10,4%_%time:~0,2: =0%_%time:~3,2%_%time:~6,2%.dump"' 
    FROM pg_database
) TO 'E:\PostgresBackups\Script\FULL_Postgres_Backup_Job_TEST.bat' (FORMAT text, ENCODING 'UTF8');

注意事项 #

• 脚本生成后建议手动打开校验内容，确认双引号成对、路径配置正确
• 若任务计划执行时提示找不到pg_dump，请将命令中的pg_dump替换为pg_dump.exe的绝对路径，例如"C:\Program Files\PostgreSQL\16\bin\pg_dump.exe"
• 请确保运行任务计划的Windows账号对备份存储目录有写入权限

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