[pgpool-general: 3340] Re: Pgpool and backup by PITR
Michal Šťava
stavamichal at gmail.com
Thu Dec 4 00:17:55 JST 2014
Ok, i did it.
Thanks for help,
there was probably some other problem with wal settings and connection. Now
slave is in "t" when calling pg_is_in_recovery() and everything seems to be
ok.
Now i have only few questions:
1] Can I use more slaves than one? Like backend1,backand2 and so on?
2] When i set loadbalancing mode to on, will pgpool server use all servers
(master and slaves) for balancing read queries?
Again,
thanks for your help,
Michal Stava
2014-12-03 12:43 GMT+01:00 Tatsuo Ishii <ishii at postgresql.org>:
> > Both backends returns this (f):
> >
> > # psql -U postgres -h 10.18.1.14 -p 5444 postgres
> > psql (9.3.5)
> > Type "help" for help.
> >
> > postgres=# select pg_is_in_recovery();
> > pg_is_in_recovery
> > -------------------
> > f
> > (1 row)
>
> So node 1 works as primary (or standalone PostgreSQL).
>
> > # psql -U postgres -h 10.18.1.13 -p 5444 postgres
> > psql (9.3.5)
> > Type "help" for help.
> >
> > postgres=# select pg_is_in_recovery();
> > pg_is_in_recovery
> > -------------------
> > f
> > (1 row)
> >
> > and for command (call pcp on pgpool server port 9898):
> > pcp_recovery_node -d 30 localhost 9898 postgres password 1
> >
> > i get from log this:
> > DEBUG: send: tos="R", len=46
> > DEBUG: recv: tos="r", len=21, data=AuthenticationOK
> > DEBUG: send: tos="D", len=6
> > DEBUG: recv: tos="e", len=20, data=recovery failed
> > DEBUG: command failed. reason=recovery failed
> > BackendError
> > DEBUG: send: tos="X", len=4
> >
> > 2014-12-03 12:31:27: pid 21555: DEBUG: received PCP packet
> > 2014-12-03 12:31:27: pid 21555: DETAIL: PCP packet type of service 'M'
> > 2014-12-03 12:31:27: pid 21555: DEBUG: PCP: sent md5 salt to client
> > 2014-12-03 12:31:27: pid 21555: DEBUG: initializing backend status
> > 2014-12-03 12:31:27: pid 21555: DEBUG: received PCP packet
> > 2014-12-03 12:31:27: pid 21555: DETAIL: PCP packet type of service 'R'
> > 2014-12-03 12:31:27: pid 21555: DEBUG: PCP: processing authentication
> > request
> > 2014-12-03 12:31:27: pid 21555: DETAIL: authentication OK
> > 2014-12-03 12:31:27: pid 21555: DEBUG: initializing backend status
> > 2014-12-03 12:31:27: pid 21555: DEBUG: received PCP packet
> > 2014-12-03 12:31:27: pid 21555: DETAIL: PCP packet type of service 'O'
> > 2014-12-03 12:31:27: pid 21555: DEBUG: PCP: processing recovery request
> > 2014-12-03 12:31:27: pid 21555: DETAIL: start online recovery
> > 2014-12-03 12:31:27: pid 21555: LOG: starting recovering node 1
> > 2014-12-03 12:31:27: pid 21555: ERROR: node recovery failed, node id: 1
> is
> > alive
> > 2014-12-03 12:31:27: pid 21555: DEBUG: initializing backend status
>
> You need to shutdown node 1 PostgreSQL before doing pcp_recovery_node.
>
> > for node 0 i get info that primary not cannot be recovery. That is
> probably
> > ok.
> >
> > There is info about backends from pgpool.conf:
> > backend_hostname0 = '10.18.1.13'
> > backend_port0 = 5444
> > backend_weight0 = 1
> > backend_data_directory0 = '/home/postgres/data'
> > backend_flag0 = 'ALLOW_TO_FAILOVER'
> >
> > backend_hostname1 = '10.18.1.14'
> > backend_port1 = 5444
> > backend_weight1 = 1
> > backend_data_directory1 = '/home/postgres/data/'
> > backend_flag1 = 'ALLOW_TO_FAILOVER'
> >
> > Thanks again for helping,
> > Michal Stava
> >
> > 2014-12-03 12:25 GMT+01:00 Tatsuo Ishii <ishii at postgresql.org>:
> >
> >> 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
> >> >> >>
> >> >>
> >>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20141203/05daf0b0/attachment-0001.html>
More information about the pgpool-general
mailing list