Help Center/ GaussDB/ Centralized_2.x/ Database Security Management/ Checking the Number of Database Connections
Updated on 2023-10-23 GMT+08:00
View PDF

Checking the Number of Database Connections

Background

If the number of connections reaches its upper limit, new connections cannot be created. Therefore, if a user fails to connect a database, the administrator must check whether the number of connections has reached the upper limit. The following are details about database connections:

  • The maximum number of global connections is specified by the max_connections parameter.
  • The number of a user's connections is specified by CONNECTION LIMIT connlimit in the CREATE ROLE statement and can be changed using CONNECTION LIMIT connlimit in the ALTER ROLE statement.
  • The number of a database's connections is specified by the CONNECTION LIMIT connlimit parameter in the CREATE DATABASE statement.

Procedure

  1. Connect to a database. For details, see Connecting to a Database.
  2. View the upper limit of the number of global connections.

    1
2
3
4
5
    		 
    openGauss=# SHOW max_connections;
 max_connections
-----------------
 800
(1 row)

    800 is the maximum number of session connections.

  3. View the number of connections that have been used.

    For details, see Table 1.

    Except for database and usernames that are enclosed in double quotation marks (") during creation, uppercase letters are not allowed in the database and usernames in the commands in the following table.

    Table 1 Viewing the number of session connections

    Description

    Command

    View the maximum number of sessions connected to a specific user.

    Run the following commands to view the upper limit of the number of omm's session connections. –1 indicates that no upper limit is set for the number of omm's session connections.

    1
2
3
4
5
    		 
    openGauss=# SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='omm';
 rolname  | rolconnlimit
----------+--------------
 omm |           -1
(1 row)

    View the number of session connections that have been used by a user.

    Run the following commands to view the number of session connections that have been used by omm. 1 indicates the number of session connections that have been used by omm.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
    		 
    openGauss=# CREATE OR REPLACE VIEW DV_SESSIONS AS
	       SELECT
		    sa.sessionid AS SID,
		    0::integer AS SERIAL#,
		    sa.usesysid AS USER#,
		    ad.rolname AS USERNAME
	        FROM pg_stat_get_activity(NULL) AS sa
	        LEFT JOIN pg_authid ad ON(sa.usesysid = ad.oid)
	        WHERE sa.application_name <> 'JobSchedul
openGauss=# SELECT COUNT(*) FROM DV_SESSIONS WHERE USERNAME='omm';

 count
-------
     1
(1 row)

    View the maximum number of sessions connected to a specific database.

    Run the following commands to view the upper limit of the number of postgres's session connections. –1 indicates that no upper limit is set for the number of postgres's session connections.

    1
2
3
4
5
6
    		 
    openGauss=# SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='postgres';

 datname  | datconnlimit
----------+--------------
 postgres |           -1
(1 row)

    View the number of session connections that have been used by a specific database.

    Run the following commands to view the number of session connections that have been used by postgres. 1 indicates the number of session connections that have been used by postgres.

    1
2
3
4
5
    		 
    openGauss=# SELECT COUNT(*) FROM PG_STAT_ACTIVITY WHERE DATNAME='postgres';
 count 
-------
     1
(1 row)

    View the number of session connections that have been used by all users.

    Run the following commands to view the number of session connections that have been used by all users:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
    		 
    openGauss=# CREATE OR REPLACE VIEW DV_SESSIONS AS
	       SELECT
		    sa.sessionid AS SID,
		    0::integer AS SERIAL#,
		    sa.usesysid AS USER#,
		    ad.rolname AS USERNAME
	        FROM pg_stat_get_activity(NULL) AS sa
	        LEFT JOIN pg_authid ad ON(sa.usesysid = ad.oid)
	        WHERE sa.application_name <> 'JobSchedul
openGauss=# SELECT COUNT(*) FROM DV_SESSIONS;
 count
-------
     10
(1 row)