Failed to create a backend connection

[helirrodriguez]

The scenario is the following: The primary server (backend 0) goes down and the standby (backend 1) server is promoted to primary. Postgres works perfectly, reads, writes and works as an isolated primary on another ip and server different from where pgpool resides.
Pgpool would have to redirect connections from the IP and server where it resides to the IP where the Postgre server resides automatically.
When pgpool starts, it detects backend 0 as up and backend 1 as down, when it is the opposite.
psql -h rocky-cluster-ha-1 -p 9999 -U repuser -d testdb
psql: error: connection to server at "rocky-cluster-ha-1" (10.86.14.212), port 9999 failed: FATAL: failed to create a backend connection
DETAIL: executing failover on backend
Finally it is not able to connect to the primary server that is installed on another IP, rocky-cluster-ha-2, and that was previously the standby server. I don't know if it is a validation problem between pgpool and postgre, connection, etc., but I have not been able to make it work with any configuration.

[pengbo0328]

@helirrodriguez Was Pgpool-II able to connect to the two PostgreSQL servers before failover?

The scenario is the following: The primary server (backend 0) goes down and the standby (backend 1) server is promoted to primary.

Was the failover performed by pgpool?
Pgpool-II saves the backend status in pgpool_status file. If pgpool detects backend failure, pgpool will update the status file.
When pgpool starts, pgpool will retrieve the backend status from pgpool_status file.
If failover is performed manually or by other HA tool, pgpool may not be able to properly manage the backend status.

There are two ways to reset the status managed by pgpool:

• start pgpool using "-D" option
  https://www.pgpool.net/docs/latest/en/html/pgpool.html
• remove the pgpool_status file then start pgpool.
  If you are using the RPM packages, the default path of pgpool_status file is in "/tmp/".

If the above settings does not work properly, please share the configuration file and pgpool logs.

[helirrodriguez]

Hello Bo Peng, the document you gave me works. Many thanks. I have the maintenance scenario with postgres 14: There must be an order in the maneuvers between Postgresql and Pgpool? I turn off the primary postgres for maintenance reasons, then turn on the secondary postgres on another server. The secondary postgres is put into recovery mode. When I start the primary server again, the slots are resynchronized, but the secondary server says it cannot be inserted because it is in read-only mode. I don't want to promote secondary school to primary school, but I don't know how to get secondary school out of this state? [image: image.png] That is to say the secondary postgres server is stuck in read-only mode. Many thanks for your support. Helí Rodríguez. El vie, 19 abr 2024 a las 5:28, Bo Peng ***@***.***>) escribió:

…

@helirrodriguez <https://github.com/helirrodriguez> Was Pgpool-II able to connect to the two PostgreSQL servers before failover? The scenario is the following: The primary server (backend 0) goes down and the standby (backend 1) server is promoted to primary. Was the failover performed by pgpool? Pgpool-II saves the backend status in pgpool_status file. If pgpool detects backend failure, pgpool will update the status file. When pgpool starts, pgpool will retrieve the backend status from pgpool_status file. If failover is performed manually or by other HA tool, pgpool may not be able to properly manage the backend status. There are two ways to reset the status managed by pgpool: - start pgpool using "-D" option https://www.pgpool.net/docs/latest/en/html/pgpool.html - remove the pgpool_status file then start pgpool. If you are using the RPM packages, the default path of pgpool_status file is in "/tmp/". If the above settings does not work properly, please share the configuration file and pgpool logs. — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHH2EMFRAKVJQIDMB2BDY6CFM3AVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRVGY4DEOBSGU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[pengbo0328]

@helirrodriguez

There must be an order in the maneuvers between Postgresql and Pgpool?

To do maintenance of primary, if you turn off primary, pgpool will automatically promote standby to primary.
Alternatively, if you don't mind stopping the service, stop pgpool first, and then stop postgresql.
After maintenance, start PostgreSQL first, and then start pgpool.

That is to say the secondary postgres server is stuck in read-only mode.

I think failover didn't performed because the configuration was incorrect.

You can find the settings of failover in the following configuration example:
https://www.pgpool.net/docs/latest/en/html/example-cluster.html

