Skip to content

PostgreSQL for WordPress

PostgreSQL for WordPress (or PG4WP) is a plugin to install and use WordPress with a PostgreSQL database.

Current development is done with WordPress version 3.2.1  but it may work with other versions too (2.9.2 was tested ok recently).

The original code was inspired by usleepless’ script « mysql2pgsql.php » and has evolved to become compatible with newer versions of WordPress and PostgreSQL.

PG4WP rewrites the SQL code on the fly and makes the call to the native function adequately in place of the « mysql_*() » initial call.

The plugin is based on a wpdb class identical to the WordPress’ one, except that all calls to « mysql_*() » are renamed « wpsql_*() », and the « converter  » defines these « wpsql_*() » functions.
You don’t need to disable MySQL support in PHP to use a « converter » for a particular database and MySQL support is still available (the MySQL « converter » is included).

The WordPress core works, but there was very little testing.
If you test it (successfully or not) , I’d like to be informed of the results.

Plugins using WordPress’ wpdb with standard SQL should work without modification.

If you use a plugin successfully (or not) , please leave a comment.

If a plugin you wish to use doesn’t work and you think PG4WP is the cause, please leave a comment and I’ll try fix the problem.

You can download the latest version of PG4WP from the plugin directory

11 commentaires

  1. Blayne dit :

    Thanks! My school linux server only uses postgres so this is a huge help and seems to be working perfectly!!!

  2. Blayne dit :

    Although its a tad slow :-/ Probly just a server issue.

  3. Hawk__ dit :

    Nice to hear it helped you.

    Maybe you could give me some informations about your setup (WordPress version, PostgreSQL version, maybe Web server version and PHP version, …).

    v1.0 was intended to « just work », I know there are some bottlenecks in the code and the next step will be to optimize almost everything.

    I have some ideas, such as separating « setup » code from « daily usage » code.
    This would mainly reduce memory footprint but may also give a little speed boost.

    I’ll make some code profiling in the next few days so that I can determine which part of my own code is the slowest, and try to make it a little faster.

  4. Christoph dit :

    Hey Hawk,
    thanks for your huge effort on this!
    I just stumbled upon those news items:
    http://www.h-online.com/open/news/item/Oracle-adds-commercial-extensions-to-MySQL-1344611.html
    (was linked from here: http://monty-says.blogspot.com/2011/09/oracle-adding-close-source-extensions.html)
    My conclusion: that’s going to drive development further from mysql more towards true open source projects – like postgresql.
    Since WP-Core still doesn’t allow other dbms than mysql (https://codex.wordpress.org/Using_Alternative_Databases) your project/plugin seems the closest wordpress gets to postgresql.
    I guess that your target audience is going to grow in the future and wish you all the best!
    Thanks again for your work on this wonderful plugin!
    Cheers,
    C

  5. Hawk777 dit :

    Hi,
    I’ve found a bug in this code, in this line:

    $sql = preg_replace( ‘/COUNT(.+)ORDER BY.+/’, ‘COUNT$1′, $sql);

    Specifically, the « dot » character in a PCRE does *NOT* match a newline by default, which means in an SQL query in which COUNT and ORDER BY are on different lines, the ORDER BY clause will not be deleted. WordPress seems to generate such queries, which then blew up in my installation. I fixed it by changing like so:

    $sql = preg_replace( ‘/COUNT(.+)ORDER BY.+/s’, ‘COUNT$1′, $sql);

    The « s » modifier makes dots include newlines.

  6. Metulski dit :

    PG4WP with WP-E-COMMERCE plugin

    Hello Hawk,

    your plugin was the reason to give WordPress a try. Sadly there are not so many CMS based on Postgresql. So this plugin is a real enrichment for WP.

    I tryed to get the wp-e-commerce plugin (3.8.7.6.2) working on WordPress 3.3.1 and Postgresql 8.3 but I could not setup the shop at « Settings -> Store -> General » because the drop-down lists and drop-down-menus for « Base Country/Region: », « Target Markets: », « Currency Type: » are empty and the currency symbol at « Currency Sign Location: » is not visible.

    I looked in the « pg4wp_erros.log » file and I found several hundred lines of errors that can be associated with the wp-e-commerce plugin (these errors occure directly after activating the plugin)

    The errors that occures are like this:

    —- converted to —-
    SHOW TABLE STATUS LIKE ‘wp_wpsc_also_bought’
    —-
    FEHLER: Syntaxfehler bei »TABLE«
    LINE 1: SHOW TABLE STATUS LIKE ‘wp_wpsc_also_bought’
    .
    .
    .
    —- converted to —-
    ALTER TABLE wp_wpsc_checkout_forms ADD checkout_order int(10) unsigned NOT NULL DEFAULT ’0′ AFTER active;
    —-
    FEHLER: Syntaxfehler bei »(«
    LINE 1: …ABLE wp_wpsc_checkout_forms ADD checkout_order int(10) unsig…
    ^
    ———————
    .
    .
    .
    —- converted to —-
    ALTER TABLE wp_wpsc_currency_list ADD id bigint(20) unsigned NOT NULL auto_increment ;
    —-
    FEHLER: Syntaxfehler bei »(«
    LINE 1: ALTER TABLE wp_wpsc_currency_list ADD id bigint(20) unsigned…
    ^
    ———————
    Error running :

    —- converted to —-
    ALTER TABLE wp_wpsc_currency_list ADD country varchar(255) NOT NULL DEFAULT  » AFTER id;
    —-
    FEHLER: Syntaxfehler bei »AFTER«
    LINE 1: …list ADD country varchar(255) NOT NULL DEFAULT  » AFTER id;
    ^
    ———————

    … and so on …

    I already tryed the bugfix of Hawk777 with the <> modifier in the « driver_pgsql.php » file but this hat no effect on these errors.

    If you need more error logs or details just tell me.
    Thanks for reading my comment.

  7. Toine dit :

    Hi!

    I have found an error in driver_mysql.php in line 35, you remap mysql_escape_string() but now this function is named mysql_real_escape_string(). The PHP documentation indicates that mysql_escape_string is deprecated and will be removed in later version.

    I have another problem, wpDelta seem doesn’t work correctly with pg4wp, i dont understand why. With the plugin « WP Banner Lite 1.40″ the SQL table are not created and all query return « mysql_* » doesn’t exists. :(

    pg4wp work for WP plugins?

    Thank you, Toine

  8. Toine dit :

    I have found this error in my log:
    2012-04-08 11:11:21 CEST ERROR: syntax error at or near « collate » at character 150
    2012-04-08 11:11:21 CEST STATEMENT: CREATE TABLE wp_wpbl_banners (
    id int NOT NULL DEFAULT nextval(‘wp_wpbl_banners_seq’::text),
    type_id int NOT NULL,
    title varchar(255) collate utf8_unicode_ci NOT NULL,
    url varchar(255) collate utf8_unicode_ci NOT NULL,
    file varchar(255) collate utf8_unicode_ci NOT NULL,
    email varchar(255) collate utf8_unicode_ci NOT NULL,
    days_purchased int NOT NULL,
    price float NOT NULL,
    currency varchar(15) collate utf8_unicode_ci NOT NULL,
    shows_displayed int NOT NULL,
    clicks int NOT NULL,
    status int NOT NULL,
    id_str varchar(63) collate utf8_unicode_ci NOT NULL,
    registered int NOT NULL,
    blocked int NULL,
    deleted int NULL
    );
    CREATE SEQUENCE wp_wpbl_banners_seq;
    CREATE UNIQUE INDEX wp_wpbl_banners_id ON wp_wpbl_banners (id);

  9. Hawk__ dit :

    The way PG4WP works should permit plugins correctly written (using the $wpdb object for database interaction and absolutely no direct mysql_*() call) to function properly.

    But complex ones (such as WP-E-COMMERCE) need specific handling to work.

    I’m working on a method that will allow to create some kind of « drop-in plugins » for PG4WP to add support for particular WordPress plugins, to keep PG4WP code relatively simple and small.
    When this is done I’ll eventually start creating « drop-in plugins » for PG4WP to support other WordPress plugins, but I can’t give any delay for now.

  10. [...] to remove Postgres support. So it put it into the same camp as WordPress. Conveniently there is a PG4WP “plugin” (hack) that lets (most of) WordPress work on Postgres, [...]

  11. Grob dit :

    Hi, thanks to this plugin i was able to install wordpress 3.4.1 on heroku with shared database.
    Now i’m migrating to heroku postgresql dev plan and i have a problem.
    PG4WP use template1 as database when it’s yet not installed but on this hosting you cannot CONNECT to template1.
    A simple workaround is to comment out line 79-80 of file pg4wp/driver_pgsql.php but i guess it’s not a solution for every case.
    Regards.