PostgrSQL

• What Should a PostgreSQL Migrator do?: discusses schema migration methods from version to version
• arrays
• inheritance
• analytics
• statistics collector
• dynamic tracing
• logs
• proxies

This is something worth recording. The original article Transactionally Staged Job Drains in Postgres, which is informative by itself, was linked from Hacker News, which has even more supporting information.

Building a robust background worker system that leverages transactional isolation to never work a job too early, and transactional durabiity to never let one drop.

With this pattern, jobs aren’t immediately sent to the job queue. Instead, they’re staged in a table within the relational database itself, and the ACID properties of the running transaction keep them invisible until they’re ready to be worked. A secondary enqueuer process reads the table and sends any jobs it finds to the job queue before removing their rows.

Jobs are only removed after they’re successfully transmitted to the queue, so even if the worker dies partway through, it will pick back up again and send along any jobs that it missed. At least once delivery semantics are guaranteed.

Release 5.0.1 of Debian's Lenny GNU/Linux distribution includes version 8.3 of PostgreSQL.

During the creation of a new Debian Lenny server, a list of software packages is provided. To make a new PostgreSQL-only server, unselect everything, including the 'Standard system', then select 'SQL Database', and proceed with the installation.

Once installation has completed, and the new server has rebooted, the PostgreSQL service is not auto-started. There are a couple of manual commands to be applied. In prior versions, PostgreSQL was auto-started. I think I understand the reasoning, particularily because it is useful for my situation.

During the server creation, I have a separate set of disks allocated for the database. By manually finishing the PostgreSQL implementation, I am able to initialize the directory location during service creation. If I have I've mounted my drives at /var/local/db, then these two commands get the PostgreSQL 8.3 service started:

pg_createcluster -d /var/local/db 8.3 main
/etc/init.d/postgresql-8.3 start

Back in Febrary, I wrote a longish article on how to upgrade PostgreSQL. That article is outdated. An upgrade can now take place with two lines:

pg_upgradecluster -v 8.3 8.2 main
pg_dropcluster 8.2 main

The first copies the older version 8.2 files to the new 8.3 files directory. It does any modifications necessary. The second line then removes the old stuff.

Upgrading a PostgreSQL server version 7.x to version 8.x was relatively painless. It helped that there wasn't a significant amount of data to move, or blob objects to move.

For a Debian server on which I recently ran 'apt-get dist-upgrade', the Debian crew made PostgreSQL 8.2 run along side version 7.4. The /etc/postgresql/8.2/main/postgresql.conf file showed the server port as being 5433, by default. Since the service wasn't turned on, I set it for 5432, and changed the listen_addresses for '* Continue reading "Upgrading PostgreSQL 7.x to 8.x" »

I've looked at the C library for PostgreSQL and then wanted to see if there were any C++ wrappers for it. The most current appears to be libpqxx. On first blush, it looks very good. It if functional and robust. But... it has one draw back. The library insists on converting all binary stuff into text for passing back to the caller. For some applications, that can be a reasonable library simplifier.

But in this day and age of Templates and polymorphism, it seems to be a copout. Yes, if I had time, I'd probably try my hand at implementing some sort of 'variant' implementation to handle the various types of data that come back. Continue reading "libpqxx: A PosgreSQL C++ Wrapper Library" »