To understand the filesystem layout used by PostgreSQL, we can have a look at what we can find inside the data directory (created by initdb at the time of installation):

[hs@paulapgdata]$ ls -l
total 92
-rw------- 1 hs staff     4 Feb 11 18:14 PG_VERSION
drwx------ 6 hs staff  4096 Feb 11 18:14 base
drwx------ 2 hs staff  4096 Feb 11 18:14 global
drwx------ 2 hs staff  4096 Feb 11 18:14 pg_clog
-rw------- 1 hs staff  4458 Feb 11 18:14 pg_hba.conf
-rw------- 1 hs staff  1636 Feb 11 18:14 pg_ident.conf
drwx------ 4 hs staff  4096 Feb 11 18:14 pg_multixact
drwx------ 2 hs staff  4096 Feb 11 18:14 pg_notify
drwx------ 2 hs staff  4096 Feb 11 18:14 pg_serial
drwx------ 2 hs staff  4096 Feb 11 18:14 pg_snapshots
drwx------ 2 hs staff  4096 Feb 11 18:19 pg_stat_tmp
drwx------ 2 hs staff  4096 Feb 11 18:14 pg_subtrans
drwx------ 2 hs staff  4096 Feb 11 18:14 pg_tblspc
drwx------ 2 hs staff  4096 Feb 11 18:14 pg_twophase
drwx------ 3 hs staff  4096 Feb 11 18:14 pg_XLOG
-rw------- 1 hs staff 19630 Feb 11 18:14 postgresql.conf
-rw------- 1 hs staff    47 Feb 11 18:14 postmaster.opts
-rw------- 1 hs staff    69 Feb 11 18:14 postmaster.pid

You will see a range of files and directories, which are needed to run a database instance. Let us take a look at those in detail.

This file will tell the system at startup if the data directory contains the right version number. Please note that only the major release version is in this file. It is easily possible to replicate between different minor versions of the same major version.

[hs@paulapgdata]$ cat PG_VERSION
9.2

The file is plain text readable.

The base directory is one of the most important things in our data directory. It actually contains the real data (meaning tables, indexes, and so on). Inside the base directory, each database will have its own subdirectory:

[hs@paula base]$ ls -l
total 24
drwx------ 2 hs staff 12288 Feb 11 18:14 1
drwx------ 2 hs staff  4096 Feb 11 18:14 12865
drwx------ 2 hs staff  4096 Feb 11 18:14 12870
drwx------ 2 hs staff  4096 Feb 11 18:14 16384

We can easily link these directories to the databases in our system. It is worth noticing that PostgreSQL uses the object ID of the database here. This has many advantages over using the name because the object ID never changes and offers a good way to abstract all sorts of problems, such as issues with different character sets on the server and so on:

test=# SELECT oid, datname FROM pg_database;
oid    |datname
-------+-----------
     1 | template1
 12865 | template0
 12870 | postgres
 16384 | test
(4 rows)

Now we can see how data is stored inside those database-specific directories. In PostgreSQL, each table is related to (at least) one data file. Let us create a table and see what happens:

test=# CREATE TABLE t_test (id int4);
CREATE TABLE

We can check the system table now to retrieve the so called relfilenode, which represents the name of the storage file on disk:

test=# SELECT relfilenode, relname
      FROM	pg_class
WHERE  relname = 't_test';
relfilenode  | relname
-------------+---------
       16385 | t_test
(1 row)

As soon as the table is created, PostgreSQL will create an empty file on disk:

[hs@paula base]$ ls -l 16384/16385*
-rw------- 1 hs staff 0 Feb 12 12:06 16384/16385

global will contain the global system tables. This directory is small, so you should not expect excessive storage consumption.

The commit log is an essential component of a working database instance. It stores the status of the transactions on this system. A transaction can be in four states (TRANSACTION_STATUS_IN_PROGRESS, TRANSACTION_STATUS_COMMITTED, TRANSACTION_STATUS_ABORTED, and TRANSACTION_STATUS_SUB_COMMITTED), and if the commit log status for a transaction is not available, PostgreSQL will have no idea whether a row should be seen or not.

If the commit log of a database instance is broken for some reason (maybe because of filesystem corruption), you can expect some funny hours ahead.

The pg_hba.conf file configures the PostgreSQL-internal firewall and represents one of the two most important configuration files in a PostgreSQL cluster. It allows the users to define various types of authentication based on the source of a request. To a database administrator, understanding the pg_hba.conf file is of vital importance because this file decides whether a slave is allowed to connect to the master or not. If you happen to miss something here, you might see error messages in the slave's logs (for instance: no pg_hba.conf entry for ...).

The pg_ident.conf file can be used in conjunction with the pg_hba.conf file to configure ident authentication.

The multi-transaction-log manager is here to handle shared row locks efficiently. There are no replication-related practical implications of this directory.

In this directory, the system stores information about LISTEN/NOTIFY (the async backend interface). There are no practical implications related to replication.

Information about serializable transactions is stored here. We have to store information about commits of serializable transactions on disk to ensure that long-running transactions will not bloat memory. A simple SLRU structure is used internally to keep track of those transactions.

This is a file consisting of information needed by the PostgreSQL snapshot manager. In some cases, snapshots have to be exported to disk to avoid going to memory. After a crash, those exported snapshots will be cleaned out automatically.

Temporary statistical data is stored in this file. This information is needed for most pg_stat_* system views (and therefore also for the underlying function providing the raw data).

In this directory, we store information about subtransactions. pg_subtrans(and pg_clog) directories are permanent (on-disk) storage of transaction-related information. There is a limited number of pages of each kept in the memory, so in many cases there is no need to actually read from disk. However, if there's a long-running transaction or a backend sitting idle with an open transaction, it may be necessary to be able to read and write this information from disk. They also allow the information to be permanent across server restarts.

The pg_tblspc directory is a highly important one. In PostgreSQL, a tablespace is simply an alternative storage location, which is represented by a directory holding the data.

The important thing here is: If a database instance is fully replicated, we simply cannot rely on the fact that all servers in the cluster use the very same disk layout and the very same storage hardware. There can easily be scenarios in which a master needs a lot more I/O power than a slave, which might just be around to function as backup or standby. To allow users to handle different disk layouts, PostgreSQL will place symlinks into the pg_tblspc directory. The database will blindly follow those symlinks to find those tablespaces, regardless of where they are.

This gives end users enormous power and flexibility. Controlling storage is both essential to replication as well as to performance in general. Keep in mind that those symlinks can only be changed ex post. It should be carefully thought over.

PostgreSQL has to store information about two-phase commit. While two-phase commit can be an important feature, the directory itself will be of little importance to the average system administrator.

The PostgreSQL transaction log is the essential directory we have to discuss in this chapter. pg_XLOG contains all files related to the so called XLOG. If you have used PostgreSQL already in the past, you might be familiar with the term WAL (Write Ahead Log). XLOG and WAL are two names for the very same thing. The same applies to the term transaction log. All these three terms are widely in use and it is important to know that they actually mean the same thing.

The pg_XLOG directory will typically look like this:

[hs@paulapg_XLOG]$ ls -l
total 81924
-rw------- 1 hs staff 16777216 Feb 12 16:29 000000010000000000000001
-rw------- 1 hs staff 16777216 Feb 12 16:29 000000010000000000000002
-rw------- 1 hs staff 16777216 Feb 12 16:29 000000010000000000000003
-rw------- 1 hs staff 16777216 Feb 12 16:29 000000010000000000000004
-rw------- 1 hs staff 16777216 Feb 12 16:29 000000010000000000000005
drwx------ 2 hs staff     4096 Feb 11 18:14 archive_status

What you see is a bunch of files, which are always exactly 16 MB in size (default setting). The filename of an XLOG file is generally 24 bytes long. The numbering is always hexadecimal. So, the system will count "… 9, A, B, C, D, E, F, 10" and so on.

One important thing to mention is that the size of the pg_XLOG directory will not vary wildly over time and it is totally independent of the type of transactions you are running on your system. The size of the XLOG is determined by postgresql.conf parameters, which will be discussed later in this chapter. In short: No matter if you are running small or large transactions, the size of the XLOG will be the same. You can easily run a transaction as big as 1 TB with just a handful of XLOG files. This might not be too efficient, performance wise, but it is technically and perfectly feasible.

Finally, there is the main PostgreSQL configuration file. All configuration parameters can be changed in postgresql.conf and we will use this file extensively to set up replication and to tune our database instances to make sure that our replicated setups provide us with superior performance.

Let us assume that we are doing a simple INSERT statement like the following one:

INSERT INTO foo VALUES ('abcd'):

As one might imagine, the goal of an INSERT operation is to somehow add a row to an existing table. We have seen in the previous section about the disk layout of PostgreSQL that each table will be associated with a file on disk.

Let us perform a mental experiment and assume that the table we are dealing with here is 10 TB large. PostgreSQL will see the INSERT operation and look for some spare place inside this table (either using an existing block or adding a new one). For the purpose of this example, we simply just put the data into the second block of the table.

Everything will be fine as long as the server actually survives the transaction. What happens if somebody pulls the plug after just writing abc instead of the entire data? When the server comes back up after the reboot, we will find ourselves in a situation where we have a block with an incomplete record, and to make it even funnier, we might not even have the slightest idea where this block containing the broken record might be.

In general, tables containing incomplete rows in unknown places can be considered to be corrupted tables. Of course, systematic table corruption is nothing the PostgreSQL community would ever tolerate, especially not if problems like that are caused by clear design failures.

To fix the problem that we have just discussed, PostgreSQL uses the so called WAL (Write Ahead Log) or simply XLOG. Using WAL means that a log is written ahead of data. So, before we actually write data to the table, we make log entries in sequential order indicating what we are planning to do to our underlying table. The following image shows how things work:

As we can see from the figure, once we have written data to the log (1), we can go ahead and mark the transaction as done (2). After that, data can be written to the table (3).

Let us demonstrate the advantages of this approach with two examples:

The shared buffer is the I/O cache of PostgreSQL. It helps to cache 8k blocks, which are read from the operating system and it helps to hold back writes to the disk to optimize efficiency (how this works will be discussed later in this chapter).

But, performance is not the only issue we should be focused on when it comes to the shared buffer. Let us assume that we want to issue a query. For the sake of simplicity, we also assume that we need just one block to process this read request.

What happens if we do a simple read? Maybe we are looking up something simple like a phone number or a username given a certain key. The following list shows, in a heavily simplified way, what PostgreSQL will do under the assumption the instance has been restarted freshly:

Let us assume the same block will be used again by a second query. In this case, things will work as follows:

Remember, in this section, it is all about understanding writes to make sure that our ultimate goal, full and deep understanding of replication, can be achieved. Therefore we have to see how reads and writes go together. Let's see how a write and a read go together:

What is the point of this example? Well, as you might have noticed, we have never talked about actually writing to the underlying table. We talked about writing to the cache, to the XLOG and so on, but never about the real data file.

It is important to understand that data is usually not sent to a data file directly after or during a write operation. It makes perfect sense to write data a lot later to increase efficiency. The reason why this is important is that it has subtle implications for replication. A data file itself is worthless because it is neither necessarily complete nor correct. To run a PostgreSQL instance, you will always need data files along with the transaction log. Otherwise, there is no way to survive a crash.

From a consistency point of view, the shared buffer is here to complete the view a user has of the data. If something is not in the table, it logically has to be in memory.

In case of a crash, memory will be lost, and so the XLOG is consulted and replayed to turn data files into a consistent data store again. Under any circumstances, data files are only half of the story.