[pgpool-general: 4649] Re: pg_basebackup with streaming replication option over pgpool

Tatsuo Ishii ishii at postgresql.org
Wed Apr 20 14:02:47 JST 2016


> Hey Tatsuo, cool, thanks for the confirmation. My workaround for now is to
> contact postgresql on the virtual IP, but on the postgresql port (not the
> pgpool one) for all things streaming replication, to avoid problems. I
> guess I have a couple of questions:

Sure.

> 1) Now that you have seen the issue, is this something that could be
> expected to happen for regular replication connections, or just for
> pg_basebackup with SR? Right now I am bypassing pgpool for all things
> replication.

I guess the issue could happen for all replication things in
theory. However the only case pgpool-II have take care of replication
protocols I can think of is, pg_basebackup. There may be third party
tools which use the protocol though.

Please note that reapplication things between the primary and standbys
are not affected by the issue, since in that case primary and standbys
talk each other directly.

> 2) What log level did you use to get those logs?

The default. Since those messages are in "LOG" level, you should be
able to see them in the default log level.

> 3) I have been playing with this last version of pgpool for a while, and
> found a couple of scenarios that don't behave in a sane way IMHO. Would it
> be better to post them here or just try to open bug requests?

Either ways are ok. Probably the bug track is proffered since it's
easy to track down.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> Thanks!
> Regards
> On Apr 19, 2016 11:07 PM, "Tatsuo Ishii" <ishii at postgresql.org> wrote:
> 
>> Yeah, I got same errors. I see followings in the pgpool log. It seems
>> pgpool-II was confused by the special replication commands. I will
>> look into this when I have time (I'm busy for now).
>>
>> 2016-04-20 11:02:07: pid 24241: LOG:  Unable to parse the query:
>> "IDENTIFY_SYSTEM" from local client
>> 2016-04-20 11:02:07: pid 24241: LOG:  DB node id: 0 backend pid: 24257
>> statement: IDENTIFY_SYSTEM
>> 2016-04-20 11:02:07: pid 24241: LOG:  Unable to parse the query:
>> "BASE_BACKUP LABEL 'pg_basebackup base backup'    NOWAIT  " from local
>> client
>> 2016-04-20 11:02:07: pid 24241: LOG:  DB node id: 0 backend pid: 24257
>> statement: BASE_BACKUP LABEL 'pg_basebackup base backup'    NOWAIT
>> 2016-04-20 11:02:07: pid 24242: LOG:  Unable to parse the query:
>> "IDENTIFY_SYSTEM" from local client
>> 2016-04-20 11:02:07: pid 24242: LOG:  DB node id: 0 backend pid: 24260
>> statement: IDENTIFY_SYSTEM
>> 2016-04-20 11:02:07: pid 24242: LOG:  Unable to parse the query:
>> "TIMELINE_HISTORY 3" from local client
>> 2016-04-20 11:02:07: pid 24242: LOG:  DB node id: 0 backend pid: 24260
>> statement: TIMELINE_HISTORY 3
>> 2016-04-20 11:02:07: pid 24242: LOG:  Unable to parse the query:
>> "START_REPLICATION 0/25000000 TIMELINE 3" from local client
>> 2016-04-20 11:02:07: pid 24242: LOG:  DB node id: 0 backend pid: 24260
>> statement: START_REPLICATION 0/25000000 TIMELINE 3
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > Hey Tatsuo, thanks!
>> > I think you are not using streaming replication for your backup (
>> -X=stream
>> > option). That is what fails for me. Regular pg_basebackup works for me
>> too.
>> > the advantage of the stream option is that it doesn't require your you to
>> > stop transactions to get to the same points. The logs I see are on the
>> > first email. I tried detailed logs too, but did not see any information.
>> >
>> > RegardS!
>> > On Apr 19, 2016 7:52 PM, "Tatsuo Ishii" <ishii at postgresql.org> wrote:
>> >
>> > Oh, I misunderstood your intention. You just want to create a backup
>> > using pg_basebackup via pgpool-II. That should work. In fact it worked
>> > for me (in an environment created by pgpool_setup). Did you see any
>> > errors in the pgpool log?
>> >
>> > $ pg_basebackup -p 11000 -D data
>> > NOTICE:  pg_stop_backup complete, all required WAL segments have been
>> > archived
>> >
>> > $ ls -l data/
>> > 合計 136
>> > -rw------- 1 t-ishii t-ishii     4  4月 20 07:46 PG_VERSION
>> > -rw------- 1 t-ishii t-ishii   208  4月 20 07:46 backup_label
>> > -rw------- 1 t-ishii t-ishii   209  4月 20 07:46 backup_label.old
>> > drwx------ 6 t-ishii t-ishii  4096  4月 20 07:46 base
>> > -rwxr-xr-x 1 t-ishii t-ishii   939  4月 20 07:46 basebackup.sh
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 global
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_clog
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_commit_ts
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_dynshmem
>> > -rw------- 1 t-ishii t-ishii  4462  4月 20 07:46 pg_hba.conf
>> > -rw------- 1 t-ishii t-ishii  1636  4月 20 07:46 pg_ident.conf
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_log
>> > drwx------ 4 t-ishii t-ishii  4096  4月 20 07:46 pg_logical
>> > drwx------ 4 t-ishii t-ishii  4096  4月 20 07:46 pg_multixact
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_notify
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_replslot
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_serial
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_snapshots
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_stat
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_stat_tmp
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_subtrans
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_tblspc
>> > drwx------ 2 t-ishii t-ishii  4096  4月 20 07:46 pg_twophase
>> > drwx------ 3 t-ishii t-ishii  4096  4月 20 07:46 pg_xlog
>> > -rwxr-xr-x 1 t-ishii t-ishii   284  4月 20 07:46 pgpool_remote_start
>> > -rw------- 1 t-ishii t-ishii    88  4月 20 07:46 postgresql.auto.conf
>> > -rw------- 1 t-ishii t-ishii 22074  4月 20 07:46 postgresql.conf
>> > -rw------- 1 t-ishii t-ishii   113  4月 20 07:46 recovery.done
>> >
>> >
>> >> Hello Tatsuo, thanks again for the answer:
>> >>
>> >> How is pgpool-II supposed to work with pg_basebackup (and i guess,
>> >> streaming replication in general then).
>> >> The way i implemented my solution, i try to connect to the database
>> always
>> >> through pgpool-II, to make things more uniform.
>> >> Since pg_basebackup is not working through pgpool-II(using streaming
>> >> replication) i guess ill have to use it directly on postgres port, but
>> > that
>> >> also makes me wonder, would regular streaming replication connections
>> work
>> >> if done through pgpool-II or could i face the same problems, and i'd be
>> >> better connecting directly to the master?
>> >>
>> >> Thanks.
>> >> Regards
>> >>
>> >> On Tue, Apr 19, 2016 at 12:58 AM, Tatsuo Ishii <ishii at postgresql.org>
>> > wrote:
>> >>
>> >>> Well, pgpool-II is not supoosed to work with pg_basebackup in such a
>> >>> way.
>> >>>
>> >>> Best regards,
>> >>> --
>> >>> Tatsuo Ishii
>> >>> SRA OSS, Inc. Japan
>> >>> English: http://www.sraoss.co.jp/index_en.php
>> >>> Japanese:http://www.sraoss.co.jp
>> >>>
>> >>> > Hey Tatsuo, thanks for the answer.
>> >>> >
>> >>> > I am actually running pg_basebackup from the command line.
>> >>> >
>> >>> > If i run it through pgpool (which listens in port 9999):
>> >>> >
>> >>> > pg_basebackup -X stream -h myhost -p 9999 -D /var/lib/pgsql/9.5/data
>> -U
>> >>> > replication -v -P
>> >>> >
>> >>> > It fails with the mentioned descriptions.
>> >>> >
>> >>> > If i run it to port 5432 (where postgresql backend listens) It works.
>> >>> >
>> >>> > That is the reason i posted question in this list, i only seem to be
>> >>> having
>> >>> > this issue when going through pgpool.
>> >>> >
>> >>> > Any pointers on how to troubleshoot the issue would be appreciated.
>> >>> >
>> >>> >
>> >>> > Thanks a lot!
>> >>> >
>> >>> > Regards
>> >>> >
>> >>> >
>> >>> > On Apr 18, 2016 10:27 PM, "Tatsuo Ishii" <ishii at postgresql.org>
>> wrote:
>> >>> >
>> >>> >> I assume you invoke pg_basebackup from recovery_1st_stage_command.
>> >>> >>
>> >>> >> If pg_basebackup succeeds when you invoke it by hand, there's
>> >>> possibility
>> >>> >> that:
>> >>> >>
>> >>> >> - the difference between process owner. recovery_1st_stage_command
>> is
>> >>> >>   executed by the same owner as pgpool.
>> >>> >>
>> >>> >> - wrong parameter passed to pg_basebackup. You can log the
>> parameters
>> >>> >>   for pg_basebackup by tweaking recovery_1st_stage_command.
>> >>> >>
>> >>> >> Best regards,
>> >>> >> --
>> >>> >> Tatsuo Ishii
>> >>> >> SRA OSS, Inc. Japan
>> >>> >> English: http://www.sraoss.co.jp/index_en.php
>> >>> >> Japanese:http://www.sraoss.co.jp
>> >>> >>
>> >>> >> > Hello Guys:
>> >>> >> > I was wondering if anyone here has tried to use pg_basebackup with
>> > the
>> >>> >> > -X=stream option over pgpool.
>> >>> >> > I a
>> >>> >> >
>> >>> >> > I am using postgresql 9.5.2 with pgpool 3.5.1  an issue where
>> when i
>> >>> try
>> >>> >> to
>> >>> >> > do this, pg_basebackup hangs on the following line of the progress
>> >>> >> report:
>> >>> >> >
>> >>> >> > pg_basebackup: waiting for background process to finish streaming
>> > ...
>> >>> >> >
>> >>> >> > and then it just quits with the following errors:
>> >>> >> >
>> >>> >> > pg_basebackup: unexpected termination of replication stream:
>> FATAL:
>> >>> >> unable
>> >>> >> > to read data from DB node 0
>> >>> >> > DETAIL:  EOF encountered with backend
>> >>> >> > pg_basebackup: child process exited with error 1
>> >>> >> >
>> >>> >> >
>> >>> >> > from the backend point of view, i see the following:
>> >>> >> > < 2016-04-18 14:34:33.376 ADT >LOG:  terminating walsender process
>> >>> due to
>> >>> >> > replication timeout
>> >>> >> > < 2016-04-18 14:34:33.384 ADT >ERROR:  syntax error
>> >>> >> >
>> >>> >> > pg_basebackup works ok with the fetch option (But i would like to
>> >>> avoid
>> >>> >> > using it, as it would require for me to also implement PITR, or to
>> >>> stop
>> >>> >> > transactions to the DB)
>> >>> >> >
>> >>> >> > pg_basebackup works ok if i connect directly to the backend
>> >>> >> >
>> >>> >> > Any ideas how i could troubleshoot this?
>> >>> >> > Thanks.
>> >>> >> > Regards
>> >>> >>
>> >>>
>>


More information about the pgpool-general mailing list