openGauss数据库创建方法详解

CREATE DATABASE database_name
[ [ WITH ] 
    [ OWNER [=] user_name ]
    [ TEMPLATE [=] template ]
    [ ENCODING [=] encoding ]
    [ LC_COLLATE [=] lc_collate ]
    [ LC_CTYPE [=] lc_ctype ]
    [ DBCOMPATIBILITY [=] compatibility_type ]
    [ TABLESPACE [=] tablespace_name ]
    [ CONNECTION LIMIT [=] connlimit ]
    [ IS_TEMPLATE [=] is_template ] ];

-- 创建名为sales的数据库
CREATE DATABASE sales;

-- 创建数据库并指定所有者
CREATE DATABASE sales 
    OWNER = omm;

-- 创建数据库并指定字符编码
CREATE DATABASE sales 
    OWNER = omm
    ENCODING = 'UTF8';

-- 创建具有完整参数的数据库
CREATE DATABASE hr_system
    OWNER = hr_admin
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    DBCOMPATIBILITY = 'PG'
    TABLESPACE = pg_default
    CONNECTION LIMIT = 100
    IS_TEMPLATE = false;

# 使用gsql连接数据库
gsql -d postgres -U omm -p 5432 -h 127.0.0.1

# 在gsql中执行创建命令
postgres=# CREATE DATABASE test_db;

# 使用gsql直接执行SQL命令创建数据库
gsql -d postgres -U omm -c "CREATE DATABASE production;"

-- 生产环境数据库创建示例
CREATE DATABASE production_db
    OWNER = dba_user
    TEMPLATE = template0
    ENCODING = 'UTF8'
    LC_COLLATE = 'zh_CN.UTF-8'
    LC_CTYPE = 'zh_CN.UTF-8'
    DBCOMPATIBILITY = 'PG'
    CONNECTION LIMIT = 500
    ALLOW_CONNECTIONS = true;

-- 首先创建表空间
CREATE TABLESPACE fast_ts 
    LOCATION '/gaussdb/data/fast_tablespace';

-- 使用专用表空间创建数据库
CREATE DATABASE fast_db
    TABLESPACE = fast_ts;

-- 创建模板数据库
CREATE DATABASE custom_template
    IS_TEMPLATE = true;

-- 基于自定义模板创建数据库
CREATE DATABASE new_db
    TEMPLATE = custom_template;

-- 切换到新创建的数据库
\c sales

-- 或者使用连接字符串
gsql -d sales -U username

-- 创建模式
CREATE SCHEMA sales_schema;

-- 创建表
CREATE TABLE sales_schema.customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 授予权限给特定用户
GRANT CONNECT ON DATABASE sales TO sales_user;
GRANT USAGE ON SCHEMA sales_schema TO sales_user;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA sales_schema TO sales_user;

-- 检查当前用户权限
SELECT usename, usecreatedb FROM pg_user WHERE usename = CURRENT_USER;

-- 如果需要，使用管理员账户授权
GRANT createdb TO username;

-- 查看支持的字符集
SELECT * FROM pg_catalog.pg_encoding_to_char;

-- 创建数据库时指定正确的字符集
CREATE DATABASE mydb 
    ENCODING = 'UTF8' 
    LC_COLLATE = 'en_US.UTF-8' 
    LC_CTYPE = 'en_US.UTF-8';

-- 检查数据库是否已存在
SELECT datname FROM pg_database WHERE datname = 'target_db';

-- 使用IF NOT EXISTS避免错误
CREATE DATABASE IF NOT EXISTS target_db;

-- 批量创建多个数据库
DO $$
DECLARE
    db_name TEXT;
BEGIN
    FOR db_name IN 
        SELECT unnest(ARRAY['db1', 'db2', 'db3', 'db4'])
    LOOP
        EXECUTE format('CREATE DATABASE %I', db_name);
        RAISE NOTICE 'Database % created successfully', db_name;
    END LOOP;
END $$;

-- 检查新创建的数据库
SELECT 
    datname as database_name,
    datdba as owner_oid,
    encoding,
    datcollate,
    datctype,
    datconnlimit
FROM pg_database 
WHERE datname = 'your_database_name';