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_PORT=5432
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

SET PG_FILENAME=%PG_PATH%\%PG_DATABASE%_postgres.sql
%PG_BIN% -h %PG_HOST% -p %PG_PORT% -U %PG_USER% %PG_DATABASE% > "%PG_FILENAME%"
%ZIP_BIN% a "%PG_FILENAME:~0,-4%.zip" "%PG_FILENAME%" &DEL "%PG_FILENAME%"

@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