PSQL Connect To AWS Redshift From Windows 10 PowerShell

Coming from a completely Linux background, I was tasked with connecting to a aws redshift cluster or a postgres cluster via Windows powershell and PSQL. I knew it was possible and searching the internet came up with CMD prompt solutions, when I attempted via powershell, I was faced with the following error:

Turns out a colleague of mine and I figured out you will need to set the variable PGCLIENTENCODING via the powershell command line. This was expected but we could not nail down the syntax, we found it.

Once this is set, you can connect to PG as normal.

Python Generator Find Files With Wildcard

This is a neat way to generate file names in a directory that match a specific pattern, I use this to generate a list of files exported out of hive to load into S3.

Amazon Redshift Long Running Query Alert to Slack

This python code when called with a user that can query the STV_RECENTS table will check the duration on a current running query against the threshold set by the cli arguments and send an alert to slack if it exceeds 30 minutes. I have it cronned up and running every 30 minutes.

CLI example:

You will need slackclient:
You will need psycopg2:

INI file:

Slack message example:

Nagios Python Plugin Check If File Is Stale

Wrote this simple plugin to check if a log file was stale on a server using nagios and nrpe. This plugin checks multiple files with the app. naming convention.

POSTGRES – Top 100 Tables In Tablespace

I had a situation where I needed to find the top 100 largest tables in a certain tablespace on a postgres 9 database, in my case we archive tables into an archive1 tablespace. This query will find all the largest relations in the archive1 tablespace. Its important to swap out ‘archive1’ with whatever tablespace you are trying to list.

Hope this helps you out, took some time to get it to work.

Nagios Check Postgres Table Date Column Against now()

I had a situation where a daily sync of a table from one database to another was failing. This table was updated daily so the query should return something like this when it was synced correctly:

I use Nagios very heavily and I setup a custom plugin to check the query’s date against today’s date, this should warn or critical based on user supplied arguments. Here is what a failure looks like when running from the nagios servers command line. This worked well at alerting me when the sync failed, this was integrated into the nagios subsystem and emails and slack alerts are generated as expected.


I needed a quick plugin to warn me if one of our AWS REDSHIFT instances had a table count above 6000 and alert critical if above 7000. I decided to write a python plugin for nagios to do the chore. You can see the source code and the example of executing it below on the nagios host.

Python Backup WORDPRESS Site / DATABASE and HTML

I have this blog hosted on a LINODE dedicated LINUX server. It’s about 10 dollars a month for a 1 core system with about 250GB of disk space and 1GB of RAM, this server runs the common LAMP stack, I needed a quick and dirty script to backup MYSQL database and the PHP code contained in the /var/www/html folder. I wanted the script to compress the contents of both and move them into a directory with the correct date. See the comments below outlining the code and the action of running it.

So you can see we generated 2 files in a dated directory, I chose to use both zip and gunzip for compression algoritims. To view the contents you can run the normal linux commands to extract the files.

So there you have it, I can tar up the entire dated directory for easy offsite backup now of my entire site Hope this helps someone, feel free to copy the source code and change at will.


Python Mysql Connector

Thought I would try my hand at some SQL programming with Python, I was stuck using a Windows machine(BLAH) I wanted to setup a MySQL database and 1 table for testing. I am on a Windows machine so I installed WAMP which is a Windows Apache Mysql Php server. You can get the installer for Windows here:

You can get the python library / module here:

Mysql Python
Using PhpMyAdmin I setup a database and added the table.


As you can see I created a function that added data into my new database. I pass it 4 parameters, id-name-dept-salary, when you execute this script the database gets populated with the correct data. Pretty cool!!

Here is how you can see the help and call the command properly:
help command

Here is what you can see when performing a select * from the table.
database command

Here is the awesome IDE pyCharm and the results from running the code.
Here is an example of how to query that database from the table.

Here is the output of the fetch command using ID as a parameter:
database command

Took me a while to figure out the syntax, so hopefully this helps someone.

Nagios Python Plugin Check Lock File Exists