Rudolf Rittmann schrieb:
> Hi All,

Ho!

> I'm new to this mailing list, but have been testing OX 0.8.2 for some
> time now and are ready to implement this as a production system for our
> institution.
> We are in the process of installing a second server which we would use a
> a replication server for the production one. On this replication server
> (slave) we will  have the ldap database, imap mailbox database,
> postgresql database replicated from the master server.
> 
> My big problem is getting slony1.1.1.5 to replicate my open-xchange
> database. The database schema for openexchange 0.8.2 has 130 tables from
> which only 29 tables have primary keys, unique indexes and sequences.
> This is a big problem with slony1, because slony1 replicates only those
> tables that falls under these categories as far as I understand. I need
> to add primary keys or unique keys to those tables that does not have
> them but need help in deciding which columns to choose for as the
> primary or unique key.

I decided to use another approach for the missing primary keys.

When you setup slony, you have to execute a lot of commands with the slonik
command line tool. This tool adds the necessary informations for master and
slave host of replication and the replication paths. It adds the information
which tables to replicate, too. Before you add the tables to the replication the
tool provides a command to add a primary key generated by slony to the tables.

Let's have a look at the commands. You can add a table with the following
command, if the table has a primary key:

SET ADD TABLE (
   SET ID = 1,
   ORIGIN = 1,
   ID = 65,
   FULLY QUALIFIED NAME = 'public.prg_docufolders',
   COMMENT = 'Table prg_docufolders'
);

If the table doesn't have a primary key, first add one with the following command:

TABLE ADD KEY (
   NODE ID = 1,
   FULLY QUALIFIED NAME = 'public.prg_dates'
);

Beware that the name of the added primary key is generated automatically. I
encountered the problem, that if the table name is very long, the name of the
primary key column is even longer and may exceed the maximum column name length
of postgres. At least I believe, that this was the cause of the problem, that
some primary keys can't be added.
These primary keys are no problem for OX because they are added as last column
and they are filled by a default value. Hence, no SQL commands have to be rewritten.
Now after adding the auto generated primary keys you can add the table to the
replication with:

SET ADD TABLE (
   SET ID = 1,
   ORIGIN = 1,
   ID = 8,
   FULLY QUALIFIED NAME = 'public.prg_dates',
   COMMENT = 'Table prg_dates',
   KEY = SERIAL
);

Look at the extra line "KEY = SERIAL".
Now you are able to replicate the data of all tables to your slave. Btw. I wrote
myself a small script, that extracts the names of the tables of the database
initialization script, that have no primary key.

And beware of shutting down and starting up your master and slave hosts because
slony doesn't always deal correctly with this and replication simply stops.

> I would appreciate any help or documentation regarding these issues.
> 
> Thank you.

Best regards,
Marcus Klein