Postgresql Backup all databases seperate files I need to create an automatic backup of all databases from PostgreSQL ( running on a Windows machine ).
I need to know if is possible to get backup of all databases but on separate files?
Using pg_dumpall creates a script that has all databases on it. But i need each database on a separate file
You can combine pg_dump with a batch script that iterates over all databases:
I use something like this to dump my development databases:
@echo off
set PGBINDIR=c:\Program Files\PostgreSQL\13\bin

rem avoid the password and user prompt from pg_dump
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=********
set PGPORT=5432

rem create a text file listing all databases
rem adjust here if you want to exclude a database
&quot;%PGBINDIR%\psql&quot; -X -c &quot;select datname from pg_database where not datistemplate&quot; -A -t -o dblist.txt -d template1

rem dump all user accounts and roles
&quot;%PGBINDIR%\pg_dumpall&quot; --globals-only --file=postgres_globals.sql 

for /f %%i in (dblist.txt) do (
  echo Backing up: %%i
  &quot;%PGBINDIR%\pg_dump&quot; --create --format=c --file=%%i.pgdmp %%i
)