[pgpool-general: 9199] Re: pcp_recovery_node with snapshot isolation and large database

[Tatsuo Ishii]

Hello Bill,

> Hello,
> 
> Ran into the issue where attempting a pcp_recovery_node with mode
> snapshot_isolation fails due to active database connections. Have a
> large database that takes hours to pg_basebackup, can't afford closing
> the database connections for that long.
> 
> Any known practices to work around this?

In the first stage of the online recovery (executing
recovery_1st_stage_command), connections to pgpool do not need to be
closed, thus pg_basebackup taking hours should not be a problem. I
guess actually you are getting the error before running into the
second stage of the online recovery (executing
recovery_2nd_stage_command). In this case you can tweak
client_idle_limit_in_recovery. Setting -1 to the configuration will
force to close all the connections to pgpool immediately before
running into the second stage of the online recovery, and you should
success the online recovery task.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

> Have two thoughts myself:
> 
> 1) Run through recovery_stage_two process manually, first closing all
> connections, then running the related script. What else is done
> outside of the script, besides likely pcp_attach_node?
> 
> 2) Add a method to close all user connections to the end of
> recovery_stage_one script. Add inverse to end of
> recovery_stage_two. Seems to me there are two big steps for this.
>    a) Can't trust any pg_* tables unless going to individual
> nodes. How to close connections? Am I missing a    pgpool method that
> would all one to close user connections, as in not superuser?
>    b) Cannot stop/start Postgres itself, as that would stop the
> recovery.
> 
> 
> Thanks,
> 
> -- 
> Bill Carlson
> 
> Carlson Computer Services
> http://apgtm.com/
> 
> "Anything is Possible Given Time and Money"
> 
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general