創建數據庫
更新時間 2025-02-05 09:36:25
最近更新時間: 2025-02-05 09:36:25
分享文章
本文為您介紹創建數據庫的相關操作。
注意
只有超級用戶或具有特殊的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
