What mistakes you can avoid when looking for help on IRC?

Today, there was this one person on IRC, which asked question and provided some data. While working on helping him (her?), I noticed some things, that bugged me before in other cases, but this time i decided to write about it – it’s kind of rant, and if you (the reader) are the person that … Continue reading “What mistakes you can avoid when looking for help on IRC?”

How to remove backups?

Question from title sounds weird to you? It’s just a ‘rm backup_filename’? Well. I really wish it was so simple in some cases. One of the servers I’m looking into, there is interesting situation: quite busy database server (2k tps is the low point of the day) very beefy hardware daily backups, each sized at … Continue reading “How to remove backups?”

Setting WAL Replication

There are several approaches on replication/failover – you might have heard of Slony, Londiste, pgPool and some other tools. WAL Replication is different from all of them in one aspect – it doesn’t let you query slave database (until 9.0, in which you actually can run read only queries on slave. Since you can’t run … Continue reading “Setting WAL Replication”

Calculating backlog of events to handle

Yesterday on my favorite IRC channel fooqux asked interesting question. I took some more questions, and here is problem description: We have a system which, every 5 minutes, takes a number of tasks to be done. Tasks are uniform. Within 5 minutes we can handle at most 100 tasks. Given the history of number of … Continue reading “Calculating backlog of events to handle”

Speeding up dump/restore process

As some of you know, I’ve been working lately for OmniTI company. When doing things for them (PostgreSQL related of course :), I stumbled on very interesting problem. One of our clients is working on PostgreSQL 8.2, and wants to upgrade to 8.3. This is generally trivial – pg_dump, pg_restore/psql, and you’re done. But, this … Continue reading “Speeding up dump/restore process”

Hunting “idle in transactions”

If you ever encountered “idle in transaction” connections, you most likely hate them. I know, I personally hate them. They interfere with most of “cool toys” like replication, vacuum, DDL queries. So, when I saw them on a database I was looking on, I decided to act. Easier to say, difficult to do. How to … Continue reading “Hunting “idle in transactions””