创建数据库
要创建一个数据库，您必须是一个超级用户或者具有特殊的 CREATEDB 特权，默认情况下，新数据库将通过克隆标准系统数据库 template1被创建。可以通过写 TEMPLATE name 指定一个不同的模板。特别地，通过写 TEMPLATE template0 您可以创建一个干净的数据库，它将只包含您的 TBase 版本所预定义的标准对象。
默认参数创建数据库
■ 默认参数创建数据库postgres=# create database tbase_db;CREATEDATABASE
指定克隆库
postgres=# create database tbase_db_template TEMPLATE template0;CREATEDATABASE
指定所有者
postgres=# create role pgxz with login;CREATEROLEpostgres=# create database tbase_db_owner owner pgxz;CREATEDATABASEpostgres=# \\l+ tbase_db_ownerListofdatabasesName|Owner|Encoding|Collate|Ctype|Accessprivileges|Size|Tablespace|Description----------------+-------+----------+------------+------------+-------------------+-------+------------+-------------tbase_db_owner|pgxz|UTF8|en_US.utf8|en_US.utf8||18MB|pg_default|(1row)
指定编码
postgres=# create database tbase_db_encoding ENCODING UTF8;CREATE DATABASEpostgres=# \\l+ tbase_db_encodingList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description-------------------+-------+----------+------------+------------+-------------------+-------+------------+-------------tbase_db_encoding | tbase | UTF8 | en_US.utf8 | en_US.utf8 | | 18 MB | pg_default |(1 row)
创建 gbk 编码
postgres=# CREATE DATABASE db_gbk template template0 encoding = gbk LC_COLLATE = 'zh_CN.gbk' LC_CTYPE = 'zh_CN.gbk';CREATE DATABASEpostgres=# \\l+List of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description-----------+-------+----------+------------+------------+-------------------+-------+------------+--------------------------------------------db_gbk | tbase | GBK | zh_CN.gbk | zh_CN.gbk | | 19 MB | pg_default |postgres | tbase | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | 26 MB | pg_default | default administrative connection databasetemplate0 | tbase | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/tbase +| 19 MB | pg_default | unmodifiable empty database| | | | | tbase=CTc/tbase | | |template1 | tbase | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/tbase +| 23 MB | pg_default | default template for new databases| | | | | tbase=CTc/tbase | | |(4 rows)
创建 gb18030编码
postgres=# create database db_gb18030 template=template0 encoding=gb18030 LC_COLLATE = 'zh_CN.gb18030' LC_CTYPE = 'zh_CN.gb18030';CREATE DATABASEpostgres=# \\lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges------------+-------+----------+---------------+---------------+-------------------db_gb18030 | tbase | GB18030 | zh_CN.gb18030 | zh_CN.gb18030 |db_gbk | tbase | GBK | zh_CN.gbk | zh_CN.gbk |postgres | tbase | UTF8 | zh_CN.utf8 | zh_CN.utf8 |template0 | tbase | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/tbase +| | | | | tbase=CTc/tbasetemplate1 | tbase | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/tbase +| | | | | tbase=CTc/tbasetest | tbase | UTF8 | zh_CN.utf8 | zh_CN.utf8 |(6 rows)
指定排序规则
postgres=# create database tbase_db_lc_collate lc_collate 'C';CREATE DATABASEpostgres=# \\l+ tbase_db_lc_collateList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description---------------------+-------+----------+---------+------------+-------------------+-------+------------+-------------tbase_db_lc_collate | tbase | UTF8 | C | en_US.utf8 | | 18 MB | pg_default |(1 row)
指定分组规则
postgres=# create database tbase_db_lc_ctype LC_CTYPE 'C' ;CREATE DATABASEpostgres=# \\l+ tbase_db_lc_ctypeList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description-------------------+-------+----------+------------+-------+-------------------+-------+------------+-------------tbase_db_lc_ctype | tbase | UTF8 | en_US.utf8 | C | | 18 MB | pg_default |(1 row)
配置数据可连接
postgres=# create database tbase_db_allow_connections ALLOW_CONNECTIONS true;CREATE DATABASEpostgres=# select datallowconn from pg_database where datname='tbase_db_allow_connections';datallowconn--------------t(1 row)
配置连接数
postgres=# create database tbase_db_connlimit CONNECTION LIMIT 100;CREATE DATABASEpostgres=# select datconnlimit from pg_database where datname='tbase_db_connlimit';datconnlimit--------------100(1 row)
配置数据库可以被复制
postgres=# create database tbase_db_istemplate is_template true;CREATE DATABASEpostgres=# select datistemplate from pg_database where datname='tbase_db_istemplate';datistemplate---------------t(1 row)
多个参数一起配置
postgres=# create database tbase_db_mul owner pgxz CONNECTION LIMIT 50 template template0 encoding 'utf8' lc_collate 'C';CREATE DATABASE
修改数据库配置
修改数据库名称
postgres=# alter database tbase_db rename to tbase_db_new;ALTER DATABASE
修改连接数
postgres=# alter database tbase_db_new connection limit 50;ALTER DATABASE
修改数据库所有者
postgres=# alter database tbase_db_new owner to tbase;ALTER DATABASE
配置数据默认运行参行
postgres=# alter database tbase_db_new set search_path to public,pg_catalog,pg_oracle;ALTER DATABASE
Alter database 不支持的项目
项目 | 备注 |
encoding | 编码 |
lc_collate | 排序规则 |
lc_CcType | 分组规 |
postgres=# drop database tbase_db_new;DROP DATABASE##删除数据库时，如果该数据库已经有session连接上来，则会提示如下错误postgres=# drop database tbase_db_template;ERROR: database "tbase_db_template" is being accessed by other usersDETAIL: There is 1 other session using the database.##使用下面方法可以把session断开，然后再删除postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname='tbase_db_template';pg_terminate_backend----------------------t(1 row)postgres=# drop database tbase_db_template;DROP DATABASE
