PostgreSQL backup script for Windows

postgresql_backup.py

# -*- coding: utf-8
""" 
PostgreSQL backup script for Windows.
Olivier Cortès <[email protected]> 2014
Licensed under the GNU GPL version 3.

https://gist.github.com/Karmak23/62872892dd49f04f58d2

## Dependancies

- Python 2.7.x
- humanize (via PIP)


## Installation

Python is simple: https://www.python.org/downloads/windows/

Then, alter PATH:
	- System properties -> Environment variables
	- add "C:\Python27\Scripts"


### Install PIP & Git

- open another CMD/PowerShell for updated `%PATH%`
- Install PIP:
	- download https://bootstrap.pypa.io/ez_setup.py
	- then:

	python ez_setup.py
	easy_install pip

- Install Git: http://msysgit.github.io/ (it will alter PATH)

### Install dependancies

- open another CMD/PowerShell, then:

	pip install git+https://github.com/Karmak23/humanize.git@master#egg=humanize

And you should be ready to roll.

"""

import os
import sys
import time
import logging
import subprocess
from datetime import date, datetime
from humanize.time import naturaldelta

logging.basicConfig(level=logging.INFO)
LOGGER = logging.getLogger(__name__)

START = datetime.now()

# --------------------------------------------------------------- Configuration

nr_backups_to_keep = 5

# --------------------------------------------------------------- Configuration

PG_DUMP = [
	'C:\\Program Files\\PostgreSQL\\9.3\\bin\\pg_dump.exe', 
	'-i', '-F', 'c', '-b', '-f',  
 ]


def bkp_exit():
	""" Exit correctly. """

	STOP = datetime.now()

	LOGGER.info(u'Backup terminated @%s (duration: %s)', 
				STOP, naturaldelta(STOP - START))
	raise SystemExit(0)

def bkp_die(message, *args):
	""" Exit not correctly. """

	LOGGER.info(message, *args)
	raise SystemExit(1)

def check_parameters():

	for parameter, default in (
		('PGHOST',  u'localhost'),
		('PGPORT',  u'5432'),
		('PGDATABASE',  u'postgres'),
		('PGUSER',  u'postgres'),
		('PGPASSWORD',  None),
	):
		if os.environ.get(parameter, None) is None:
			if default is None:
				bkp_die(u'Missing environment variable %s!', parameter)
			else:
				os.environ[parameter] = default
				LOGGER.warning(u'Using default value "%s" for variable %s.',
							   default, parameter)

def main():
	""" Prepare, run and cleanup the PG backup. """

	check_parameters()

	today = date.today()
	database_name = os.environ['PGDATABASE']

	backup_directory = u'C:\\Users\\root2\\Desktop\\Sauvegardes'
	backup_filename = u'pg_dump_{0}-{1}{2}{3}.dump'.format(
		database_name, today.year, today.month, today.day)

	LOGGER.info(u'PostgreSQL backup starting @%s', START)

	if not os.path.exists(backup_directory):
		os.makedirs(backup_directory)
		LOGGER.info(u'Created backup directory "%s".', backup_directory)

	os.chdir(backup_directory)

	command = PG_DUMP[:]
	command.extend([backup_filename, database_name])

	try:
		LOGGER.debug(u'Running "%s"', u' '.join(command))
		subprocess.check_output(command)

	except:
		LOGGER.exception('pg_dump failed; old backups left untouched')
		raise SystemExit(1)

	backups = []

	for dirpath, dirnames, filenames in os.walk(u'.'):

		for filename in filenames:
			if filename.startswith(u'pg_dump_'):
				backups.append(filename)

	if len(backups) <= nr_backups_to_keep:
		LOGGER.info(u'Not yet %s backups, not touching anything.', 
					nr_backups_to_keep)
		bkp_exit()

	for backup_to_delete in sorted(backups)[:-nr_backups_to_keep]:
		os.unlink(backup_to_delete)
		LOGGER.info(u'Removed old backup file "%s".', backup_to_delete)

if __name__ == u'__main__':
	main()