Application-Consistent Protection for PostgreSQL on Kubernetes

Updated

You can add a pre-process script file or a post-process script file for PostgreSQL on the Kubernetes access nodes.

Example Pre-Process Script

The following example is a pre-process script for PostgreSQL. Edit the example script to include the PostgreSQL port, database user credentials, and the name of a database on the PostgreSQL server, other than the template0 database.

#!/usr/bin/env python

import psycopg2

import os, time, datetime

dt=datetime.datetime.now().strftime("%I:%M%p on %B %d, %Y")

file1 = open("/scripts/pre-freeze.log","a+" )

conn = None

exit_status = 0

try:

conn = psycopg2.connect(host='localhost',port='<POSTGRESQL_PORT>',user='<POSTGRESQL_USER>',password='<POSTGRESQL_PASSWORD>',database='<POSTGRESQL_DB_NAME>')

cur = conn.cursor()

cur.execute ("select version();")

data = cur.fetchone()

file1.write (dt)

file1.write ("\n-------------------------------------------\n")

file1.write ("\t PostgreSQL version is %s: "%data)

file1.write ("\n-------------------------------------------\n")

file1.write (dt)

file1.write ("\t executing query to quiesce the database \n")

cur.execute (" select pg_start_backup('label'); ")

file1.write (dt)

file1.write ("\t Database is quiesced now successfully \n")

cur.close()

except (Exception, psycopg2.DatabaseError) as error:

file1.write(dt)

file1.write(str(error))

file1.write( "\n Error in quiesce DB, please check PostgreSQL logs for more info\n")

exit_status = 1

finally:

if conn is not None:

conn.close()

file1.write( "\n Database connection closed\n")

file1.close()

exit(exit_status)

Example Post-Process Script

The following example is a post-process script for PostgreSQL. Edit the example script to include the PostgreSQL port, database user credentials, and the name of a database on the PostgreSQL server, other than the template0 database.

import psycopg2

import os, time, datetime

dt=datetime.datetime.now().strftime("%I:%M%p on %B %d, %Y")

file1 = open("/scripts/post-thaw.log","a+" )

conn = None

exit_status = 0

try:

conn = psycopg2.connect(host='localhost',port='<POSTGRESQL_PORT>',user='<POSTGRESQL_USER>',password='<POSTGRESQL_PASSWORD>',database='<POSTGRESQL_DB_NAME>')

cur = conn.cursor()

cur.execute ("select version();")

data = cur.fetchone()

file1.write (dt)

file1.write ("\n-------------------------------------------\n")

file1.write ("\t PostgreSQL version is %s: "%data)

file1.write ("\n-------------------------------------------\n")

file1.write (dt)

file1.write ("\t executing query to unquiesce the database \n")

cur.execute ("select pg_stop_backup();")

file1.write (dt)

file1.write ("\t Database is unquiesced now \n")

cur.close()

except(Exception, psycopg2.DatabaseError) as error:

file1.write(dt)

file1.write(str(error))

file1.write( "\n Error in unquiesce DB, please check PostgreSQL logs for more info \n")

exit_status = 1

finally:

if conn is not None:

conn.close()

file1.write( "\n Database connection closed\n")

file1.close()

exit(exit_status)

Output and Log Files

Output from the scripts is written to the following files on your production PostgreSQL pod:

  • Pre-execution script: /scripts/pre-freeze.log

  • Post-execution script: /scripts/pre-thaw.log

To modify the paths, edit the scripts that are stored on your access nodes.

Caution

  • There is no log rotation implemented in these log files. Restore to a file system that has appropriate disk space.

  • Backup jobs depend on the return codes of pre- and post-scripts. Any return code value other than 0 fails backup jobs.