Bernd's Blog

Schedule for PGConf.DE released

2011-10-07T09:23:00.013+02:00

The session schedule for PGConf.DE, the German-speaking PostgreSQL Conference, was published. The conference will be hold November 11th in Oberhausen, Germany, in front of the OpenRheinRhur, a conference focused on free software.

If you haven't heard about PGConf.DE, it is the German-speaking continuation of the very successful PostgreSQL Conference Europe held in Stuttgart last year. There are many great talks about various topics (replication, performance and optimization, development and much more). The conference let you get in touch with developers and users, exchanging experiences about PostgreSQL and creating contacts to various members of the community.

Registration is open already and the entrance fee also includes access to the OpenRheinRhur conference for the whole weekend. See you there!

Yes, there is one (at least)...

2011-04-21T12:04:00.007+02:00

Yesterday it occured to me again, that i was asked for german literature about PostgreSQL 9.0 and the lack thereof. Okay, there is one...look for ISBN-13 978-3897216617 ;)

PostgreSQL Administration 2nd Edition is available since the very early days of 2011 and covers various topics about PostgreSQL 9.0 Administration, in addition to the first edition which covers up to 8.3 only. Seems people got confused about this.

A quick check also gives some other german book about this topic, look for ISBN-13 978-3942151061. If you have additional tips, feel free to leave a comment here.

FOSDEM, the same procedure as every year

2011-02-03T23:33:00.004+01:00

So FOSDEM starts this weekend again...and again i will attend, especially on sunday. Meet the PostgreSQL guys, hear the talks in the PostgreSQL devroom and maybe get some other interesting news from the community.
See you there!

PostgreSQL vs. libreadline on OSX

2011-01-21T18:26:00.011+01:00

Today Thom Brown posted a bug report to pgsql-bugs, where he asked for a potential issue with the interactive terminal psql. As the following reply from Tom Lane explained, this is a long standing issue with the libedit package Apple ships with OSX. Yes, libedit, since libreadline under OSX is just a symlink to libedit:


% ls -la /usr/lib/libreadline.dylib 
lrwxr-xr-x  1 root  wheel  15 15 Sep  2009 /usr/lib/libreadline.dylib@ -> libedit.2.dylib

If you just configure your PostgreSQL build without any special options, the PostgreSQL binaries will be linked against libedit.
There were several discussions about this in the past and this was even reported to Apple. However, since now (i'm on OSX 10.6.6 for example), nothing happened and the shipped libedit is still unusable with psql.

If you really need to build your own PostgreSQL binaries on OSX (for example you want do a review for a patch in the current CommitFest ;)), i always recommend to use MacPorts and their packaging. I use it for nearly two years and it works quite well (well, at least all libraries required by my installation are working). There's a fairly good instruction on how to setup MacPorts on your MacBook. If you have done your package installation, simply point the configure script to the correct location of the libraries and header files:


./configure --with-libraries=/opt/local/lib --with-includes=/opt/local/include ...

/opt/local is the default location for libraries installed from MacPorts. You also might want to specify additional options. At least, --with-includes and --with-libraries are necessary to point your PostgreSQL builds to the correct libraries.

Building Slony-I on OSX

2010-11-05T09:46:00.016+01:00

Just stumpled across this today while trying to build a fresh git clone of Slony-I on my MacBook; To build Slony-I on a OSX box (in my case 10.6.4), you might do the following on your box:


% ./configure --prefix=$PGSQL_HOME --with-pgconfigdir=$PGSQL_HOME/bin
...
configure: error: Headers for libpqserver are not found in the includeserverdir.
    This is the path to postgres.h. Please specify the includeserverdir with
    --with-pgincludeserverdir=<dir>

Now, of course you are following this gentle hint and try again with a proper include path:


% ./configure --prefix=$PGSQL_HOME --with-pgconfigdir=$PGSQL_HOME/bin --with-pgincludeserverdir=$PGSQL_HOME/include/server
...
configure: error: Headers for libpqserver are not found in the includeserverdir.
    This is the path to postgres.h. Please specify the includeserverdir with
    --with-pgincludeserverdir=<dir>

