At the basic level, your data is simply stored as a set of files on disk.

If you want a backup you can simply stop the database, copy all the database data files that are on your disk and you create a backup. There are also a number of other ways to back up your PostgreSQL data and I’ll go over that in another blog entry. For now though, back to where the data resides.

Let’s assume that your PostgreSQL data directory is a directory called/database

If you aren’t quite sure where the database directory is but you can log into the database using psql you can find where your database cluster’s directory is very easily by typing the following:

and you’ll see something like:

If you take a look in the/database directory you will see lots of files including:

• postgresql.conf
• pg_hba.conf and
• various other files

You’ll also see a directory calledbase.

This is where all the database data for your whole cluster is held.

You’ll see many directories in the base directory that are just numbers. Each one of these numbered directories is a single database in your cluster.

If you want to know which directory number relates to which database, you can log into your database using psql and type the following:

Which returns:

You could alternatively use the suppliedoid2name utility from a UNIX shell like this:

Which returns:

Now you have a list of databases and each object id which is used as the name of the directory within the/database/base directory.

If you wanted to look at the data in my test database (which has an OID of 20886), you couldcd /database/base/20886 then list the directory contents usingls -l , at which point you will see the following:

This is a small subset of the files in that directory. Notice anything about the file sizes? They all divide exactly by 8192 (8k). This is because PostgreSQL (by default) writes blocks of data (what PostgreSQL calls pages) to disk in 8k chunks. If you have a large table that has more than 1GB of data in it, you will see multiple files with the same number appended with .1 .2 .3 and so on like this:

In this test database I have a table called test_data. If you want to see which file actually contains the table test_data’s data you can do the following:

Start up psql and:

You could alternatively use the oid2name utility again like this:

Which would output this:

So the file/database/base/20866/186770 contains the actual data for the tabletest_data
If you run something like this (assuming you have hexdump installed):

You will see the test_data’s data table as it’s stored in the file on the disk.

So it’s time to test this all out:

Don’t forget to issue a checkpoint to make sure everything is written to the database tables from the WAL

And there it is.

My inserted varchar string,'hello there' is now stored and visible on my disk.You might consider this visibility on disk to be a problem. If you wantto encrypt the data in the files on disk you can use theFUJITSU Enterprise Postgres transparent data encryption feature that I have describedhere.

The files that have a number and have_fsm or_vm appended are the free space map and the visibility map for each page. This will be the subject of another post. Stay tuned.

If you require any help at all with your PostgreSQL database, then feel free to review ourSupport services, customizedTraining, orHealth Check Assessment.

email:enterprisepostgresql@fujitsu.com

All Rights Reserved. Copyright 2025 Fujitsu Australia Ltd