[pgpool-general: 4088] Re: Unable to configure replication with pgpool.
Giles Gomes
giles.gomes at retailephant.com
Mon Oct 5 22:18:58 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.
-----------------------------------------------------------------
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151005/3405ca86/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: failover.sh
Type: application/x-sh
Size: 383 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151005/3405ca86/attachment-0001.sh>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool.conf
Type: application/octet-stream
Size: 33808 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151005/3405ca86/attachment-0002.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgpool_remote_start
Type: application/octet-stream
Size: 295 bytes
Desc: not available
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20151005/3405ca86/attachment-0003.obj>
More information about the pgpool-general
mailing list