用 PostgreSQL 数据库已经有一段时间了,始终对他的目录结构不是特别了解。这篇文章简单总结一下这段时间的发现。就当作是学习笔记了。原文链接: https://step-by-step.tech/posts/path-of-databases-in-pg
PostgreSQL 服务有很多种启动方式,为了获得一个洁净的环境,我们需要重新启动一个 PG 服务。最简单的方式是使用pg_ctl
。
> mkdir pg_study
> pg_ctl initdb -D pg_study // 初始化数据库服务目录
> vim pg_study/postgresql.conf // 把端口号改成5678,并保存(假设5432端口已经被占用不跟已有的服务冲突)
> pg_ctl -D pg_study start // 启动服务
这里简单采用 socket 无密码的方式来登陆,需要用 5678 端口连接这个新的数据库服务
> psql -U lan -d postgres -p 5678
postgres=#
当你窥探数据库文件夹的时候
> ls -hS pg_study
postgresql.conf pg_wal pg_notify
pg_hba.conf pg_subtrans pg_replslot
global pg_xact pg_serial
pg_ident.conf postmaster.pid pg_snapshots
base postgresql.auto.conf pg_stat
pg_logical postmaster.opts pg_tblspc
pg_stat_tmp pg_commit_ts pg_twophase
pg_multixact pg_dynshmem PG_VERSION
会看到一堆不太看得懂的东西,除了一堆文件目录之外就是类似于postgresql.conf
, pg_hba.conf
这种配置文件,还有进程文件postmaster.pid
。他们各自代表什么暂且不管(笔者也不是很清楚),我现在想知道数据库放在哪。答案就是pg_study/base
目录下。
> ls -lhS pg_study/base
total 0
drwx------ 297 lan staff 9.3K Sep 10 21:08 14023
drwx------ 296 lan staff 9.3K Sep 10 21:01 1
drwx------ 296 lan staff 9.3K Sep 10 21:01 14022
只有一堆不明觉厉的以数字为名的目录,数字分别代表什么一点头绪都没有。这个时候可以利用数据库的内省机制。从数据库里面查询数据库本身的资料。
postgres=# select oid, datname from pg_database ;
postgres=# select oid, datname from pg_database ;
oid | datname
-------+-----------
14023 | postgres
1 | template1
14022 | template0
可见总共只有 3 个数据库,每个数据库都有对应的oid
这个oid
其实就是pg_study/base
目录下对应的目录名。我们可以多创建一个新的数据库看看是不是会多一个对应的文件夹
postgres=# CREATE DATABASE study;
CREATE DATABASE
postgres=# SELECT oid, datname FROM pg_database ;
oid | datname
-------+-----------
14023 | postgres
16384 | study
1 | template1
14022 | template0
可见多了一个oid
为16384
的数据库,在去看看 base 目录下是不是这么回事
> ls -lhS pg_study/base
total 0
drwx------ 297 lan staff 9.3K Sep 10 21:08 14023
drwx------ 296 lan staff 9.3K Sep 10 21:01 1
drwx------ 296 lan staff 9.3K Sep 10 21:01 14022
drwx------ 296 lan staff 9.3K Sep 10 21:20 16384
不出所料,果然多了一个16384
的文件夹。
虽然这种需求不怎么常见,但有没有可能把我们的数据库放在base
之外的目录里呢?这种时候需要利用 PostgreSQL 表空间 (Tablespace) 这个概念了。假设我们想要把数据库放在目录/var/tmp/hello
里面,则针对这个目录创建一个表空间
postgres=# CREATE TABLESPACE newspace LOCATION '/var/tmp/hello';
CREATE TABLESPACE
然后在创建数据库的时候指定这个表空间即可
postgres=# CREATE DATABASE database_in_newspace TABLESPACE newspace ;
CREATE DATABASE
postgres=# SELECT oid, datname FROM pg_database WHERE datname ~ 'database_in_newspace';
16453 | database_in_newspace
新数据库的oid
是16453
,再去查看一下/var/tmp/hello
目录下是否有名为16453
这个文件夹
> ls /var/tmp/hello
PG_14_202107181
> ls /var/tmp/hello/PG_14_202107181
16453
倒是不出我们所料,只不过外面还套了一层PG_14_202107181
。这串主要是根据PG_{{VERSION}}_{{CATALOG VERSION NUMBER}}
。最后一串玩意可以这样去获取
> pg_controldata pg_study | grep 'Catalog'
Catalog version number: 202107181
一般来说,只要不指定表空间,数据表一般都会默认存放在对应的数据库目录里面的。我们重新创建一个数据库,并在该数据库里面创建一个数据表试试看?
postgres=# CREATE DATABASE where_is_table;
CREATE DATABASE
postgres=# \c where_is_table
You are now connected to database "where_is_table" as user "lan".
where_is_table=# CREATE TABLE mytable(id integer);
CREATE TABLE
where_is_table=# SELECT oid, datname FROM pg_database WHERE datname = 'where_is_table';
16455 | where_is_table
数据库的oid
是16455
,而在pg_study/base/16455
目录下其实有很多文件
> ls pg_study/base/16455 | wc -l
296
哪个才是数据表呢?我们可以通过查询pg_catalog.pg_class
数据表来找到对应记录。
where_is_table=# select relname, relfilenode from pg_catalog.pg_class where relname = 'mytable';
relname | relfilenode
---------+-------------
mytable | 16456
可以看到relfilenode
的值为16456
,这便是对应数据表的文件名,再去pg_study/base/16455
搜索下看看
> ls -la pg_study/base/16455 | grep 16456
-rw------- 1 lan staff 0 Sep 11 18:23 16456
该文件是存在的,并且是一个普通文件,并不是目录。只是现在表里面没有任何数据,所以表文件的大小是 0。往里面插入一点数据看看
where_is_table=# INSERT INTO mytable values (1);
INSERT 0 1
> ls -la pg_study/base/16455 | grep 16456
-rw-r--r-- 1 lan staff 8192 Sep 12 18:03 16456
有数据了,文件大了 8KB 左右(这跟 PG 的存储规则有关)。数据表存放路径大概可以用以下公式来概括
# 没有表空间的情况
> "#{root_path_of_database_service}/base/#{oid_of_database}/#{relfilenode_of_table}"
# 有表空间的情况
> "#{location_of_tablespace}/PG_#{catalog_version}/#{oid_of_database}/#{relfilenode_of_table}"
小贴士:为何在pg_catalog.pg_class
里面查找表信息是通过relname
以及relfilenode
,而不是普遍认知的tablename
或者tablefilenode
?因为跟很多常用的数据库不同,在 PostgreSQL 里面把数据表都称作Relation。不知道您是否注意到,当我们运行命令\d
来查看数据库内表信息的时候它会显示List of relations而不是List of tables。
这篇文章是一篇简单的学习笔记,简单窥探 PostgreSQL 里面数据库,数据表是如何存放的,同时也展示了 PostgreSQL 里面的一些内省查询。