how to back-up all databases in postgresql iteratively?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is more a Windows question, than a Postgres question. But the following works for me:

psql -U postgres -c "\copy (SELECT datname FROM pg_database WHERE NOT datistemplate and datname not in ('postgres')) to dblist.txt"

for /f "tokens=1 delims= " %%i in (dblist.txt) do (
  pg_dump -U postgres -Fc --file=%%i.pgdump %%i
)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply