纲要
Database Cluster（数据库集群）与Instance（实例）的概念辨析Cluster的物理存储视角Instance的运行时视角
Database、Schema、Object的逻辑层级结构- 跨数据库访问的限制：
dblink与postgres_fdw
- 跨数据库访问的限制：
Template Database（模板数据库）：template0与template1CREATE DATABASE的复制机制template0的“原始”用途与编码/区域设置datistemplate与datallowconn标志
Tablespace（表空间）CREATE TABLESPACE与软链接机制pg_default与pg_global默认表空间- 冷热分离的存储策略
OID（Object Identifier，对象标识符）pg_class系统表与regclass类型oid2name工具
Database Cluster 与 Instance
在 PostgreSQL 的体系架构中，Database Cluster（数据库集群，SQL 标准中称为 Catalog Cluster）与 Instance（实例）是两个最基础的概念。
Database Cluster 指的是由单个正在运行的数据库服务器实例管理的一组数据库的集合。从文件系统的角度看，一个 Database Cluster 就是一个目录（即 PGDATA 所指向的数据目录），该目录下存放了该集群中所有数据库的全部数据文件。
Instance（实例）则是指正在运行的 postgres 进程及其所管理的内存结构与后台进程。一个 postgres 实例在任何时刻都恰好管理一个 Database Cluster 的数据。在一台物理主机上，可以运行多个 PostgreSQL 实例，只要它们使用不同的数据目录和不同的通信端口即可。
在 PostgreSQL 的官方术语中，“实例”与“数据库集群”在非正式语境下有时会被混用，但严格区分如下：
- Instance（实例） ：运行时的概念，指
postgres主进程、子进程以及共享内存等运行时资源。 - Cluster（集群） ：存储与数据的概念，指
PGDATA目录下的全部数据文件与配置文件。
一个 Database Cluster 在初始化（通过 initdb）后，会默认包含三个数据库：postgres、template1 和 template0。postgres 是供工具程序和用户连接的默认数据库。
# 初始化一个数据库集群
initdb -D /usr/local/pgsql/data
# 通过 pg_ctl 启动实例
pg_ctl -D /usr/local/pgsql/data start
Database、Schema 与 Object 的逻辑层级
在一个 Database Cluster 内部，PostgreSQL 的逻辑组织结构为：Cluster → Database → Schema → Object。
- Database（数据库） ：相互隔离的逻辑容器。PostgreSQL 中的各个数据库之间是严格隔离的，默认情况下不支持跨数据库的直接访问。
- Schema（模式） ：数据库内部的命名空间。默认情况下，用户创建的对象（如表、索引、视图、函数、序列、触发器、存储过程等）都位于
public模式下。 - Object（对象） ：具体的数据库实体，包括
TABLE、INDEX、VIEW、FUNCTION、SEQUENCE、TRIGGER、PROCEDURE等。
PostgreSQL 的跨数据库访问（Cross-Database Reference）默认不被支持。以下示例展示了尝试跨库查询时的错误：
-- 创建两个数据库
CREATE DATABASE mydb1;
CREATE DATABASE mydb2;
\c mydb1
CREATE TABLE t1 (id int);
-- 尝试跨库查询（会报错）
\c mydb2
SELECT * FROM mydb1.public.t1;
-- ERROR: cross-database references are not implemented
要实现跨数据库访问，官方提供了两种标准方案：
dblink模块：基于函数的较老方案，通过dblink_connect()建立连接后执行远程查询。postgres_fdw模块：基于 SQL/MED（Foreign Data Wrapper）标准的较新方案，语法更透明，性能更优。
-- 使用 postgres_fdw 访问同一集群内的另一个数据库
CREATE EXTENSION postgres_fdw;
CREATE SERVER mydb1_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'mydb1', host 'localhost', port '5432');
CREATE USER MAPPING FOR CURRENT_USER
SERVER mydb1_server
OPTIONS (user 'postgres');
CREATE FOREIGN TABLE remote_t1 (id int)
SERVER mydb1_server
OPTIONS (schema_name 'public', table_name 't1');
-- 现在可以像访问本地表一样查询远程表
SELECT * FROM remote_t1;
Template Databases（模板数据库）
CREATE DATABASE 命令在 PostgreSQL 中实际上是通过复制一个现有数据库来工作的。默认情况下，它复制名为 template1 的标准系统数据库。
template1
template1 是创建新数据库的默认模板。如果在 template1 中添加了对象（如扩展、函数、表等），这些对象会被自动复制到所有后续创建的用户数据库中。这使得 DBA 可以方便地对所有新数据库进行“站点本地定制”。
template0
template0 是第二个标准系统数据库。它包含的是 PostgreSQL 版本预定义的标准对象，不包含任何用户添加的内容。template0 在数据库集群初始化后不应被修改。
使用 template0 作为模板创建数据库主要有两个场景：
- 恢复
pg_dump转储：确保恢复到一个“原始”状态的数据库中，避免与template1中的站点本地对象产生冲突。 - 指定不同的编码或区域设置（Locale） ：复制
template1时必须使用与template1相同的编码和区域设置，而复制template0时可以指定新的编码和区域设置。
-- 使用 template0 创建数据库，并指定不同的编码和排序规则
CREATE DATABASE mydb3
TEMPLATE template0
ENCODING 'UTF8'
LC_COLLATE 'C'
LC_CTYPE 'C';
-- 查看数据库的模板属性
SELECT datname, datistemplate, datallowconn
FROM pg_database;
pg_database 系统表中的两个标志列控制着模板数据库的行为：
datistemplate：若为true，任何具有CREATEDB权限的用户都可以克隆该数据库；若为false，则只有超级用户和数据库所有者可以克隆。datallowconn：若为false，则不允许对该数据库建立新的连接。template0通常被标记为datallowconn = false以防止被意外修改。
-- 将一个现有数据库标记为模板数据库
UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template_db';
快速克隆数据库
利用模板数据库的特性，可以快速“克隆”（fork）一个现有的数据库：
-- 基于现有的 mydb 快速创建一个新数据库 mydb_clone
CREATE DATABASE mydb_clone TEMPLATE mydb;
Tablespace（表空间）
Tablespace（表空间）允许数据库管理员在文件系统中定义存储数据库对象文件的位置。创建表空间后，可以在创建数据库对象时按名称引用。
创建表空间
-- 首先在操作系统中创建空目录
-- mkdir -p /ssd1/postgresql/data
-- chown postgres:postgres /ssd1/postgresql/data
CREATE TABLESPACE fastspace
LOCATION '/ssd1/postgresql/data';
表空间的 LOCATION 必须是一个已存在的、空目录，并且必须由 PostgreSQL 操作系统用户拥有。
默认表空间
数据库集群初始化时会自动创建两个表空间：
| 表空间 | 用途 | 映射路径 |
|---|---|---|
pg_default | 存储用户数据（非共享对象） | $PGDATA/base/ |
pg_global | 存储共享系统目录 | $PGDATA/global/ |
pg_default 是 template1 和 template0 的默认表空间，因此也是绝大多数新数据库的默认表空间。
表空间的使用
-- 在指定表空间中创建表
CREATE TABLE foo (i int) TABLESPACE fastspace;
-- 在指定表空间中创建索引
CREATE INDEX idx_foo_i ON foo(i) TABLESPACE fastspace;
-- 创建数据库时指定默认表空间
CREATE DATABASE mydb TABLESPACE fastspace;
-- 设置会话级别的默认表空间
SET default_tablespace = fastspace;
CREATE TABLE bar (j int); -- 自动使用 fastspace
冷热分离策略
表空间的核心应用场景之一是冷热数据分离：
- 热数据（频繁访问的表和索引）放置在 SSD 上的表空间（如
fastspace）。 - 冷数据（历史归档表、不常访问的索引）放置在大容量 HDD 上的表空间（如
slowspace）。
表空间的物理存储
PostgreSQL 中的表空间在文件系统层面通过软链接（symlink） 实现。表空间目录下会有一个 PG_<version>_<timestamp> 子目录，其中存储了该表空间中各数据库的 OID 目录。
# 查看表空间的软链接
ls -la $PGDATA/pg_tblspc/
# 示例输出:
# lrwxrwxrwx 1 postgres postgres 25 Jan 15 10:00 16384 -> /ssd1/postgresql/data
表空间的限制
- 表空间是数据库集群的不可分割的一部分，不能单独备份或迁移到另一个集群。
- 如果表空间丢失或损坏，整个数据库集群可能无法启动。
- 表空间的存储容量受限于底层文件系统的容量。
OID（Object Identifier，对象标识符）
OID（Object Identifier，对象标识符）是 PostgreSQL 内部用于标识各种系统表主键的 32 位整数。
在文件系统层面，PostgreSQL 使用 OID 来组织数据文件。$PGDATA/base/ 目录下的数字目录名就是数据库的 OID，而其中的数字文件名则是表或索引的 relfilenode（通常与 OID 一致或相关）。
-- 查询数据库的 OID
SELECT oid, datname FROM pg_database;
-- 查询表的 OID 和 relfilenode
SELECT oid, relname, relfilenode
FROM pg_class
WHERE relname = 'foo';
PostgreSQL 提供了 regclass 别名类型，可以将表名转换为 OID：
-- 使用 regclass 将表名转换为 OID
SELECT 'foo'::regclass::oid;
oid2name 工具
PostgreSQL 提供了 oid2name 命令行工具，用于将 OID 映射为对应的对象名称：
# 查看指定 OID 对应的对象名称
oid2name -d mydb -o 16384
# 列出数据库中所有表及其 OID
oid2name -d mydb -s
API 速览（API Overview）
数据库管理 API
| API | 所属模块 | 方法签名 | 说明 |
|---|---|---|---|
CREATE DATABASE | PostgreSQL DDL | CREATE DATABASE name [WITH] [TEMPLATE template] [ENCODING encoding] [LC_COLLATE collate] [LC_CTYPE ctype] [TABLESPACE tablespace] | 创建新数据库，可通过 TEMPLATE 指定模板 |
DROP DATABASE | PostgreSQL DDL | DROP DATABASE name | 删除数据库 |
ALTER DATABASE | PostgreSQL DDL | ALTER DATABASE name SET parameter = value | 修改数据库配置 |
表空间管理 API
| API | 所属模块 | 方法签名 | 说明 |
|---|---|---|---|
CREATE TABLESPACE | PostgreSQL DDL | CREATE TABLESPACE name LOCATION 'directory' | 创建表空间 |
DROP TABLESPACE | PostgreSQL DDL | DROP TABLESPACE name | 删除空表空间 |
SET default_tablespace | PostgreSQL GUC | SET default_tablespace = 'name' | 设置会话级默认表空间 |
系统目录查询 API
| API | 所属模块 | 方法签名 | 说明 |
|---|---|---|---|
pg_database | 系统表 | SELECT * FROM pg_database | 查询所有数据库及其属性（datistemplate、datallowconn） |
pg_class | 系统表 | SELECT * FROM pg_class | 查询所有表和索引的元数据（oid、relname、relfilenode） |
::regclass | PostgreSQL 类型转换 | 'table_name'::regclass | 将表名转换为 OID |
oid2name | 命令行工具 | oid2name -d dbname -o oid | 将 OID 映射为对象名称 |
跨库访问 API
| API | 所属模块 | 方法签名 | 说明 |
|---|---|---|---|
dblink_connect | dblink | dblink_connect(text connstr) | 建立跨库连接 |
dblink | dblink | dblink(text connname, text sql) | 执行远程 SQL 查询 |
CREATE FOREIGN DATA WRAPPER | postgres_fdw | CREATE FOREIGN DATA WRAPPER name | 创建外部数据包装器 |
CREATE SERVER | postgres_fdw | CREATE SERVER name FOREIGN DATA WRAPPER fdw OPTIONS (...) | 创建外部服务器 |
CREATE FOREIGN TABLE | postgres_fdw | CREATE FOREIGN TABLE name (...) SERVER server OPTIONS (...) | 创建外部表映射 |
Demo 完整示例（Complete Demo）
以下是一个基于 Node.js 和 node-postgres（pg 库）的完整演示，涵盖数据库连接、模板数据库创建、表空间操作和系统目录查询。
# 项目初始化
mkdir pg-demo && cd pg-demo
npm init -y
npm install pg
// index.ts
import { Client, Pool } from 'pg';
// PostgreSQL 连接配置
const config = {
host: 'localhost',
port: 5432,
user: 'postgres',
password: 'postgres',
database: 'postgres',
};
// 创建连接池
const pool = new Pool(config);
/**
* 执行 SQL 查询的辅助函数
*/
async function executeQuery(sql: string, params: any[] = []): Promise<any> {
const client = await pool.connect();
try {
const result = await client.query(sql, params);
return result.rows;
} finally {
client.release();
}
}
/**
* 核心 Demo：展示 PostgreSQL 逻辑体系架构的核心操作
*/
async function main() {
console.log('=== PostgreSQL 逻辑体系架构 Demo ===\n');
// 1. 查看数据库集群中的数据库列表
console.log('1. 当前数据库集群中的所有数据库:');
const dbs = await executeQuery(
`SELECT oid, datname, datistemplate, datallowconn
FROM pg_database
ORDER BY datname`
);
console.table(dbs);
// 2. 创建模板数据库
console.log('\n2. 创建一个自定义模板数据库 (my_template):');
await executeQuery(`DROP DATABASE IF EXISTS my_template`);
await executeQuery(`CREATE DATABASE my_template`);
await executeQuery(`\c my_template`);
await executeQuery(`CREATE TABLE template_table (id int, name text)`);
await executeQuery(`INSERT INTO template_table VALUES (1, 'template data')`);
// 标记为模板数据库
await executeQuery(
`UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template'`
);
console.log(' ✓ 模板数据库 my_template 创建完成，并标记为 datistemplate = true');
// 3. 基于模板数据库创建新数据库
console.log('\n3. 基于 my_template 克隆新数据库 (cloned_db):');
await executeQuery(`DROP DATABASE IF EXISTS cloned_db`);
await executeQuery(`CREATE DATABASE cloned_db TEMPLATE my_template`);
console.log(' ✓ 克隆数据库 cloned_db 创建完成');
// 4. 验证克隆结果
console.log('\n4. 验证克隆结果 (查询 cloned_db 中的数据):');
const rows = await executeQuery(
`SELECT * FROM cloned_db.public.template_table`
);
console.table(rows);
// 5. 使用 template0 创建指定编码的数据库
console.log('\n5. 使用 template0 创建指定 LC_COLLATE=C 的数据库 (mydb_c):');
await executeQuery(`DROP DATABASE IF EXISTS mydb_c`);
await executeQuery(`
CREATE DATABASE mydb_c
TEMPLATE template0
ENCODING 'UTF8'
LC_COLLATE 'C'
LC_CTYPE 'C'
`);
console.log(' ✓ 数据库 mydb_c 创建完成 (LC_COLLATE = C)');
// 6. 验证排序规则
console.log('\n6. 验证数据库的排序规则:');
const collateInfo = await executeQuery(
`SELECT datname, datcollate, datctype FROM pg_database WHERE datname = 'mydb_c'`
);
console.table(collateInfo);
// 7. 表空间操作
console.log('\n7. 表空间操作:');
// 查看现有表空间
const tablespaces = await executeQuery(
`SELECT spcname, pg_tablespace_location(oid) as location
FROM pg_tablespace`
);
console.log(' 当前表空间列表:');
console.table(tablespaces);
// 8. OID 查询
console.log('\n8. OID 查询示例:');
// 切换到 cloned_db 创建测试表
await executeQuery(`\c cloned_db`);
await executeQuery(`CREATE TABLE IF NOT EXISTS test_oid (id int)`);
const oidInfo = await executeQuery(
`SELECT oid, relname, relfilenode
FROM pg_class
WHERE relname = 'test_oid'`
);
console.table(oidInfo);
// 使用 regclass 转换
const regclassResult = await executeQuery(
`SELECT 'test_oid'::regclass::oid as oid_from_regclass`
);
console.log(' 通过 regclass 获取 OID:', regclassResult[0]?.oid_from_regclass);
console.log('\n=== Demo 执行完成 ===');
}
// 执行主函数
main()
.then(() => process.exit(0))
.catch((err) => {
console.error('Demo 执行失败:', err);
process.exit(1);
});
运行说明
# 确保 PostgreSQL 已启动
# 方式一：使用 TypeScript（需安装 ts-node）
npx ts-node index.ts
# 方式二：使用 Node.js（将 .ts 改为 .js 或使用编译后的文件）
node index.js
关键技术点总结
- 模板数据库克隆：
CREATE DATABASE ... TEMPLATE实现了数据库级别的快速复制。 datistemplate标志：控制数据库是否可作为模板被克隆。template0与编码/区域设置：使用template0可以突破template1的编码限制。pg_database系统表：查询数据库的 OID、模板属性和连接属性。pg_class与regclass：获取表和索引的 OID 及relfilenode。- 表空间查询：
pg_tablespace系统表与pg_tablespace_location()函数。
多语言示例
本节提供与 Node.js Demo 功能等价的 Go、Python 和 Java 实现，涵盖数据库连接、模板数据库克隆、表空间查询和 OID 查询等核心操作。
Go 示例
使用 pgx 驱动（推荐）或 lib/pq。以下基于 pgx/v5。
# 项目初始化
mkdir pg-demo-go && cd pg-demo-go
go mod init pg-demo
go get github.com/jackc/pgx/v5
// main.go
package main
import (
"context"
"fmt"
"log"
"github.com/jackc/pgx/v5"
)
func main() {
ctx := context.Background()
// 连接到 postgres 数据库
connStr := "postgres://postgres:postgres@localhost:5432/postgres"
conn, err := pgx.Connect(ctx, connStr)
if err != nil {
log.Fatalf("连接失败: %v", err)
}
defer conn.Close(ctx)
fmt.Println("=== PostgreSQL 逻辑体系架构 Demo (Go) ===\n")
// 1. 查看所有数据库
fmt.Println("1. 当前数据库集群中的所有数据库:")
rows, err := conn.Query(ctx, `SELECT oid, datname, datistemplate, datallowconn FROM pg_database ORDER BY datname`)
if err != nil {
log.Fatalf("查询失败: %v", err)
}
defer rows.Close()
for rows.Next() {
var oid uint32
var datname string
var datistemplate, datallowconn bool
if err := rows.Scan(&oid, &datname, &datistemplate, &datallowconn); err != nil {
log.Fatal(err)
}
fmt.Printf(" OID: %d, 名称: %s, 可作模板: %v, 允许连接: %v\n", oid, datname, datistemplate, datallowconn)
}
if err = rows.Err(); err != nil {
log.Fatal(err)
}
// 2. 创建自定义模板数据库
fmt.Println("\n2. 创建自定义模板数据库 (my_template):")
_, err = conn.Exec(ctx, `DROP DATABASE IF EXISTS my_template`)
if err != nil {
log.Fatal(err)
}
_, err = conn.Exec(ctx, `CREATE DATABASE my_template`)
if err != nil {
log.Fatal(err)
}
// 切换到 my_template 创建表
_, err = conn.Exec(ctx, `\c my_template`) // 注意：pgx 不支持 \c，需要新建连接
// 改为新建连接
connTemplate, err := pgx.Connect(ctx, "postgres://postgres:postgres@localhost:5432/my_template")
if err != nil {
log.Fatal(err)
}
defer connTemplate.Close(ctx)
_, err = connTemplate.Exec(ctx, `CREATE TABLE template_table (id int, name text)`)
if err != nil {
log.Fatal(err)
}
_, err = connTemplate.Exec(ctx, `INSERT INTO template_table VALUES (1, 'template data')`)
if err != nil {
log.Fatal(err)
}
// 标记为模板数据库
_, err = conn.Exec(ctx, `UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template'`)
if err != nil {
log.Fatal(err)
}
fmt.Println(" ✓ 模板数据库 my_template 创建完成")
// 3. 基于模板克隆
fmt.Println("\n3. 基于 my_template 克隆新数据库 (cloned_db):")
_, err = conn.Exec(ctx, `DROP DATABASE IF EXISTS cloned_db`)
if err != nil {
log.Fatal(err)
}
_, err = conn.Exec(ctx, `CREATE DATABASE cloned_db TEMPLATE my_template`)
if err != nil {
log.Fatal(err)
}
fmt.Println(" ✓ 克隆数据库 cloned_db 创建完成")
// 4. 验证克隆数据
fmt.Println("\n4. 验证克隆结果:")
connClone, err := pgx.Connect(ctx, "postgres://postgres:postgres@localhost:5432/cloned_db")
if err != nil {
log.Fatal(err)
}
defer connClone.Close(ctx)
var id int
var name string
err = connClone.QueryRow(ctx, `SELECT id, name FROM template_table`).Scan(&id, &name)
if err != nil {
log.Fatal(err)
}
fmt.Printf(" cloned_db 中数据: id=%d, name=%s\n", id, name)
// 5. 使用 template0 创建指定编码数据库
fmt.Println("\n5. 使用 template0 创建 LC_COLLATE=C 的数据库 (mydb_c):")
_, err = conn.Exec(ctx, `DROP DATABASE IF EXISTS mydb_c`)
if err != nil {
log.Fatal(err)
}
_, err = conn.Exec(ctx, `
CREATE DATABASE mydb_c
TEMPLATE template0
ENCODING 'UTF8'
LC_COLLATE 'C'
LC_CTYPE 'C'
`)
if err != nil {
log.Fatal(err)
}
fmt.Println(" ✓ 数据库 mydb_c 创建完成")
// 6. 查询排序规则
fmt.Println("\n6. 验证数据库的排序规则:")
var datcollate, datctype string
err = conn.QueryRow(ctx, `SELECT datcollate, datctype FROM pg_database WHERE datname = 'mydb_c'`).Scan(&datcollate, &datctype)
if err != nil {
log.Fatal(err)
}
fmt.Printf(" datcollate: %s, datctype: %s\n", datcollate, datctype)
// 7. 表空间列表
fmt.Println("\n7. 当前表空间列表:")
rowsTS, err := conn.Query(ctx, `SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace`)
if err != nil {
log.Fatal(err)
}
defer rowsTS.Close()
for rowsTS.Next() {
var spcname string
var location *string
if err := rowsTS.Scan(&spcname, &location); err != nil {
log.Fatal(err)
}
loc := "NULL"
if location != nil {
loc = *location
}
fmt.Printf(" %s -> %s\n", spcname, loc)
}
// 8. OID 查询
fmt.Println("\n8. OID 查询示例:")
// 在 cloned_db 中建表
_, err = connClone.Exec(ctx, `CREATE TABLE IF NOT EXISTS test_oid (id int)`)
if err != nil {
log.Fatal(err)
}
var oidVal uint32
var relname, relfilenode string
err = connClone.QueryRow(ctx, `SELECT oid, relname, relfilenode::text FROM pg_class WHERE relname = 'test_oid'`).Scan(&oidVal, &relname, &relfilenode)
if err != nil {
log.Fatal(err)
}
fmt.Printf(" test_oid: OID=%d, relfilenode=%s\n", oidVal, relfilenode)
// 使用 regclass
var oidFromRegclass uint32
err = connClone.QueryRow(ctx, `SELECT 'test_oid'::regclass::oid`).Scan(&oidFromRegclass)
if err != nil {
log.Fatal(err)
}
fmt.Printf(" 通过 regclass 获取 OID: %d\n", oidFromRegclass)
fmt.Println("\n=== Demo 执行完成 ===")
}
运行说明：
go run main.go
Python 示例
使用 psycopg2（推荐）或 asyncpg。以下基于 psycopg2。
# 安装依赖
pip install psycopg2-binary
# main.py
import psycopg2
from psycopg2 import sql
def main():
conn = psycopg2.connect(
host="localhost",
port=5432,
user="postgres",
password="postgres",
dbname="postgres"
)
conn.autocommit = True
cur = conn.cursor()
print("=== PostgreSQL 逻辑体系架构 Demo (Python) ===\n")
# 1. 查看所有数据库
print("1. 当前数据库集群中的所有数据库:")
cur.execute("SELECT oid, datname, datistemplate, datallowconn FROM pg_database ORDER BY datname")
for oid, datname, datistemplate, datallowconn in cur.fetchall():
print(f" OID: {oid}, 名称: {datname}, 可作模板: {datistemplate}, 允许连接: {datallowconn}")
# 2. 创建模板数据库
print("\n2. 创建自定义模板数据库 (my_template):")
cur.execute("DROP DATABASE IF EXISTS my_template")
cur.execute("CREATE DATABASE my_template")
# 切换到 my_template
conn_template = psycopg2.connect(
host="localhost", port=5432, user="postgres", password="postgres", dbname="my_template"
)
conn_template.autocommit = True
cur_t = conn_template.cursor()
cur_t.execute("CREATE TABLE template_table (id int, name text)")
cur_t.execute("INSERT INTO template_table VALUES (1, 'template data')")
cur_t.close()
conn_template.close()
# 标记为模板
cur.execute("UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template'")
print(" ✓ 模板数据库 my_template 创建完成")
# 3. 克隆
print("\n3. 基于 my_template 克隆新数据库 (cloned_db):")
cur.execute("DROP DATABASE IF EXISTS cloned_db")
cur.execute("CREATE DATABASE cloned_db TEMPLATE my_template")
print(" ✓ 克隆数据库 cloned_db 创建完成")
# 4. 验证
print("\n4. 验证克隆结果:")
conn_clone = psycopg2.connect(
host="localhost", port=5432, user="postgres", password="postgres", dbname="cloned_db"
)
conn_clone.autocommit = True
cur_c = conn_clone.cursor()
cur_c.execute("SELECT id, name FROM template_table")
row = cur_c.fetchone()
print(f" cloned_db 中数据: id={row[0]}, name={row[1]}")
cur_c.close()
conn_clone.close()
# 5. 使用 template0
print("\n5. 使用 template0 创建 LC_COLLATE=C 的数据库 (mydb_c):")
cur.execute("DROP DATABASE IF EXISTS mydb_c")
cur.execute("""
CREATE DATABASE mydb_c
TEMPLATE template0
ENCODING 'UTF8'
LC_COLLATE 'C'
LC_CTYPE 'C'
""")
print(" ✓ 数据库 mydb_c 创建完成")
# 6. 排序规则
print("\n6. 验证数据库的排序规则:")
cur.execute("SELECT datcollate, datctype FROM pg_database WHERE datname = 'mydb_c'")
datcollate, datctype = cur.fetchone()
print(f" datcollate: {datcollate}, datctype: {datctype}")
# 7. 表空间
print("\n7. 当前表空间列表:")
cur.execute("SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace")
for spcname, location in cur.fetchall():
loc = location if location else "NULL"
print(f" {spcname} -> {loc}")
# 8. OID
print("\n8. OID 查询示例:")
# 在 cloned_db 中操作
conn_clone2 = psycopg2.connect(
host="localhost", port=5432, user="postgres", password="postgres", dbname="cloned_db"
)
conn_clone2.autocommit = True
cur_c2 = conn_clone2.cursor()
cur_c2.execute("CREATE TABLE IF NOT EXISTS test_oid (id int)")
cur_c2.execute("SELECT oid, relname, relfilenode::text FROM pg_class WHERE relname = 'test_oid'")
oid_val, relname, relfilenode = cur_c2.fetchone()
print(f" test_oid: OID={oid_val}, relfilenode={relfilenode}")
cur_c2.execute("SELECT 'test_oid'::regclass::oid")
oid_reg = cur_c2.fetchone()[0]
print(f" 通过 regclass 获取 OID: {oid_reg}")
cur_c2.close()
conn_clone2.close()
cur.close()
conn.close()
print("\n=== Demo 执行完成 ===")
if __name__ == "__main__":
main()
运行说明：
python main.py
Java 示例
使用 JDBC 驱动（org.postgresql:postgresql）。以下基于 Maven 项目。
# 创建 Maven 项目
mvn archetype:generate -DgroupId=com.example -DartifactId=pg-demo -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
cd pg-demo
# 编辑 pom.xml 添加依赖
# 然后在 src/main/java/com/example/ 下创建 App.java
<!-- pom.xml 依赖片段 -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.3</version>
</dependency>
// src/main/java/com/example/App.java
package com.example;
import java.sql.*;
import java.util.Properties;
public class App {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/postgres";
Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", "postgres");
try (Connection conn = DriverManager.getConnection(url, props)) {
conn.setAutoCommit(true);
System.out.println("=== PostgreSQL 逻辑体系架构 Demo (Java) ===\n");
// 1. 查看所有数据库
System.out.println("1. 当前数据库集群中的所有数据库:");
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(
"SELECT oid, datname, datistemplate, datallowconn FROM pg_database ORDER BY datname")) {
while (rs.next()) {
int oid = rs.getInt("oid");
String datname = rs.getString("datname");
boolean datistemplate = rs.getBoolean("datistemplate");
boolean datallowconn = rs.getBoolean("datallowconn");
System.out.printf(" OID: %d, 名称: %s, 可作模板: %b, 允许连接: %b%n",
oid, datname, datistemplate, datallowconn);
}
}
// 2. 创建模板数据库
System.out.println("\n2. 创建自定义模板数据库 (my_template):");
try (Statement stmt = conn.createStatement()) {
stmt.execute("DROP DATABASE IF EXISTS my_template");
stmt.execute("CREATE DATABASE my_template");
}
// 连接到 my_template
String urlTemplate = "jdbc:postgresql://localhost:5432/my_template";
try (Connection connTemplate = DriverManager.getConnection(urlTemplate, props);
Statement stmtT = connTemplate.createStatement()) {
stmtT.execute("CREATE TABLE template_table (id int, name text)");
stmtT.execute("INSERT INTO template_table VALUES (1, 'template data')");
}
// 标记为模板
try (Statement stmt = conn.createStatement()) {
stmt.execute("UPDATE pg_database SET datistemplate = true WHERE datname = 'my_template'");
}
System.out.println(" ✓ 模板数据库 my_template 创建完成");
// 3. 克隆
System.out.println("\n3. 基于 my_template 克隆新数据库 (cloned_db):");
try (Statement stmt = conn.createStatement()) {
stmt.execute("DROP DATABASE IF EXISTS cloned_db");
stmt.execute("CREATE DATABASE cloned_db TEMPLATE my_template");
}
System.out.println(" ✓ 克隆数据库 cloned_db 创建完成");
// 4. 验证
System.out.println("\n4. 验证克隆结果:");
String urlClone = "jdbc:postgresql://localhost:5432/cloned_db";
try (Connection connClone = DriverManager.getConnection(urlClone, props);
Statement stmtC = connClone.createStatement();
ResultSet rs = stmtC.executeQuery("SELECT id, name FROM template_table")) {
if (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
System.out.printf(" cloned_db 中数据: id=%d, name=%s%n", id, name);
}
}
// 5. 使用 template0
System.out.println("\n5. 使用 template0 创建 LC_COLLATE=C 的数据库 (mydb_c):");
try (Statement stmt = conn.createStatement()) {
stmt.execute("DROP DATABASE IF EXISTS mydb_c");
stmt.execute("CREATE DATABASE mydb_c TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C'");
}
System.out.println(" ✓ 数据库 mydb_c 创建完成");
// 6. 排序规则
System.out.println("\n6. 验证数据库的排序规则:");
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT datcollate, datctype FROM pg_database WHERE datname = 'mydb_c'")) {
if (rs.next()) {
String datcollate = rs.getString("datcollate");
String datctype = rs.getString("datctype");
System.out.printf(" datcollate: %s, datctype: %s%n", datcollate, datctype);
}
}
// 7. 表空间
System.out.println("\n7. 当前表空间列表:");
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace")) {
while (rs.next()) {
String spcname = rs.getString(1);
String location = rs.getString(2);
String loc = (location == null) ? "NULL" : location;
System.out.printf(" %s -> %s%n", spcname, loc);
}
}
// 8. OID
System.out.println("\n8. OID 查询示例:");
String urlClone2 = "jdbc:postgresql://localhost:5432/cloned_db";
try (Connection connClone2 = DriverManager.getConnection(urlClone2, props);
Statement stmtC2 = connClone2.createStatement()) {
stmtC2.execute("CREATE TABLE IF NOT EXISTS test_oid (id int)");
try (ResultSet rs = stmtC2.executeQuery(
"SELECT oid, relname, relfilenode::text FROM pg_class WHERE relname = 'test_oid'")) {
if (rs.next()) {
int oidVal = rs.getInt("oid");
String relname = rs.getString("relname");
String relfilenode = rs.getString("relfilenode");
System.out.printf(" test_oid: OID=%d, relfilenode=%s%n", oidVal, relfilenode);
}
}
try (ResultSet rs = stmtC2.executeQuery("SELECT 'test_oid'::regclass::oid")) {
if (rs.next()) {
int oidReg = rs.getInt(1);
System.out.printf(" 通过 regclass 获取 OID: %d%n", oidReg);
}
}
}
System.out.println("\n=== Demo 执行完成 ===");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
运行说明：
mvn compile
mvn exec:java -Dexec.mainClass="com.example.App"
多语言示例技术点总结
| 语言 | 驱动/库 | 连接字符串示例 | 关键特性 |
|---|---|---|---|
| Node.js | pg | postgres://user:pass@host:port/db | 基于 Pool 连接池，支持异步/await |
| Go | pgx/v5 | postgres://user:pass@host:port/db | 高性能，支持上下文，自动重连 |
| Python | psycopg2 | DSN 或关键字参数 | 成熟稳定，支持 autocommit 和原生 SQL 拼接 |
| Java | JDBC (PostgreSQL 驱动) | jdbc:postgresql://host:port/db | 标准 JDBC API，需手动管理连接 |
所有示例均实现了以下核心功能：
- 查询系统目录
pg_database和pg_class - 创建自定义模板数据库并标记
datistemplate = true - 使用
TEMPLATE子句克隆数据库 - 使用
template0创建指定编码和LC_COLLATE的数据库 - 查询表空间及其物理位置
- 通过
regclass转换获取 OID - 切换数据库连接（通过新建连接模拟
\c）
官方文档与参考链接
官方文档
- PostgreSQL 官方文档: Database Clusters
- PostgreSQL 官方文档: Template Databases
- PostgreSQL 官方文档: Tablespaces
- PostgreSQL 官方文档: CREATE DATABASE
- PostgreSQL 官方文档: CREATE TABLESPACE
- PostgreSQL 官方文档: Object Identifier Types
- PostgreSQL 官方文档: dblink
- PostgreSQL 官方文档: postgres_fdw
- PostgreSQL 官方文档: initdb
- PostgreSQL 官方文档: pg_ctl
参考链接
总结
本文深入探讨了 PostgreSQL 逻辑体系架构中的核心概念：
- Database Cluster（数据库集群） 是存储层面的概念，指
PGDATA目录下由单个实例管理的所有数据库的集合。 - Instance（实例） 是运行时的概念，指
postgres进程及其管理的内存与后台进程。 - 模板数据库（
template1/template0） 是CREATE DATABASE的复制源，template1支持站点本地定制，template0提供“原始”环境并支持自定义编码与区域设置。 - 表空间（Tablespace） 允许 DBA 控制数据库对象的物理存储位置，实现冷热分离与性能优化。
- OID（对象标识符） 是 PostgreSQL 内部用于标识系统表主键和文件组织的基础机制。
- 跨数据库访问可通过
dblink或postgres_fdw实现，其中postgres_fdw提供了更符合 SQL/MED 标准的语法和更好的性能。
891
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
