Technology Insights

PostgreSQL: Server Instrumentation Not Installed. The Server Lacks Instrumentation Functions.

When connecting to a PostgreSQL database using pgAdmin you may receive an error letting you know that the server instrumentation is not installed. This is a warning to let you know that you can install the adminpack contribution which will provide more functionality to pgAdmin including the ability to modify the pg_hba.conf and postgresql.conf configuration files. Below is information on the pop up warning you may receive when logging into a Postgres database using pgAdmin III as well as how to install the adminpack to not receive the error any longer.

Technology Insights

Upgrade PostgreSQL 8.3 To PostgreSQL 8.4 On A CentOS Server Using Yum

I have been needing to upgrade the PostgreSQL servers at my work for awhile now and was finally forced to do so after needing some of the features available with PostgreSQL 8.4.X. Currently we are running PostgreSQL 8.3.10 which is super old but it is definitely not the latest stable release available. One thing I am looking forward to testing is the improvements to the vacuum process. Over the past two years I have become a lot more familiar with Postgres and one thing that has been disappointing is the downtime involved in reclaiming disk space via data removed from PSQL database tables. The full vacuum process, which is required to reclaim disk after deleting data from a table, in 8.3 requires a lock on tables which essentially causes downtime. When you are dealing with large tables over 10GB in size this can take days to complete. Follow the directions below to first backup all of your PostgreSQL data, remove the current PostgreSQL installation, and then install PostgreSQL 8.4 on a CentOS Linux server.

Technology Insights

PostgreSQL Log Entries: CDTLOG: statement: SHOW client_min_messages

I was troubleshooting an issue at work the other day regarding some PostgreSQL connections that were not closing. They were left IDLE, never closed, and eventually used up all of the possible connections (which totaled 100) configured in the  postgresql.conf configuration file. In the process of troubleshooting I noticed a bunch of Postgres log entries that I was unable to immediately pin down to what was causing the entries. Below I describe the view of the idle Postgres connections, the PostgreSQL log entries that were unfamiliar, and the cause of both.

Technology Errors

CriticalMailer ::Exception:: FATAL C53300 Msorry, too many clients already Fproc.c L276 RInitProcess

I was tasked with troubleshooting a Ruby Rufus Task Scheduler issue today so I started with reviewing the log files. Upon digging into the logs I noticed numerous errors which are expanded below. The primary error appeared to be a connection issue from Ruby to PostgreSQL as it was complaining about too many connections. While the server I was looking into is actively used in a development environment the connections limit of 100 should have been more than enough to handle the load. After looking into the issue more I found out that some large Postgres queries from within the Ruby code were causing the issue and once those were optimized things started working properly. Below I describe the errors noticed via the Ruby logs, steps taken to troubleshoot the errors, and final resolution.