[pgpool-general: 4092] Re: Unable to configure replication with pgpool.
Giles Gomes
giles.gomes at retailephant.com
Tue Oct 6 18:39:39 JST 2015
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151006/22f8fa7e/attachment-0001.html>
More information about the pgpool-general
mailing list