Category Archives: PostgreSQL

PostgreSQL 9.3 Debian

To install PostgreSQL 9.3 I used the following steps as root:

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -c | awk '{print $2}'`-pgdg main" > /etc/apt/sources.list.d/postgre.list &&
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update && apt-get dist-upgrade && apt-get install postgresql-9.3

 

JBoss JDBC Module Creator

Every time I download a new jboss I have to add the needed jdbc module manually. And every time I have to look how the module.xml has to look like. So I decided to write a simple script that creates a module for PostgreSQL. with some configuration, it should be capable of creating every jdbc module you want. Just see the options it supports.

You can see my project at github: https://github.com/mbogner/jboss_module_creator

Here some instructions how to run it with default options:

[codesyntax lang=”bash”]

git clone https://github.com/mbogner/jboss_module_creator
cd jboss_module_creator
./jboss_module_creator.pl

[/codesyntax]

WordPress problem with PostgreSQL

I found strange errors like the following in my apache_error.log

[Thu May 02 20:59:58 2013] [error] [client 84.114.158.229] WordPress database error ERROR:
  column p.id does not exist\\\\\\\\nLINE 1: SELECT p.id FROM wp_posts AS p  WHERE ...

I found out that this comes from line 1178 in wp-includes/link-template.php. Just change the p.id at after the SELECT to p.ID and save the file (SELECT p.ID …). That’s the trick.

PostgreSQL 9.2 released

see http://www.postgresql.org/about/news/1415/ for further information about the new features.

To install it in ubuntu you could use Martin Pitt’s ppa under https://launchpad.net/~pitti/+archive/postgresql

sudo apt-add-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get dist-upgrade
sudo apt-get install postgresql-9.2 postgresql-client-9.2

WARNING: This won’t upgrade your 9.1 if you’ve already installed that. Of course you could get rid of your database with apt-get remove. But you’ll have to migrate your data manually!

NOTICE: To avoid autostart of postgre just make

sudo update-rc.d -f postgresql remove

PostgreSQL Table Partitioning

I was just messing around with postgresql and table partitions and tablespaces. Here is a short SQL script that uses a virtual table that is partitioned by the modulus of the id column. Each partition lies on a different tablespace and in the schema partition.

begin;

drop table if exists partition.test_0;
drop table if exists partition.test_1;
drop table if exists test;

CREATE TABLE test (
	id SERIAL PRIMARY KEY
);

CREATE TABLE partition.test_0 (
	PRIMARY KEY (id),
	CHECK ( id % 2 = 0 )
)
INHERITS (test)
TABLESPACE ts1;

CREATE TABLE partition.test_1 (
	PRIMARY KEY (id),
	CHECK ( id % 2 = 1 )
)
INHERITS (test)
TABLESPACE ts2;

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS TRIGGER AS $$
BEGIN
	IF ( NEW.id % 2 = 0 ) THEN 
		INSERT INTO partition.test_0 VALUES (NEW.*); 
	ELSIF ( NEW.id % 2 = 1 ) THEN 
		INSERT INTO partition.test_1 VALUES (NEW.*);
	ELSE
		RAISE EXCEPTION 'This should not happen! See test_insert_trigger.';
	END IF;
	RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_test_mod_trigger 
	BEFORE INSERT ON test
	FOR EACH ROW EXECUTE PROCEDURE test_trigger_function();

commit;

JBoss Postgresql Module

Just download the latest jdbc4 driver and place it into JBOSS_HOME/modules/org/postgresql/main. Then create a file module.xml in the same folder with the following content (replace filename if necessary):

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="org.postgresql">
    <resources>
        <resource-root path="postgresql-9.1-901.jdbc4.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>