Project currently abandoned see http://www.hawkix.net/2011/09/new-release-pg4wp-1-2-2/ for an updated version.

Getting WordPress 3.1.0 RC 2 to run on PostgreSQL can be a rather tricky thing to do. I had to run through a lot of files manually and do a lot of patching. Before getting started there is a few things I want to go through. first is a bit of reasoning behind running WordPress on PostgreSQL. If you are not interested or already know the reasoning you can skip directly to the requirements and the reason for not giving the patched files for direct download.

Reasoning

There can be many reasons for changing the database backend for a system like. One could be that the administrator of the server only runs one database server and refuses to install another, for either security reasons or simply to keep things simple. One of the other motivating factors is simply the principle of not running the system on a lesser capable database server, such as MySQL.

In my case I would probably have been able to convince my server admin to install MySQL, we both agree that it is a horrible server. Therefore find it interesting to use PostgresSQL out of the sheer principle.

It is important to be aware that if you switch out of principle there will most likely be no actual benefits. The plugin that makes sure that things run as they should, by converting the MySQL specific SQL into SQL that is compatible with PostgreSQL. Therefore the code will not be upgraded to actually use that PostgreSQL is a way more powerful server.

That said. Good luck.

Requirements

This is tested on WordPress 3.1.0 RC 2 and I will try to keep it up to date, so it will continue to work in later versions. PostgreSQL 9 or better – I had to change one of the SQL calls to one that is introduced in PostgreSQL 9.

I have not tested the system on any PostgreSQL previous to 9, but I have made sure to have a compatibility mode in ‘db.php’, where ‘PG_SERVER_VERSION’ can be set to something below 9 to try to run compatible code.

Hosting the files

Both WordPress and the PG4WP plugin, which I use for this, is released under GPL. This is a problem!

If I want to release the code changes I have introduced, I am be forced to release the code under the GPL or a compatible licence. Since GPL requires me to be able to provide the source code up to three years after I released it I can not and will not comply with GPL.

The good news is that Pete Deffendol aka fragility have given me access to commit to his patched version of the plugin on github. Currently my changes are accessible by visiting fragility’s PG4WP project on the wp310 branch. Since the changes have not been tested on other WordPress versions than 3.1.0 RC 2 yet, they have not been merged into the main branch.

Installation and configuration

To install it the easiest way is to simply pull the project from github and then switch the branch. To do this run the following commands:

In most cases you only need to copy ‘db.php’ to ‘wp-content’ and pg4wp folder (with files of course) to ‘wp-content/plugins‘.

On my system I needed to modify ‘wp-includes/wp-db.php‘ to make the plugin work. I am not sure how it is possible to make it work without doing this, but according to the original instructions this step is not needed. Feel free to test it without the changes first, I would love to have some feedback on if it is my system or it is the new WordPress version that forces those changes.

The modification is really simple, open ‘wp-includes/wp-db.php‘ in your favorite text editor, and do a search and replace for ‘mysql_’ and replace it with ‘wpsql_’. Et voila, it works.

Configuration

In most cases no further configuration is needed. The default configuration will work with PostgreSQL 8 or better, as long as the database admin is not overly paranoid. All configuration is done in ‘db.php‘, and of course the database settings is set in ‘wp-config.php‘ as you normally would.

If you are running on and PostgreSQL server older than 8, you need to set ‘PG_SERVER_VERSION‘ to 7 or less. I recommend you to set it to the right version, since more version dependent features might appear in time. If you are running an old PostgreSQL server I would also recommend you to upgrade it or nag your database admin till he updates.

If you have a paranoid database admin you might not have access to connect to the ‘postgres’ database. Even though this is overly paranoid and conflicts with the sole purpose of the ‘postgres’ database, I have made sure to have a workaround. You can set ‘PG_DEFAULT_DB‘ to either ‘templat0′, ‘templat1′ or to you own database.

Known bugs;

Even though they are small, there is a three known bugs at the present time (WordPress 3.1.0 RC2). The first two is only visible in the admin panel. The last is a permission problem specific to PostgreSQL  servers before version 8. The last will not be fixed.

Currently there will be some error messages in the admin panel. I have not seen any cases where they have done any damage apart from being annoying. One of them will be visible on the dashboard after a while. The second is when you edit comments. The data is still stored correctly, it just gives an error message. Reload the admin panel and everything is fine.