On 9/3/2007 2:19 PM, Mason Hale wrote:
> Hello everyone --
> 
> I'm planning to use Slony divide work across two postgres servers.
> 
> Our environment is such that there are two mostly separate pieces,
> each updated by a separate application. The data that is updated by
> the first application is only read by the second application and data
> updated by the second application is only read by the first
> application.
> 
> Slony looks like a good solution to our needs, as we can replicate one
> set of tables in each direction between a first and second database
> server.
> 
> We're running 8.2.4 and our db is ~150GB in size (and growing fast).
> 
> We are using the table partitioning features of Postgres to break up
> our larger tables and keep indexes in RAM.
> 
> My questions are:
> 
> - In slony, when replicating inherited/partitioned tables, do you
> replicate each child table individually? Or can you add the slony
> triggers to the parent table? (Because some of these tables are
> partitioned based on a creation date, we end up adding new tables
> frequently).

Which tuple or update hits which table is decided by Postgres in layers 
that are far above the trigger mechanism Slony uses to record the 
changes. And those triggers have absolutely no information about how 
that operation got there, so it is impossible to replay the entire 
decision process on the replica. For example, an insert into a child 
table could have been redirected to it because a trigger or rule on the 
parent table did so, or the application might have inserted directly 
into the child ... how to tell the difference from inside a trigger?

With that background information, you will have to replicate each child 
table and the parent too (in case you don't have a default rule and rows 
can possibly end up in it).

> 
> - Is it possible to replicate from a partitioned table to a
> non-partitioned table?

No. The table structure of a slony replica is 1:1 identical to that of 
the origin.

> 
> - Is it possible to replicate from a partitioned table to table that
> is partitioned differently?

Same as above.

> 
> The access patterns of our two applications are quite different. One
> application would benefit partitioning based primary key, while the
> other would be better off partitioning the same table based on
> creation date of the row. Granted there is high correlation between
> the primary key and creation date, but the actual queries use one or
> the other.
> 
> So would it be possible to create a table in the first database
> partitioned by serial primary key, and replicate that table (and its
> child tables) to a second database where the same table is partitioned
> based on creation date?

Same as third question.


Sorry
Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck at Yahoo.com #