Postgres Backup and Restore with Python

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

Do you want to automate Postgres backup and restore with Python? We’ll build a Python application for backing up and restoring your Postgres schema and/or data as an exercise, as well as to help you get your application built using some of Python’s and Postgres’ most popular tools. Along the way you will learn all about PG_dump, PG_restore, and how to call these utilities from within a Python application using the SH library.

Why Backup your schema and/or tables?

Data Rollback

Have you ever received email from a client saying they accidentally deleted or changed vital data and want to know if you can restore their Postgres data to save them hours of data entry? This is where you will feel secure and happy that you frequently backed up of your PostgreSQL database.

Database Security

Whether we are thinking about hardware failure, a hacker, bugs, or a virus, no database application is perfectly safe. Nobody wants to lose their work designing, building, and filling up a database.

Moving a Postgres database

When you want to move your PostgreSQL database to another server, whether it is local or on a different host, one of the easiest ways to do this migration of your Postgres database is to use pg_dump and pg_restore.

pg_dump Postgres utility

PostgreSQL’s pg_dump is a utility created for backing up your database data and/or schema. There are many parameter choices that allow you to refine exactly what you want to back up. This utility makes perfect backups even when Postgres is under high frequency usage. In addition, there is no risk that the backup will interrupt any of the database’s read/write operations.

Postgres pg_dump syntax

In order to use pg_dump via the command line, you can find the utility in the bin folder of your PostgreSQL folder. So first, you will want to navigate to that spot on your system:

1
CD c:\program files\PostgreSQL\[PG VERSION NUMBER HERE]\bin

NOTE: If you are migrating data, etc. from an older version of Postgres to a newer one, use pg_dump from the newer version’s install.

Syntax

1
pg_dump [connect options] [other options] [name of database]

Postgres pg_dump example

There are many ways we can use PostgreSQL’s pg_dump utility. We’ll focus here on two of the most common types of backup. If you are doing a migration, you may want to do the schema and data separately, starting with dumping the schema.

Postgres backup of schema

1
pg_dump -s -d name_of_postgres_database -f \mybackups\2020-01-01-db-schema.sql

Analysis (the following parameters are case sensitive)

  • -s: The “-s” tells PostgreSQL to backup the schema only, meaning the structure of tables, etc., not the data. The “s” here is case sensitive. If you capitalize to “-S”, it comes to mean “username is next parameter”.
  • -d: The “-d” instructs the system to use the following text as the name of the Postgres database to be backed up.
  • -f: The path to use and file name you want pg_dump to create (text file by default) in that folder. Again, upper/lowercase matters. If you capitalize to “-F”, it means “format”, with the default being “p” for “plain text”.

A different way to get the same backup result is to change out the “-f” with a “>” as you see here:

1
pg_dump -s -d name_of_database -p 5432 > \mybackups\2020-01-01-schema.sql

Postgres backup of data

Now that you see how to use the command line to back up the schema, we’ll learn you how to back up only the PostgreSQL data.

1
pg_dump -d name_of_postgres_database -F t -f \mybackups\2020-01-01-db-data.tar

Analysis

  • -d: The “-d” tells pg_dump to use the next word as the name of the PostgreSQL database to be backed up. The “d” here is case sensitive.
  • -F: “-F” means format, “t” means use tar compression.
  • -f: This precludes the name of the path to use and text file name you want pg_dump to create. Again, case is important.

Another way to do this same operation removes the “-f” parameter and instead uses “>” to mean “save as this path and file”:

1
pg_dump -d name_of_database -p 5432 -F t > \mybackups\2020-01-01-schema.tar

Postgres pg_dumpall

If you have multiple Postgres databases to backup at once, you can use pg_dump. Two things to be cautious about when thinking about using PostgreSQL’s pg_dumpall:

  • The pg_dumpall can take more time than doing a single database dump.
  • Since pg_dumpall exports all your PostgreSQL databases, keep in mind – unless you otherwise specify – pg_dumpall will export all to one single file, which could get pretty huge and present challenges when wanting to restore only one database of the many that were backed up.

PostgreSQL pg_dumpall examples

pg_dumpall schemas only

To export all database schemas on the Postgres server with no data.

1
pg_dumpall --schema-only > \mybackups\2020-01-01-db-schemas-all.sql

pg_dumpall data only

To export all data existing on the current Postgres server.

1
pg_dumpall -F t > \mybackups\2020-01-01-db-data-all.tar

Analysis

  • -t: Use tar for compression.

Now that we understand the variety of ways to use the above PostgreSQL command line tools for backup and restore, as well as an understanding of why backups are important, we’ll move ahead to building the various pieces of our application.

List all Postgres tables

The first part of our application we’ll work on is how we list all the tables in our database for the user to pick a table to backup. Later, we’ll look at picking a table to restore. That will essentially be the scope of our application.

