Postgresql Backup all databases separate files

Question

I need to create an automatic backup of all databases from PostgreSQL (running on a Windows machine).
I need to know if it is possible to get a backup of all databases but in separate files.
Using pg_dumpall creates a script that has all databases in it. But I need each database in a separate file.

Answer 1

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
"%PGBINDIR%\psql" -X -c "select datname from pg_database where not datistemplate" -A -t -o dblist.txt -d template1

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

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

Answer 2

I converted @a_horse_with_no_name's answer above to pure bash, which may be useful for some.

I'm now using the below script to back up remote postgresql databases using rsnapshot, following the similar method here for msyql databases.

Here's the script:

backupdir=/my/backupdir

# backup globals
sudo -u postgres pg_dumpall --globals-only | gzip > $backupdir/postgres_globals.sql.gz

# backup individual databases
for db in `sudo -u postgres psql -t -c "select datname from pg_database where not datistemplate" | grep '\S' | awk '{$1=$1};1'`; do
   sudo -u postgres pg_dump $db | gzip > $backupdir/$db.sql.gz
done

Answer 3

To backup a separate database, use the pg_dump utility

SET PGPASSWORD = my_password
pg_dump -U postgres database_name > database.sql

to restore a PostgreSQL database

SET PGPASSWORD = my_password
psql -U postgres database_name < database.sql

pg_dump is a utility for performing logical database backups, it is installed along with the PostgreSQL server and is good for backing up small databases. More information about using this utility on Windows can be found here and here

An alternative approach to perform a backup is copying the database files, this may be the best solution for large databases, but this is a more complicated way - you can see more details here