<div dir="ltr">I have followed the following steps to solve this problem.<div><br><div><br></div><div><b>a) Modification in pool_hba.conf</b><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">To connect Primary Server through pgpool we need to configure pool_hba.conf</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">There is a sample file at /usr/local/etc at osspc16</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">To copy the file run following command as root user:</p><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># cd /usr/local/etc/</pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># cp pool_hba.conf.sample pool_hba.conf</pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># vim pool_hba.conf</pre><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">Add following line below this line, # TYPE DATABASE USER CIDR-ADDRESS METHOD</p><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px">host all all <a href="http://10.1.1.0/24">10.1.1.0/24</a> md5</pre><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">And change the method of other connection from trust to md5.</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">Then run the following commands:</p><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># chown apache:apache pool_hba.conf</pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># chmod 777 pool_hba.conf</pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"><br></pre><b>b) Modification in pgpool.conf</b><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># cd /usr/local/etc/</pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># vim pgpool.conf</pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px">enable_pool_hba=on</pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"><br></pre><b>c) Modification in pool_passwd</b><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">To make sure that pool_passwd file has appropriate entry:</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">Login to to Primary Server Database,</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">Please note that the user name and password must be identical to those registered in PostgreSQL.</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">In my case I have used apache user. So run the following command to change the password at postgres database in Primary Server.</p><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># alter user apache with password '<password>'; </pre><pre style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"># alter role apache superuser;</pre><pre class="" style="margin-top:10px;margin-bottom:0px;padding:0px;font-family:ConfluenceInstalledFont,monospace;color:rgb(51,51,51);font-size:14px;line-height:22.2222px"><code><span class="" style="color:rgb(0,0,139)"># select</span><span class="" style="color:black"> passwd </span><span class="" style="color:rgb(0,0,139)">from</span><span class="" style="color:black"> pg_shadow </span><span class="" style="color:rgb(0,0,139)">where</span><span class="" style="color:black"> usename ='apache';</span></code></pre><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">Now copy the encrypted password including md5 and paste it in pool_passwd as following:</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">apache:<encrypted_password></p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">restart pgpool after all configuration is done.</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">Then connect the Primary Server Database with following command:</p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">psql -h <hostname> -U apache -p 9999 -W <database_name></p><p style="margin:10px 0px 0px;padding:0px;color:rgb(51,51,51);font-family:Arial,sans-serif;font-size:14px;line-height:22.2222px">Here 9999 is pgpool port.</p></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 1 February 2016 at 10:15, Giles Gomes <span dir="ltr"><<a href="mailto:giles.gomes@retailephant.com" target="_blank">giles.gomes@retailephant.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Hi Kiet,<div><br></div><div>I have solved the problem. Thanks for you cooperation.</div><div><br></div><div>Regards,</div><div>Giles.</div></div><div class="HOEnZb"><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On 1 February 2016 at 09:59, kiet.h.lieng <span dir="ltr"><<a href="mailto:kiet.h.lieng@gmail.com" target="_blank">kiet.h.lieng@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word">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.<div><br></div><div>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.</div><div><div><br></div><div><br></div><div>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.</div><div><br></div><div><br></div><div>Hope that helps.</div><div><div><div><br><div><blockquote type="cite"><div>On Jan 31, 2016, at 1:28 AM, Giles Gomes <<a href="mailto:giles.gomes@retailephant.com" target="_blank">giles.gomes@retailephant.com</a>> wrote:</div><br><div><div dir="ltr">Hi Keit,<div><br></div><div>Thanks for you reply. I have tried in your way but following error is showing both with 'apache' and 'postgres' user.</div><div><br></div><div><div>$ psql -h test001pgp01 -U apache -p 9999 -W postgres</div><div>Password for user apache:</div><div>psql: ERROR: MD5 authentication is unsupported in replication, master-slave and parallel modes.</div><div>HINT: check pg_hba.conf</div><div><br></div><div>$ psql -h test001pgp01 -U postgres -p 9999 -W postgres</div><div>Password for user postgres:</div><div>psql: ERROR: MD5 authentication is unsupported in replication, master-slave and parallel modes.</div><div>HINT: check pg_hba.conf</div></div><div><br></div><div>Regards,</div><div>Giles.</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 30 January 2016 at 05:22, kiet.h.lieng <span dir="ltr"><<a href="mailto:kiet.h.lieng@gmail.com" target="_blank">kiet.h.lieng@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div style="word-wrap:break-word"><div>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.</div><div><br></div><div>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).</div><div><br></div><div>Something like "psql -h pgpoolhostname -U someuser -p 9999 -W postgres” where 9999 is the port of the pgpool connection.</div><div><br></div><div>Let me know if this is not clear enough and I’ll try to explain it better. </div><div><br></div><div><br></div><div>Thanks.</div><div><div><div><br></div><br><div><blockquote type="cite"><div>On Jan 28, 2016, at 5:52 AM, Dang Minh Huong <<a href="mailto:kakalot49@gmail.com" target="_blank">kakalot49@gmail.com</a>> wrote:</div><br><div><div style="word-wrap:break-word">Hi,<div><br></div><div><span style="font-family:HelveticaNeue">You can connect to database </span>through pgpool-II with<font face="HelveticaNeue"> port which is defined in pgpool.conf (parameter “port").</font></div><div><span style="font-family:HelveticaNeue">Take a look at manual, there are several parameters you also need to configure too.</span><div style="font-family:HelveticaNeue"><br></div><div style="font-family:HelveticaNeue"><a href="http://www.pgpool.net/docs/latest/pgpool-en.html#config" target="_blank">http://www.pgpool.net/docs/latest/pgpool-en.html#config</a></div><div style="font-family:HelveticaNeue"><br></div><div style="font-family:HelveticaNeue">Best regards,</div><div style="font-family:HelveticaNeue">bocap</div><div><div><br><div><blockquote type="cite"><div>On Jan 28, 28 Heisei, at 17:46, Giles Gomes <<a href="mailto:giles.gomes@retailephant.com" target="_blank">giles.gomes@retailephant.com</a>> wrote:</div><br><div><div dir="ltr">Hello Tatsuo,<div><br></div><div>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.</div><div><br></div><div>Please let me know how I can connect postgresql database through pgpool.<br clear="all"><div><br></div>-- <br><div><div dir="ltr"><div><div dir="ltr">Best regards,<div>Giles Cornelius Gomes</div><div>Database Administrator</div><div>Impel IT Solutions Ltd.</div><div>Phone: +88 01 714329389 </div></div></div></div></div>
</div></div>
_______________________________________________<br>pgpool-general mailing list<br><a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br><a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br></div></blockquote></div><br></div></div></div></div>_______________________________________________<br>pgpool-general mailing list<br><a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br><a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br></div></blockquote></div><br></div></div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div><div dir="ltr"><div><div dir="ltr">Best regards,<div>Giles Cornelius Gomes</div><div>Database Administrator</div><div>Impel IT Solutions Ltd.</div><div>Phone: +88 01 714329389 </div></div></div></div></div>
</div>
</div></blockquote></div><br></div></div></div></div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div><div dir="ltr"><div><div dir="ltr">Best regards,<div>Giles Cornelius Gomes</div><div>Database Administrator</div><div>Impel IT Solutions Ltd.</div><div>Phone: +88 01 714329389 </div></div></div></div></div>
</div>
</div></div></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature"><div dir="ltr"><div><div dir="ltr">Best regards,<div>Giles Cornelius Gomes</div><div>Database Administrator</div><div>Impel IT Solutions Ltd.</div><div>Phone: +88 01 714329389 </div></div></div></div></div>
</div>