1
2
3
4
5
6
7
8
9
10
11
s = ""
s += "SELECT"
s += " table_name"
s += " FROM"
s += " information_schema.tables"
s += " WHERE"
s += " (table_schema = 'public')"
s += " ORDER BY table_name;"
data_cursor.execute(s)
return data_cursor.fetchall()
data_cursor.close()

Analysis

WHERE table_schema = ‘public’: This limits the returned data to make sure we get only the “public” tables, that is because leaving out this part of the WHERE clause, we would get all tables in the database, which includes system tables .

Next we’ll look at how we use a for loop to iterate through all the table names returned.

Python for loop

1
2
for t_table_current in list_of_tables:
    print(t_table_current + "\n")

Analysis

  • for t_table_current in list_of_tables: Creates “t_table_current” to track each iteration of values in the “list_of_tables” array.

Now that we have a list of tables (list_of_tables) Python list/array, we can build a dynamic HTML “template” to be used via render_template(). The template displays list_of_tables, which will be sent to the HTML page as a parameter.

List all backups

Here we’ll assume you created a table called tbl_backups for keeping track of backups, file name, type of backup (data or schema), and the date/time of the backup.

1
2
3
4
5
6
7
8
9
10
11
12
s = ""
s += "SELECT"
s += " t_backup_file"
s += ", t_backup_type"
s += ", t_table"
s += ", d_backup"
s += " FROM"
s += " tbl_backups"
s += " ORDER BY d_backup DESC;"
data_cursor.execute(s)
list_of_backups = data_cursor.fetchall()
data_cursor.close()

Build Python HTML template

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
<html>
<head>
<title>PostgreSQL backup and restore with Python</title>
</head>
<body style='background-color: #DDDDDD;'>
<div class='div_main_top'>
    <table style='border:1px;'>
    <tr>
    <td valign='top'>
        <!-- BACKUP -->
        <div class='heading-larger'>PICK A TABLE TO BACKUP</div>
        {%
        for t_table_current in list_of_tables:
        %}
            <div class='div_row'>
                <div class='div_col'>{{ t_table_current }}</div>
                <div class='div_col'>
                <a href='
               {{ t_url_for_application }}
               ?t_user_choice_of_action=backup
               &t_table_current={{ t_table_current }}
               &t_backup_type=data
               '>

                data-only</a>
                </div>
                <div class='div_col'>
                <a href='
               {{ t_url_for_application }}
               ?t_user_choice_of_action=backup
               &t_table_current={{ t_table_current }}
               &t_backup_type=schema
               '>

                schema-only</a>
                </div>
            </div>
        {%
        endfor
        %}
    </td>
    <td valign='top'>
        <!-- RESTORE -->
        <div class='heading-larger'>PICK A BACKUP TO RESTORE</div>
        {%
        for t_backup_current in list_of_backups:
        %}
            <div class='div_row'>
                <div class='div_col'>{{ t_backup_current }}</div>
                <div class='div_col'><a href='
               {{ t_url_for_application }}
               ?t_user_choice_of_action=restore
               &t_backup_current={{ t_backup_current }}
               &t_backup_type=data
               '>

                data-only</a></div>
                <div class='div_col'><a href='
               {{ t_url_for_application }}
               ?t_user_choice_of_action=restore
               &t_backup_current={{ t_backup_current }}
               &t_backup_type=schema
               '>

                schema-only</a></div>
            </div>
        {%
        endfor
        %}
    </td>
    </tr>
    </table>
</form>
</div>
</body>
</html>

Analysis

  • This page displays two columns. On the left is a list of tables the user can choose from to tell Python to backup that table, as well as using “t_backup_type” to designate either “data only” or “schema only” by sending “data” or “schema”, respectively, back to Python.
  • for t_table_current in list_of_tables: Loop through each item in the “list_of_tables” Python List, which was passed to our HTML via render_template.
  • {{ t_url_for_application }}: As you can see, two curly brackets together tell the server to place values in that spot. In this case, we are filling that spot with the contents of the “t_url_for_application” variable and “t_table_current”. t_url_for_application is the URL for our Python application and t_table_current is the current for loop item from the list_of_tables array.
  • endfor: Normally we don’t need a “next” or “endfor” type statement to designate the end of a for loop in Python. Dyanmic HTML from render_template requires the “endfor” because we are not relying on indenting to tell Python when the loop ends.

Now that we have created a template (dynamic HTML page) to be rendered from our Python application, save the file in a folder off the root of your server called “templates\”. Save the file as “backup_manager_template.html” in that folder.

