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

[Mary Ellen Fitzpatrick]

I was using the following docs to get things working and both of these 
docs install pgpool on the same host as the primary server.  But it did 
not make sense for a production environment:  if the primary server 
crashes, so does pgpool... 

http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html
http://pgpool.projects.postgresql.org/contrib_docs/pgpool-II_for_beginners.pdf

So my web server application needs to send database requests to the 
pgpool head node, then it replicates to the primary and secondary 
database servers.

Got it,
Thanks


Tatsuo Ishii wrote:
> 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
>>     

-- 
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