pgbouncer and postgresql 9.0
Just ran into the issue with "Pooler Error: Unknown startup parameter" when connecting to PostgreSQL 9.0 using pgbouncer. A quick search turned up the following discussion pointing out that adding ignore_startup_parameters = application_name resolves the issue.
Reviewing PostgreSQL Admin 9.0 Cookbook
I was asked by Likesh Bhambhwani to review the PostgreSQL Admin 9.0 Cookbook written by Simon Riggs and Hannu Krosing. It will no doubt be an interesting read.
Review will be posted as soon as I'm done!
New hg repo from subdir
It's a joy when tools just work.
I needed to create a new repository from a subdir of another repository while preserving history. Since I'm pretty new to mercurial and was not sure there was a clean way of doing this.
After a quick search and a couple of tests I found out that convert was the thing to use. (It works with a lot of other SCM tools too.)
- Init the new repo
- Create a filemap
- Run convert
The filemap is just a text file with files to include or exclude. In my case I need just to include the subdir I wanted. There was a small gottcha here since I once had renamed the subdir. I needed to include the previous name in the filemap to preserve the entire history.
$ hg convert --filemap filemap /path/to/src/repo /path/to/new/repo
pg_upgrade and shared libraries
I ran pg_upgrade on a dev cluster of mine and it halted on some shared libraries I had forgot to install for the new cluster.
Most of them were plain contrib that I'll continue to use so no problem there but there were some testing code I had been playing around with. I didn't know in which of my test databases I had used it so I had to find out somehow.
This is what I came up with, a query to list what shared libraries are used in a database. With that information I could drop the functions depending on shared libraries I no longer used.
select p.proname as "name", pg_catalog.pg_get_function_arguments(p.oid) as "args", n.nspname as "namespace", p.probin::text as "lib", u.usename as "owner" from pg_proc p join pg_user u on u.usesysid=p.proowner join pg_language l on l.oid=p.prolang join pg_namespace n on n.oid=p.pronamespace where nspname not in ('pg_catalog', 'information_schema') and lanname='c'
To avoid running it manually on all databases I wrote some python code to do that for me. If it's of use to anyone I'd be more than happy to share that too.
Print pg_stat_bgwriter analysis from CSV file
From the lecture Monitoring PostgreSQL Buffer Cache Internals by Greg Smith I created a simple bgwriter-print.py script to read, calculate and print the pg_stat_bgwriter analysis. It's a simple script but maybe someone else does find it useful to.
Android + PostgreSQL = ?
What would a great android application for postgresql be? That's the nexus-one-question... Join the competition!
PostgreSQL 9.0 Alpha4
OT: Django 1.2 on track
Sun Oracle PostgreSQL
EnterpriseDB + Red Hat = <3
This press release from Reuters about Red Hat investing in EnterpriseDB is great news.
Since Red Hat once manage to bring Linux to the room of Enterprise IT I'm hoping this would increase the acceptance of PostgreSQL adoption in the Enterprise world. We all know how slowly the Enterprise wheels are turning... :)
Interesting choice of words
Just read the article Is it Postgres' time to shine? and one sentence caught my attention:Postgres is an enterprise Java database, more suitable for carrying corporate data than the Web's consumer data.
My thought was "If I didn't know about PostgreSQL I would think that it was a database system written in Java". What's your take?
(As a side note I don't think that article was particularly interesting but I suspect CNET News to have a fair amount of management among their readers.)
strip html tags but keep href attribute value
To be able to match hostnames in links and at the same time get the benefits of tsearch I created a small function to strip of html tags while keeping the link intact for tsearch to tokenize.
The second regexp_replace is really not necessary since tsearch will ignore any HTML tags, or rather see them as XML tokens.
I'm sure there are more clever ways of accomplish the same thing but this seemed as a fine compromise for the moment. Thoughts and comments are of course welcome. :)
begin; -- strip tags function -- we use this to strip all html tags but still preserving the href -- attribute value so tsearch later can match host. span> -- Does two runs: -- 1) strip all tags containg the attribute href but preserve the -- attribute value and put it in parentheses. -- 2) strip of any remaining tags CREATE OR REPLACE FUNCTION strip_tags(TEXT) RETURNS TEXT AS $$ SELECT regexp_replace( regexp_replace($1, E'<[^>]*?(\s* href \s* = \s* ([\'"]) ([^>]*?) ([\'"]) ) [^>]*?>', E' (\\3) ', 'gx'), E'(< [^>]*? >)', E'', 'gx') $$ LANGUAGE SQL;
pgbouncer: stats entries in syslog
Quick understanding of HBase
In an article at High Scalability this article explaining HBase on a conceptual level was referenced. It's a very good starting point for understanding the basic concept of HBase (and BigTable) and it's no more than a five minute read.
Finally a real world webservice that enterprises can use to save money
Meet the Meeting Ticker.