PostgreSQL Backup script (python)

#!python

from time import gmtime, strftime
import subprocess
import os
import glob
import time

# change these as appropriate for your platform/environment :
USER = "postgres"
PASS = "postgres"
HOST = "localhost"

BACKUP_DIR = "e:\\postgresql_backups\\"
dumper = """ "c:\\program files\\postgresql\\8.1\\bin\\pg_dump" -U %s -Z 9 -f %s -F c %s  """                   

def log(string):
    print time.strftime("%Y-%m-%d-%H-%M-%S", time.gmtime()) + ": " + str(string)

# Change the value in brackets to keep more/fewer files. time.time() returns seconds since 1970...
# currently set to 2 days ago from when this script starts to run.

x_days_ago = time.time() - ( 60 * 60 * 24 * 2 )

os.putenv('PGPASSWORD', PASS)

database_list = subprocess.Popen('echo "select datname from pg_database" | psql -t -U %s -h %s template1' % (USER,HOST) , shell=True, stdout=subprocess.PIPE).stdout.readlines()

# Delete old backup files first.
for database_name in database_list :
    database_name = database_name.strip()
    if database_name == '':
        continue

    glob_list = glob.glob(BACKUP_DIR + database_name + '*' + '.pgdump')
    for file in glob_list:
        file_info = os.stat(file)
        if file_info.st_ctime < x_days_ago:
            log("Unlink: %s" % file)
            os.unlink(file)
        else:
            log("Keeping : %s" % file)

log("Backup files older than %s deleted." % time.strftime('%c', time.gmtime(x_days_ago)))

# Now perform the backup.
for database_name in database_list :
    log("dump started for %s" % database_name)
    thetime = str(strftime("%Y-%m-%d-%H-%M")) 
    file_name = database_name + '_' + thetime + ".sql.pgdump"
    #Run the pg_dump command to the right directory
    command = dumper % (USER,  BACKUP_DIR + file_name, database_name)
    log(command)
    subprocess.call(command,shell = True)
    log("%s dump finished" % database_name)

log("Backup job complete.")

11 Replies to “PostgreSQL Backup script (python)”

1. I happened upon this while trying to searching for information on os.putenv and subprocess.Popen, and learned a bunch of new Python tricks. Thanks for posting this!

2. Hi, I tried to run this script on Windows 7 64-bit, and the following error occurs:

   Traceback (most recent call last):
   File “teste.py”, line 25, in
   database_list = subprocess.Popen(‘echo “select datname from pg_database” | psql -t -U %s -h %s template1’ % USER , shell=True, stdout=subprocess.PIPE).stdout.readlines()
   TypeError: not enough arguments for format string

3. Hi,

   I think I’ve corrected the above, I must have made a mistake writing the above. I think it should have been :

   database_list = subprocess.Popen(‘echo “select datname from pg_database” | psql -t -U %s -h %s template1′ % (USER,HOST) , shell=True, stdout=subprocess.PIPE).stdout.readlines()

4. Hi,

   I tried to run the script and I got following error:

   E:\Scryer>python postgreesql_db_dump_script.py
   File “postgreesql_db_dump_script.py”, line 6
   SyntaxError: Non-ASCII character ‘\xa0’ in file postgreesql_db_dump_script.py on
   line 6, but no encoding declared; see http://www.python.org/peps/pep-0263.html
   for details

   Please help.

   Thanks

5. Try looking at : http://www.perlmonks.org/?node_id=325765

   There’s some sort of weird whitespace character there (by the looks of things).

6. i have the following error on this script:

   glob_list = glob.glob(BACKUP_DIR + database_name + ‘*’ + ‘.pgdump’)
   TypeError: Can’t convert ‘bytes’ object to str implicitly

7. I can’t replicate that; are you running a new version of postgres, or a special (very new?) version of python?

   Perhaps rewriting it to :

   glob_list = glib.glob(“%s%s*.pgdump” % (BACKUP_DIR, database_name))

   May help.

   may help.

8. Hi David,

   Any specific modifs for the 9.3 VERSION?

   While tinning it just prints

   “2015-11-23-12-39-36: Backup files older than 11/21/15 12:39:36 deleted.
   2015-11-23-12-39-36: Backup job complete.”

   exits and generates no backup files. thanks

9. Why not use psycopg2 to establish a database connection within python (rather than handling the complexity of supporting connections to postgres via multiple OS’s?

   See: http://stackoverflow.com/questions/23732900/postgresql-database-backup-using-python

10. Oh, quite … It was quick and dirty!

11. how to get dockerized Postgresql backup & restore using a python script?