The installation of PostgreSQL is basically done at the /var/lib/postgresql location or the /usr/local/postgresql on the Linux operating system, where as in the Windows opearting the installed directory is C:\Program Files\PostgreSQL\14. PostgreSQL stores the configuration files such as pg_hba.conf,pg_ident.conf, postgresql.conf and the database in the data directory.
Directory Structure of PostgreSQL on Linux
The following are the PostgreSQL directory structure in Linux operating system.
The PostgreSQL instance/cluster is by default installed with the name main and it uses the default post 5432 as mentioned in the below figure. We can use the pg_lsclusters command to check the location of the main.
The PostgreSQL configuration files are present under /etc/postgresql directory. Use the ls -R /etc/postgresql command to verify it.
Directory Structure of PostgreSQL on Windows
The following are the PostgreSQL directory structure in Windows operating system.
PostgreSQL Directory Structure Description
Let us see the description of each PostgreSQL Directory structure in the below section.
| PostgreSQL Directory | Description |
|---|---|
| bin | This directory contains the executable programs such as createdb, createuser, dropdb, dropuser, initdb, and so on. |
| data | This directory contains the database, log, configuration files, and so on. |
| debug_symbols | This Directory contains the debugging-related information of PostgreSQL. |
| Doc | This directory contains the Documentation. |
| Include | This directory contains Header files. |
| Installer | This directory contains Installer files. |
| lib | This directory contains library files. |
| pgadmin | This directory contains pgadmin files. |
| Scripts | This directory contains scripts like runpsql, serverctl vbscript files. |
| Share | This directory contains sample configuration files. |
PostgreSQL Database Directory Structure
The following table presents the PostgreSQL Database Directory Structure which contains many subdirectories. Each subdirectory is used for different operations and the detail of each subdirectory are as below.
| Directory Name | Description |
|---|---|
| Base | This directory contains the per-database subdirectories. |
| Current_logfiles | This directory contains the current log files that are written by the logging collector. |
| Global | The Cluster-wide tables which are pg_tablespace, pg_database, and pg_index are present under this directory. |
| pg_commit_ts | This Directory stores the transaction commit timestamp data= 9.5 and track_commit_timestamp. |
| pg_dynshmem | These files that are used by the Dynamic shared memory subsystem are stored here. |
| pg_logical | The Logical Data decoding status data are stored in this Directory. |
| pg_multixact | The multi-transaction status data are stored in this directory that is used for shared row locks. |
| pg_notify | The LISTEN/NOTIFY status data are present under this Directory. |
| pg_replslot | The Replication slot data are present under this Directory. |
| pg_serial | The Committed serializable transactions data are present under this Directory. |
| Log | The error logs are present under this Directory. |
| pg_snapshots | The exported snapshots are present under this Directory. |
| pg_stat | The permanent files for the statistics subsystem are present under this Directory. |
| pg_stat_tmp | The temporary files for the statistics subsystem are present under this Directory. |
| pg_subtrans | The subtransaction status data are present under this Directory. |
| pg_tblspc | The symbolic links to tablespaces are present under this Directory. |
| pg_twophase | The state files for prepared transactions are present under this Directory. |
| pg_wal | The WAL (Write Ahead Log) files are stored under this Directory. |
| pg_xact | The transaction commits status data, transaction metadata logs are stored under this Directory. |
| Pg_ident.conf | In this configuration file, we can define the configuration to Map the operating system user with the Database user. |
| postgresql.auto.conf | The configuration parameters are stored in this file which can be set by Alter System command. |
| postmaster.opts | This file contains the information about the command-line option that was used to start the PostgreSQL server. |
| postmaster.pid | This file contains the information about the current postmaster process ID (PID), postmaster start timestamp, port number, Unix-domain socket directory path (empty on Windows), cluster data directory path, shared memory segment ID, and so on. |
| PG_VERSION | The PostgreSQL version details are mentioned in this file. |
PostgreSQL Base_Directory Structure
The PostgreSQL Base_Directory contains the database information which is represented as the directory name post their OID(Object Identifier). There is a default database Template 1 that has OID 1.
We can check the OID and database name using the below SQL command.
postgres=# Select oid,datname from pg_database;
The following output has come with default databases template0/1 and the postgres database.
