[pgpool-general: 4648] Re: pg_basebackup with streaming replication option over pgpool
Tatsuo Ishii
ishii at postgresql.org
Wed Apr 20 11:07:53 JST 2016
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