创建数据库
要创建一个数据库,必须是一个超级用户或者具有特殊的 CREATEDB 特权,默认情况下,新数据库将通过克隆标准系统数据库 template1 被创建。可以通过写 TEMPLATE name 指定一个不同的模板。特别地,通过写 TEMPLATE template0 您可以创建一个干净的数据库,它将只包含的 TDSQL PostgreSQL 版所预定义的标准对象。
默认参数创建数据库
postgres=# create database tdsql_pg_db;CREATE DATABASE
指定克隆库
postgres=# create database tdsql_pg_db_template TEMPLATE template0;CREATE DATABASE
指定所有者
postgres=# create role pgxz with login;CREATE ROLEpostgres=# create database tdsql_pg_db_owner owner pgxz;CREATE DATABASEpostgres=# \\l+ tdsql_pg_db_ownerList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description----------------+-------+----------+------------+------------+-------------------+-------+------------+-------------tdsql_pg_db_owner | pgxz | UTF8 | en_US.utf8 | en_US.utf8 | | 18 MB | pg_default |(1 row)
指定编码
postgres=# create database tdsql_pg_db_encoding ENCODING UTF8;CREATE DATABASEpostgres=# \\l+ tdsql_pg_db_encodingList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description-------------------+-------+----------+------------+------------+-------------------+-------+------------+-------------tdsql_pg_db_encoding | dbadmin | UTF8 | en_US.utf8 | en_US.utf8 | | 18 MB | pg_default |(1 row)
创建 gbk 编码
说明:
TDSQL PostgreSQL 版(Oracle 兼容版)支持。
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 | dbadmin | GBK | zh_CN.gbk | zh_CN.gbk | | 19 MB | pg_default |postgres | dbadmin | UTF8 | zh_CN.utf8 | zh_CN.utf8 | | 26 MB | pg_default | default administrative connection databasetemplate0 | dbadmin | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/dbadmin +| 19 MB | pg_default | unmodifiable empty database| | | | | dbadmin=CTc/dbadmin | | |template1 | dbadmin | UTF8 | zh_CN.utf8 | zh_CN.utf8 | =c/dbadmin +| 24 MB | pg_default | default template for new databases| | | | | dbadmin=CTc/dbadmin | | |(4 rows)
指定排序规则
postgres=# create database tdsql_pg_db_lc_collate lc_collate 'C';CREATE DATABASEpostgres=# \\l+ tdsql_pg_db_lc_collateList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description---------------------+-------+----------+---------+------------+-------------------+-------+------------+-------------tdsql_pg_db_lc_collate | dbadmin | UTF8 | C | en_US.utf8 | | 18 MB | pg_default |(1 row)
指定分组规则
postgres=# create database tdsql_pg_db_lc_ctype LC_CTYPE 'C' ;CREATE DATABASEpostgres=# \\l+ tdsql_pg_db_lc_ctypeList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description-------------------+-------+----------+------------+-------+-------------------+-------+------------+-------------tdsql_pg_db_lc_ctype | dbadmin | UTF8 | en_US.utf8 | C | | 18 MB | pg_default |(1 row)
配置数据可连接
postgres=# create database tdsql_pg_db_allow_connections ALLOW_CONNECTIONS true;CREATE DATABASEpostgres=# select datallowconn from pg_database where datname='tdsql_pg_db_allow_connections';datallowconn--------------t(1 row)
配置连接数
postgres=# create database tdsql_pg_db_connlimit CONNECTION LIMIT 100;CREATE DATABASEpostgres=# select datconnlimit from pg_database where datname='tdsql_pg_db_connlimit';datconnlimit--------------100(1 row)
配置数据库可以被复制
postgres=# create database tdsql_pg_db_istemplate is_template true;CREATE DATABASEpostgres=# select datistemplate from pg_database where datname='tdsql_pg_db_istemplate';datistemplate---------------t(1 row)
多个参数一起配置
postgres=# create database tdsql_pg_db_mul owner pgxz CONNECTION LIMIT 50 template template0 encoding 'utf8' lc_collate 'C';CREATE DATABASE
修改数据库配置
修改数据库名称
postgres=# alter database tdsql_pg_db rename to tdsql_pg_db_new;ALTER DATABASE
修改连接数
postgres=# alter database tdsql_pg_db_new connection limit 50;ALTER DATABASE
修改数据库所有者
postgres=# alter database tdsql_pg_db_new owner to tdsql_pg;ALTER DATABASE
配置数据默认运行参行
postgres=# alter database tdsql_pg_db_new set search_path to public,pg_catalog,pg_oracle;ALTER DATABASE
Alter database 不支持的项目
项目 | 备注 |
encoding | 编码 |
lc_collate | 排序规则 |
lc_ctype | 分组规则 |
删除数据库
postgres=# drop database tdsql_pg_db_new;DROP DATABASE
删除数据库时,如果该数据库已经有 session 连接上来,则会提示如下错误:
postgres=# drop database tdsql_pg_db_template;ERROR: database "tdsql_pg_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='tdsql_pg_db_template';pg_terminate_backend----------------------(1 rows)postgres=# drop database tdsql_pg_db_template;DROP DATABASE