Sample Header Ad - 728x90

how to back-up all databases in postgresql iteratively?

-1 votes
1 answer
598 views
I have a large number of databases in postgresql 9.5 which I want to back-up using pg_dump tool. Each back-up should get its name from the corresponding database. when I run
SELECT datname FROM pg_database WHERE datistemplate=false and datname!='postgres';
in a psql terminal, it displays all the database names correctly. My issue now is how to use it in pg_dump inside a loop (if required,in a batch script) such that each database is backed up with its corresponding name. Something like this:
FOR %%d IN ("SELECT datname FROM pg_database WHERE datistemplate=false and datname!='postgres'") DO(
	
	%pgdump% %%d -f %%d.backup
)
when I run this, nothing happens. I am using windows 10 and I'd greatly appreciate any help/hint. Thank you.
Asked by Brian (3 rep)
Jul 29, 2022, 06:26 PM
Last activity: Jul 30, 2022, 09:59 AM