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.

  • By "databases", do you mean "schemas"?
    – mustaccio
    Nov 18, 2020 at 15:22
  • I need to create a backup of all databases that are in Postgres but on seperate files. In case i have problems with one database I only restore one and not execute all the script
    – Anisa Ibro
    Nov 19, 2020 at 9:33

3 Answers 3


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
  • That was very usefull. Thank you a_horse_with_no_name. Great job!
    – Anisa Ibro
    Dec 13, 2020 at 11:18

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:


# 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
  • Works well, thanks. I used pg_dump --create for easier disaster recovery. 2 hours ago

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.