Check SSL certificate's expiration

If you ever want to quickly check the expiration date on your HTTPS server’s
SSL certificate all you need is OpenSSL, luckily most of your Linux and OSX workstations will
already have it installed.

openssl s_client -showcerts -connect domain.com:443 </dev/null 2>/dev/null \
  | openssl x509 -noout -dates

You should get back a nice and tidy response with a notBefore and a notAfter date:

notBefore=Mar 13 00:00:00 2015 GMT
notAfter=Mar 12 23:59:59 2018 GMT

PostgreSQL vacuum script

PostgreSQL does have a built in auto vacuum, but sometimes you just want a small script that can be ran through Jenkins to perform the vacuum for you.

Wanted to share with you guys a small Python script I wrote that will perform a
VACUUM VERBOSE ANALYZE on every table within a database.

You will need to get psycopg2 installed from PyPi first:

pip install psycopg2

At which point you should be able to use the below script with the correct environment
variables to vacuum your database:

import os
import psycopg2
from datetime import datetime

dbname = os.environ.get('PG_DATABASE', '')
user = os.environ.get('PG_USER', '')
host = os.environ.get('PG_HOST', '')
password = os.environ.get('PG_PASS', '')
schema = os.environ.get('PG_SCHEMA', '')


class Vaccum(object):

    def __init__(self):
        """
        Connect to Postgre database.
        """
        c = "dbname='%s' user='%s' host='%s' password='%s'"
        try:
            self.conn = psycopg2.connect(c % (dbname, user, host, password))
        except:
            raise Exception('Unable to connect to PostgreSQL.')

    def get_tables(self):
        """
        Get all tables in database's schema.
        """

        query = """
            SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = '%s'
            """ % schema

        cur = self.conn.cursor()
        cur.execute(query)

        for table in cur.fetchall():
            yield(table[0])

    def vaccum(self, table):
        """
        Run Vacuum on a given table.
        """

        query = "VACUUM VERBOSE ANALYZE %s" % table

        # VACUUM can not run in a transaction block,
        # which psycopg2 uses by default.
        # http://bit.ly/1OUbYB3
        isolation_level = self.conn.isolation_level
        self.conn.set_isolation_level(0)

        cur = self.conn.cursor()
        cur.execute(query)

        # Set our isolation_level back to normal
        self.conn.set_isolation_level(isolation_level)

        return self.conn.notices


if __name__ == '__main__':

    v = Vaccum()
    for table in v.get_tables():

        # simple verbose printout for VACUUM process.
        now = datetime.utcnow()
        verbose = '[%s] VACUUM VERBOSE ANALYZE %s' % (now, table)
        print '=' * len(verbose)
        print verbose

        # print out the VACUUM  VERBOSE results
        # using formating.
        notices = v.vaccum(table)
        for notice in notices:
            print notice

Usage looks like the following:

## set our environment variables for our database.
export PG_DATABASE=project
export PG_USER=user
export PG_PASS=password
export PG_SCHEMA=public
export PG_HOST=localhost

## execute the script to vacuum the database
$ python pg_vacuum.p

You should then get verbose output from the vacuum:

=====================================================================
[2016-03-08 18:56:28.332974] VACUUM VERBOSE ANALYZE django_migrations
INFO:  vacuuming "public.django_migrations"

INFO:  index "django_migrations_pkey" now contains 22 row versions in 2 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "django_migrations": found 0 removable, 22 nonremovable row
    versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  vacuuming "pg_toast.pg_toast_95319"

INFO:  index "pg_toast_95319_index" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  "pg_toast_95319": found 0 removable, 0 nonremovable row versions
    in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  analyzing "public.django_migrations"

INFO:  "django_migrations": scanned 1 of 1 pages, containing 22 live rows
    and 0 dead rows; 22 rows in sample, 22 estimated total rows