[pgpool-general: 9337] Re: error in first stage of recovery

[Adam Blomeke]

Ok, some progress:

I had not installed the pgpool_recovery extension on the template1 database. I assume it must operate out of that database in order to work? I don't see any configuration parameter to specify a different database in pgpool.conf. Installing the extension got me to this error:
< usr:repl host:10.6.1.191 time:2025-02-06 14:14:38.616 EST 42501 pid:2574580 >ERROR:  must be superuser to use pgpool_recovery function
It seems that the recovery user should not be "repl" with only the replication privilege.

Changed that parameter in pgpool.conf:
 recovery_user = 'postgres'

Restarted, then ran pcp_recovery_node again:

sh: /opt/data/data15_new//etc/pgpool-II/scripts/recovery_1st_stage.sh: No such file or directory

/opt/data/data15_new is the home directory for my database. I have not installed any pgpool code yet on the database nodes themselves. All the load balancing configuration has been done on a separate instance.  My plan has been to have three machines with pgpool on them and then two database nodes on separate machines. I had assumed that pgpool would be the thing running the scripts, so I had set up certificate-based logins on the pgpool node to the databases, It sounds like what I actually need to do is to install the scripts in a subdirectory of the postgres instance, then set up certificate-based passwordless logins between the database nodes. Is this correct?

Thanks,
Adam


-----Original Message-----
From: Tatsuo Ishii <ishii at postgresql.org> 
Sent: Wednesday, February 5, 2025 22:33
To: Adam Blomeke <adam.blomeke at volanno.com>
Cc: pgpool-general at pgpool.net
Subject: Re: [pgpool-general: 9335] Re: error in first stage of recovery

>>> Is it possible you started pgpool with a schema search_path
>>Sorry I meant PostgreSQL, not pgpool since pgpool_recovery is executed by PostgreSQL.
>>> which does not have the public schema?
> Not from what I can tell:
> [postgres@####### ~]$ psql -p 5433 ##### psql (15.10) Type "help" for 
> help.
> 
> #####=# show search_path;
>    search_path
> -----------------
>  "$user", public
> (1 row)

Pgpool-II recover process connects to PostgreSQL as:

user = recovery_user in pgpool.conf
database = template1
host = backend_hostname (0 or 1 depending on which is the streaming replication primary) port = backend_porthostname (0 or 1 depending on which is the streaming replication primary)

Please confirm.

>> BTW, are you sure that replication_mode_recovery_1st_stage.sh is the 
>> right script for you? It's for the native replication mode, not for 
>> the streaming replication mode.
> 
> Ah, now here you may have me. I am using streaming replication mode, so should I be using recovery_1st_stage.sh instead?

Yes.

> Will that have an impact on the pcp command?

I have not tested but as far as checking the script differences, I think the answer is no.

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