[pgpool-general: 4096] Re: Unable to configure replication with pgpool.
Giles Gomes
giles.gomes at retailephant.com
Wed Oct 7 23:48:29 JST 2015
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151007/e3fe5edb/attachment-0001.html>
More information about the pgpool-general
mailing list