Source: Creating and deleting tables with Python

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
# ---------------------------------------------
# Include Python framework/library dependencies
# ---------------------------------------------
from flask import Flask
from flask import render_template # for render_template
from flask import request # for getting user input from HTML page
import psycopg2 # for database operations
import datetime
import gzip
import sh
from sh import pg_dump
from sh import pg_restore

# -------------------
# Database connection
# -------------------
t_host = "Postgres database address"
t_port = "5432"
t_dbname = "database"
t_name_user = "user name"
t_password = "password"
db_conn = psycopg2.connect(host=t_host, port=t_port, dbname=t_dbname, user=t_name_user, password=t_password)
db_cursor = db_conn.cursor()

@app.route("/Main", methods=["POST","GET"])

# ----------------------------------------
# Get list of available backups to restore
# ----------------------------------------
def GetListOfBackups():
    s = ""
    s += "SELECT"
    s += " t_backup_file"
    s += ", t_backup_type"
    s += ", t_table"
    s += ", d_backup"
    s += " FROM"
    s += " tbl_backups"
    s += " ORDER BY d_backup DESC;"
    # Get data and return it to main below
    db_cursor.execute(s)
    return db_cursor.fetchall()
    # ... to list_of_backups
    db_cursor.close()

# ----------------------------------
# Get list of all public tables
#   for users to pick from to backup
# ----------------------------------
def GetListOfTables():
    s = ""
    s += "SELECT"
    s += " table_name"
    s += " FROM information_schema.tables"
    s += " WHERE"
    s += " ("
    s += " table_schema = 'public'"
    s += " )"
    s += " ORDER BY table_name;"
    # Retrieve all rows and return to main below
    db_cursor.execute(s)
    return db_cursor.fetchall()
    # ... to list_of_tables
    db_cursor.close()

def DoBackup(t_table_current, t_backup_type):
    d_backup = datetime.datetime.now()
    t_backup_file = d_backup:text + "-" + t_table_current
    if t_backup_type == "schema":
        t_backup_file += ".sql"
    else:
        t_backup_file += ".gz"
    # using gzip and pg_dump (from sh library)
    # do the backup
    if t_backup_type == "schema":
        with gzip.open(t_backup_file, 'wb') as g:
            pg_dump('postgres', '-s' , _out=f)
    else:
        with gzip.open(t_backup_file, 'wb') as g:
            pg_dump('postgres', _out=f)

    # save to our backups table to track
    s = ""
    s += "INSERT INTO tbl_backups"
    s += "("
    s += " t_backup_file"
    s += ", t_backup_type"
    s += ", t_table"
    s += ", d_backup"
    s += ") VALUES ("
    s += "(%t_backup_file)"
    s += ", '(%t_backup_type)'"
    s += ", '(%t_table_current)'"
    s += ", '(%d_backup)'"
    s += ")"
    db_cursor.execute(s, [t_backup_file, t_backup_type, t_table_current, d_backup])

def DoRestore(t_table_current, t_backup_type):
    s = ""
    s += "SELECT"
    s += " t_backup_file"
    s += " FROM"
    s += " tbl_backups"
    s += " WHERE "
    s += " t_table = '(%t_table_current)'"
    s += " AND"
    s += " t_backup_type = '(%t_backup_type)'"
    # Get data and return it to main below
    db_cursor.execute(s, [t_backup_type, t_table_current])
    t_backup_file = db_cursor.fetch("t_backup_file")
    if t_backup_type == "schema":
        with gzip.open(t_backup_file, 'wb') as g:
            pg_restore('postgres', '-s' , _in=f)
    else:
        with gzip.open(t_backup_file, 'wb') as g:
            pg_restore('postgres', _in=f)

# ----------------------------
# Application main function
# ----------------------------
def Main():
    # check querystring for action to take
    t_user_choice_of_action = request.args.get("t_user_choice_of_action", "")
    t_table_current = request.args.get("t_table_current")
    t_backup_type = request.args.get("t_backup_type", "")
    # if backup
    if t_user_choice_of_action == "backup":
        return DoBackup(t_table_current, t_backup_type)
    # if restore
    if t_user_choice_of_action == "restore":
        return DoRestore(t_table_current, t_backup_type)
    # Retrieve list of tables into list_of_tables array
    list_of_tables = GetListOfTables()
    # Retrieve list of backups into list_of_backups array
    list_of_backups = GetListOfBackups()
    # Display dynamic HTML template for the user
    t_url_for_application = "[URL to application]" # fill this in!
    return render_template("backup_manager_template.html", list_of_tables = list_of_tables, list_of_backups = list_of_backups, t_url_for_application = t_url_for_application)

Conclusion

We used exercises and we built a Python application in order to gain a deeper understanding of the ins and outs of Postgres Backup and Restore with Python. We built a Python application for backing up and restoring your Postgres schema and/or data as an exercise, as well as being useful in case you are building a similar application. Code samples included.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.