[Pgpool-general] configure replication with pgpool installed on separate host

[Tatsuo Ishii]

Welcome to pgpool world:-)

> Fairly new to pgpool and trying to get replication working with pgpool
> installed on a separate host.   My setup consists of two identical
> database systems (hostname0:wenders and hostname1:bioinfodb) and a
> pgpool on a separate system (kaya1).  Running postgres-8.4.3 on all
> systems, and pgpoolII-3 on the pgpool head node.  pgpool is running
> with
> the -d option and pgpool.conf info below.   Ports 5432, 9999, and 9898
> are open on all systems.
> 
> When I test replication, it will only work if I create databases from
> the pgpool head node and then replicates to both database systems.
> What I want to have happen is to create a database on the primary
> server
> and have it replicate to the secondary server.
> Is this possible, or do I need to install pgpool on one of database
> servers to get it work the way I want?

Pgpool is kind of proxy server. That means to get your databases
replicated, you need to send *all* SQL to pgpool. I don't know why you
do something like on your primary server wenders:

createdb -p 9999 -h kaya1 ...

But if you prefer not to use -h option on wenders, you could start
second pgpool server on wenders. In this case the pgpool.conf file
must be identical to the one on kaya1.

Hope this helps.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> I believe I have an error in my configuration, etc. or my
> understanding
> of how replication works with when pgpool is installed on a separate
> host.
> 
> primary server (wenders):
> bash-3.2$ createdb -p 9999 bench_replication
> createdb: could not connect to database postgres: could not connect to
> server: No such file or directory
>          Is the server running locally and accepting
>          connections on Unix domain socket "/tmp/.s.PGSQL.9999"?
> 
> pgpool head node(kaya1):
> bash-3.2$ createdb -p 9999 bench_replication
> replicates to both primary and secondary servers
> 
> Here is the relevant pgpool.conf info:
>   listen_addresses = '*'
> port = 9999
> pcp_port = 9898
> replication_mode = true
> load_balance_mode = false
> reset_query_list = 'ABORT; DISCARD ALL'
> failover_command = 'echo host:%h, new master id:%m, old master id%M >
> /usr/local/pgpoolII/log/failover.log'
> failback_command = 'echo host:%h, new master id:%m, old master id:%M >
> /usr/local/pgpoolII/log/failback.log'
> pgpool2_hostname = 'kaya1'
> system_db_hostname = 'localhost'
> system_db_port = 5432
> system_db_dbname = 'pgpool'
> system_db_schema = 'pgpool_catalog'
> system_db_user = 'pgpool'
> system_db_password = ''
> backend_hostname0 = 'wenders'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/database/pgsql/data'
> backend_hostname1 = 'bioinfodb'
> backend_port1 = 5432
> backend_weight1 = 1
> backend_data_directory1 = '/database/pgsql/data'
> enable_pool_hba = false
> 
> 
> 
> -- 
> Thanks
> Mary Ellen
> 
> 
> Mary Ellen FitzPatrick
> Systems Analyst
> Bioinformatics
> Boston University
> 24 Cummington St.
> Boston, MA 02215
> office 617-358-2771
> cell 617-797-7856
> mfitzpat at bu.edu
> 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general