0

Recently the streaming replication fails after I restart the primary. I can not find the record using select * from pg_stat_replication

Now, I want to see it from the system log file at /var/log/postgresql# on the primary. I find the following using grep -i -m 10 **fatal** postgresql-12-main.log:

2022-12-06 01:56:01.890 UTC [11513] FATAL:  remaining connection slots are reserved for non-replication superuser connections
2022-12-05 11:36:57.024 UTC [27076] LOG:  background worker "logical replication launcher" (PID 27089) exited with exit code 1
2022-12-05 11:36:57.637 UTC [2465] rep_user@[unknown] LOG:  standby "12/main" is now a synchronous standby with priority 1
2022-12-06 01:55:27.344 UTC [11360] FATAL:  remaining connection slots are reserved for non-replication superuser connections

I have tried some keywords that might be able to be grep-ed like : replication, slave, ip address of the client and physical. But I do not find anything.

BTW, I do not turn on the postgres log file so there is no file at var/lib/postgresql/12/main/log.

There is no error on the replica side. I have grep keyword like fatal and error. Nothing.

What are the keywords that I can grep to find errors related to replication?

thanks

Improve this question

1 Answer 1

Reset to default
0

There are too many connections open on the primary server. You have to close some if you want the standby to be able to connect.

Improve this answer
4
  • okay. I read this on how to close connection : stackoverflow.com/questions/25058649/…. It said "...you don't want to terminate important queries (or connections) that way.". If I have to restart the postgre service, then all the connections in pg_stat_activity will be wiped out. So how can one terminate something important? Just wait till they finish and then restart? @LaurenzAlbe
    – padjee
    Dec 7, 2022 at 3:10
  • If you terminate a connection, be it with a restart of the database server or with pg_terminate_backend, you are going to hurt some client whose connection gets interrupted. That cannot be avoided. You can use a "smart shutdown" that does not terminate existing connections, but then new connections will be denied service.
    – Laurenz Albe
    Dec 7, 2022 at 5:24
  • Ok. Furthermore, how can the "too many connections" error happens when I have max_connections 700 and count pg_stat_activity only 193 records? It just does not adds up. Another fact, I see so many errors related to queries such as no relation found. The log file itself is huge : 92MB (yesterday) and 1GB(2 days ago). @LaurenzAlbe
    – padjee
    Dec 7, 2022 at 7:21
  • The database server can count to 700. But there could also be a separate, more restrictive limit set on the database or the database user. The errors you see are not directly related and caused by your program doing weird things.
    – Laurenz Albe
    Dec 7, 2022 at 8:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.