Uhm, doesn't work either. A good way to find out what's really going on now is to have a look into the config.log (located in the Slony-I source directory) and check what exactly is causing this error. Open the file and simply search for postgres.h. You'll find the following line there (ignore my path names here, they surely differ from yours):


In file included from /Users/bernd/pgsql-dev/install/REL9_0_STABLE/include/server/postgres.h:47,
                 from conftest.c:80:
/Users/bernd/pgsql-dev/install/REL9_0_STABLE/include/server/c.h:99:21: error: libintl.h: No such file or directory

Ah okay, it is indeed failing compiling the conftest.c file while including the PostgreSQL headers. Now that i saw this i remembered that i had my local development PostgreSQL-builds configured with --enable-nls, which need to include gettext/libintl. Since OSX doesn't ship with the latter, they were installed from MacPorts, which is located in /opt/local/include. So the solution is to just include those header path as well. PostgreSQL has an extra configure switch --with-includes, unfortunately the configure script in Slony-I is missing this, so i decided to go with this:


% CFLAGS='-I /opt/local/include' ./configure --prefix=$PGSQL_HOME --with-pgconfigdir=$PGSQL_HOME/bin

That worked and i was finally able to sucessfully build Slony-I on my MacBook. Hope this helps anybody facing the same issue.

PostgreSQL at FroSCon 2010

2010-08-06T16:32:00.013+02:00

Like last year, the German PostgreSQL User Group will have a Devroom at FroSCon (Sankt Augustin, Germany) this year. There will be talks around PostgreSQL, ASP.NET, the upcoming feature set in 9.0 and a workshop how to setup PostgreSQL 9.0 replication. The talks in detail can be found here (look for room C117).

For people interested in technical discussions or brainstorming, there's a booth at the conference, too, where people can meet PostgreSQL community members to discuss their questions. See you there!

CeBIT 2010

2010-02-20T00:56:00.010+01:00

... is coming up soon. CeBIT (March 2th to March 6th) is one of the biggest shows for IT and PostgreSQL will have a booth in the Open Source Project Lounge, organized by Andreas Scherbaum. I will staff the booth with Andreas on friday, 2010-03-05. If you are interested, feel free to meet us in hall 2, maybe we see us on friday!

EU, Open Source Databases

2010-01-21T12:00:00.004+01:00

There were plenty of discussions about the acquisition of Sun by Oracle and the future of MySQL. Now, it seems the EU is going to approve the deal, as long as we can trust this article from Financial Times Germany. So far so good...please note that i don't want to rant about MySQL and the acquisition through Oracle (i really have no interest in this). However, this article lists comments from an EU comissioner involved in the decision making process, which makes me wonder.

According to the article, commissioner Neelie Kroes has concerns regarding to the acquisition, since Oracle might starve the development of MySQL to get rid of a competitor on the database market. Without MySQL, customers won't have a free alternative to commercial databases from Oracle, Microsoft and IBM anymore.

This is just crazy, if you look at the feature set PostgreSQL currently delivers (think of Window Functions, Recursive Queries). With the upcoming release PostgreSQL will catch up with Oracle Standby Options and will add extended SQL features like exclusion constraints. Commercial support isn't a problem, a look at the current list of support companies gives plenty of them. So there is still one alternative at least, but maybe i'm missing their point.

PostgreSQL and its increasing popularity

2009-12-02T21:03:00.003+01:00

So i had just another PostgreSQL course at the LinuxHotel in Essen. As always, i enjoyed working there with people interested in learning PostgreSQL within a very nice atmosphere. The reason i'm blogging about this, is that i'm really impressed how PostgreSQL has gained momentum over the last three years.

Doing such courses a few times a year at the LinuxHotel and getting regular feedback from attendees, i noticed that PostgreSQL has reached various places in the german industry and administration. Its used in ERP, Inventory Control, Data Warehouses, Webapplications, Information Retrieval. Often PostgreSQL serves mission critical applications, sometimes its used to implement a satellite system along with some other proprietary product. In many installations this was the entry key for PostgreSQL at all, since this allowed people to get their experience with PostgreSQL in a production environment without a radical change. They used this opportunity to build up their knowledge and infrastructure they need to run PostgreSQL as a backend for their applications and to recognize its reliability and stability. There are examples out there, where PostgreSQL finally replaced the main system afterwards.