[helirrodriguez]

Hello Bo, many thanks for your reply. Which would be the correct sequence of maneuvers between postgresql and pgpool. The correct start sequence would be first postgres and the pgpool or the opposite. And the switch off in order to maintain tasks. Many thanks for a new time. Helí Rodríguez. El mar, 30 abr 2024 a las 8:54, Bo Peng ***@***.***>) escribió:

…

@helirrodriguez <https://github.com/helirrodriguez> There must be an order in the maneuvers between Postgresql and Pgpool? To do maintenance of primary, if you turn off primary, pgpool will automatically promote standby to primary. That is to say the secondary postgres server is stuck in read-only mode. I think failover didn't performed because the configuration was incorrect. You can find the settings of failover in the following configuration example: https://www.pgpool.net/docs/latest/en/html/example-cluster.html — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHH52K7K4DEKFH3HTVATY7452DAVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBUGUYTGNRSGE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[pengbo0328]

@helirrodriguez It seems you have resolved this issue.
May I close this issue?

[helirrodriguez]

Hello Bo, yes you can close this issue. But please, if you are so kind, can you tell me the order of maneuvers between postgresql and pgpool. Of course many thanks. Helí. El mié, 1 may 2024 a las 8:38, Bo Peng ***@***.***>) escribió:

…

@helirrodriguez <https://github.com/helirrodriguez> It seems you have resolved this issue. May I close this issue? — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHH7M7JDA6SAJLEQ6HYTZACEX7AVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBYGA2TAMJVGU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[pengbo0328]

But please, if you are so kind, can you tell me the order of maneuvers
between postgresql and pgpool.

Sorry!
If you mean start and stop order,
start order is:
start PostgreSQL first and then start Pgpool-II.

Stop order is:
stop Pgpool-II first and then stop PostgreSQL.

Below is the doc:
https://www.pgpool.net/docs/latest/en/html/install-rpm.html#START-RPM

After this, to start Pgpool-II, run the following command or restart the whole system. Please note that PostgreSQL servers must have been started before this.

To stop Pgpool-II, do this once. Please note that Pgpool-II must be stopped before stopping PostgreSQL.

[helirrodriguez]

Hello Bo, how are you? today. I have a question that I don't find in the documentation. I have installed pgpool by compiling the tarball. In this case I have to install the extensions pgpool-II-pgXX-extensions to use online recovery functionality. Many thanks. Helí Rodríguez. El mié, 1 may 2024 a las 14:17, Bo Peng ***@***.***>) escribió:

…

But please, if you are so kind, can you tell me the order of maneuvers between postgresql and pgpool. Sorry! If you mean start and stop order, start order is: start PostgreSQL first and then start Pgpool-II. Stop order is: stop Pgpool-II first and then stop PostgreSQL. Below is the doc: https://www.pgpool.net/docs/latest/en/html/install-rpm.html#START-RPM After this, to start Pgpool-II, run the following command or restart the whole system. Please note that PostgreSQL servers must have been started before this. To stop Pgpool-II, do this once. Please note that Pgpool-II must be stopped before stopping PostgreSQL. — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHHZDMGKW2ZDISGMCF4DZADMMRAVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBYGM4DKMRWHE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[pengbo0328]

@helirrodriguez If you installed pgpool by compiling the source code, you should follow the following doc to install pgpool recovery.
https://www.pgpool.net/docs/latest/en/html/install-pgpool-recovery.html

Note that pgpool recovery extension needs to be installed on the PostgreSQL servers. If Pgpool-II and PostgreSQL are installed on separate servers, it is not required to install it on the servers where Pgpool-II are running.

[helirrodriguez]

Hello dear Bo Peng, I am doing tests analyzing what happens when I turn off the standby server. Pgpool closes all connections and cannot create the backend connection. There is a way for the connection to continue only with the primary one. This form of behavior is more dangerous than if the primary falls, because although the primary is still alive we cannot provide service. Is there a solution for this behavior? Thank you so much. Helí Rodríguez. El mar, 7 may 2024 a las 2:14, Bo Peng ***@***.***>) escribió:

…

