首页 PostgreSQL 正文

PostgreSQL教程（4）对象的基本管理

PostgreSQL 2025-08-01 54 0

一、PostgreSQL 对象介绍

对象指的是在 PostgreSQL 数据库实例中可以被创建、管理和操作的各类实体或结构。包含数据库、表、索引、视图、序列、函数、触发器等，这些不同的对象构成了完整的数据库系统。而在对数据库运维工作中最主要的工作内容也是对这些对象进行管理，比如对象权限控制、生命周期管理、性能调优及存储资源分配等操作，以保障系统的高可用性、稳定性与运行效率。

二、表空间管理（Tablespace）

表空间是PostgreSQL数据库中各个对象的物理存储位置，通过表空间可以将数据库、表、索引以及其他对象存放在指定的磁盘或者存储设备上，提高性能或灵活利用存储资源

1、创建 PostgreSQL 表空间

# 创建表空间语法（需要超级用户权限）
CREATE TABLESPACE tablespace_name    #表空间名字，不能pg_打头
[ OWNER user_name ]    #表空间属主，不指定的话默认为执行该命令的用户
LOCATION 'directory'    #表空间存放绝对路径，该目录需要提前创建且为空
[ WITH (tablespace_option = value [, ... ] ) ]    #表空间的参数设置，通常可忽略
    
#示例    
create tablespace tanglu_space location '/data/postgres/tanglu';

#创建表空间tanglu_space，存储路径为/data/postgres/tanglu（数据目录下会生成一个oid并通过软连接指向该目录），并将所属权赋予 tanglu 用户 
create user tanglu password '123456';
create tablespace tanglu_space owner tanglu location '/data/postgres/tanglu';

#改变数据库默认表空间(已有表的表空间不会改变),在执行该操作时不能有用户连接到这个数据库上,否则会报错
alter database testdb set tablespace tanglu_space;

2、使用 PostgreSQL 表空间

#创建数据库时指定表空间，在此数据库中创建的表、 索引会自动存储到该表空间
create database tanglu tablespace tanglu_space;

#创建表时指定表空间
create table t1(id int, name text,age int) tablespace tanglu_space;

#创建索引时指定表空间
create index on t1(id) tablespace tanglu_space;
 
#创建唯一约束时指定约束索引的表空间
alter table t1 add unique(id) using index tablespace tanglu_space;
 
#增加主键时指定主键索引的表空间
alter table t1 add primary key(id) using index tablespace tanglu_space;
 
#把表从一个表空间移到另一个表空间，该操作会锁表，包括SELECT操作，慎重移表
alter table t1 set tablespace pg_default;

3、查看 PostgreSQL 表空间

tanglu_database=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres |

4、修改 PostgreSQL 表空间

#RENAME TO：更改表空间名称 
ALTER TABLESPACE name RENAME TO new_name

#OWNER TO：更改表空间所属用户  
ALTER TABLESPACE name OWNER TO { new_owner |CURRENT_USER | SESSION_USER }

#SET|RESET：设置表空间参数 
ALTER TABLESPACE name SET ( tablespace_option = value [,... ] )
ALTER TABLESPACE name RESET ( tablespace_option [, ... ])

#将表空间tbs_data重命名为tbs_data2：
alter tablespace tbs_data rename to tbs_data2;

#更改表空间tbs_data的拥有者：
alter tablespace tbs_data owner to user2;
 
#更改表空间tbs_data的random_page_cost参数
alter tablespace tbs_data set (random_page_cost=1.1)；
 
#恢复表空间tbs_data的random_page_cost参数
alter tablespace tbs_data reset (random_page_cost)；

5、删除 PostgreSQL 表空间

只有表空间的拥有者或超级用户才能删除表空间。在删除表空间之前，必须保证其上所有的数据库对象已经被清空。如果有任何数据文件存在此表空间，则drop命令执行失败。

# 语法
DROP TABLESPACE [ IF EXISTS ] name

# 删除表空间tanglu_space
drop tablespace tanglu_space;

二、数据库管理（Database）

在PostgreSQL中database是一个逻辑概念，属于顶层对象。一个PostgreSQL实例可以创建多个Database，每个Database 下可有多个 Schema，每个Schema下了包含了表、视图、索引、函数等对象。当客户端连接到一个数据库时，只能访问该数据库中的数据。

1、数据库模板

在PostgreSQL中，创建数据库都是通过复制数据库模板实现的。模板包含了数据库创建后的初始配置，如默认表、数据、索引、函数、权限等。PostgreSQL默认提供template0和template1两个数据库模板，template0 是一个纯净的数据库模板，不包含任何用户定义的对象，适用于需要一个完全干净的数据库的场景。template1是默认模板，如果没有指定模板则会级自动使用该模板包含的相关信息。

2、创建数据库

