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

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

The issue apparently raises because pgpool-II does not recognize
replication protocol. In theory pgpool-II can deal with the protocol
but I doubt it's worth the trouble, since the protocol will carry lots
of data, and if pgpool-II takes care of the data, time to process it
will be significantly longer than directly connecting PostgreSQL due
to the packet forwarding overhead in pgpool-II.

My recommendation is, pg_basebackup should directly connect to
PostgreSQL, rather than via pgpool-II. And we should write it down in
the restriction section in the docs.

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, 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
>>> >>> >>
>>> >>>
>>>
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general

More information about the pgpool-general mailing list