Idle in transaction – DELETE waiting

We have a Linux server (Redhat Enterprise 6) running a multi-site installation of Mediawiki. In total, we have today 120 unique wiki’s, each having its one Postgresql 9.1 database.

Suddenly the server stopped responding. In Apache webserver, the total number concurrent apache processes went up to Apache’s max-client setting in httpd.conf, and people couldn’t log in or see any wiki-pages.

In the apache log, we did not see anything special. But, when we started to check which processes were running during a full stop on the server with the Unix command ps, we could see entries like:

A search on Internet found that also others experienced this, and the solution to our problem we found here:

* http://www.mail-archive.com/dspace-tech@lists.sourceforge.net/msg00109.html

* https://wiki.duraspace.org/display/DSPACE/Idle+In+Transaction+Problem#IdleInTransactionProblem-Workaround:Killing

We implemented a fix to our problem by adding a bash script to /etc/crontab, running each fifth minute.

The script will first check if there are more then three (3) processes with the description “idle in transaction”.

If so, then the pkill command will stop (“kill”) the oldest one.

fix-transactions.sh:

The script runs every fifth minute. Here is our entry in the /etc/crontab file.

*/5 * * * * root /usr/share/scripts/fix-transactions/fix-transactions.sh

 

Authenticate Postgresql client agains backend ldap

We have a Postgresql server open to many different people at our organization. In order to add a ldap authentication you can add these 3 lines to your current pg_hba.conf file:

where the first one will allow users that access the server from localhost (either from a shell or 127.0.0.1 connections). The second line is allowing a computer from the following IP adresse: 129.177.XXX.YYY.

Finally the last line will use ldap backend for local connections. That means a Linux user that logged in with SSH, will still be validated agains the ldap server, regardless of local password.

Remember this is only authentication, so you have to add the username to Postgresql database anyway (hint: use createuser command).

If you want to allow a user to connect remotely over web, remember to use SSL in the webserver, HTTPS, so that the password is not transmittet un-encrypted.

Note also:  This is also only LDAP, not LDAPS connections. So data going from the Postgresql server to the ldap is not encrypted. In short: Keep the distance between the postgresql server and the LDAP server short, or try to implement LDAPS, which I did, where I failed, so far…

Open Journal System missing email logs

I upgraded our Open Journal Systems from 2.2.2 to 2.4.2 and afterwards we saw the email logs were gone. This was related to:

http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=9140

My solution was this:
Established a new Postgresql server in a virtual enviroment (vmware, test-machine), and import the old and the new (upgraded) database.

ojs_old: was the original 2.2.2 database
and
ojs_new: was the upgraded 2.4.2 one

In order to fix the assoc_id numbers, I used this bash script:

myscript.sh:

The output from this script is sent to STDOUT. I redirected it to another script, and ran the script on our Production server.

Postgresql 9.1 BACKUP DUMP BASH script

We wrote an improved postgresql dump bash script for Postgresql version 9.1. This one will save each dump file with the name: database_name_DAYNAME.sql.bz2
In this way, we would only have 7 backups at any time, because each file will be overwritten after seven days. Since our backup system (TSM) saves 7 versions of each file, we would then have 49 versions at any time. That means we can go 49 days back in time to restore a certain dumpfile. In addition the script saves monthly a dump file with the name: database_name_MONTHNAME.sql.bz2.
You can also set a variable TEST to ‘yes’ to, then the script will only dump one specific filename: database_name_daily.sql.bz2. This can be useful on Test servers, where you might not be interested in a historical backup back in time.

Here it is: