< Back to blog home Fujitsu PostgreSQL blog

How PostgreSQL maps your tables into physical files

Gary Evans | August 29, 2019

A quick recap

In my last blog post I showed how to protect your data using Transparent Data Encryption, and how the resulting data would look like should an attacker gain hold of your files.

But how exactly would one go about from accessing the data via PostgreSQL to locating their store in the file system?

It all starts with pg_database

PostgreSQL stores its data files in the same location as its configuration and control files for a database cluster, and it is usually in the directory specified by the PGDATA environment variable. These files are organized into a number of subdirectories.

Starting at the top, each database within the cluster has a folder named after the OID of the database, in the subdirectory base. In this example, we will have PGDATA as /cluster/acme/data, so the database directories will be located in /cluster/acme/data/base.

Using psql, list the OIDs of your databases:

Based on the result, we can expect the directory PGDATA/base to have the OID subdirectories 1, 15210, and 15215 — so in the command line we can confirm:

Note above the directory named data — it was automatically created by initdb when I created the cluster.  If I had used the WebAdmin GUI tool to create the cluster, apart from data, it would also have created the backup directory by default.

These OID subdirectories contain the system catalog for each database, and are the default location for database files. Each of these subdirectories contain a number of files containing data for either a table or a single index, and each of these files is named after the filenode number. There are several other types of files, and particularly prominent are those with the _fsm or _vm suffix — these files track information such as free space available and occurrences of dead tuples for the corresponding relations.

To find out the filenode of a database object (such as table, view, index, catalog, etc), we can use the catalog pg_class. If we wanted to find out which are the physical files for catalog pg_statistic in the database postgres (OID 15215), we would retrieve its filenode as follows:

The filenode for pg_statistic is 14947, so it means that its files will be named 14947*, and they will be stored in PGDATA/base/15215. We can check the directory in Linux, as follows:

But what if I am using tablespaces?

In our example we are using tablespaces, since we are using Transparent Data Encryption, so the structure is a little different. When a user creates a tablespace, along with the location on disk, a symbolic link to the folder in the pg_tblspc sub folder is also created. The symbolic link is named the same as the tablespace OID.

Within the tablespace location is a folder that corresponds to the version of PostgreSQL that FUJITSU Enterprise Postgres shares core components with. This allows subsequent versions of a database to use the same tablespace without conflicts.

Now, within the version folder, the structure is essentially the same as the base structure with folders named after the database OID.

Accessing the data directly via file

We now know how to trace the location of a database object all the way down to its physical location. So let’s have a look inside one of the physical files and see if we can read anything.  First, to make things easier let’s see what data we are looking for. So I created a table that just has some text values called and stored 3 rows in it - here is what it looks like:

At this stage we already know the corresponding physical file name is for the table, because we already obtained its filenode (16385) from pg_class a few steps above.

If we now edit the corresponding physical file 16385 using vi, we can easily see the values of those rows. I find it a bit easier to read if we switch vi to hex mode by using :%!xxd:

Someone who has hacked into your system probably isn’t going to log into a psql session and select the names of the files they want to look at. They will more likely grab as much as they can, then put the information together after trying to derive some meaning from it.

Unless you are using Transparent Data Encryption

Even if an attacker or unauthorized personnel gains access to your data files, they can be protected using PCI DSS-compliant transparent data encryption. In this case, even after a breach, the only information obtained would be a strongly encrypted file.

If you are interested in learning more about Transparent Data Encryption or Data Masking, and how they may improve your PostgreSQL database, please contact us directly. Fujitsu provides 24/7 PostgreSQL support and services, DBA and developer training, and our own enhanced version of PostgreSQL — FUJITSU Enterprise Postgres.