SQL

• 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.

Performing database versioning the hard way means going into some management software and manually making changes to fields, tables, and relationships. This works fine for a one person project. But as soon as more developers are added to a project making use of one or more databases, chaos is bound to ensue.

Many shops have learned the wisdom of using version control to ensure consistency and centralization of source code control. On the other hand, very few have been able to do database versioning in a like fashion.

Mooney has a different way of saying it, but I suppose it boils down to:

• software versioning is reasonably easy as it only relies on getting everyone's text to play well together, and is usually processed in batches, but is otherwise in a quiescent state
• database versioning is more difficult as there are at least two factors at work: a) the database schema, which defines the data, and b) the data itself, with both living in a dynamic, always-on environment

Versioning your development databases has another good overview of handling versioning, testing, and deployment of database changes. All handled in a fashion similar to normal code version management.

The most elegant solution I've seen to-date is found in the Python language environment, specifically the SqlAlchemy database library through the Alembic add-on.

And to prove that this is not a new problem, there is a book called "Refactoring Databases: Evolutionary Database Design" which has been out since 2006.

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" »

Back in October 2007, I wrote an article about PostgreSQL, C++ and the lack of binary data and wrappers.

I stand corrected. On two counts. The first is binary access. After looking through the PostgreSQL API, I see that I can indeed get binary data into and out of a database easily. The C based library, libpq, has an API call named PQexecParams which allows full separation of binary values from the query statement. This is a good step towards preventing SQL injection attacks. Continue reading "Open Source Site of the Day -- SOCI: The C++..." »

Many web sites use a back end SQL engine for serving up data. Some Credit Card number thefts can be attributed to poor protection of web page interactions with a back end SQL server through injecting SQL statements into web requests.

A well written article called The Unexpected SQL Injection goes into detail on how SQL Injection happens, and how to prevent it.

Spend some time on the site. There are many other documents providing valuable information regarding the protection of internet based activity.