[pgpool-general: 4097] Re: Unable to configure replication with pgpool.
Tatsuo Ishii
ishii at postgresql.org
Thu Oct 8 10:15:52 JST 2015
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
More information about the pgpool-general
mailing list