[pgpool-general: 4098] Re: Unable to configure replication with pgpool.

Giles Gomes giles.gomes at retailephant.com
Thu Oct 8 16:54:23 JST 2015


Hi Tatsuo,

failover.log is totally blank. I have used following command.

failover_command = '/usr/local/etc/failover.sh 192.168.10.54 192.168.10.54
192.168.10.41 /var/log/pgpool'


Regards,
Giles.

On 8 October 2015 at 07:15, Tatsuo Ishii <ishii at postgresql.org> wrote:

> Giles,
>
> It would be helpful to take a log of failover command.
>
> For example,
>
> #!/bin/bash -x
> FALLING_NODE=$1         # %d
> OLDPRIMARY_NODE=$2      # %P
> NEW_PRIMARY=$3          # %H
> PGDATA=$4               # %R
>
> exec > /tmp/failover.log 2>&1 <--- add this
>
> if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then
> [snip]
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese:http://www.sraoss.co.jp
>
> > Dear Tatsuo,
> >
> > I have tried the  failover_command in various ways as following:
> >
> > 1. '/usr/local/etc/failover.sh %d %P "%H" %R'
> > 2. '/usr/local/etc/failover.sh %d %P "%H"  /var/log/pgpool'
> > 3. '/usr/local/etc/failover.sh 192.168.10.41 192.168.10.41 192.168.10.54
> > /var/log/pgpool'
> > 4. 'ssh postgres at 192.168.10.54 touch
> /var/log/pgpool/trigger/trigger_file1'
> >
> > Command 3 and 4 works if I execute them from command line. But none of
> > these works automatically when Primay Server is down.
> >
> > Best Regards,
> > Giles.
> >
> >
> > On 6 October 2015 at 15:39, Giles Gomes <giles.gomes at retailephant.com>
> > wrote:
> >
> >> Dear Tatsuo,
> >>
> >> Thanks for your quick response. I have edited the failover_command as
> >> following:
> >>
> >> '/usr/local/etc/failover.sh %d %P "%H" %R'
> >>
> >> But still it is not working :(
> >>
> >> Best regards,
> >> Giles.
> >>
> >> On 6 October 2015 at 05:23, Tatsuo Ishii <ishii at postgresql.org> wrote:
> >>
> >>> > Hi Tatsuo,
> >>> >
> >>> > Please help me to solve the following problems.
> >>> >
> >>> > *Problem:1*
> >>> > Replication is working properly after I added  'password=1234' at
> >>> > primary_conninfo
> >>> > parameter in recovery.conf, but Standby Server didn't started
> >>> automatically
> >>> > after clicking "Recovery" from pgpool. I have edited the path of
> PFCTL
> >>> > in pgpool_remote_start
> >>> > file, do I need to edit anything else there?
> >>> >
> >>> > *Problem:2*
> >>> > I have stopped the Primary Server, but autofailover didn't work. Here
> >>> are
> >>> > some logs from Standby Server when I had stopped the Primary Server.
> >>>
> >>>
> >>> You have: failover_command = '/usr/local/etc/failover.sh %d "%h" %p %D
> >>> %m %M "%H" %P' in pgpool.conf but your failover.sh does below. You are
> >>> comparing failing node id with failing hostname, which never succeeds.
> >>>
> >>> #!/bin/bash -x
> >>> FALLING_NODE=$1         # %d
> >>> OLDPRIMARY_NODE=$2      # %P
> >>> NEW_PRIMARY=$3          # %H
> >>> PGDATA=$4               # %R
> >>>
> >>> if [ $FALLING_NODE = $OLDPRIMARY_NODE ]; then
> >>> [snip]
> >>>
> >>> > -----------------------------------------------------------------
> >>> > 9381 2015-10-05 11:47:01 UTC STATEMENT:  INSERT INTO rep_test VALUES
> >>> > ('Shumi');
> >>> > 9381 2015-10-05 11:47:32 UTC LOG:  statement: select * from rep_test;
> >>> > 9381 2015-10-05 11:48:04 UTC LOG:  statement: select * from rep_test;
> >>> > 9521 2015-10-05 11:49:28 UTC LOG:  statement: select * from t1;
> >>> > 9521 2015-10-05 11:49:42 UTC LOG:  statement: select * from t1;
> >>> > 9357 2015-10-05 11:53:22 UTC LOG:  replication terminated by primary
> >>> server
> >>> > 9357 2015-10-05 11:53:22 UTC DETAIL:  End of WAL reached on timeline
> 1
> >>> at
> >>> > 0/4D000090.
> >>> > 9357 2015-10-05 11:53:22 UTC FATAL:  could not send end-of-streaming
> >>> > message to primary: no COPY in progress
> >>> > 9356 2015-10-05 11:53:22 UTC LOG:  invalid record length at
> 0/4D000090
> >>> > 9536 2015-10-05 11:53:28 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9537 2015-10-05 11:53:29 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9538 2015-10-05 11:53:30 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9539 2015-10-05 11:53:31 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9540 2015-10-05 11:53:32 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9542 2015-10-05 11:53:32 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9543 2015-10-05 11:53:33 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9544 2015-10-05 11:53:34 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9545 2015-10-05 11:53:35 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9546 2015-10-05 11:53:36 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9547 2015-10-05 11:53:37 UTC FATAL:  could not connect to the primary
> >>> > server: could not connect to server: Connection refused
> >>> > Is the server running on host "192.168.10.54" and accepting
> >>> > TCP/IP connections on port 5432?
> >>> > 9548 2015-10-05 11:53:37 UTC LOG:  statement: SELECT
> pg_is_in_recovery()
> >>> > 9550 2015-10-05 11:53:42 UTC FATAL:  could not connect to the primary
> >>> > server: could not connect to server: Connection refused
> >>> > Is the server running on host "192.168.10.54" and accepting
> >>> > TCP/IP connections on port 5432?
> >>> >
> >>>
> ---------------------------------------------------------------------------------------------------------------------------------------------
> >>> >
> >>> > Please check the attachment.
> >>> >
> >>> > Best regards,
> >>> > Giles.
> >>> >
> >>> >
> >>> > On 4 October 2015 at 11:36, Giles Gomes <
> giles.gomes at retailephant.com>
> >>> > wrote:
> >>> >
> >>> >> Hi Tatsuo,
> >>> >>
> >>> >> Thanks for your response. I have attached postgresql.conf of primary
> >>> and
> >>> >> recovery.conf of standby with this email.
> >>> >>
> >>> >> Regards,
> >>> >> Giles.
> >>> >>
> >>> >> On 3 October 2015 at 13:08, Tatsuo Ishii <ishii at postgresql.org>
> wrote:
> >>> >>
> >>> >>> Seems streaming replication is not working.  Standby requests a WAL
> >>> >>> segment but primary does not have it. Can you show me
> postgresql.conf
> >>> >>> of primary and recovery.conf of standby?
> >>> >>>
> >>> >>> Best regards,
> >>> >>> --
> >>> >>> Tatsuo Ishii
> >>> >>> SRA OSS, Inc. Japan
> >>> >>> English: http://www.sraoss.co.jp/index_en.php
> >>> >>> Japanese:http://www.sraoss.co.jp
> >>> >>>
> >>> >>> > Hi Tatsuo,
> >>> >>> >
> >>> >>> > I was able to minimize some errors at master (primary)
> PostgreSQL's
> >>> >>> log. I
> >>> >>> > have attached the new log with this email.
> >>> >>> >
> >>> >>> > Best regards,
> >>> >>> > Giles.
> >>> >>> >
> >>> >>> >
> >>> >>> > On 30 September 2015 at 18:59, Giles Gomes <
> >>> >>> giles.gomes at retailephant.com>
> >>> >>> > wrote:
> >>> >>> >
> >>> >>> >> Hi Tatsuo,
> >>> >>> >>
> >>> >>> >> The pgpool log, master (primary) PostgreSQL's log and
> basebackup,sh
> >>> >>> have
> >>> >>> >> been attached with this email.
> >>> >>> >>
> >>> >>> >> Best regards,
> >>> >>> >> Giles.
> >>> >>> >>
> >>> >>> >> On 28 September 2015 at 16:50, Giles Gomes <
> >>> >>> giles.gomes at retailephant.com>
> >>> >>> >> wrote:
> >>> >>> >>
> >>> >>> >>> Hi Tatsuo,
> >>> >>> >>>
> >>> >>> >>> The pgpool log, master (primary) PostgreSQL's log and
> >>> basebackup,sh
> >>> >>> have
> >>> >>> >>> been attached with this email.
> >>> >>> >>>
> >>> >>> >>> Best regards,
> >>> >>> >>> Giles.
> >>> >>> >>>
> >>> >>> >>> On 27 September 2015 at 11:06, Tatsuo Ishii <
> ishii at postgresql.org
> >>> >
> >>> >>> wrote:
> >>> >>> >>>
> >>> >>> >>>> pcp_recovery_node command failed for some reason.
> Unfortunately
> >>> the
> >>> >>> >>>> error code emitted by pgpoolAdmin is not very helpful. pgpool
> >>> log and
> >>> >>> >>>> master (primary) PostgreSQL's log when the command ran will be
> >>> >>> >>>> more helpful.
> >>> >>> >>>>
> >>> >>> >>>> Best regards,
> >>> >>> >>>> --
> >>> >>> >>>> Tatsuo Ishii
> >>> >>> >>>> SRA OSS, Inc. Japan
> >>> >>> >>>> English: http://www.sraoss.co.jp/index_en.php
> >>> >>> >>>> Japanese:http://www.sraoss.co.jp
> >>> >>> >>>>
> >>> >>> >>>> > Hi, how are you??
> >>> >>> >>>> > A query, you could solve this problem you had ??
> >>> >>> >>>> > I have the same error connection error pgpooladmin
> >>> >>> >>>> >
> >>> >>> >>>> >
> >>> >>> >>>> > 2015-09-20 11:29 GMT-03:00 Giles Gomes <
> >>> >>> giles.gomes at retailephant.com>:
> >>> >>> >>>> >
> >>> >>> >>>> >> Hello Tatsuo Ishii,
> >>> >>> >>>> >>
> >>> >>> >>>> >> After enabling health check parameters 'postgres' is
> showing
> >>> up
> >>> >>> now.
> >>> >>> >>>> But
> >>> >>> >>>> >> when I click on Recovery an error is generating.
> >>> >>> >>>> >> Error Code: e1012, Error Message: pcp_recovery_node command
> >>> error
> >>> >>> >>>> occurred and
> >>> >>> >>>> >> 'Stop', 'Restart', 'Reload' and 'Remove' buttons are still
> >>> >>> disabled.
> >>> >>> >>>> >>
> >>> >>> >>>> >> I have attached the screen shots and waiting for your help.
> >>> >>> >>>> >>
> >>> >>> >>>> >> Best regards,
> >>> >>> >>>> >> Giles.
> >>> >>> >>>> >>
> >>> >>> >>>> >>
> >>> >>> >>>> >> On 17 September 2015 at 19:52, Giles Gomes <
> >>> >>> >>>> giles.gomes at retailephant.com>
> >>> >>> >>>> >> wrote:
> >>> >>> >>>> >>
> >>> >>> >>>> >>> Hello Tatsuo Ishii,
> >>> >>> >>>> >>>
> >>> >>> >>>> >>> I was able to start the Standby Database and Successfully
> >>> >>> Installed
> >>> >>> >>>> >>> pgpoolAdmin. But 'postgres' is showing down and 'Stop',
> >>> >>> 'Restart',
> >>> >>> >>>> 'Reload'
> >>> >>> >>>> >>> and 'Remove' buttons are disabled.
> >>> >>> >>>> >>>
> >>> >>> >>>> >>> pgpool.log and the screen shot is attached with this
> email.
> >>> >>> >>>> >>>
> >>> >>> >>>> >>> --
> >>> >>> >>>> >>> Best regards,
> >>> >>> >>>> >>> Giles Cornelius Gomes
> >>> >>> >>>> >>> Database Administrator
> >>> >>> >>>> >>> Impel IT Solutions Ltd.
> >>> >>> >>>> >>> Phone: +88 01 714329389
> >>> >>> >>>> >>>
> >>> >>> >>>> >>> On 16 September 2015 at 21:24, Giles Gomes <
> >>> >>> >>>> giles.gomes at retailephant.com>
> >>> >>> >>>> >>> wrote:
> >>> >>> >>>> >>>
> >>> >>> >>>> >>>> Hi Tatsuo,
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> Thanks for your response. install-functions.sh has worked
> >>> >>> properly
> >>> >>> >>>> >>>> after installing postgresql-server-dev-9.3.
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> I was also able to run basebackup.sh, although "mkdir:
> >>> cannot
> >>> >>> create
> >>> >>> >>>> >>>> directory ‘/var/lib/postgresql/9.3/main/pg_xlog’: File
> >>> exists"
> >>> >>> is
> >>> >>> >>>> showing
> >>> >>> >>>> >>>> while running this as following:
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>>
> >>> >>>
> >>>
> -------------------------------------------------------------------------------------------
> >>> >>> >>>> >>>> postgres at osspc17:~/9.3/main$ ./basebackup.sh
> >>> >>> >>>> >>>>  pg_start_backup
> >>> >>> >>>> >>>> -----------------
> >>> >>> >>>> >>>>  0/1C000028
> >>> >>> >>>> >>>> (1 row)
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> mkdir: cannot create directory
> >>> >>> >>>> ‘/var/lib/postgresql/9.3/main/pg_xlog’:
> >>> >>> >>>> >>>> File exists
> >>> >>> >>>> >>>> mytemp23915
> >>> >>> >>>> >>>>
> >>> >>>  100%
> >>> >>> >>>> 153
> >>> >>> >>>> >>>> 0.2KB/s   00:00
> >>> >>> >>>> >>>> NOTICE:  pg_stop_backup complete, all required WAL
> segments
> >>> have
> >>> >>> >>>> been
> >>> >>> >>>> >>>> archived
> >>> >>> >>>> >>>>  pg_stop_backup
> >>> >>> >>>> >>>> ----------------
> >>> >>> >>>> >>>>  0/1C0000F0
> >>> >>> >>>> >>>> (1 row)
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>>
> >>> >>>
> >>>
> --------------------------------------------------------------------------------------------
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> After running this script I have restarted the standby
> >>> database
> >>> >>> as
> >>> >>> >>>> >>>> following:
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> $ service postgresql restart
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> After restarting the database I am unable to login to
> >>> database.
> >>> >>> >>>> >>>> following error is showing:
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>>
> >>> >>>
> >>>
> --------------------------------------------------------------------------
> >>> >>> >>>> >>>> postgres at osspc18:~$ psql
> >>> >>> >>>> >>>> psql: FATAL:  the database system is starting up
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>>
> >>> >>>
> >>>
> --------------------------------------------------------------------------
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> What should I do now?
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> Best regards,
> >>> >>> >>>> >>>> Giles.
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> On 16 September 2015 at 04:59, Tatsuo Ishii <
> >>> >>> ishii at postgresql.org>
> >>> >>> >>>> >>>> wrote:
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>> > Hi Tatsuo,
> >>> >>> >>>> >>>>> >
> >>> >>> >>>> >>>>> > Thanks for your quick response. I was trying to
> configure
> >>> >>> >>>> replication
> >>> >>> >>>> >>>>> with
> >>> >>> >>>> >>>>> > pgpool II between three VM.
> >>> >>> >>>> >>>>> > 1. Pgpool Server. where pgpool-II-3.4.3 is installed
> .I
> >>> wish
> >>> >>> to
> >>> >>> >>>> >>>>> install
> >>> >>> >>>> >>>>> > Pgpool Admin here also.
> >>> >>> >>>> >>>>> > 2. Primary Server, where  PostgreSQL 9.3.9 is
> installed.
> >>> >>> >>>> >>>>> > 3. Standby Server, where  PostgreSQL 9.3.9 is
> installed.
> >>> >>> >>>> >>>>> >
> >>> >>> >>>> >>>>> > The main problem is I didn't get any complete document
> >>> where
> >>> >>> all
> >>> >>> >>>> the
> >>> >>> >>>> >>>>> > necessary steps are described. Last time I was
> following
> >>> the
> >>> >>> >>>> document
> >>> >>> >>>> >>>>> below.
> >>> >>> >>>> >>>>> >
> >>> >>> >>>> >>>>> >
> >>> >>> >>>>
> >>> >>>
> http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/
> >>> >>> >>>> >>>>> >
> >>> >>> >>>> >>>>> > Here they told to run a script named
> install-functions.sh
> >>> >>> >>>> >>>>> >
> >>> >>> >>>> >>>>> > But following errors are showing while running it.
> >>> >>> >>>> >>>>> >
> >>> >>> >>>> >>>>>
> >>> >>> >>>>
> >>> >>>
> >>>
> --------------------------------------------------------------------------------------------------------------------
> >>> >>> >>>> >>>>> > postgres at osspc16:/home/vagrant$
> ./install-functions.sh
> >>> >>> >>>> >>>>> > Makefile:19:
> >>> /usr/lib/postgresql/9.3/lib/pgxs/src/makefiles/
> >>> >>> >>>> pgxs.mk:
> >>> >>> >>>> >>>>> No
> >>> >>> >>>> >>>>> > such fil
> >>> >>> >>>> >>>>> >                      e or directory
> >>> >>> >>>> >>>>> > make: *** No rule to make target
> >>> >>> >>>> >>>>>
> >>> >>> >>>> >>>>> Seems like a PostgreSQL RPM installation problem, rather
> >>> than a
> >>> >>> >>>> >>>>> pgpool-II installation problem. Have you installed
> >>> >>> postgresql-devel
> >>> >>> >>>> >>>>> package?
> >>> >>> >>>> >>>>>
> >>> >>> >>>> >>>>> Best regards,
> >>> >>> >>>> >>>>> --
> >>> >>> >>>> >>>>> Tatsuo Ishii
> >>> >>> >>>> >>>>> SRA OSS, Inc. Japan
> >>> >>> >>>> >>>>> English: http://www.sraoss.co.jp/index_en.php
> >>> >>> >>>> >>>>> Japanese:http://www.sraoss.co.jp
> >>> >>> >>>> >>>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>>
> >>> >>> >>>> >>>> --
> >>> >>> >>>> >>>> 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
> >>> >>> >>>> >>
> >>> >>> >>>> >> _______________________________________________
> >>> >>> >>>> >> 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
> >>> >>>
> >>> >>
> >>> >>
> >>> >>
> >>> >> --
> >>> >> 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
> >>
> >
> >
> >
> > --
> > 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/20151008/d194ddb5/attachment-0001.html>


More information about the pgpool-general mailing list