Posts Tagged ‘PostgreSQL’

Looking for

October 13, 2010 2 comments

I had always been building Slony from source, but for the first time I tried using the community version from “”. I was using PostgreSQL version 8.3 so it was really easy to find the correct rpms at but as I tried installing Slony package I was hit with an error:

" not found"

This looked really weird to me as I already had the libpq library installed as but it was still looking for

Looking for a solution I started going through the 8.3 RPMs and tried looking for file file…. bingooo!!! I found one which had it. compat-postgresql-libs was the answer to this problem.

After installing that package, Slony got installed successfully without any problems.

Shoaib Mir

Categories: PostgreSQL Tags: , ,

(SLONY) cache lookup failed for type

October 13, 2010 1 comment

Just been away from blogging for quite a while now as work had been keeping me extremly busy but finally got sometime today to blog about an interesting problem I saw with Slony yesterday…

While at a customer site, I upgraded them to Slony version 1.2.21 and also during the process re-initialized the cluster. The whole process was done without any problem but right after everything was synched across, one of the Sysadmins saw this error in the database server log file:

"cache lookup failed for type 27889"

On doing an INSERT into sl_log_1 table.

Having never seen such a problem before with Slony, it got me a little worried as this was a production database so we had to fix it very quickly.

After doing a little bit of research found out that, by simply restarting the application server which is making connections to the database server will solve the problem. Reason being, the application server had already established a connection to database when we were dropping and re-creating the cluster and that is why it was still looking for old values  that were sitting in the cache. Once we restarted the application server, it was all fine.

Shoaib Mir

Understanding ‘iostat’ output for database I/O loads

June 5, 2010 2 comments

From the Linux man page:

“The iostat command is used for monitoring system input/output device loading by observing the time the devices are active in relation to their average transfer rates. The iostat command generates reports that can be used to change system configuration to better balance the input/output load between physical disks.”

Reports that we get from ‘iostat’ are really useful but I myself had a little bit of trouble when trying to interpret the results while using the the first time, but since then its my preferred go-to tool when trying to debug disk overloads.

I usually use the iostat command with the following switches:

iostat –d –x <interval>


-d = gets rid of the CPU stats so that we can easily concentrate on the I/O only

-x = some additional info like ‘await’ and ‘svctm’ (will discuss them later)

<interval> = this is time in seconds, so every number of <interval> seconds you will get a new ‘iostat’ report

Let’s now see a sample output of ‘iostat’:

If we look at stats above usually we would look at %util and if we see close to 100% it can identify the problem for a single disk setup, but not in a usual multi-disks scenario.

Columns that we look at it in order to identify the problem will be:

syvctm: The average service time (in milliseconds) for I/O requests that were issued to the device

await: The average time (in milliseconds) for I/O requests issued to the device to be served. This includes the time spent by the requests in queue and the time spent servicing them.

This basically means:

 await = syvctm + wait time in queue

Now using the above we can have a basic rule to identify an overloaded setup:

…if you can see a lot of difference in values for ‘syvctm’ and ‘await’ every now and then, that can tell you about I/O requests being going into long waits and this should help you identify the problem.

Shoaib Mir

Categories: PostgreSQL Tags: , ,

TRUNCATE problems with Slony

June 3, 2010 5 comments

