数据库 PostgreSQL 数据库存放路径初窥探
用 PostgreSQL 数据库已经有一段时间了，始终对他的目录结构不是特别了解。这篇文章简单总结一下这段时间的发现。就当作是学习笔记了。原文链接： https://step-by-step.tech/posts/path-of-databases-in-pg
新的目录，从 0 开始探索
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 里面？
虽然这种需求不怎么常见，但有没有可能把我们的数据库放在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 里面的一些内省查询。