Cloning Slony Nodes

2009-09-22T22:00:00.006+02:00

Motivation

The new stable branch 2.0 of Slony-I is out for a while now. Time to blog about one of my favorite new features there, cloning an existing node without doing an initial SUBSCRIBE command.

Normally administrators have to perform a subscription for each new node they want to add to their Slony-I cluster. This initial subscription procedure uses PostgreSQL's COPY command to copy over the whole dataset and creating all indexes afterwards. This whole approach can take a fairly long time on bigger slaves, especially if you have many indexes on them.

In PostgreSQL 8.4 there exists a new pg_restore method, parallel restore (see the pg_restore -j command line option). This allows to restore tables and indexes in parallel. If you have machines with more than one cpu core (and number of discs, depending on your storage setup), this can speedup creation of the database, thus making the CLONE PREPARE interesting.

Cloning Procedure

First at all, we have to figure out, which node is the source and which node is the new target node. In Slony-I, a node defines a specific database which takes part in the replication. Consider the following three nodes:

 
node 1: host=master.db dbname=movies 
node 2: host=slave1.db dbname=movies 
node 3: host=slave2.db dbname=movies

node 1 is our origin, the master database node in a Slony-I cluster. node 2 and node 3 hosts the slave database node (or subscriber in the Slony-I universe), where node 3 is going to be added to the cluster as a clone of node 2. We assume master.db and slave1.db are already set up with Slony-I.

To start over, we need a small slonik script, which clones the node info of node 2 to our new node. The CLONE PREPARE command copies the node information and adjusts all required node information in the Slony-I catalog (e.g. adding the node to sl_path). Every slonik script needs a preamble, which defines the cluster name and the node connection information described above. To ease this task, i use a small file which can be easily included from other slonik scripts, e.g.

 
$ cat slonik.inc 
define cluster movies; 
define conn_master 'host=master.db dbname=movies'; 
define conn_slave1 'host=slave1.db dbname=movies'; 
define conn_slave2 'host=slave2.db dbname=movies'; 
define master 1; 
define slave1 2; 
define slave2 3; 

cluster name = @cluster; 

node @master admin conninfo = @conn_master; 
node @slave1 admin conninfo = @conn_slave1; 
node @slave2 admin conninfo = @conn_slave2;

Any slonik script can use the INCLUDE directive to include the preamble. To clone slave1.db (node id 2) to slave2.db (node id 3) we use the following script then:

 
$ cat slony_clone_prepare.slonik 

include <slonik.inc>; 

## prepare cloning slave1.db (identified by node id 2)

echo ' => prepare cloning'; 

clone prepare (id = @slave2, provider = @slave1, comment = 'clone of slave1.db'); 

# Wait until all nodes have received the event  
wait for event(origin = @slave1, confirmed = ALL, wait on = @slave1); 

# Create a SYNC event and wait on every other node until they have confirmed. 
# We will do this to prevent the new node to miss any events created 
# in the meantime 

sync(id=@master); 
wait for event(origin=@master, confirmed=@slave1, wait on=@slave1); 

echo ' => clone prepared'; 
echo ' now copy movies@slave1.db to movies@slave2.db';

To execute the script, pass it to slonik:

 
$ slonik < slony_clone_prepare.slonik

Now we can start and dump the database from node 2 to the new node 3. Create the database on the new node (don't forget to create pl/pgsql on it, it's required for Slony-I). Of course, you need to have Slony-I installed on this node, too. We are going to use three CPU cores to speedup the restore with pg_restore's parallel restore capability. On the new node, we are executing the following command:


$ su - postgres
$ createdb movies && createlang plpgsql movies
$ pg_dump -Fc -h slave1.db movies > movies.pg_dump 
$ pg_restore -j3 -h slave2.db -d movies movies.pg_dump

After the database ist restored, we have to finish the clone process with CLONE FINISH command, implemented by the following script:

 
$ cat slony_clone_finish.slonik 

include <slonik.inc>; 

## finish cloning of slave1.db
echo ' => try to finish cloning slave1.db'; 
clone finish(id = @slave2, provider = @slave1); 

