How to backup Postgresql DB to an FTP site using Python

Using the standard tools of Postgresql pg_dump and Python's subprocess module we can call the command line tool via Python and have the dump file uploaded to an FTP site.  This is of course a very simple way to backup.

An alternative would be to use Pgbarman https://www.pgbarman.org/ which is a higly effective way to minimize data loss.

	import ftplib
	import os
	import sys
	import subprocess
	from optparse import OptionParser
	from datetime import datetime
	DB_USER = 'databaseuser'
	DB_NAME = 'databasename'
	BACKUP_PATH = r'/webapps/myapp/db_backups'
	FILENAME_PREFIX = 'myapp.backup'
	# FTP SETTINGS
	FTP_URL = 'my.ftp.site.com'
	FTP_USER = 'myusername'
	FTP_PWD = 'secret'
	def main():
	parser = OptionParser()
	parser.add_option('-t', '--type', dest='backup_type',
	help="Specify either 'hourly' or 'daily'.")
	now = datetime.now()
	filename = None
	(options, args) = parser.parse_args()
	if options.backup_type == 'hourly':
	hour = str(now.hour).zfill(2)
	filename = '%s.h%s' % (FILENAME_PREFIX, hour)
	elif options.backup_type == 'daily':
	day_of_year = str(now.timetuple().tm_yday).zfill(3)
	filename = '%s.d%s' % (FILENAME_PREFIX, day_of_year)
	else:
	parser.error('Invalid argument.')
	sys.exit(1)
	destination = r'%s/%s' % (BACKUP_PATH, filename)
	print 'Backing up %s database to %s' % (DB_NAME, destination)
	ps = subprocess.Popen(
	['pg_dump', '-U', DB_USER, '-Fc', DB_NAME, '-f', destination],
	stdout=subprocess.PIPE
	)
	output = ps.communicate()[0]
	for line in output.splitlines():
	print line
	print 'Uploading %s to FTP...' % filename
	upload2ftp(destination, filename)
	def upload2ftp(destination, filename):
	session = ftplib.FTP_TLS(FTP_URL,FTP_USER, FTP_PWD)
	file = open(destination,'rb') # file to send
	session.storbinary('STOR {0}'.format(filename), file) # send the file
	file.close() # close file and FTP
	session.quit()
	if __name__ == '__main__':
	main()

view raw backup_postgresql2ftp.py hosted with ❤ by GitHub

Here you go: