One of the questions that pop up frequently on IRC is how to see queries are now executed on the server, and what queries were earlier. Theoretically answer to this is simple – pg_stat_activity and log_min_duration_statement. Or log_statement. What is the difference? That’s exactly why I’m writing this post.
Relatively soon we will have 9.0, and we will move with development to 9.1 (we as in: PostgreSQL community, I don’t know C so I can’t help developing myself, but I will at least try to keep up with changes in the ‘Waiting for’ series).
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?”
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?”
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”
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”
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”
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””
Having new VARIADIC functions, I decided it would be cool to be able to write sprintf() function. Basically the idea is simple, plperl function, which takes “format”, and list of arguments and returns generated output.