Database Cannot Be Connected Using the gsql Client

Symptom

Users fail to connect to the database by running gsql on the client.

Possible Causes

• The number of system connections exceeds the upper limit. The following error information is displayed:

  1 2	gsql -d human_resource -h 10.168.0.74 -U user1 -p 8000 -W password -r gsql: FATAL: sorry, too many clients already

• Users do not have the access permission to the database. The following error information is displayed:

  1 2 3

  gsql -d human_resource -h 10.168.0.74 -U user1 -p 8000 -W password -r gsql: FATAL: permission denied for database "human_resource" DETAIL: User does not have CONNECT privilege.

• The network connection fails.

Solution

• The number of system connections exceeds the upper limit.

  You can set max_connections on the GaussDB(DWS) console.

  Set max_connections:

  1. Log in to the GaussDB(DWS) management console.
  2. In the navigation tree on the left, click Clusters.
  3. In the cluster list, find the target cluster and click its name. The Basic Information page is displayed.
  4. Click the Parameter Modifications tab and modify the value of parameter max_connections. Then click Save.
  5. In the Modification Preview dialog box, confirm the modification and click Save.

  You can check the number of connections as described in Table 1.

  Table 1 Viewing the numbers of connections

  Description	Command
  View the upper limit of a user's connections.	Run the following command to view the upper limit of user user1's connections. -1 indicates that no connection upper limit is set for user user1. 1 2 3 4 5 SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='user1'; rolname | rolconnlimit ---------+-------------- user1 | -1 (1 row)
  View the number of connections that have been used by a specified user.	Run the following command to view the number of connections that have been used by user user1. 1 indicates the number of connections that have been used by user user1. 1 2 3 4 5 6 SELECT COUNT(*) FROM V$SESSION WHERE USERNAME='user1'; count ------- 1 (1 row)
  View the upper limit of connections to database.	Run the following command to view the upper limit of connections used by gaussdb. -1 indicates that no upper limit is set for the number of connections that have been used by gaussdb. 1 2 3 4 5 6 SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='gaussdb'; datname | datconnlimit ----------+-------------- gaussdb | -1 (1 row)
  View the number of connections that have been used by a database.	Run the following commands to view the number of session connections that have been used by gaussdb. 1 indicates the number of session connections that have been used by gaussdb. 1 2 3 4 5 SELECT COUNT(*) FROM PG_STAT_ACTIVITY WHERE DATNAME='gaussdb'; count ------- 1 (1 row)
  View the total number of connections that have been used by all users.	Run the following command to view the number of connections that have been used by all users: 1 2 3 4 5 SELECT COUNT(*) FROM PG_STAT_ACTIVITY; count ------- 10 (1 row)

• Users do not have the access permission to the database.
  1. Connect to the database as user dbadmin.

     1	gsql -d human_resource -h 10.168.0.74 -U dbadmin -p 8000 -W password -r

  2. Grant the users with the access permission to the database.

     1	GRANT CONNECT ON DATABASE human_resource TO user1;

     

     Common misoperations may also cause a database connection failure, for example, entering an incorrect database name, user name, or password. In this case, the client tool will display the corresponding error messages.

     1 2 3 4 5

     gsql -d human_resource -p 8000 gsql: FATAL: database "human_resource" does not exist gsql -d human_resource -U user1 -W password -p 8000 gsql: FATAL: Invalid username/password,login denied.

• The network connection fails.

  Check the network connection between the client and the database server. If you cannot ping from the client to the database server, the network connection is abnormal. Contact technical support.

  1 2 3 4 5 6 7 8

  ping -c 4 10.10.10.1 PING 10.10.10.1 (10.10.10.1) 56(84) bytes of data. From 10.10.10.1: icmp_seq=2 Destination Host Unreachable From 10.10.10.1 icmp_seq=2 Destination Host Unreachable From 10.10.10.1 icmp_seq=3 Destination Host Unreachable From 10.10.10.1 icmp_seq=4 Destination Host Unreachable --- 10.10.10.1 ping statistics --- 4 packets transmitted, 0 received, +4 errors, 100% packet loss, time 2999ms