[pgpool-general: 2854] Re: PGPool Nodes with different schemas

Tatsuo Ishii ishii at postgresql.org
Tue May 20 07:14:01 JST 2014

> Hi,
> I want to offload a write-heavy job from one of our Postgres DB servers.
> Currently, this job writes data to its own tables and has its own schema (let's call it schema1). 
> However, this job relies on metadata from another schema (call it schema2) within the same database.
> So basically, I want to replicate schema2 to another DB server and perform this write-heavy job on that new server. 
> I've done some initial testing using PGPool and it does support the configuration that I want.
> Config (3 servers to start with):
> 1. node1 (master) - Postgres DB with schema2 only
> 2. node2 (slave) - Postgres DB with both schema1 and schema2
> 3. PGPool server replicates schema2 data to both node1 and node2

I assume you are using pgpool-II's native replication mode.
(not PostgreSQL's streaming replication).

> My questions:
> 1. First, is PGPool be the best solution for this type of project?
> If not, could you recommend any other better/alternative solutions if any?

As far as I know slony is better suited for this kind of
purpose. Pgpool-II is not best solution because of your concern below #2.

> 2. How do I recover node2 in case of a failure with this configuration? 
> I believe the recovery process overwrites the entire database and schema1 would be lost.

There's no way to prevent the recovery process from overwriting the
whole database unless you write a very smart recovery script.

> 3. Would the write-heavy load on node2 impact the performance of PGPool replication to the master node1? 

No. However DB clients have to wait until writing to both master and
slave finishes.

Best regards,
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

More information about the pgpool-general mailing list