数据库管理
更新时间 2025-02-14 10:22:24
最近更新时间: 2025-02-14 10:22:24
本文为您介绍创建数据库、修改数据库配置和删除数据库。
创建数据库
要创建一个数据库，必须是一个超级用户或者具有特殊的CREATEDB特权，默认情况下，新数据库将通过克隆标准系统数据库template1被创建。可以通过写TEMPLATE name指定一个不同的模板。特别地，通过写TEMPLATE template0您可以创建一个干净的数据库，它将只包含的Teledb所预定义的标准对象。
默认参数创建数据库
teledb=# create database teledb_db; CREATE DATABASE指定克隆库
teledb=# create database teledb_db_template TEMPLATE template0; CREATE DATABASE指定所有者
teledb=# create role teledb_user with login; CREATE ROLE teledb=# create database teledb_db_owner owner teledb_user; CREATE DATABASE teledb=# \l+ teledb_db_owner List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ------------------+--------------+----------+-------------+-------------+-------------------+-------+------------+------------- teledb_db_owner | teledb_user | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 19 MB | pg_default | (1 row)指定编码
teledb=# create database teledb_db_encoding ENCODING UTF8; CREATE DATABASE teledb=# \l+ teledb_db_encoding List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ---------------------+---------+----------+-------------+-------------+-------------------+-------+------------+------------- teledb_db_encoding | teledb | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | | 19 MB | pg_default | (1 row)创建gbk编码
teledb=# CREATE DATABASE db_gbk template template0 encoding = gbk LC_COLLATE = 'zh_CN.gbk' LC_CTYPE = 'zh_CN.gbk'; CREATE DATABASE teledb=# \d db_gbk Did not find any relation named "db_gbk". teledb=# \l+ db_gbk List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description --------+---------+----------+-----------+-----------+-------------------+-------+------------+------------- db_gbk | teledb | GBK | zh_CN.gbk | zh_CN.gbk | | 19 MB | pg_default | (1 row)指定排序规则
teledb=# create database teledb_db_lc_collate lc_collate 'C' template template0; CREATE DATABASE指定分组规则
teledb=# create database teledb_lc_ctype LC_CTYPE 'C' template template0; CREATE DATABASE配置数据可连接
teledb=# create database teledb__allow_connections ALLOW_CONNECTIONS true; CREATE DATABASE teledb=# select datallowconn from pg_database where datname = 'teledb__allow_connections'; datallowconn -------------- t (1 row) teledb=# \c teledb__allow_connections You are now connected to database "teledb__allow_connections" as user "teledb".配置连接数
teledb=# create database teledb_connlimit CONNECTION LIMIT 100; CREATE DATABASE teledb=# select datconnlimit from pg_database where datname='teledb_connlimit'; datconnlimit -------------- 100 (1 row)配置数据库可以被复制（是否模板数据库）
teledb=# create database teledb_istemplate is_template true; CREATE DATABASE teledb=# select datconnlimit from pg_database where datname='teledb_connlimit'; datconnlimit -------------- 100 (1 row)多个参数一起配置
teledb=# create database teledb_mul owner teledb_user CONNECTION LIMIT 50 template template0 encoding 'utf8' lc_collate 'C'; CREATE DATABASE
修改数据库配置
修改数据库名称
teledb=# alter database teledb_db rename to teledb_db_new; ALTER DATABASE修改连接数
teledb=# alter database teledb_db_new connection limit 50; ALTER DATABASE修改数据库所有者
teledb=# alter database teledb_db_new owner to teledb; ALTER DATABASE配置数据默认search_path
teledb=# alter database teledb_db_new set search_path to public, pg_catalog; ALTER DATABASEalter database不支持修改的项目
项目 备注 encoding 编码 lc_collate 排序规则 lc_ctype 分组规则
删除数据库
-- 删除数据库teledb_db_new
teledb=# drop database teledb_db_new;
-- 仍有会话连接数据库时，会报错
ERROR: database "teledb_db_new" is being accessed by other users
DETAIL: There is 1 other session using the database.
-- 停止该数据库的所有连接，后重新删除数据库
teledb=# select pg_terminate_backend(pid) from pg_stat_activity where datname='teledb_db_new';
pg_terminate_backend
----------------------
t
(1 row)
teledb=# drop database teledb_db_new;
DROP DATABASE
