TDSQL PostgreSQL版数据库管理-开发指南-文档中心-腾讯云

创建数据库
要创建一个数据库，必须是一个超级用户或者具有特殊的 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 ROLE
postgres=#  create database tdsql_pg_db_owner owner pgxz;
CREATE DATABASE
postgres=# \\l+ tdsql_pg_db_owner
                        List of databases
  Name    | 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 DATABASE
postgres=# \\l+  tdsql_pg_db_encoding  
                         List of databases
   Name     | 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 DATABASE
postgres=# \\l+
                             List of databases
Name   | 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 database
 template0 | 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 DATABASE
postgres=# \\l+ tdsql_pg_db_lc_collate
                        List of databases
   Name     | 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 DATABASE
postgres=# \\l+  tdsql_pg_db_lc_ctype 
                       List of databases
   Name     | 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 DATABASE
postgres=# 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 DATABASE
postgres=# 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 DATABASE
postgres=# 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 ---------------------- t(1 row)postgres=# drop database tdsql_pg_db_template;DROP DATABASE
﻿
﻿
项目	备注
encoding	编码
lc_collate	排序规则
lc_ctype	分组规则
数据库管理

本页目录：

创建数据库

修改数据库配置

删除数据库
