用PostgreSQL数据库已经有一段时间了,始终对他的目录结构不是特别了解。这篇文章简单总结一下这段时间的发现。就当作是学习笔记了。

新的目录,从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.confpg_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

可见多了一个oid16384的数据库,在去看看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

新数据库的oid16453,再去查看一下/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

数据库的oid16455,而在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里面的一些内省查询。