数据库操作

本章将介绍PostgreSQL数据库的创建、管理、Schema操作等基本操作。

数据库概念

数据库集群

-- PostgreSQL数据库集群是指单个PostgreSQL服务器管理的数据库集合

-- 数据库集群结构：
-- 1. 一个PostgreSQL实例
-- 2. 多个数据库
-- 3. 每个数据库包含多个Schema
-- 4. 每个Schema包含多个对象（表、视图、函数等）

-- 层级关系：
-- PostgreSQL实例 -> 数据库 -> Schema -> 对象（表、视图等）

系统数据库

-- PostgreSQL安装后自动创建的系统数据库：

-- 1. postgres
--    默认管理数据库，用于管理连接
--    建议不要在其中创建用户对象

-- 2. template0
--    干净的模板数据库
--    不应该修改此数据库
--    用于创建新数据库时使用

-- 3. template1
--    默认模板数据库
--    可以在其中创建公共对象
--    新数据库默认使用此模板创建

-- 查看所有数据库
SELECT datname, datdba, encoding, datcollate
FROM pg_database;

-- 使用psql命令查看
\l

创建数据库

基本语法

-- 创建数据库的基本语法

-- 最简单的创建方式
CREATE DATABASE mydb;

-- 指定所有者
CREATE DATABASE mydb OWNER myuser;

-- 完整语法
CREATE DATABASE mydb
    WITH
    OWNER = myuser           -- 所有者
    ENCODING = 'UTF8'        -- 编码
    LC_COLLATE = 'zh_CN.UTF-8'    -- 排序规则
    LC_CTYPE = 'zh_CN.UTF-8'      -- 字符分类
    TEMPLATE = template0     -- 模板数据库
    TABLESPACE = pg_default  -- 表空间
    CONNECTION LIMIT = -1;   -- 连接限制（-1表示无限制）

使用模板创建

-- 使用已存在的数据库作为模板

-- 使用template0创建干净的数据库
CREATE DATABASE mydb TEMPLATE template0;

-- 使用自定义模板
CREATE DATABASE mydb_copy TEMPLATE mydb;

-- 注意：
-- 1. 模板数据库不能有其他连接
-- 2. 模板数据库中的所有对象都会被复制
-- 3. 复制后两个数据库完全独立

查看数据库

-- 查看数据库列表

-- 使用SQL查询
SELECT
    datname AS 数据库名,
    pg_catalog.pg_get_userbyid(datdba) AS 所有者,
    pg_catalog.pg_encoding_to_char(encoding) AS 编码,
    datcollate AS 排序规则
FROM pg_database
WHERE datistemplate = false;  -- 排除模板数据库

-- 使用psql命令
\l
\l+  -- 显示详细信息

-- 查看当前数据库
SELECT current_database();

-- 查看数据库大小
SELECT
    datname,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

修改数据库

修改数据库属性

-- 修改数据库名称
ALTER DATABASE mydb RENAME TO newdb;

-- 修改所有者
ALTER DATABASE mydb OWNER TO newuser;

-- 修改表空间
ALTER DATABASE mydb SET TABLESPACE new_tablespace;

-- 设置连接限制
ALTER DATABASE mydb CONNECTION LIMIT 100;

-- 设置配置参数
ALTER DATABASE mydb SET work_mem = '256MB';
ALTER DATABASE mydb SET log_statement = 'all';

-- 重置配置参数
ALTER DATABASE mydb RESET log_statement;

配置数据库参数

-- 为数据库设置运行时参数

-- 设置单个参数
ALTER DATABASE mydb SET work_mem = '256MB';

-- 设置多个参数
ALTER DATABASE mydb
    SET work_mem = '256MB',
    SET maintenance_work_mem = '512MB',
    SET random_page_cost = 1.1;

-- 查看数据库配置
SELECT * FROM pg_db_role_setting;

-- 重置所有配置
ALTER DATABASE mydb RESET ALL;

删除数据库

删除数据库

-- 删除数据库

-- 基本语法
DROP DATABASE mydb;

-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;

-- 注意：
-- 1. 不能删除正在连接的数据库
-- 2. 删除操作不可逆
-- 3. 需要有CREATEDB权限或超级用户权限

断开连接后删除

-- 断开所有连接后删除数据库

-- 方法1：断开所有连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'mydb'
  AND pid <> pg_backend_pid();  -- 排除当前连接

DROP DATABASE mydb;

-- 方法2：使用脚本
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT pid FROM pg_stat_activity
        WHERE datname = 'mydb' AND pid <> pg_backend_pid()
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

DROP DATABASE mydb;