@helirrodriguez <https://github.com/helirrodriguez> If you installed pgpool by compiling the source code, you should follow the following doc to install pgpool recovery. https://www.pgpool.net/docs/latest/en/html/install-pgpool-recovery.html Note that pgpool recovery extension needs to be installed on the PostgreSQL servers. If Pgpool-II and PostgreSQL are installed on separate servers, it is not required to install it on the servers where Pgpool-II are running. — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHH6BVLMT4OA4JLN5QB3ZBAMGFAVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOJXGEZTCNZZGM> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[pengbo0328]

Hello dear Bo Peng, I am doing tests analyzing what happens when I turn off
the standby server. Pgpool closes all connections and cannot create the
backend connection. There is a way for the connection to continue only with
the primary one.

@helirrodriguez If standby goes down, the existing connection failed.
However, the subsequent connections will be sent to primary.

Do you mean the subsequent connections can't be sent to primary?

You can find the settings of failover in the following configuration example:
https://www.pgpool.net/docs/latest/en/html/example-cluster.html

[helirrodriguez]

Hello a new time Bo Peng, First of all, thank you very much for your kind collaboration, without your help it would be impossible to advance in the analysis of Pgpool. The scenario is a primary server and a standby server, apparently everything works fine. [image: image.png] I kill the standby process [image: image.png] Pgpool does not redirect connections to the primary that is alive and well. [image: image.png] [image: image.png] I don't know if this is a configuration problem or how to fix this issue. This is a huge problem because the chances of the standby server going down are the same as those of the primary server. If the primary goes down I can promote the secondary, but in this case pgpool does not accept any more connections. Best regard. Helí Rodríguez. El jue, 16 may 2024 a las 4:38, Bo Peng ***@***.***>) escribió:

…

Hello dear Bo Peng, I am doing tests analyzing what happens when I turn off the standby server. Pgpool closes all connections and cannot create the backend connection. There is a way for the connection to continue only with the primary one. @helirrodriguez <https://github.com/helirrodriguez> If standby goes down, the existing connection failed. However, the subsequent connections will be sent to primary. Do you mean the subsequent connections can't be sent to primary? You can find the settings of failover in the following configuration example: https://www.pgpool.net/docs/latest/en/html/example-cluster.html — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHH2U2WN7OORDSE4LXADZCQL3FAVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMJTHEYDMNJVGE> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[pengbo0328]

@helirrodriguez I think it is a configuration problem.
Could you share your pgpool.log which contains the logs when you killed the standby process and your pgpool.conf?

[pengbo0328]

@helirrodriguez I am not able to see the images that you uploaded?
Could you upload them to this issue again?

[helirrodriguez]

Hello Bo, a new time many thanks for your support. I send the files that you requested. Helí. El vie, 19 abr 2024 a las 5:28, Bo Peng ***@***.***>) escribió:

…

@helirrodriguez <https://github.com/helirrodriguez> Was Pgpool-II able to connect to the two PostgreSQL servers before failover? The scenario is the following: The primary server (backend 0) goes down and the standby (backend 1) server is promoted to primary. Was the failover performed by pgpool? Pgpool-II saves the backend status in pgpool_status file. If pgpool detects backend failure, pgpool will update the status file. When pgpool starts, pgpool will retrieve the backend status from pgpool_status file. If failover is performed manually or by other HA tool, pgpool may not be able to properly manage the backend status. There are two ways to reset the status managed by pgpool: - start pgpool using "-D" option https://www.pgpool.net/docs/latest/en/html/pgpool.html - remove the pgpool_status file then start pgpool. If you are using the RPM packages, the default path of pgpool_status file is in "/tmp/". If the above settings does not work properly, please share the configuration file and pgpool logs. — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHH2EMFRAKVJQIDMB2BDY6CFM3AVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANRVGY4DEOBSGU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[helirrodriguez]

Hello Bo, good morning. I didn't know you were a woman, nice to meet you. Do you have any news about the problem that I have reported. Best regards Helí Rodríguez. El mié, 1 may 2024 a las 11:55, Helí Rodríguez ***@***.***>) escribió:

…