# store connection path for the new node 
store path(server = 1, client = 3, conninfo = @conn_master); 
store path(server = 3, client = 1, conninfo = @conn_slave2); 

echo ' => finished cloning slave1.db'; 

$ slonik < slony_clone_finish.slonik

This script calls CLONE FINISH first and stores the connection path for the new node in the cluster configuration. This is important, otherwise the slon daemon won't be able to talk to the new node. Now start the slon daemon for the new node:

 
$ slon movies 'host=slave2.db dbname=movies'

Depending on the platform you are, you may need to adjust your runlevel scripts to start the slon daemon automatically on each start (at least, don't forget to adjust the runlevel configuration). You also have to take care which user runs the slon daemon, you may have to specify a specific user in the connection string. The new node should immediately starting to catch up with all pending events and replicate all data changes which occured during the cloning procedure.

Conclusion

Cloning a node with CLONE PREPARE has the advantage that it enables the usage of parallel restore to speed up building a new Slony-I slave. It also avoids the additional load on the origin, since all I/O is located on the target and source node only. The new node becomes a complete clone of the source node, thus it replicates exactly the same objects as the source node.

Building PostgreSQL Documentation on MacOS X

2009-09-04T10:26:00.005+02:00

During the latest CommitFest i've started to use MacOS X while reviewing patches as a Round Robin Reviewer. I'm going to love OS X and its shiny GUI, but i had heavy problems to build the PostgreSQL Documentation on that platform. The core problem is, that on OS X there's no really good Docbook distribution available (at least, i haven't found one, which is usable out of the box). So i decided to go with macports, since i had some very good experience with them in the past. Please note that i'm not a DocBook hacker...i'm just using it to build at least the PostgreSQL docs. So i've started installing openjade from macports first:

$ sudo port install openjade

This installs openjade into /opt/local/share/sgml/openjade. Unfortunately, there's no Docbook SGML in macports, so i followed the PostgreSQL instructions and downloaded the following packages:

DocBook SGML DTD
DocBook DSSSL
ISO 8879 Entities

The ISO 8879 character entities can also be obtained from macports:

$ sudo port install iso8879

This is what I preferred. I went on and installed the DocBook DTD and DocBook DSSSL packages as described in the PostgreSQL Documentation. Instead of /usr/local/share/sgml i've used the already created directory /opt/local/share/sgml, used by macports. Unzipping the downloaded archives gives the following layout:


drwxr-xr-x  36 root  admin   1,2K  3 Sep 23:45 docbook/
drwxr-xr-x  21 root  admin   714B  3 Sep 23:34 docbook-dsssl-1.79/
drwxr-xr-x  22 root  admin   748B  3 Sep 14:10 iso8879/
drwxr-xr-x  12 root  admin   408B  4 Sep 10:24 openjade/

Because the ISO character entities are located outside the docbook directories, I needed to adjust the catalog file /opt/local/share/sgml/docbook/docbook.cat. I dumped the whole entity section from the file and added a CATALOG reference instead:


$ cat docbook/docbook.cat
[...]
  -- ...................................................................... --
  -- ISO entity sets ...................................................... --
CATALOG "../iso8879/catalog"

This references /opt/local/share/sgml/iso8879/catalog which includes all ISO character entities then. Afterwards i tried PostgreSQL configure, unfortunately that didn't work. The first problem i've encountered was that /opt/local/bin/collateindex.pl wasn't installed with executable rights:


-rw-r--r--  2 root  admin  17503  3 Sep 15:21 collateindex.pl

