Backup script for PostgreSQL with zip support (Windows)

Simplest solution for backing up PostgreSQL databases. With this bat file you can dump one database to a file and compress it using 7-zip.

PS. If you want a date in your filename simply add %date% var to it’s string.
PS2. Of course check your paths before complaining it does not work 😛

@echo off
:: Global config
SET PG_BIN="C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe"
SET PG_HOST=localhost
SET PG_PATH="C:\inetpub\temporary_backups"
SET ZIP_BIN="C:\Program Files\7-Zip\7z.exe"

:: Database config for 
SET PG_DATABASE=your_database_name
SET PG_USER=your_database_user
SET PGPASSWORD=youe_database_password


@echo Backup Taken Complete %PG_PATH%%PG_FILENAME%

How to list all PostgreSQL databases and their sizes in MB

You must provide proper database name and user:

File: gistfile1.txt

psql -c "SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER BY pg_database_size DESC;" -d