Technology Errors

PostgreSQL Not Starting: FATAL: could not write lock file “postmaster.pid”: No space left on device

I have a couple development servers I have been using for testing PostgreSQL configured with walmgr for log shipping. The secondary server stopped working the other day and I finally got a chance to look into it this morning and attempted to restart Postgres via a terminal but it failed. Below I describe how the error was noticed, what was done to troubleshoot PSQL not starting, and what was done to resolve the problem so PostgreSQL would start without issue.

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.

Code Snippets

Updated Bash Script To Provide Read Only Postgres Database Table Access

I wrote an article a couple weeks ago regarding a shell script to run that would provide a postgres user read only access to every table in a specific database. Tonight I was updating a over a dozen databases and needed to provide read only access to all of them to a specific user so I decided to update the bash script from a couple of weeks ago to take a couple variables from the shell so you could specify database and user. The new script along with instructions on how to use it are below.