Issue When running the Xray database sync the Xray has a large number of analysis tasks running that will create a large number of PostgreSQL database connection links. If the number of database connection links created by the Xray is greater than the maximum number of database connection links opened by the PostgreSQL configuration, it will return the SQLSTATE 53300 with too many connection errors.
The error snippets are as follows:
[33m[jfxr ][0m [1m[31m[ERROR][0m [ ] [vulnerability:44 ] [main ] Failed to check if vulnerability with id XRAY-id is already exists: failed to fetch public vulnerability --- at /go/src/jfrog.com/xray/backend/dbaccess/dao/public_vulnerabilities_dao.go:1116 (publicVulnerabilitiesDao.GetVulnerabilityBasicInfo) --- Caused by: failed to connect to `host=<host_addr> user=<username> database=<db_name>`: server error (FATAL: remaining connection slots are reserved for non-replication superuser connections (SQLSTATE 53300))
Solutions Check the number of database maximum connections configured by Xray. We can get the maximum connections each Xray microservice can open to the Postgres DB.
1. This will be configured in the $XRAY_HOME/etc/system.yaml file.
server: database: maxOpenConnections: 60 # Defualt analysis: database: maxOpenConnections: 30 #Default indexer: database: maxOpenConnections: 30 #Default persist: database: maxOpenConnections: 30 #Default
The maximum connections will open by the Xray cluster:
Total of connections = (number of nodes) * (maxOpenConnServer + maxOpenConnPersist + maxOpenConnAnalysis + maxOpenConnIndexer) # Defualt: 150
2. Check the number of database maximum connections configured in the PostgreSQL using select sql or confirm it in the config configuration fileļ¼
# show the maximum connections SQL, default 100 show max_connections; # show the status of all current connections select datname,pid,application_name,state from pg_stat_activity;
Or
# $PGDATA/postgres.conf max_connections = 100 # Default
3. Change the maximum number of the open connection links of the PostgreSQL, edit it in the $PGDATA/postgres.conf config file to change the max_connections. The max_connections number needs to be more than the maximum connections set by the Xray server.
# Total of connections need set for the PostgreSQL Total of connections need set = (number of nodes) * (maxOpenConnServer + maxOpenConnPersist + maxOpenConnAnalysis + maxOpenConnIndexer) + 50 # Defualt total: 200
# $PGDATA/postgres.conf max_connections = <Total of connections need set> # (change requires restart) # Default:200
4. Restart the PostgreSQL server.
5. About the more the PostgreSQL Error Codes: https://www.postgresql.org/docs/current/errcodes-appendix.html
5. About the more the PostgreSQL Error Codes: https://www.postgresql.org/docs/current/errcodes-appendix.html