엑셈

Physical Structure

This document covers the various files and directories PostgreSQL manages, such as data files, engine log files, and configuration files. These files and directories live in the data directory of the database cluster, called PGDATA. PGDATA is usually at /var/lib/pgsql/13/data, and the path can vary depending on how PostgreSQL was installed.

📌 For databases, clusters, and related topics, see Logical Structure.

Data Directory Structure (PGDATA)

The components of a database cluster usually live under the data directory. When the database cluster is initialized (initDB), the data directory is created at the specified location, and various directories and files are added under it.

For convenience, this document divides the data directory into ① the Data area, ② the Log area, ③ the Configuration area, and ④ other areas, and covers ①-③.

Data Area

The Data area is where actual user data is stored. It can be divided into ① the global directory, which stores data related to the database cluster, ② the base directory, which stores user data (tables, indexes, etc.), and ③ the pg_tblspc directory, which stores tablespace information.

Global Directory

The global directory is the default global tablespace directory created at initial PostgreSQL installation (InitDB), also called the pg_global tablespace. It stores information shared by the databases under the PostgreSQL cluster. The global directory contains 4 kinds of files:

pg_internal.init, pg_filenode.map

Contains attribute information for objects in the database, the actual information of those objects, and their mapping to data files.

pg_control

A file holding the metadata needed to operate the database, similar to Oracle's control file. You can view its contents with the pg_controldata utility.

Other numbered files

Files holding data shared by multiple databases, such as tablespaces, databases, and roles. The table below maps common OIDs (Object IDs) to table names.

Base Directory

The base directory is also one of the default tablespace directories created at initial PostgreSQL installation (initDB), also called the pg_default tablespace. It is where objects in the database, such as tables, indexes, and functions, are actually stored, and the storage path follows $PGDATA/base/{database_OID}/{object_id}.

In other words, under the base directory there are directories named after database OIDs, and each database directory holds the actual object files.

📌 The oid2name utility lets you look up database and table names using the pg_class table and the pg_relation_filepath function.

pg_tblspc Directory

This area stores PostgreSQL tablespace information. When you create a tablespace in a specific directory outside the data directory, that directory's path is linked through a symbolic link created in the pg_tblspc directory.

Log Area

pg_wal Directory

Stores WAL files, which play the same role as Oracle's redo log.

Usually used for recovery after an abnormal database shutdown, and also used by the replication feature.

Up to PostgreSQL 9.6 the directory was named pg_xlog, but from PostgreSQL 10 it was renamed to pg_wal.

📌 Configure it by editing the parameters in the "WRITE-AHEAD LOG" section of postgresql.conf .

Log Directory

Similar to Oracle's alert log, it records notable events that occur while the database is running.

Up to PostgreSQL 9.6 the directory was named pg_log, but from PostgreSQL 10 it was renamed to log.

📌 Configure it by editing the parameters in the "REPORTING AND LOGGING" section of postgresql.conf .

Configuration Area

PG_VERSION

Shows the PostgreSQL database version (major number). PostgreSQL starts only if the version in this file matches the PostgreSQL engine version. PostgreSQL does not start in these 2 cases:

The PG_VERSION file does not exist

The PostgreSQL database version differs from the version in the file

pg_hba.conf

A file containing PostgreSQL authentication settings. You can configure the hosts that connect to PostgreSQL, how they transfer data, and encrypted transfer. After changing pg_hba.conf, you must restart PostgreSQL for the changes to take effect.

📌 HBA in pg_hba stands for Host-Based Authentication. For more details, see the pg_hba.conf page.

postgresql.conf

The PostgreSQL database configuration file. It plays a role similar to Oracle's pfile.

From PostgreSQL 12, the recovery.conf file was merged into this file. (In earlier versions, recovery.conf must be set up when needed.)

📌 For details on each parameter, see the postgresql.conf page.

postgresql.auto.conf

postgresql.auto.conf is not meant to be edited by users directly; it stores changes made with the alter system command.

Changes take effect only after you call the pg_reload_conf() function or run the pg_ctl reload command. However, some parameters require a database restart.