[pgpool-general: 3336] Re: Pgpool and backup by PITR
Tatsuo Ishii
ishii at postgresql.org
Wed Dec 3 20:25:56 JST 2014
Ok, probably node 1 is out of sync or streaming replication is not
working. To confirm you type:
psql -h 10.18.1.14 -p 5444 postgres
then
select pg_is_in_recovery();
If it returns t, it works as standby. If not, you could resync the
standby by using pcp_recovery_command.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
> %/home/postgres/config# psql -p 9999 -U postgres -h localhost
> psql (9.3.5)
> Type "help" for help.
>
> postgres=# show pool_nodes;
> node_id | hostname | port | status | lb_weight | role
> ---------+------------+------+--------+-----------+---------
> 0 | 10.18.1.13 | 5444 | 2 | 0.500000 | primary
> 1 | 10.18.1.14 | 5444 | 2 | 0.500000 | standby
> (2 rows)
>
> 2014-12-03 12:11 GMT+01:00 Tatsuo Ishii <ishii at postgresql.org>:
>
>> What does "show pool_nodes" show?
>>
>> 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,
>> >
>> > this tutorial i tried before using replication mode. But there is one
>> > problem i can't figure.
>> >
>> > I have master and slave server but only master is accepting my queries.
>> If
>> > i try to create new db by:
>> > createdb -p 9999 -U postgres -h localhost DB5
>> > on pgpool-II server, i get info below from log but only master create
>> this
>> > DB5. If i try to connect by pgpool-II to this new db, i get another info
>> > below.
>> >
>> > Is there anything i am doing wrong?
>> >
>> > PS:
>> > both are hot_standby and use configuration from manual, only pg_hba.conf
>> is
>> > set to specific hosts for trust reading all databases from postgres and
>> > also trust replication
>> >
>> > Thanks,
>> > Michal Stava
>> >
>> > LOG when connecting new DB:
>> > 2014-12-03 08:55:28: pid 21237: LOG: pool_read_kind: error message from
>> > 1 th backend:database "DB5" does not exist
>> > 2014-12-03 08:55:28: pid 21237: ERROR: unable to read message kind
>> > 2014-12-03 08:55:28: pid 21237: DETAIL: kind does not match between
>> > master(53) slot[1] (45)
>> >
>> > LOG when creating new DB:
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: I am 21153 accept fd 11
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading startup packet
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: Protocol Major: 1234 Minor: 5679
>> > database: user:
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: selecting backend connection
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: SSLRequest from client
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: SSL is requested but SSL support
>> is
>> > not available
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading startup packet
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: application_name: createdb
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading startup packet
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: Protocol Major: 3 Minor: 0
>> > database: postgres user: postgres
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: sending backend key data
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: send pid 15378 to frontend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: Query:"SET
>> > application_name TO 'createdb'"
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:0 to
>> complete
>> > the query
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'S'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'C'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'Z'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: Query:"SET
>> > application_name TO 'createdb'"
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:1 to
>> complete
>> > the query
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'S'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'C'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: do_command: kind: 'Z'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing
>> > transaction isolation. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing writing
>> > transaction. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing failed
>> > transaction. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing failed
>> > transaction. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing skip
>> > reading from backends. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing ignore
>> > till sync. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing frontend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'Q'(51) from
>> frontend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing doing
>> > extended query messaging. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting query in
>> > progress. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: decide where to send the queries
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: destination = 0 for query=
>> "CREATE
>> > DATABASE "DB5";
>> > "
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:0 to
>> complete
>> > the query
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: C
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'C'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'C'(43) from
>> backend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting command
>> > success. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'Z'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'Z'(5a) from
>> backend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: Ready For Query received
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading message length
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: slot: 0 length: 5
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing ReadyForQuery
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: transaction state 'I'(49)
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing frontend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'X'(58) from
>> frontend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: clearing doing
>> > extended query messaging. DONE
>> > 2014-12-03 08:51:03: pid 21153: LOG: Frontend terminated
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: received message kind 'X' from
>> > frontend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting query in
>> > progress. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: decide where to send the queries
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: destination = 3 for query= "
>> > DISCARD ALL"
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: waiting for query response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: waiting for backend:0 to
>> complete
>> > the query
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: detect error: kind: S
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: parameter name: is_superuser
>> > value: "on"
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: parameter name:
>> > session_authorization value: "postgres"
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: parameter name: application_name
>> > value: "createdb"
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'C'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'C'(43) from
>> backend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: session context: setting command
>> > success. DONE
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading backend data packet kind
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: backend:0 of 2 kind = 'Z'
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: received kind 'Z'(5a) from
>> backend
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing backend response
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: Ready For Query received
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: reading message length
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: slot: 0 length: 5
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: processing ReadyForQuery
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: transaction state 'I'(49)
>> > 2014-12-03 08:51:03: pid 21153: DEBUG: setting backend connection close
>> > timer
>> > 2014-12-03 08:51:03: pid 21153: DETAIL: close time 1417593063
>> >
>> > 2014-12-03 0:05 GMT+01:00 Tatsuo Ishii <ishii at postgresql.org>:
>> >
>> >> > Hi,
>> >> >
>> >> > frstly I want to say "Good job, your technology is really good, thank
>> >> you."
>> >> > and secondly "Sorry for my bad english, I do my best."
>> >> >
>> >> > Ok, so what's my problem:
>> >> >
>> >> > I created replication model for my application (1 server with pgpool2
>> >> > instance and 2 servers like host0 and host1). Everything is ok except
>> >> > online_recovery part which I really have problem to understand.
>> >> >
>> >> > For now I have 3 empty scripts so adding node works, but there is no
>> >> > sychronization of DBs:
>> >> > basebackup
>> >> > pgpool_recovery_pitr
>> >> > pgpool_remote_start
>> >> >
>> >> >
>> >> > There is a sample of basebackup script:
>> >> >
>> >> > #! /bin/sh
>> >> > DATA=$1
>> >> > RECOVERY_TARGET=$2
>> >> > RECOVERY_DATA=$3
>> >> > psql -c "select pg_start_backup('pgpool-recovery')"
>> >> > postgres echo "restore_command = 'scp $HOSTNAME:/data/archive_log/%f
>> >> > %p'" > /data/recovery.conf
>> >> > tar -C /data -zcf pgsql.tar.gz pgsql
>> >> > psql -c 'select pg_stop_backup()' postgres
>> >> > scp pgsql.tar.gz $RECOVERY_TARGET:$RECOVERY_DATA
>> >> >
>> >> > I really need some advices there, because can't understand it.
>> >> >
>> >> > 1] there is something like /data/ so its $DATA or its other data?
>> >> > 2] there is tar on pgsql in data directory, but i have no pgsql
>> directory
>> >> > there
>> >> > 3] do you have some working example? not only common sample?
>> >> > 4] very similar problem I have with other two scripts
>> >> >
>> >> > Last 2 days I spent many hours by trying to understand this online
>> >> recovery
>> >> > mechanism, but
>> >> > my karma is probably too low.
>> >> >
>> >> > Thank you for any advice,
>> >> > Michal Stava,
>> >> > CZECH REPUBLIC
>> >>
>> >> I recommend to start with this tutorial:
>> >>
>> >>
>> >>
>> http://www.pgpool.net/pgpool-web/contrib_docs/simple_sr_setting2_3.3/index.html
>> >>
>> >> Best regards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese:http://www.sraoss.co.jp
>> >>
>>
More information about the pgpool-general
mailing list