PostgreSQL Changes the database name

Modify database name modify database script is actually very simple, as follows:

alter  database  db1  rename  to  db2;
Copy the code

However, the database name cannot be modified if the following error message is encountered during processing:

ERROR:  database "db1" is being accessed by other users
DETAIL:  There are 17 other sessions using the database.

Copy the code

Solution: The database is being used by another user, so you need to close the session connected to the database. In PostgreSQL9.2 and later versions, you can close the connection using the following methods:

SELECT pg\_terminate\_backend(pg\_stat\_activity.pid) FROM pg\_stat\_activity WHERE datname='db1' AND pid<>pg\_backend\_pid(); Alter database db1 rename to DB2;Copy the code

The above SQL is briefly explained:

Pg \_terminate\_backend: function used to terminate the process ID of the connection to the database. Pg \_stat\_activity: is a system table that stores the properties and state of the service process. Pg \ _BACKEND \_pid() : is a system function that gets the ID of the server process attached to the current session.Copy the code

2) When a database is being modified or taken offline, the connection must be closed first, for example, SQL SERVER.

If you want to learn PostgreSQL, check out the following two classic books.

Highlights from the past

MySQL high availability MHA cluster deployment mysql8.0 new users and encryption rule changes those things

Monitoring tools: Prometheus+Grafana monitors MySQL and Redis databases

MySQL sensitive data encryption and decryption

MySQL > restore MySQL

MySQL database backup and restore (2)