Hello Bo, yes you can close this issue. But please, if you are so kind, can you tell me the order of maneuvers between postgresql and pgpool. Of course many thanks. Helí. El mié, 1 may 2024 a las 8:38, Bo Peng ***@***.***>) escribió: > @helirrodriguez <https://github.com/helirrodriguez> It seems you have > resolved this issue. > May I close this issue? > > — > Reply to this email directly, view it on GitHub > <#48 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ALXTHH7M7JDA6SAJLEQ6HYTZACEX7AVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDAOBYGA2TAMJVGU> > . > You are receiving this because you were mentioned.Message ID: > ***@***.***> >

[pengbo0328]

Sorry. Still couldn't see your images.
Could you upload the images to this thread?
And could you share your pgpool.log which contains the logs when you killed the standby process and your pgpool.conf?

[helirrodriguez]

Hello Bo Peng, good morning, I am sending the screenshots, the logs and the configuration. El vie, 24 may 2024 a las 4:00, Bo Peng ***@***.***>) escribió:

…

Sorry. Still couldn't see your images. Could you upload the images to this thread? And could you share your pgpool.log which contains the logs when you killed the standby process and your pgpool.conf? — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHHYHYLNTTLSFFT6AIPLZD2NMXAVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMRYGM2DSNZXGY> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[helirrodriguez]

Hello a new time Bo Peng, I tried with the version 4.5.2 with the same result. Do you have any ideas about what is happening? and the solution. Best regards. Helí Rodríguez El lun, 27 may 2024 a las 10:44, Helí Rodríguez ***@***.***>) escribió:

…

Hello Bo Peng, good morning, I am sending the screenshots, the logs and the configuration. El vie, 24 may 2024 a las 4:00, Bo Peng ***@***.***>) escribió: > Sorry. Still couldn't see your images. > Could you upload the images to this thread? > And could you share your pgpool.log which contains the logs when you > killed the standby process and your pgpool.conf? > > — > Reply to this email directly, view it on GitHub > <#48 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/ALXTHHYHYLNTTLSFFT6AIPLZD2NMXAVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMRYGM2DSNZXGY> > . > You are receiving this because you were mentioned.Message ID: > ***@***.***> >

[pengbo0328]

@helirrodriguez Sorry! Still I couldn't see your images and logs.
Did you send to my email?

Hello dear Bo Peng, I am doing tests analyzing what happens when I turn off
the standby server. Pgpool closes all connections and cannot create the
backend connection. There is a way for the connection to continue only with
the primary one. This form of behavior is more dangerous than if the
primary falls, because although the primary is still alive we cannot
provide service.

Let me answer your question.

If standby goes down, the existing connection will be disconnected.
It is the feature of pgpool. However, the subsequent connections will be sent to primary.
If you want to avoid this issue, you need to add retry in your application.

[helirrodriguez]

Hello Bo, First of all, thank you very much for your response. When postgresql standby goes down, pgpool is not able to reestablish the connection to backend 0 and closes all connections. That is, the database is inaccessible unless the standby server is de-attached. Attaching postgresql can be done in the failover script. Many thanks for your support. Best regards Helí Rodríguez. El mié, 29 may 2024 a las 1:48, Bo Peng ***@***.***>) escribió:

…

@helirrodriguez <https://github.com/helirrodriguez> Sorry! Still I couldn't see your images and logs. Did you send to my email? Hello dear Bo Peng, I am doing tests analyzing what happens when I turn off the standby server. Pgpool closes all connections and cannot create the backend connection. There is a way for the connection to continue only with the primary one. This form of behavior is more dangerous than if the primary falls, because although the primary is still alive we cannot provide service. Let me answer your question. If standby goes down, the existing connection will be disconnected. It is the feature of pgpool. However, the subsequent connections will be sent to primary. If you want to avoid this issue, you need to add retry in your application. — Reply to this email directly, view it on GitHub <#48 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ALXTHH6HK6JTXDYWK7JQHMLZEUJU3AVCNFSM6AAAAABGNBMBWOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMZWGI3TOMBQGM> . You are receiving this because you were mentioned.Message ID: ***@***.***>