Slony ( is great for high availability/load balancing and I have been recommending it to users for years now and haven’t really seen any major problems with it if you have got proper checks on the whole replication setup using proper monitoring with something like Nagios.

But well at times there is this annoying thing that always gave me trouble when a user goes and just runs a TRUNCATE on one of the master tables in order to do maintenance and all of the sudden you start getting errors like this on the slave nodes:

2010-06-01 11:02:09 ESTERROR  remoteWorkerThread_1: "insert into "public"."table1" ("a","b") values ('1','one');

" ERROR:  duplicate key value violates unique constraint "table1_pkey"

The reason behind this is…

  • You did a truncate on a table at master node and assumed that this statement is all replicated to slaves as well
  • Truncate event is not triggered like INSERT/UPDATE/DELETE (For PostgreSQL < 8.4) so that means the slave never got it and they still have the old copy of the table
  • On master the table is now empty but the slave table has old records, which means on inserting new data you might start getting duplicate key violation error

I have used the following approach in the past to solve it…

Do a TRUNCATE on slaves for the same table and you will then see all those INSERTS/DELETES/UPDATES going through in the Slony logs file that have been queued up since Slony started getting these duplicate key violation problems

In order to avoid these scenarios, you have to keep on checking Slony lag times from sl_status of replication schema and alert if it goes above the dangerous lag limit. If it does, go and check the Slony logs as they usually tell the detailed story.

Another option to avoid doing TRUNCATE is by using table partitioning, which means when you need to drop old data just get rid of the specific partition and drop it out of the Slony cluster as well instead of doing a TRUNCATE. This way it will save you a lot of database routine maintenance like vacuuming as well because you are not deleting data using DELETEs but you are now dropping the whole partition.

I have recently seen trigger support for PostgreSQL 8.4 and above but I am not sure if it’s been implemented in Slony yet or what exactly is the plan on that, but it will be great to have that with Slony. I do have seen Bucardo ( supporting truncate with triggers though.

Shoaib Mir

Categories: PostgreSQL Tags: , ,

Table level auto-vacuum settings

May 31, 2010 Leave a comment

While working with users on custom auto-vacuum settings, one of the real problems I saw with PostgreSQL 8.3 and below was making them understand the ways to update pg_autovacuum as sometimes little mistakes could cause you a lot of trouble.

I had some annoying experiences with those column values for pg_autovacuum at times as well, like once I had mistakenly autovacuum_freeze_min_age value set to zero while autovacuum_enabled set to false the auto-vacuum thread still kept doing vacuuming on that table. After doing a little bit of research found out that it had to be set to -1 so it can use the system-wide values and then it would all work. This was all a little confusing for the new users and then the other problem was as you a dump and restore of the database, all those settings are just gone so you will have to do it all over again.

I had been using pg_autovacuum with version 8.3 and just today while I was writing an upgrade plan for a client upgrading databases to 8.4, I just came across this improved custom auto-vacuum feature which lets you simply do a CREATE/ALTER TABLE command and set anything as you like for auto-vacuuming per table using the WITH option and it will also copy all these settings in the dump file (pg_dump output) as well which makes my life so easy while moving databases.

An example of it can be:

postgres=# \d test
Table "public.test"
Column |       Type        | Modifiers
a      | integer           | not null
b      | character varying |
"test_pkey" PRIMARY KEY, btree (a)
postgres=# ALTER table test SET (autovacuum_vacuum_threshold=50, 
-- PostgreSQL database dump
a integer NOT NULL,
b character varying
WITH (autovacuum_vacuum_threshold=50, autovacuum_analyze_threshold=50);
-- PostgreSQL database dump complete

You can find details for the storage settings that can be set with the CREATE/ALTER TABLE at

Shoaib Mir

Using SchemaSpy for DB diagrams

May 1, 2010 10 comments

A couple of months back I came across this very useful tool “SchemaSpy” ( ) which really helped me out in generating automated (nightly) database diagrams but never got enough time to share it with everyone.

“SchemaSpy is a Java-based tool that analyzes the metadata of a schema in a database and generates a visual representation of it in a browser-displayable format. It lets you click through the hierarchy of database tables via child and parent table relationships as represented by both HTML links and entity-relationship diagrams. It’s also designed to help resolve the obtuse errors that a database sometimes gives related to failures due to constraints”

It works great with PostgreSQL and very easy to cron as you just need to execute a single command like this:

java -jar schemaSpy.jar -t dbType -db dbName [-s schema] -u user [-p password] -o outputDir

…that will generate very nice and user-friendly DB diagrams and a whole lots of information about the database. You can have a quick look at the sample on

Update: Also found an easy to use GUI for SchemaSpy where you don’t need to remember all the option but you can easily pick them up from the frontend.

Make sure you grab the latest beta from as mentioned by John Currier.

Shoaib Mir

Categories: PostgreSQL Tags: ,

Using pg_hba.d folder for HBA rules

January 23, 2010 Leave a comment

Its been quite a while since I did post something and that was due to a very busy schedule at work but got lucky today to get a few mins and sharing something I always wanted to write about.

We got an environment at work where we have a quite a huge number of databases on each PostgreSQL development database server and it is because of the static images of the production DBs we need to create for QA and Dev most of the time. In such a scenario if we keep on adding rules to the pg_hba.conf file it can be a big mess with around 200-300 lines in there and thats a nightmare to manage for a DBA.

Solution – What we ended up doing for this was by having a folder pg_hba.d like this:


and under pg_hba.d folder:


The above two files then contain the list of databases that need to given access for those hosts.


This way you can have host files for each of your staging servers that are used by QA and then have a list of databases that need to be given access from those.

Last step is adding details for using these pg_hba.d files, this can be done in pg_hba.conf file the following way:

host @pg_hba.d/host1-dbuser1-trust.db dbuser1 trust
host @pg_hba.d/host2-dbuser2-trust.db dbuser2 md5

This way instead of having the whole list of databases in pg_hba.conf file we now just have two entries and making it very easy to manage. When ever you need to give a new database access to those hosts just add it to the specific file for that host under pg_hba.d folder and reload the DB server.

Shoaib Mir

Categories: PostgreSQL Tags: , ,