Schema管理

创建Schema

-- Schema是数据库内的命名空间

-- 创建Schema
CREATE SCHEMA myschema;

-- 指定所有者
CREATE SCHEMA myschema AUTHORIZATION myuser;

-- 为特定用户创建Schema
CREATE SCHEMA AUTHORIZATION myuser;

-- 在Schema中创建对象
CREATE TABLE myschema.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

-- 查看Schema列表
\dn
SELECT schema_name FROM information_schema.schemata;

Schema搜索路径

-- 搜索路径决定查找对象的顺序

-- 查看当前搜索路径
SHOW search_path;

-- 临时设置搜索路径
SET search_path TO myschema, public;

-- 永久设置（数据库级）
ALTER DATABASE mydb SET search_path = myschema, public;

-- 永久设置（用户级）
ALTER USER myuser SET search_path = myschema, public;

-- 在函数中设置
CREATE FUNCTION myfunc() RETURNS void AS $$
BEGIN
    -- 函数内的搜索路径
    SET LOCAL search_path = myschema;
    -- ...
END;
$$ LANGUAGE plpgsql;

删除Schema

-- 删除空Schema
DROP SCHEMA myschema;

-- 如果存在则删除
DROP SCHEMA IF EXISTS myschema;

-- 级联删除（包含所有对象）
DROP SCHEMA myschema CASCADE;

-- 注意：CASCADE会删除Schema中的所有对象！

表空间

创建表空间

-- 表空间是存储数据的物理位置

-- 创建表空间
CREATE TABLESPACE mytablespace
    LOCATION '/data/pg_tablespace/mytablespace';

-- 指定所有者
CREATE TABLESPACE mytablespace
    OWNER myuser
    LOCATION '/data/pg_tablespace/mytablespace';

-- 查看表空间
\db
SELECT * FROM pg_tablespace;

使用表空间

-- 在指定表空间创建表
CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
) TABLESPACE mytablespace;

-- 移动表到其他表空间
ALTER TABLE mytable SET TABLESPACE newtablespace;

-- 移动索引到其他表空间
ALTER INDEX myindex SET TABLESPACE newtablespace;

-- 设置默认表空间
SET default_tablespace = 'mytablespace';

-- 移动数据库到其他表空间
ALTER DATABASE mydb SET TABLESPACE mytablespace;

删除表空间

-- 删除表空间（必须为空）
DROP TABLESPACE mytablespace;

-- 如果存在则删除
DROP TABLESPACE IF EXISTS mytablespace;

-- 注意：删除前需要移动或删除其中的所有对象

数据库维护

数据库统计信息

-- 查看数据库活动统计
SELECT
    datname AS 数据库名,
    numbackends AS 活动连接数,
    xact_commit AS 提交事务数,
    xact_rollback AS 回滚事务数,
    blks_read AS 磁盘读取块数,
    blks_hit AS 缓存命中块数,
    tup_returned AS 返回行数,
    tup_fetched AS 获取行数,
    tup_inserted AS 插入行数,
    tup_updated AS 更新行数,
    tup_deleted AS 删除行数
FROM pg_stat_database;

-- 查看数据库锁
SELECT
    datname AS 数据库名,
    locktype AS 锁类型,
    mode AS 锁模式,
    granted AS 是否获取,
    count(*) AS 数量
FROM pg_locks l
JOIN pg_database d ON l.database = d.oid
GROUP BY datname, locktype, mode, granted;

VACUUM操作

-- VACUUM：清理和优化数据库

-- 基本VACUUM（不锁表）
VACUUM;

-- VACUUM FULL（锁表，完全回收空间）
VACUUM FULL;

-- 更新统计信息
ANALYZE;

-- VACUUM + ANALYZE
VACUUM ANALYZE;

-- 对特定表操作
VACUUM ANALYZE mytable;

-- 查看VACUUM进度
SELECT * FROM pg_stat_progress_vacuum;

-- 自动VACUUM配置
-- 在postgresql.conf中设置：
-- autovacuum = on
-- autovacuum_vacuum_threshold = 50
-- autovacuum_analyze_threshold = 50

本章小结

本章介绍了PostgreSQL数据库的操作：

1. 数据库概念：理解数据库集群、系统数据库
2. 创建数据库：掌握各种创建方式
3. 修改数据库：学会修改属性和配置
4. 删除数据库：掌握安全删除方法
5. Schema管理：理解Schema的作用和管理
6. 表空间：了解表空间的使用
7. 数据库维护：掌握统计和VACUUM操作

下一章，我们将学习数据类型，了解PostgreSQL丰富的数据类型。