PostgreSQL Physical Storage

-

Physical Structure:

base

Subdirectory containing per-database subdirectories

global

Subdirectory containing cluster-wide tables, such as pg_database

pg_commit_ts

  Subdirectory containing transaction commit timestamp data

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

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

log or pg_log (before postgres 9.6)

  File recording the log file(s) currently written to by the logging collector

pg_wal or pg_xlog(before postgres 9.6)

  Subdirectory containing WAL (Write Ahead Log) files

pg_xact or pg_clog (before postgres 9.6)

  Subdirectory containing transaction commit status data

pg_hba.conf

A file containing client(hostname, dbname, username and password method)
authentication and authorization

postgresql.conf

A file containing server parameters

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 with

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

Comments

Post a Comment

Popular posts from this blog

PostgreSQL pg_pool-II Installation and Configuration

-
Image
Pgpool-II Pgpool-II is a proxy software that sits between PostgreSQL servers and a PostgreSQL database client.  It provides the following features: 1.Connection Pooling:     Pgpool-II maintains established connections to the PostgreSQL servers, and reuses them whenever a new connection with the same properties (i.e. user name, database, protocol version, and other connection parameters if any) comes in. It reduces the connection overhead, and improves system's overall throughput.   2.Load Balancing:     If a database is replicated (because running in either replication mode or master/slave mode), performing a SELECT query on any server will return the same result. Pgpool-II takes advantage of the replication feature in order to reduce the load on each PostgreSQL server.  It does that by distributing SELECT queries among available servers, improving the system's overall throughput. In an ideal scenario, read performance could improve proportion...
Read more

PostgreSQL Pages and Tuples

-
Image
INTERNAL LAYOUT: a tuple or an item is a synonym for a row a relation is a synonym for a table a filenode is an id which represent a reference to a table or an index. a block and page are equals and they represent a 8kb segment information the file storing the table. a heap refer to heap file . Heap files are lists of unordered records of variable size. Although sharing a similar name, heap files are different from heap data structure. CTID represent the physical location of the row version within its table. CTID is also a special column available for every tables but not visible unless specifically mentioned. It consists of a page number and the index of an item identifier. OID stands for Object Identifier. database cluster , we call a database cluster the storage area on disk. A database cluster is a collection of databases that is managed by a single instance of a running database server. Page:            Inside the data file (heap table and index,...
Read more

PostgreSQL Reporting Tools(Pgbadger) Installation & Configuration

-
PgBadger:                 The PostgreSQL log analyzer “pgBadger” is an open source “fast PostgreSQL log analysis report” program written in Perl that takes the log output from a running PostgreSQL instance and processes it into an HTML file.                  The report it generates shows all information found in a nice and easy to read report format.  These reports can help shed light on errors happening in the system, checkpoint behavior, vacuum behavior, trends, and other basic but crucial information for a PostgreSQL system. FEATURES:  pgBadger reports everything about your SQL queries:         Overall statistics.         The most frequent waiting queries.         Queries that waited the most.         Queries generating the most temporary files.         Queries generating the largest temporar...
Read more