[pgpool-general: 4379] Re: How can I connect PostgreSQL database tthough pgpool?

[Giles Gomes]

I have followed the following steps to solve this problem.


*a) Modification in pool_hba.conf*

To connect Primary Server through pgpool we need to configure pool_hba.conf

There is a sample file at /usr/local/etc at osspc16

To copy the file run following command as root user:

# cd /usr/local/etc/

# cp pool_hba.conf.sample pool_hba.conf

# vim pool_hba.conf

Add following line below this line, # TYPE  DATABASE    USER
 CIDR-ADDRESS          METHOD

host    all         all          10.1.1.0/24            md5

And change the method of other connection from trust to md5.

Then run the following commands:

# chown apache:apache pool_hba.conf

# chmod 777 pool_hba.conf


*b) Modification in pgpool.conf*

# cd /usr/local/etc/

# vim pgpool.conf

enable_pool_hba=on


*c) Modification in pool_passwd*

To make sure that pool_passwd file has appropriate entry:

Login to to Primary Server Database,

Please note that the user name and password must be identical to those
registered in PostgreSQL.

In my case I have used apache user. So run the following command to change
the password at postgres database in Primary Server.

# alter user apache with password '<password>';

# alter role apache superuser;

# select passwd from pg_shadow where usename ='apache';

Now copy the encrypted password including md5 and paste it in pool_passwd
as following:

apache:<encrypted_password>

restart pgpool after all configuration is done.

Then connect the Primary Server Database with following command:

psql -h <hostname> -U apache -p 9999 -W <database_name>

Here 9999 is pgpool port.

On 1 February 2016 at 10:15, Giles Gomes <giles.gomes at retailephant.com>
wrote:

> Hi Kiet,
>
> I have solved the problem. Thanks for you cooperation.
>
> Regards,
> Giles.
>
> On 1 February 2016 at 09:59, kiet.h.lieng <kiet.h.lieng at gmail.com> wrote:
>
>> The hint is accurate, it’s definitely an access issue.  pg_hba.conf files
>> deserve another look at for both your master and slave nodes.  Try to
>> connect from your pgpool server to each postgres server databases
>> separately with port 5432 (regular psql connection) to see if it works.
>>
>> If that works properly I’d tail both the pgpool and the postgres
>> connection to see if there’s any errors that popup when you try a psql
>> connection via port 9999.
>>
>>
>> If you haven’t already start pgpool with error output logs.  I have my
>> pool start command alias to “/usr/local/bin/pgpool -f
>> /usr/local/etc/pgpool.conf -F /usr/local/etc/pcp.conf -n -d >
>> /tmp/pgpool.log 2>&1 &”.  This way I can tail the errors properly.
>>
>>
>> Hope that helps.
>>
>> On Jan 31, 2016, at 1:28 AM, Giles Gomes <giles.gomes at retailephant.com>
>> wrote:
>>
>> Hi Keit,
>>
>> Thanks for you reply. I have tried in your way but following error is
>> showing both with 'apache' and 'postgres' user.
>>
>> $ psql -h test001pgp01 -U apache -p 9999 -W postgres
>> Password for user apache:
>> psql: ERROR:  MD5 authentication is unsupported in replication,
>> master-slave and parallel modes.
>> HINT:  check pg_hba.conf
>>
>> $ psql -h test001pgp01 -U postgres -p 9999 -W postgres
>> Password for user postgres:
>> psql: ERROR:  MD5 authentication is unsupported in replication,
>> master-slave and parallel modes.
>> HINT:  check pg_hba.conf
>>
>> Regards,
>> Giles.
>>
>> On 30 January 2016 at 05:22, kiet.h.lieng <kiet.h.lieng at gmail.com> wrote:
>>
>>> Just to expand on this PGPool is categorized as “middleware” this is the
>>> correct term but it’s not constructive on how it’s used and how you’re
>>> supposed to interact with it.  Just that it sits between the client and
>>> actual database.
>>>
>>> To put it plainly: pgpool lives “inside” postgres and the only way you
>>> interact with pgpool is through the same postgresql connection call BUT
>>> with the pgpool port number specified in your pgpool.conf file (search for
>>> port setting).
>>>
>>> Something like "psql -h pgpoolhostname -U someuser -p 9999 -W postgres”
>>> where 9999 is the port of the pgpool connection.
>>>
>>> Let me know if this is not clear enough and I’ll try to explain it
>>> better.
>>>
>>>
>>> Thanks.
>>>
>>>
>>> On Jan 28, 2016, at 5:52 AM, Dang Minh Huong <kakalot49 at gmail.com>
>>> wrote:
>>>
>>> Hi,
>>>
>>> You can connect to database through pgpool-II with port which is
>>> defined in pgpool.conf (parameter “port").
>>> Take a look at manual, there are several parameters you also need to
>>> configure too.
>>>
>>> http://www.pgpool.net/docs/latest/pgpool-en.html#config
>>>
>>> Best regards,
>>> bocap
>>>
>>> On Jan 28, 28 Heisei, at 17:46, Giles Gomes <
>>> giles.gomes at retailephant.com> wrote:
>>>
>>> Hello Tatsuo,
>>>
>>> I have install pgpool and pgpoolAdmin in one server and there is two
>>> database servers under this pgpool server, one working as Primary and
>>> another as Standby. Replication is working fine.
>>>
>>> Please let me know how I can connect postgresql database through pgpool.
>>>
>>> --
>>> Best regards,
>>> Giles Cornelius Gomes
>>> Database Administrator
>>> Impel IT Solutions Ltd.
>>> Phone: +88 01 714329389
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
>>>
>>> _______________________________________________
>>> pgpool-general mailing list
>>> pgpool-general at pgpool.net
>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>>
>>>
>>>
>>
>>
>> --
>> Best regards,
>> Giles Cornelius Gomes
>> Database Administrator
>> Impel IT Solutions Ltd.
>> Phone: +88 01 714329389
>>
>>
>>
>
>
> --
> Best regards,
> Giles Cornelius Gomes
> Database Administrator
> Impel IT Solutions Ltd.
> Phone: +88 01 714329389
>



-- 
Best regards,
Giles Cornelius Gomes
Database Administrator
Impel IT Solutions Ltd.
Phone: +88 01 714329389
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20160201/b267de6c/attachment.html>