#语法
CREATE DATABASE name
    [ [ WITH ] [OWNER [=] user_name ]
           [TEMPLATE [=] template ]                 #使用指定模板
           [ENCODING [=] encoding ]
           [LOCALE [=] locale ]
           [LC_COLLATE [=] lc_collate ]
           [LC_CTYPE [=] lc_ctype ]
           [TABLESPACE [=] tablespace_name ] 
           [ALLOW_CONNECTIONS [=] allowconn ]        #是否允许连接
           [CONNECTION LIMIT [=] connlimit ]       #最大连接数
           [IS_TEMPLATE [=] istemplate ] ]          #是否为模板数据库


#创建数据库testdb
create database testdb;
 
#创建数据库db01，指定所属用户为user1，关联的表空间为tbs_data
create database db01 owner user1 tablespace tbs_data;

#创建数据库db02，指定语言环境为en_US.UTF8，由于这里指定了语言环境并且与template1中的语言环境不同， 所以声明使用template0模板 
create database db02 
    locale 'en_US.UTF8'
    template template0;

3、修改数据库

#语法
ALTER DATABASE name [ [ WITH ] option [ ... ] ]

#更改数据库名称
ALTER DATABASE name RENAME TO new_name
alter database testdb rename to testdb2;

#更改数据库所属用户
ALTER DATABASE name OWNER user_name

#更改数据库表空间 
ALTER DATABASE name SET TABLESPACE new_tablespace

#设置数据库参数
ALTER DATABASE name SET configuration_parameter { TO | =} { value | DEFAULT }： 
alter database db02 set enable_indexscan to off;

ALTER DATABASE name SET configuration_parameter FROMCURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

#将数据库db01中的最大连接数改为 200，该操作针对的是数据库的owner用户，管理员不受影响
alter database db01 connection limit 200；

4、删除数据库

#语法
DROP DATABASE [ IF EXISTS ] name [ [ WITH ] ( option [,...] ) ]
 
#删除db01数据库，如果该数据库有用户已经连接则无法删除 
drop database db01;

#从 PostgreSQL 13 开始，支持通过 WITH (FORCE)来删除存在连接的数据库
drop database db02 (force);

5、实际业务中建库流程

# 连接到 PostgreSQL 服务器
psql -U postgres -h localhost

# 创建表空间（可选）
CREATE TABLESPACE tanglu_space LOCATION '/path/to/tanglu_space';

# 创建数据库并指定表空间
CREATE DATABASE tanglu TABLESPACE tanglu_space;

# 连接到业务数据库
\c tanglu

# 创建 schema
CREATE SCHEMA business;

# 在 schema 中创建表
CREATE TABLE business.customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE business.orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES business.customers(customer_id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    amount DECIMAL(10, 2) NOT NULL
);

# 创建用户并分配权限
CREATE USER tanglu_user WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE tanglu TO tanglu_user;
GRANT USAGE ON SCHEMA business TO tanglu_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA business TO tanglu_user;

#  插入数据
INSERT INTO business.customers (name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO business.orders (customer_id, amount) VALUES (1, 100.50);

# 查询数据
SELECT * FROM business.customers;
SELECT * FROM business.orders;

三、模式管理（Schema）

模式用于将数据库中的各个对象（表、索引、函数等）逻辑上组织在一起，便于控制管理（表空间则是物理上组织在一起）。如果管理员没有手动创建Schema，那么所有对象默认属于一个公用模式public下，该模式是初始化数据库后自动创建的。

通常建议根据业务进行分类，同业务的对象放在该业务的模式下。模式之间的数据是互相隔离的。一个用户可以创建多个模式，而一个模式只能属于一个用户。这里容易和MySQL中的database混淆。在PostgreSQL中Database是最高级别的存储单位，每个数据库都是相互独立的，不能直接跨数据库操作。当连接到某个数据库以后所做的操作都是针对该数据库的对象，而通过Schema是数据库中的逻辑命名空间，用于组织和管理数据库对象

1、创建模式

create schema test_schema;

2、模式授权

GRANT USAGE ON SCHEMA schema_name TO user_name;
GRANT SELECT ON schema_name.table_name TO user_name;

四、表管理（Table）

表是数据库中用于存储数据的结构化对象，它由一组列组成，每列都有对应的数据类型

#在指定模式下创建表
create table test_schema.t1(id int)

五、扩展管理（Extension）

高扩展性是PostgreSQL的一大特性，PostgreSQL提供了大量扩展可以实现特殊的需求，比如通过file_fdw扩展访问外部文件系统的文件、通过oracle_fdw访问Oracle数据等。大多数的扩展通过命令行可以直接安装，都不用修改配置文件和重启数据库

-- 查看所有可用扩展
SELECT * FROM pg_available_extensions;

-- 安装pg_stat_statements扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

某些扩展需要在数据库启动时预载加载才可以安装，所以需要提前在 postgresql.conf 中设置并重启数据库才可。如果通过语句安装扩展出现类似 ERROR: could not access file "pg_stat_statements": No such file or directory这样的报错，表示需要先通过配置文件预加载

#修改postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'  #声明需要的扩展名

#重启数据库
systemctl restart postgresql  #或pg_ctl restart -D $PGDATA