[pgpool-general: 4089] Re: Unable to configure replication with pgpool.
Tatsuo Ishii
ishii at postgresql.org
Tue Oct 6 08:23:42 JST 2015
> 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
More information about the pgpool-general
mailing list