PostgreSQL Directory Layout | PostgreSQL Tutorials and Solutions

In this article, we will see PostgreSQL Directory Layout and their purpose.

PostgreSQL Database Directory Layout

Installation Directory Layout:

•
PostgreSQL is typically installed
to /usr/local/pgsql or /var/lib/pgsql on linux.

•
C:\Program
Files\PostgreSQL\<version number> on windows.

PostgreSQL Directory Layout On Windows

PostgreSQL Directory Layout

•
bin-> programs(createdb,
initdb,createuser,etc)

•
data -> Data Directory

•
Doc –> Documentation

•
Include –> Header Files

•
Installer -> Installer files

•
Scripts –> scripts like runpsql,
serverctl vbscript files

•
Share -> Sample configuration
files

•
pgadmin – pgadmin files.

PostgreSQL Directory Layout on Linux:

Directory Name

Description

Base

Subdirectory containing
per-database subdirectories

Current_logfiles

File recording the log file(s)
currently

written to by the logging collector

Global

Subdirectory containing
cluster-wide tables, such as pg_database,pg_tablespace,pg_index etc

pg_commit_ts

Subdirectory containing transaction
commit timestamp data= 9.5 and later, track_commit_timestamp

pg_dynshmem

Subdirectory containing files used
by the dynamic shared memory subsystem

pg_logical

Subdirectory containing status data
for

logical decoding

pg_multixact

Subdirectory containing
multitransaction status data (used for shared row locks)

pg_notify

Subdirectory containing
LISTEN/NOTIFY status data

pg_replslot

Subdirectory containing
replication slot data

pg_serial

Subdirectory containing information
about

committed serializable transactions

Log

All error logs kept in this
directory.

pg_snapshots

Subdirectory containing exported
snapshots

pg_stat

Subdirectory containing permanent
files for the statistics subsystem

pg_stat_tmp

Subdirectory containing temporary
files for the statistics subsystem

pg_subtrans

Subdirectory containing
subtransaction status data

pg_tblspc

Subdirectory containing symbolic
links to tablespaces

pg_twophase

Subdirectory containing state files
for prepared transactions

pg_wal

Subdirectory containing WAL (Write
Ahead Log) files

pg_xact

Subdirectory containing transaction
commit status data, transaction metadata logs

Pg_ident.conf

User name maps are defined in the
ident map file.user name map can be applied to map the operating system user
name to a database user.

postgresql.auto.conf

A file used for storing
configuration parameters

that are set by ALTER SYSTEM

postmaster.opts

A file recording the command-line
options the server was last started.

postmaster.pid

A lock file recording the current
postmaster process ID (PID), cluster data directory path, postmaster start
timestamp, port number, Unix-domain socket directory path (empty on
Windows), first valid listen_address (IP address or *, or empty if not listening
on TCP), and shared memory segment ID (this file is not present after server
shutdown)

PG_VERSION

A file containing the major version
number of PostgreSQL

Base_Directory:

•
Contains databases, that represented
as directories named after their object identifier (OID).

•
Template 1 always has oid 1.

•
Syntax to find oid of database:

Select oid,datname from pg_database;

Related Posts: