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

[Tatsuo Ishii]

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
>> >>
>>