configure was not able to locate the script (so, what's the point in installing a script in a bin directory without executable flags??). Firing a chmod a+x /opt/local/bin/collateindex.pl fixed that issue. Another problem was, that openjade wasn't able to find the docbook DTD's. After trying around a little bit, i realized that openjade looks into /opt/local/share/sgml, not in /opt/local/share/sgml/openjade, as someone might expect. I created a symlink to openjade/catalog. At a first try this didn't work either. Foolish as i am, i forgot that the catalog file references the objects with relative paths. Creating a separate catalog file in /opt/local/share/sgml/catalog did the job finally:


$ cat /opt/local/share/sgml/catalog 
PUBLIC "-//James Clark//DTD DSSSL Flow Object Tree//EN" "openjade/fot.dtd"
PUBLIC "ISO/IEC 10179:1996//DTD DSSSL Architecture//EN" "openjade/dsssl.dtd"
PUBLIC "-//James Clark//DTD DSSSL Style Sheet//EN" "openjade/style-sheet.dtd"
PUBLIC "-//OpenJade//DTD DSSSL Style Sheet//EN" "openjade/style-sheet.dtd"
SYSTEM "openjade/builtins.dsl" "openjade/builtins.dsl"
CATALOG "docbook/docbook.cat"
CATALOG "docbook-dsssl-1.79/catalog"

The directory layout i'm going to work with looks as follows:


-rw-r--r--   1 root  admin   541  4 Sep 10:22 catalog
drwxr-xr-x  18 root  admin   612  4 Sep 15:03 docbook/
drwxr-xr-x  21 root  admin   714  3 Sep 23:34 docbook-dsssl-1.79/
drwxr-xr-x   7 root  admin   238  3 Sep 14:10 html/
drwxr-xr-x  22 root  admin   748  3 Sep 14:10 iso8879/
drwxr-xr-x  12 root  admin   408  4 Sep 10:24 openjade/

The HTML docs are finally build by the commands


$ eval ./configure $(pg_config --configure) DOCBOOKSTYLE=/opt/local/share/sgml/
$ cd doc/src/sgml; make

Leave your comments if you have some additional suggestions or better ideas.

How to get a table's OID

2008-12-11T12:54:00.006+01:00

I often get questions from customers trying to work with OIDs of relations they need to add somewhere (three times just this week). For example, someone wants to add a special autovacuum configuration for a specific table. He then needs to find out the OID of the relation. People often tend to use the following query then:

SELECT oid FROM pg_class WHERE relname = 'foo' AND relkind = 'r';
oid
-------
16385
(1 row)

This returns the current OID of the table and its relation name. The OID can then be used for everything you need it to. However, there's an much easier way to get that information: just use a cast of the relation name to regclass:

SELECT 'foo'::regclass::oid;
oid
-------
16385
(1 row)

Or, to get back to the pg_autovacuum example above:

INSERT INTO pg_autovacuum
VALUES('foo'::regclass, 't', -1, 0.05, -1, -1, -1, -1, -1, -1);

If you are going to assign regclass to an OID type, you don't need the extra cast to OID, since there's an existing implicit cast to OID. There are other reg* casts available as well, for examaple:

SELECT 'now'::regproc::oid;
oid
------
1299
(1 row)

SELECT 'int4'::regtype::oid;
oid
-----
23

Froscon 2008

2008-07-03T11:58:00.001+02:00

The program schedule for Froscon 2008 is online. Again, there will be plenty of very interesting talks and i think it's going to be a very nice community event. So if you want to attend a friendly, very nice event, we should met at Froscon 2008. Regarding PostgreSQL, i'll have a talk about "PostgreSQL Troubleshooting", and there will be a PostgreSQL Workshop teached by Susanne Ebrecht.

Building PostgreSQL on IBM pSeries/SLES 10

2008-05-09T11:58:00.000+02:00

It turns out that compiling PostgreSQL on IBM's pSeries isn't that straightforward. Compiling PostgreSQL is very easy, just do a configure; make install and your job is done. However, Red Hat and SuSE on IBM's pSeries are multiarch platforms with 64-bit kernels and 32-bit userspace, capable of running and building 64-bit applications. To build PostgreSQL you have to specify some extra compiler flags. On SLES/ppc64 we had success with the following settings, passed to make:

make CC="gcc -m64" LDREL="-r -melf64ppc"

PostgreSQL development with git

2008-04-22T22:53:00.000+02:00

After getting an account from Peter Eisentraut i've just started to publish my current development efforts regarding PostgreSQL on git.postgresql.org. I found this a very neat idea, since developers are able to track development from other people in an easy way, without sending huge patches around. Also big patches are hard to read and if you are able to track development live, reviewing a new functionality gets a lot easier. I've never used git before, so let's see how it works.