Category “PostgreSQL” — 131 articles

So, if you followed the previous blog entry, now you have a new database containing all the static tables encoded in UTF-8 rather than SQL_ASCII. Because if it was not yet the case, you now severely distrust this non-encoding.

Now is the time to have a look at properly encoding the live data, those stored in tables that continue to receive write traffic. The idea is to use the UPDATE facilities of PostgreSQL to tweak the data, and too fix the applications so as not to continue inserting badly encoded strings in there.

It happens that you have to manage databases designed by your predecessor, and it even happens that the team used to not have a DBA. Those histerical raisins can lead to having a SQL_ASCII database. The horror! What SQL_ASCII means, if you’re not already familiar with the consequences of such a choice, is that all the text and varchar data that you put in the database is accepted as-is. No checks.

So, after restoring a production dump with intermediate filtering, none of our sequences were set to the right value. I could have tried to review the process of filtering the dump here, but it’s a one-shot action and you know what that sometimes mean. With some pressure you don’t script enough of it and you just crawl more and more. Still, I think how I solved it is worthy of a blog entry.

I can’t really compare PgCon 2009 with previous years versions, last time I enjoyed the event it was in 2006, in Toronto. But still I found the experience to be a great one, and I hope I’ll be there next year too! I’ve met a lot of known people in the community, some of them I already had the chance to run into at Toronto or Prato, but this was the first time I got to talk to many of them about interresting projects and ideas.

It’s time for Skytools news again! First, we did improve documentation of current stable branch with hosting high level presentations and tutorials on the PostgreSQL wiki. Do check out the Londiste Tutorial, it seems that’s what people hesitating to try out londiste were missing the most. The other things people miss out a lot in current stable Skytools (version 2.1.9 currently) are cascading replication (which allows for switchover and failover) and DDL support.