[Solved] Postgres Error: template1 is being accessed by other users

When the developer creates a Postgres database on a development machine, an error is reported. Create database statement:

CREATE DATABASE temp_kenyon
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'C'
       LC_CTYPE = 'C'
       CONNECTION LIMIT = -1;

Error information:

ERROR:  source database "template1" is being accessed by other users
DETAIL:  There are 1 other session(s) using the database.

********** ERROR **********

ERROR: source database "template1" is being accessed by other users
SQL STATUS: 55006
More:There are 1 other session(s) using the database.

Analysis: the error prompt is obvious. There is another session connected to template1, and this is the template library. PG does not allow other connections when creating a new library

 

 

solutions:

1. Use another template library template0 of PG

[postgres@kenyon  ~]$ createdb -T template0 tets_kenyon -p 5432
Password: 
[postgres@kenyon  ~]$ psql -p 5432
Password: 
psql (9.1.2)
Type "help" for help.

postgres=# \l
                              List of databases
    Name     |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-------------+----------+----------+---------+-------+-----------------------
 postgres    | postgres | UTF8     | C       | C     | 
 template0   | postgres | UTF8     | C       | C     | =c/postgres          +
             |          |          |         |       | postgres=CTc/postgres
 template1   | postgres | UTF8     | C       | C     | =c/postgres          +
             |          |          |         |       | postgres=CTc/postgres
 testof      | postgres | UTF8     | C       | C     | 
 tets_kenyon | postgres | UTF8     | C       | C     | 
(6 rows)

postgres=# drop database test_kenyon;
ERROR:  database "test_kenyon" does not exist
postgres=# drop database tets_kenyon;
DROP DATABASE

2. Kill the process connected to template1 and execute the database building statement again

postgres=# select procpid from pg_stat_activity where DATNAME = 'template1';
 procpid 
---------
   8879
postgres=# \q
[postgres@kenyon  ~]$ kill 8879

CREATE DATABASE blacktea
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'C'
       LC_CTYPE = 'C'
       CONNECTION LIMIT = -1;

It works.

 

With template library template0 and template1 and database view PG_ Description of database

1. When the database initialization is installed, template0 and template1 are the same, a clean library and the same content

2. After initialization, users can customize template1, such as adding a new user-defined function. When creating a new library, the user-defined function will be attached without creating it in the new library

3. Generally, it is not allowed to operate on template0 to ensure that it is a clean database, which is helpful for database recovery. When building a new database, you can specify template0 as the template to create a clean new database

4. When creating a new library, you cannot connect to a new session. If a new session is connected to the template library, the creation will fail, as shown in the above example

5. View PG_ Description of main fields of database

postgres=# \d pg_database;
    Table "pg_catalog.pg_database"
    Column     |   Type    | Modifiers 
---------------+-----------+-----------
 datname       | name      | not null
 datdba        | oid       | not null
 encoding      | integer   | not null
 datcollate    | name      | not null
 datctype      | name      | not null
 datistemplate | boolean   | not null
 datallowconn  | boolean   | not null
 datconnlimit  | integer   | not null
 datlastsysoid | oid       | not null
 datfrozenxid  | xid       | not null
 dattablespace | oid       | not null
 datacl        | aclitem[] | 
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname), tablespace "pg_global"
    "pg_database_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
Tablespace: "pg_global"

Datistemplate: can it be used as a template?If it is true, any user with created B can create it. Generally, the value of user database is false

Datallowconn indicates whether the connection is allowed. Template0 generally does not allow the connection. Other databases can be connected

Datconnlimit means connection limit, – 1 means no limit

Similar Posts: