英文:

Can't call pg_dump for all databases in PSQL

问题

请帮我解决以下问题：
语法错误 EXECUTE 'pg_dump...'

选择 datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');
CREATE OR REPLACE FUNCTION dump_databases() RETURNS void AS $$
DECLARE
save_path text := 'C:/Users/79209/Desktop/';
db_name text;
dbs CURSOR FOR SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');
timestamp text := to_char(now(), 'YYYY-MM-DD');
BEGIN
OPEN dbs;
LOOP
FETCH dbs INTO db_name;
EXIT WHEN NOT FOUND;
EXECUTE 'pg_dump -U postgres -Fc -d ' || db_name || ' > ' || save_path || db_name || '_' || timestamp || '.dump';
END LOOP;
CLOSE dbs;
END $$ LANGUAGE plpgsql;
SELECT dump_databases();

尝试了以下选项 - 都没有起作用...

EXECUTE 'C:/Program Files/PostgreSQL/15/bin/pg_dump -U postgres -Fc -d ' || "db_name" || ' > ' || save_path || "db_name" || '_' || timestamp || '.dump';

EXECUTE 'pg_dump -U postgres -Fc -d ' || quote_ident(db_name) || ' > ' || quote_literal(save_path || db_name || '_' || timestamp || '.dump');

EXECUTE format('pg_dump -U postgres -Fc -d ' || quote_ident(db_name) || ' > ' || quote_literal(save_path || db_name || '_' || timestamp || '.dump'));

<details>
<summary>英文:</summary>

please help me solve the following problem:
Syntax error EXECUTE &#39;pg_dump...&#39;

SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');
CREATE OR REPLACE FUNCTION dump_databases() RETURNS void AS $$
DECLARE
save_path text := 'C:/Users/79209/Desktop/';
db_name text;
dbs CURSOR FOR SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1');
timestamp text := to_char(now(), 'YYYY-MM-DD');
BEGIN
OPEN dbs;
LOOP
FETCH dbs INTO db_name;
EXIT WHEN NOT FOUND;
EXECUTE 'pg_dump -U postgres -Fc -d ' || db_name || ' > ' || save_path || db_name || '_' || timestamp || '.dump';
END LOOP;
CLOSE dbs;
END $$ LANGUAGE plpgsql;
SELECT dump_databases();

Tried the following options - nothing worked...

EXECUTE 'C:/Program Files/PostgreSQL/15/bin/pg_dump -U postgres -Fc -d ' || "db_name" || ' > ' || save_path || "db_name" || '_' || timestamp || '.dump';

EXECUTE 'pg_dump -U postgres -Fc -d ' || quote_ident(db_name) || ' > ' || quote_literal(save_path || db_name || '_' || timestamp || '.dump');

EXECUTE format('pg_dump -U postgres -Fc -d ' || quote_ident(db_name) || ' > ' || quote_literal(save_path || db_name || '_' || timestamp || '.dump'));

</details>


# 答案1
**得分**: 0

如果你分开执行这些行，它会正常工作，但运行 cmd 文件时不起作用，进入循环时关闭窗口。

<details>
<summary>英文:</summary>

If you execute the lines separately it works, launching the cmd file does not work, it closes the window when entering the loop.

chcp 65001
PAUSE
cd C:\Program Files\PostgreSQL\15\bin
PAUSE
FOR /F "usebackq" %i IN (psql -U postgres -tAc "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')") DO pg_dump -U postgres -Fc %i > "C:\Users\79209\Desktop%i-%DATE%.dump"
PAUSE

</details>