Postgres Architecture Diagram

The below diagram depicts the core elements of an PostgreSQL 16 architecture, each main component is easily identifiable to the area it belongs under using the categories listed on the left hand side.

If you like this please see Oracle example : https://www.snowdba.com/oracle-architecture-diagram/

Flushes write ahead log to disk. wal_level control details written to WAL, also be minimal or logical fsync Force WAL buffer flush on a commit, if off could cuase corruption/crash wal_buffers memory used in shared memory for WAL data, -1 uses 1/32 (3%) of shared_buffers min_wal_size WAL size to start recycling WAL files ”max_wal_size ” WAL size to start checkpointing, controls WAL segments(16Mb each) after which checkpoint forced ”checkpoint_timeout ” max time between checkpoints ”wal_compression ” WAL of full page write compress and written Flushes write ahead log to disk. wal_level control details written to WAL, also be minimal or logical fsync Force WAL buffer flush on a commit, if off could cuase corruption/crash wal_buffers memory used in shared memory for WAL data, -1 uses 1/32 (3%) of shared_buffers min_wal_size WAL size to start recycling WAL files ”max_wal_size ” WAL size to start checkpointing, controls WAL segments(16Mb each) after which checkpoint forced ”checkpoint_timeout ” max time between checkpoints ”wal_compression ” WAL of full page write compress and written
walwriter
walwriterPerform database checkpoints based on settings, this causes background writer to flush dirty block to table files.Perform database checkpoints based on settings, this causes background writer to flush dirty block to table files.
checkpointer
checkpointerLog error Messages to log file. log_destination control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog log_collector enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration log_directory dir where log files are written log_filename format of log file : postgresql-%Y-%m-%d_%H%M_SM.log log_file_mode permissions for log files log_rotation_age use for file age based log rotation log_rotation_size use for size based log rotation log_min_messages severity level when messages are logged log_min_error_statement severity level when statement that caused messaged is also logged log_min_duration_statement statements running =>duration are logged log_autovacuum_min_duration autovacuum running =>duration are logged log_statement_sample_rate Percent of queries above log_autovacuum_min_duration to be logged log_transaction_sample_rate sample a percentage of transactions be logging statements log_connections log sucessful connections to server log log_disconnections log info each time a session disconnects including duration of sessions log_temp_files log temporary files of this size or larger in Kb log_checkpoints log checkpoints and restart points to be logged to server log log_lock_waits log if session waits longer than deadlock_timeout to acquire a lock log_error_verbosity determines logging detail, default|terse|verbose log_line_prefix log additional details with each line, default %m[%p]m this logs timestamp and process ID log_statement none, ddl, mod (ddl + all other data modifying SQL), all Log error Messages to log file. log_destination control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog log_collector enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration log_directory dir where log files are written log_filename format of log file : postgresql-%Y-%m-%d_%H%M_SM.log log_file_mode permissions for log files log_rotation_age use for file age based log rotation log_rotation_size use for size based log rotation log_min_messages severity level when messages are logged log_min_error_statement severity level when statement that caused messaged is also logged log_min_duration_statement statements running =>duration are logged log_autovacuum_min_duration autovacuum running =>duration are logged log_statement_sample_rate Percent of queries above log_autovacuum_min_duration to be logged log_transaction_sample_rate sample a percentage of transactions be logging statements log_connections log sucessful connections to server log log_disconnections log info each time a session disconnects including duration of sessions log_temp_files log temporary files of this size or larger in Kb log_checkpoints log checkpoints and restart points to be logged to server log log_lock_waits log if session waits longer than deadlock_timeout to acquire a lock log_error_verbosity determines logging detail, default|terse|verbose log_line_prefix log additional details with each line, default %m[%p]m this logs timestamp and process ID log_statement none, ddl, mod (ddl + all other data modifying SQL), all
logger
loggerCleans up any dead rows in page from a update or delete. vacuum_cost_delay time in milliseconds process will wait when cost limit exceeded ”vacuum_cost_page_hit ” estimated cost of vacuuming a buffer found in buffer pool vacuum_cost_page_miss estimated cost of vacuuming a buffer that must be read into buffer pool ”vacumm_cost_page_dirty ” estimated cost charged when vacuum modifies a buffer that was clean ”vacuum_cost_limit ” accumulated cost that will cause vacuuming process to sleep ”vacuum_buffer_usage_limit ” size of buffer access strategy used by vacuum and analyze commands autovacuum if autovacuum runs and starts worker process to vacuum and analyze tables log_autovacuum_min_duration autovacuum tasks running longer than this duration are logged autovacuum_max_workers max number of autovacuum worker processes which can run at one time autovacuum_work_mem (use mainteance_work_mem), max memory used by each autovacuum worker Cleans up any dead rows in page from a update or delete. vacuum_cost_delay time in milliseconds process will wait when cost limit exceeded ”vacuum_cost_page_hit ” estimated cost of vacuuming a buffer found in buffer pool vacuum_cost_page_miss estimated cost of vacuuming a buffer that must be read into buffer pool ”vacumm_cost_page_dirty ” estimated cost charged when vacuum modifies a buffer that was clean ”vacuum_cost_limit ” accumulated cost that will cause vacuuming process to sleep ”vacuum_buffer_usage_limit ” size of buffer access strategy used by vacuum and analyze commands autovacuum if autovacuum runs and starts worker process to vacuum and analyze tables log_autovacuum_min_duration autovacuum tasks running longer than this duration are logged autovacuum_max_workers max number of autovacuum worker processes which can run at one time autovacuum_work_mem (use mainteance_work_mem), max memory used by each autovacuum worker
autovacuum
autovacuumWrites dirty blocks to disk. bgwriter_delay time between activity rounds for background writer, tuning technique is to lower value bewriter_lru_maxpages max number of pages background writer may clean per activity round bgwriter_lru_multiplier multiplier on buffer scanned per round, if system thinks 10 pages needed it cleans 10*bgwriter_lru_multiplier Writes dirty blocks to disk. bgwriter_delay time between activity rounds for background writer, tuning technique is to lower value bewriter_lru_maxpages max number of pages background writer may clean per activity round bgwriter_lru_multiplier multiplier on buffer scanned per round, if system thinks 10 pages needed it cleans 10*bgwriter_lru_multiplier
    background writer
    background writerpostmaster is the PostgreSQL multiuser database. Client applications access database by connecting to a running postmaster. The postmaster then starts a separate server process (“postgres”) to handle the connection. The postmaster also manages the communication among server processes. random_page_cost estimate cost of random page fetch, can reduce depending on caching impact seq_page_cost estimate cost of a sequential page fetch effective_cache_size estimate cost of an index scan plan_cache_mode “controls custom or generic plan execution for prepared statements can be: auto, force_custom_plan, force_generic_plan” search_path order in which schemas are searched default_tablespace tablespace which objects are created in by default temp_tablespace tablespace(s) which temporary objects are created statement_timeout , abort statement that support this duration in milliseconds idle_in_trasnation_session_timeout terminate session with open transation longer than this duration in milliseconds postmaster is the PostgreSQL multiuser database. Client applications access database by connecting to a running postmaster. The postmaster then starts a separate server process (“postgres”) to handle the connection. The postmaster also manages the communication among server processes. random_page_cost estimate cost of random page fetch, can reduce depending on caching impact seq_page_cost estimate cost of a sequential page fetch effective_cache_size estimate cost of an index scan plan_cache_mode “controls custom or generic plan execution for prepared statements can be: auto, force_custom_plan, force_generic_plan” search_path order in which schemas are searched default_tablespace tablespace which objects are created in by default temp_tablespace tablespace(s) which temporary objects are created statement_timeout , abort statement that support this duration in milliseconds idle_in_trasnation_session_timeout terminate session with open transation longer than this duration in milliseconds
postmaster
postgres
postmaster…shared_buffers size of shared buffer pool for cluster temp_buffers size of memory used caching temporary tables work_mem size of memory used for sorting and hashing maintenance_work_men size of memory used for maintenance commands autovacuum_work_mem size of memory used by autovacuum worker temp_file_limit disk space used for temporary filesshared_buffers size of shared buffer pool for cluster temp_buffers size of memory used caching temporary tables work_mem size of memory used for sorting and hashing maintenance_work_men size of memory used for maintenance commands autovacuum_work_mem size of memory used by autovacuum worker temp_file_limit disk space used for temporary files
Memory
MemoryA group of backend and auxiliary processes that communicate using a common shared memory area. One postmaster process manages the instance; one instance manages exactly one database cluster with all its databases. max_parallel_workers_per_gather enable parallel query scan parallel_tuple_cost estimate cost of transferring one tuple from a parallel work process to another parallel_setup_cost estimate cost of launchng parallel worker processes min_parallel_table_scan_size set min amount of table data that must be scanned in order for a parallel scan min_parallel_index_scan_size set min amount of index that must be scanned in order for a parallel scan force_parallel_mode use to test parallel query scan even when no benifit max_parallel_maintenace_workers enables parallel index creation A group of backend and auxiliary processes that communicate using a common shared memory area. One postmaster process manages the instance; one instance manages exactly one database cluster with all its databases. max_parallel_workers_per_gather enable parallel query scan parallel_tuple_cost estimate cost of transferring one tuple from a parallel work process to another parallel_setup_cost estimate cost of launchng parallel worker processes min_parallel_table_scan_size set min amount of table data that must be scanned in order for a parallel scan min_parallel_index_scan_size set min amount of index that must be scanned in order for a parallel scan force_parallel_mode use to test parallel query scan even when no benifit max_parallel_maintenace_workers enables parallel index creation
Unix
Process
Unix…PGHOST: 127.0.0.1 PGPORT : 5432 PGUSER : postgres PGDATABASE : postgres PGDATA : /var/lib/pgsql/16/data parameter description listen_address address which server listens for connection *=all port port server listens on max_connections max concurrent connections server can support superuser_reserved_connections connection slots reserved for superusers reserved_connections slots reserved for user with pg_use_reserved_connection_role unix_socket_directory dir used for Unix Socket connection to server unix_socket_permission access permissions of Unix domain socket ”authentication_timeout ” max time to complete client authentication in secs ”row_security ” controls row security policy behaviour ”password_encryption ” algorithm to use to encrypt password ”ssl ” enable SSL connections ssl_ca_file file containing SSL server cert authority (CA) ssl_cert_file file containing SSL server cert ssl_key_file file containing SSL server private key ssl_ciphers list of SSL ciphers that maybe used for connections ssl_dh_params_file file for custom OpenSSL DH parametersPGHOST: 127.0.0.1 PGPORT : 5432 PGUSER : postgres PGDATABASE : postgres PGDATA : /var/lib/pgsql/16/data parameter description listen_address address which server listens for connection *=all port port server listens on max_connections max concurrent connections server can support superuser_reserved_connections connection slots reserved for superusers reserved_connections slots reserved for user with pg_use_reserved_connection_role unix_socket_directory dir used for Unix Socket connection to server unix_socket_permission access permissions of Unix domain socket ”authentication_timeout ” max time to complete client authentication in secs ”row_security ” controls row security policy behaviour ”password_encryption ” algorithm to use to encrypt password ”ssl ” enable SSL connections ssl_ca_file file containing SSL server cert authority (CA) ssl_cert_file file containing SSL server cert ssl_key_file file containing SSL server private key ssl_ciphers list of SSL ciphers that maybe used for connections ssl_dh_params_file file for custom OpenSSL DH parameters
Application
Applicat…
Connectors
Connectors
ECPG
ECPGStores changes to data that needs to be flushed to the WAL segments, This is used for database recoveryStores changes to data that needs to be flushed to the WAL segments, This is used for database recovery
WAL
Buffers
WAL…Store frequently accessed data buffers default 128MbStore frequently accessed data buffers default 128Mb
Shared Buffers
Shared BuffersCommit Log (CLOG) hold commit status of all transactions, so which transactions a have commited or notCommit Log (CLOG) hold commit status of all transactions, so which transactions a have commited or not
CLOG
Buffers
CLOG…Store locks used and shared across background server and user processesStore locks used and shared across background server and user processes
Memory 
Locks
Memory…Used by a query operation (such as a sort or hash table) before writing to temporary disk files.Used by a query operation (such as a sort or hash table) before writing to temporary disk files.
Work
Memory
Work…vacuum buffers are the memory used by each of the autovacuum worker processes, it is set with autovacuum_work_mem parameter.vacuum buffers are the memory used by each of the autovacuum worker processes, it is set with autovacuum_work_mem parameter.
Vacuum
Buffers
Vacuum…memory used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEYmemory used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY
Maintenance
Work Memory
Maintenance…Memory used for temporary buffers within each database session. These are session-local buffers used only for access to temporary tablesMemory used for temporary buffers within each database session. These are session-local buffers used only for access to temporary tables
Temp Buffers
Temp BuffersRAM which is used by the processes common to an instance. It mirrors parts of database files, provides a transient area for WAL records, and stores additional common informationRAM which is used by the processes common to an instance. It mirrors parts of database files, provides a transient area for WAL records, and stores additional common information
Shared Memory
Shared Memory
Process Memory
Process Memory
pgAdmin4
pgAdmin4
psql
psql
NODE.JS
NODE.JS
Perl DBI
Perl DBI
ODBC
ODBC
LIBPQ
LIBPQ
TCP
TCP
JDBC
JDBC
.NET
.NET
Python
Python
ECPG
ECPGLogs messages to log directory logfile are named: postgresql-.log log_destination control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog log_collector enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration log_directory dir where log files are written log_filename format of log file : postgresql-%Y-%m-%d_%H%M_SM.log log_file_mode permissions for log files log_rotation_age use for file age based log rotation log_rotation_size use for size based log rotation log_min_messages severity level when messages are logged log_min_error_statement severity level when statement that caused messaged is also logged log_min_duration_statement statements running =>duration are logged log_autovacuum_min_duration autovacuum running =>duration are logged log_statement_sample_rate Percent of queries above log_autovacuum_min_duration to be logged log_transaction_sample_rate sample a percentage of transactions be logging statements log_connections log sucessful connections to server log log_disconnections log info each time a session disconnects including duration of sessions log_temp_files log temporary files of this size or larger in Kb log_checkpoints log checkpoints and restart points to be logged to server log log_lock_waits log if session waits longer than deadlock_timeout to acquire a lock log_error_verbosity determines logging detail, default|terse|verbose log_line_prefix log additional details with each line, default %m[%p]m this logs timestamp and process ID log_statement none, ddl, mod (ddl + all other data modifying SQL), all Logs messages to log directory logfile are named: postgresql-.log log_destination control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog log_collector enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration log_directory dir where log files are written log_filename format of log file : postgresql-%Y-%m-%d_%H%M_SM.log log_file_mode permissions for log files log_rotation_age use for file age based log rotation log_rotation_size use for size based log rotation log_min_messages severity level when messages are logged log_min_error_statement severity level when statement that caused messaged is also logged log_min_duration_statement statements running =>duration are logged log_autovacuum_min_duration autovacuum running =>duration are logged log_statement_sample_rate Percent of queries above log_autovacuum_min_duration to be logged log_transaction_sample_rate sample a percentage of transactions be logging statements log_connections log sucessful connections to server log log_disconnections log info each time a session disconnects including duration of sessions log_temp_files log temporary files of this size or larger in Kb log_checkpoints log checkpoints and restart points to be logged to server log log_lock_waits log if session waits longer than deadlock_timeout to acquire a lock log_error_verbosity determines logging detail, default|terse|verbose log_line_prefix log additional details with each line, default %m[%p]m this logs timestamp and process ID log_statement none, ddl, mod (ddl + all other data modifying SQL), all Logs messages to log directory logfile are named: postgresql-.log log_destination control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog log_collector enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration log_directory dir where log files are written log_filename format of log file : postgresql-%Y-%m-%d_%H%M_SM.log log_file_mode permissions for log files log_rotation_age use for file age based log rotation log_rotation_size use for size based log rotation log_min_messages severity level when messages are logged log_min_error_statement severity level when statement that caused messaged is also logged log_min_duration_statement statements running =>duration are logged log_autovacuum_min_duration autovacuum running =>duration are logged log_statement_sample_rate Percent of queries above log_autovacuum_min_duration to be logged log_transaction_sample_rate sample a percentage of transactions be logging statements log_connections log sucessful connections to server log log_disconnections log info each time a session disconnects including duration of sessions log_temp_files log temporary files of this size or larger in Kb log_checkpoints log checkpoints and restart points to be logged to server log log_lock_waits log if session waits longer than deadlock_timeout to acquire a lock log_error_verbosity determines logging detail, default|terse|verbose log_line_prefix log additional details with each line, default %m[%p]m this logs timestamp and process ID log_statement none, ddl, mod (ddl + all other data modifying SQL), all Logs messages to log directory logfile are named: postgresql-.log log_destination control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog log_collector enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration log_directory dir where log files are written log_filename format of log file : postgresql-%Y-%m-%d_%H%M_SM.log log_file_mode permissions for log files log_rotation_age use for file age based log rotation log_rotation_size use for size based log rotation log_min_messages severity level when messages are logged log_min_error_statement severity level when statement that caused messaged is also logged log_min_duration_statement statements running =>duration are logged log_autovacuum_min_duration autovacuum running =>duration are logged log_statement_sample_rate Percent of queries above log_autovacuum_min_duration to be logged log_transaction_sample_rate sample a percentage of transactions be logging statements log_connections log sucessful connections to server log log_disconnections log info each time a session disconnects including duration of sessions log_temp_files log temporary files of this size or larger in Kb log_checkpoints log checkpoints and restart points to be logged to server log log_lock_waits log if session waits longer than deadlock_timeout to acquire a lock log_error_verbosity determines logging detail, default|terse|verbose log_line_prefix log additional details with each line, default %m[%p]m this logs timestamp and process ID log_statement none, ddl, mod (ddl + all other data modifying SQL), all
Log Files
Log Files
1
1
4
4
5
5
17121
17121
Unix
Files
Unix…The base directory on the file system of a server that contains all data files and subdirectories associated with a database cluster (with the exception of tablespaces, and optionally WAL)The base directory on the file system of a server that contains all data files and subdirectories associated with a database cluster (with the exception of tablespaces, and optionally WAL)
Data
Directory
Data…A collection of databases and global SQL objects, and their common static and dynamic metadata also can be called instance.A collection of databases and global SQL objects, and their common static and dynamic metadata also can be called instance.
Cluster
Cluster A database cluster is a collection of databases that postgres server containsA database cluster is a collection of databases that postgres server containsA database cluster is a collection of databases that postgres server containsA database cluster is a collection of databases that postgres server contains
Database Cluster
Database Cluster
Role
Ro…
User
Us…CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1.CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1.CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1.CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1.
template1 DB
template1 DB
Extension
Ext…
Tablespace
Tablesp…
Catalog
Cat…template0 should never be changed after the database cluster has been initialized. Use to create a “pristine” user database, useful for pg_dump loads.template0 should never be changed after the database cluster has been initialized. Use to create a “pristine” user database, useful for pg_dump loads.template0 should never be changed after the database cluster has been initialized. Use to create a “pristine” user database, useful for pg_dump loads.template0 should never be changed after the database cluster has been initialized. Use to create a “pristine” user database, useful for pg_dump loads.
template0 DB
template0 DB
Extension
Ext…
OID 4
OID 4
Catalog
Cat…postgres database is a default database meant for use by users, utilities and third party applicationspostgres database is a default database meant for use by users, utilities and third party applicationspostgres database is a default database meant for use by users, utilities and third party applicationspostgres database is a default database meant for use by users, utilities and third party applications
postgres DB
postgres DB
Extension
Ext…
OID 5
OID 5
Catalog
Cat…
Application DB
Application DB
Extension
Ext…
OID 17121
OID 17121
Catalog
Cat…
Schema
Schema
Function
Function
View
View
Trigger
Trigger
Relation
Relation
Sequence
Sequence
Schema
Schema
Function
Function
View
View
Trigger
Trigger
Relation
Relation
Sequence
Sequence
Schema
Schema
Function
Function
View
View
Trigger
Trigger
Relation
Relation
Sequence
Sequence
OID 1
OID 1Subdirectory containing WAL (Write Ahead Log) files: 00000001000000020000005F 000000010000000200000060 000000010000000200000061 archive_status Subdirectory containing WAL (Write Ahead Log) files: 00000001000000020000005F 000000010000000200000060 000000010000000200000061 archive_status
pg_wal
pg_walSubdirectory containing symbolic links to tablespaces: 24745-> /postgres/data/TEST_TS Subdirectory containing symbolic links to tablespaces: 24745-> /postgres/data/TEST_TS
24745
24745A file containing the major version number of PostgreSQL, in this case the value 16A file containing the major version number of PostgreSQL, in this case the value 16A file containing the major version number of PostgreSQL, in this case the value 16
PG_VERSION
PG_…File recording the log file(s) currently written to by the logging collector stderr log/postgresql-.log File recording the log file(s) currently written to by the logging collector stderr log/postgresql-.log File recording the log file(s) currently written to by the logging collector stderr log/postgresql-.log
current_logfiles
cur…A file used for storing configuration parameters that are set by ALTER SYSTEM A file used for storing configuration parameters that are set by ALTER SYSTEM A file used for storing configuration parameters that are set by ALTER SYSTEM
postgres.auto.conf
pos…A file recording the command-line options the server was last started with: /usr/pgsql-16/bin/postgres “-D” “/var/lib/pgsql/16/data/” A file recording the command-line options the server was last started with: /usr/pgsql-16/bin/postgres “-D” “/var/lib/pgsql/16/data/” A file recording the command-line options the server was last started with: /usr/pgsql-16/bin/postgres “-D” “/var/lib/pgsql/16/data/”
postmaster.opts
pos…Temporary files (for operations such as sorting more data than can fit in memory)Temporary files (for operations such as sorting more data than can fit in memory)
pgsql_tmp
pgsql_tmpHost Based Access Control file, configure authenication methods and rules.Host Based Access Control file, configure authenication methods and rules.Host Based Access Control file, configure authenication methods and rules.
pg_hba.conf
pg_…Main postgres database parameter configuration file, read on postgres startup. Connection Settings - listen_address : default * , address which server listens for connection *=all - port : default 5432, port server listens on - max_connections : default 100 , max concurrent connections server can support - superuser_reserved_connections: default 3 , connection slots reserved for superusers - reserved_connections : default 0 , slots reserved for user with pg_use_reserved_connection_role - unix_socket_directory : default /tmp, dir used for Unix Socket connection to server - unix_socket_permission : default 0777, access permissions of Unix domain socket Security and Authentication Settings - authentication_timeout : default 1 min, max time to complete client authentication in secs - row_security : default on, controls row security policy behaviour - password_encryption : default scram-sha-256, algorithm to use to encrypt password - ssl : default off, enable SSL connections Memory settings - Server – shared_buffers : size of shared buffer pool for cluster - Session – temp_buffers : size of memory used caching temporary tables – work_mem : size of memory used for sorting and hashing – maintenance_work_men : size of memory used for maintenance commands – autovacuum_work_mem : size of memory used by autovacuum worker – temp_file_limit : disk space used for temporary files Query Planner Settings - random_page_cost : default 4.0 , estimate cost of random page fetch, can reduce depending on caching impact - seq_page_cost : default 1.0 , estimate cost of a sequential page fetch - effective_cache_size : default 4GB , estimate cost of an index scan - plan_cache_mode : default auto, controls custom or generic plan execution for prepared statements can be: auto, force_custom_plan, force_generic_plan Write Ahead Log Setting - wal_level : default replica , control details written to WAL, also be minimal or logical - fsync : default on , Force WAL buffer flush on a commit, if off could cuase corruption/crash - wal_buffers : default -1, autotune , memory used in shared memory for WAL data, -1 uses 1/32 (3%) of shared_buffers - min_wal_size : default 80Mb , WAL size to start recycling WAL files - max_wal_size : default 1Gb , WAL size to start checkpointing, controls WAL segments(16Mb each) after which checkpoint forced - checkpoint_timeout : default 5 mins , max time between checkpoints - wal_compression : default off , WAL of full page write compress and written Where to Log - log_destination : control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog - log_collector : enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration - log_directory : dir where log files are written - log_filename : format of log file : postgresql-%Y-%m-%d_%H%M_SM.log - log_file_mode : permissions for log files - log_rotation_age : use for file age based log rotation - log_rotation_size : use for size based log rotation When to Log - log_min_messages : severity level when messages are logged - log_min_error_statement : severity level when statement that caused messaged is also logged - log_min_duration_statement : statements running =>duration are logged - log_autovacuum_min_duration : autovacuum running =>duration are logged - log_statement_sample_rate : Percent of queries above log_autovacuum_min_duration to be logged - log_transaction_sample_rate : sample a percentage of transactions be logging statements What to Log - log_connections : log sucessful connections to server log - log_disconnections : log info each time a session disconnects including duration of sessions - log_temp_files : log temporary files of this size or larger in Kb - log_checkpoints : log checkpoints and restart points to be logged to server log - log_lock_waits : log if session waits longer than deadlock_timeout to acquire a lock - log_error_verbosity : determines logging detail, default|terse|verbose - log_line_prefix : log additional details with each line, default %m[%p]m this logs timestamp and process ID - log_statement : nonem ddl, mod (ddl + all other data modifying SQL), all Background Writer Settings - bgwriter_delay : default 200ms, time between activity rounds for background writer, tuning technique is to lower value - bewriter_lru_maxpages : default 100 , max number of pages background writer may clean per activity round - bgwriter_lru_multiplier : default 2.0 , multiplier on buffer scanned per round Statement Behaviour - search_path : default “$user”, public, order in which schemas are searched - default_tablespace : tablespace which objects are created in by default - temp_tablespace : tablespace(s) which temporary objects are created - statement_timeout : default 0 (off), abort statement that support this duration in milliseconds - idle_in_trasnation_session_timeout : terminate session with open transation longer than this duration in milliseconds Parallel Query Scan Settings : Advanced server supports parallel exec if RO queries - max_parallel_workers_per_gather : default 2 , enable parallel query scan - parallel_tuple_cost : default 0.1 , estimate cost of transferring one tuple from a parallel work process to another - parallel_setup_cost : default 1000 , estimate cost of launchng parallel worker processes - min_parallel_table_scan_size : default 8 , set min amount of table data that must be scanned in order for a parallel scan - min_parallel_index_scan_size : default 512k , set min amount of index that must be scanned in order for a parallel scan - force_parallel_mode : default off , use to test parallel query scan even when no benifit Parallel Maintenance Settings, support for creating an index of type btree - max_parallel_maintenace_workers : default 2, enables parallel index creation Vacuum Cost Settings - vacuum_cost_delay : default 0ms , time in milliseconds process will wait when cost limit exceeded - vacuum_cost_page_hit : default 1 , estimated cost of vacuuming a buffer found in buffer pool - vacuum_cost_page_miss : default 10 , estimated cost of vacuuming a buffer that must be read into buffer pool - vacumm_cost_page_dirty : default 20 , estimated cost charged when vacuum modifues a buffer that was clean - vacuum_cost_limit : default 200 , accumulated cost that will cause vacuuming process to sleep - vacuum_buffer_usage_limit : default 256k, size of buffer access strategy used by vacuum and analyze commands Autovacuum Settings - autovacuum : default on , if autovacuum runs and starts worker process to vacuum and analyze tables - log_autovacuum_min_duration : default -1 , autovacuum tasks running longer than this duration are logged - autovacuum_max_workers : default 3 , max number of autovacuum worker processes which can run at one time - autovacuum_work_mem : default -1 (use mainteance_work_mem), max memory used by each autovacuum worker Main postgres database parameter configuration file, read on postgres startup. Connection Settings - listen_address : default * , address which server listens for connection *=all - port : default 5432, port server listens on - max_connections : default 100 , max concurrent connections server can support - superuser_reserved_connections: default 3 , connection slots reserved for superusers - reserved_connections : default 0 , slots reserved for user with pg_use_reserved_connection_role - unix_socket_directory : default /tmp, dir used for Unix Socket connection to server - unix_socket_permission : default 0777, access permissions of Unix domain socket Security and Authentication Settings - authentication_timeout : default 1 min, max time to complete client authentication in secs - row_security : default on, controls row security policy behaviour - password_encryption : default scram-sha-256, algorithm to use to encrypt password - ssl : default off, enable SSL connections Memory settings - Server – shared_buffers : size of shared buffer pool for cluster - Session – temp_buffers : size of memory used caching temporary tables – work_mem : size of memory used for sorting and hashing – maintenance_work_men : size of memory used for maintenance commands – autovacuum_work_mem : size of memory used by autovacuum worker – temp_file_limit : disk space used for temporary files Query Planner Settings - random_page_cost : default 4.0 , estimate cost of random page fetch, can reduce depending on caching impact - seq_page_cost : default 1.0 , estimate cost of a sequential page fetch - effective_cache_size : default 4GB , estimate cost of an index scan - plan_cache_mode : default auto, controls custom or generic plan execution for prepared statements can be: auto, force_custom_plan, force_generic_plan Write Ahead Log Setting - wal_level : default replica , control details written to WAL, also be minimal or logical - fsync : default on , Force WAL buffer flush on a commit, if off could cuase corruption/crash - wal_buffers : default -1, autotune , memory used in shared memory for WAL data, -1 uses 1/32 (3%) of shared_buffers - min_wal_size : default 80Mb , WAL size to start recycling WAL files - max_wal_size : default 1Gb , WAL size to start checkpointing, controls WAL segments(16Mb each) after which checkpoint forced - checkpoint_timeout : default 5 mins , max time between checkpoints - wal_compression : default off , WAL of full page write compress and written Where to Log - log_destination : control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog - log_collector : enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration - log_directory : dir where log files are written - log_filename : format of log file : postgresql-%Y-%m-%d_%H%M_SM.log - log_file_mode : permissions for log files - log_rotation_age : use for file age based log rotation - log_rotation_size : use for size based log rotation When to Log - log_min_messages : severity level when messages are logged - log_min_error_statement : severity level when statement that caused messaged is also logged - log_min_duration_statement : statements running =>duration are logged - log_autovacuum_min_duration : autovacuum running =>duration are logged - log_statement_sample_rate : Percent of queries above log_autovacuum_min_duration to be logged - log_transaction_sample_rate : sample a percentage of transactions be logging statements What to Log - log_connections : log sucessful connections to server log - log_disconnections : log info each time a session disconnects including duration of sessions - log_temp_files : log temporary files of this size or larger in Kb - log_checkpoints : log checkpoints and restart points to be logged to server log - log_lock_waits : log if session waits longer than deadlock_timeout to acquire a lock - log_error_verbosity : determines logging detail, default|terse|verbose - log_line_prefix : log additional details with each line, default %m[%p]m this logs timestamp and process ID - log_statement : nonem ddl, mod (ddl + all other data modifying SQL), all Background Writer Settings - bgwriter_delay : default 200ms, time between activity rounds for background writer, tuning technique is to lower value - bewriter_lru_maxpages : default 100 , max number of pages background writer may clean per activity round - bgwriter_lru_multiplier : default 2.0 , multiplier on buffer scanned per round Statement Behaviour - search_path : default “$user”, public, order in which schemas are searched - default_tablespace : tablespace which objects are created in by default - temp_tablespace : tablespace(s) which temporary objects are created - statement_timeout : default 0 (off), abort statement that support this duration in milliseconds - idle_in_trasnation_session_timeout : terminate session with open transation longer than this duration in milliseconds Parallel Query Scan Settings : Advanced server supports parallel exec if RO queries - max_parallel_workers_per_gather : default 2 , enable parallel query scan - parallel_tuple_cost : default 0.1 , estimate cost of transferring one tuple from a parallel work process to another - parallel_setup_cost : default 1000 , estimate cost of launchng parallel worker processes - min_parallel_table_scan_size : default 8 , set min amount of table data that must be scanned in order for a parallel scan - min_parallel_index_scan_size : default 512k , set min amount of index that must be scanned in order for a parallel scan - force_parallel_mode : default off , use to test parallel query scan even when no benifit Parallel Maintenance Settings, support for creating an index of type btree - max_parallel_maintenace_workers : default 2, enables parallel index creation Vacuum Cost Settings - vacuum_cost_delay : default 0ms , time in milliseconds process will wait when cost limit exceeded - vacuum_cost_page_hit : default 1 , estimated cost of vacuuming a buffer found in buffer pool - vacuum_cost_page_miss : default 10 , estimated cost of vacuuming a buffer that must be read into buffer pool - vacumm_cost_page_dirty : default 20 , estimated cost charged when vacuum modifues a buffer that was clean - vacuum_cost_limit : default 200 , accumulated cost that will cause vacuuming process to sleep - vacuum_buffer_usage_limit : default 256k, size of buffer access strategy used by vacuum and analyze commands Autovacuum Settings - autovacuum : default on , if autovacuum runs and starts worker process to vacuum and analyze tables - log_autovacuum_min_duration : default -1 , autovacuum tasks running longer than this duration are logged - autovacuum_max_workers : default 3 , max number of autovacuum worker processes which can run at one time - autovacuum_work_mem : default -1 (use mainteance_work_mem), max memory used by each autovacuum worker Main postgres database parameter configuration file, read on postgres startup. Connection Settings - listen_address : default * , address which server listens for connection *=all - port : default 5432, port server listens on - max_connections : default 100 , max concurrent connections server can support - superuser_reserved_connections: default 3 , connection slots reserved for superusers - reserved_connections : default 0 , slots reserved for user with pg_use_reserved_connection_role - unix_socket_directory : default /tmp, dir used for Unix Socket connection to server - unix_socket_permission : default 0777, access permissions of Unix domain socket Security and Authentication Settings - authentication_timeout : default 1 min, max time to complete client authentication in secs - row_security : default on, controls row security policy behaviour - password_encryption : default scram-sha-256, algorithm to use to encrypt password - ssl : default off, enable SSL connections Memory settings - Server – shared_buffers : size of shared buffer pool for cluster - Session – temp_buffers : size of memory used caching temporary tables – work_mem : size of memory used for sorting and hashing – maintenance_work_men : size of memory used for maintenance commands – autovacuum_work_mem : size of memory used by autovacuum worker – temp_file_limit : disk space used for temporary files Query Planner Settings - random_page_cost : default 4.0 , estimate cost of random page fetch, can reduce depending on caching impact - seq_page_cost : default 1.0 , estimate cost of a sequential page fetch - effective_cache_size : default 4GB , estimate cost of an index scan - plan_cache_mode : default auto, controls custom or generic plan execution for prepared statements can be: auto, force_custom_plan, force_generic_plan Write Ahead Log Setting - wal_level : default replica , control details written to WAL, also be minimal or logical - fsync : default on , Force WAL buffer flush on a commit, if off could cuase corruption/crash - wal_buffers : default -1, autotune , memory used in shared memory for WAL data, -1 uses 1/32 (3%) of shared_buffers - min_wal_size : default 80Mb , WAL size to start recycling WAL files - max_wal_size : default 1Gb , WAL size to start checkpointing, controls WAL segments(16Mb each) after which checkpoint forced - checkpoint_timeout : default 5 mins , max time between checkpoints - wal_compression : default off , WAL of full page write compress and written Where to Log - log_destination : control logging type for db cluster: stderr, csvlog, jsonlog, syslog, eventlog - log_collector : enables logger process to capture stderrr and csv logging messages which can be redirected based on configuration - log_directory : dir where log files are written - log_filename : format of log file : postgresql-%Y-%m-%d_%H%M_SM.log - log_file_mode : permissions for log files - log_rotation_age : use for file age based log rotation - log_rotation_size : use for size based log rotation When to Log - log_min_messages : severity level when messages are logged - log_min_error_statement : severity level when statement that caused messaged is also logged - log_min_duration_statement : statements running =>duration are logged - log_autovacuum_min_duration : autovacuum running =>duration are logged - log_statement_sample_rate : Percent of queries above log_autovacuum_min_duration to be logged - log_transaction_sample_rate : sample a percentage of transactions be logging statements What to Log - log_connections : log sucessful connections to server log - log_disconnections : log info each time a session disconnects including duration of sessions - log_temp_files : log temporary files of this size or larger in Kb - log_checkpoints : log checkpoints and restart points to be logged to server log - log_lock_waits : log if session waits longer than deadlock_timeout to acquire a lock - log_error_verbosity : determines logging detail, default|terse|verbose - log_line_prefix : log additional details with each line, default %m[%p]m this logs timestamp and process ID - log_statement : nonem ddl, mod (ddl + all other data modifying SQL), all Background Writer Settings - bgwriter_delay : default 200ms, time between activity rounds for background writer, tuning technique is to lower value - bewriter_lru_maxpages : default 100 , max number of pages background writer may clean per activity round - bgwriter_lru_multiplier : default 2.0 , multiplier on buffer scanned per round Statement Behaviour - search_path : default “$user”, public, order in which schemas are searched - default_tablespace : tablespace which objects are created in by default - temp_tablespace : tablespace(s) which temporary objects are created - statement_timeout : default 0 (off), abort statement that support this duration in milliseconds - idle_in_trasnation_session_timeout : terminate session with open transation longer than this duration in milliseconds Parallel Query Scan Settings : Advanced server supports parallel exec if RO queries - max_parallel_workers_per_gather : default 2 , enable parallel query scan - parallel_tuple_cost : default 0.1 , estimate cost of transferring one tuple from a parallel work process to another - parallel_setup_cost : default 1000 , estimate cost of launchng parallel worker processes - min_parallel_table_scan_size : default 8 , set min amount of table data that must be scanned in order for a parallel scan - min_parallel_index_scan_size : default 512k , set min amount of index that must be scanned in order for a parallel scan - force_parallel_mode : default off , use to test parallel query scan even when no benifit Parallel Maintenance Settings, support for creating an index of type btree - max_parallel_maintenace_workers : default 2, enables parallel index creation Vacuum Cost Settings - vacuum_cost_delay : default 0ms , time in milliseconds process will wait when cost limit exceeded - vacuum_cost_page_hit : default 1 , estimated cost of vacuuming a buffer found in buffer pool - vacuum_cost_page_miss : default 10 , estimated cost of vacuuming a buffer that must be read into buffer pool - vacumm_cost_page_dirty : default 20 , estimated cost charged when vacuum modifues a buffer that was clean - vacuum_cost_limit : default 200 , accumulated cost that will cause vacuuming process to sleep - vacuum_buffer_usage_limit : default 256k, size of buffer access strategy used by vacuum and analyze commands Autovacuum Settings - autovacuum : default on , if autovacuum runs and starts worker process to vacuum and analyze tables - log_autovacuum_min_duration : default -1 , autovacuum tasks running longer than this duration are logged - autovacuum_max_workers : default 3 , max number of autovacuum worker processes which can run at one time - autovacuum_work_mem : default -1 (use mainteance_work_mem), max memory used by each autovacuum worker
postgresql.conf
pos…controls PostgreSQL user name mapping. It maps external user names to their corresponding PostgreSQL user names.controls PostgreSQL user name mapping. It maps external user names to their corresponding PostgreSQL user names.controls PostgreSQL user name mapping. It maps external user names to their corresponding PostgreSQL user names.
pg_ident.conf
pg_…A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (could be empty), 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): 250510 /var/lib/pgsql/16/data 1705281925 5432 /var/run/postgresql * 137383279 7 ready A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (could be empty), 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): 250510 /var/lib/pgsql/16/data 1705281925 5432 /var/run/postgresql * 137383279 7 ready A lock file recording the current postmaster process ID (PID), cluster data directory path, postmaster start timestamp, port number, Unix-domain socket directory path (could be empty), 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): 250510 /var/lib/pgsql/16/data 1705281925 5432 /var/run/postgresql * 137383279 7 ready
postmaster.pid
pos…
Schema
Schema
Function
Function
View
View
Trigger
Trigger
Relation
Relation
Sequence
Sequence
Databases
DatabasesDiagram created by Paul SammyDiagram created by Paul Sammy
PostgreSQL 16 Architecture Diagram v1.5
PostgreSQL 16 Architecture Di…contains log files such as: postgresql-Mon.logcontains log files such as: postgresql-Mon.log
log
logSubdirectory containing symbolic links to tablespaces: 24745-> /postgres/data/TEST_TS Subdirectory containing symbolic links to tablespaces: 24745-> /postgres/data/TEST_TS
pg_tblsc
pg_tblscSubdirectory containing symbolic links to tablespaces: 24745-> /postgres/data/TEST_TS Subdirectory containing symbolic links to tablespaces: 24745-> /postgres/data/TEST_TS
PG_16_202307071
PG_16_202307071
17121
17121
pg_dump
pg_dump
pg_dumpall
pg_dumpall
pg_basebackup
pg_basebackup
pgBackTest
pgBackTest
barman
barman
PEM
PEM
pgpool-II
pgpool-II
pgbouncer
pgbouncerProgramming Connectorspostgres Admin toolsPostgres Backup ToolsPostgres connection pooling
pg_ctl
pg_ctl
xmin
xmin
xmax
xmax
cmin
cmin
cmax
cmax
ctid
ctid
natts
natts
infomask
infomask
hoff
hoff
bits
bits
Attributes
Attribut…
Tuple Internals
Tuple Internals
8k Page
8k PageField Description pd_lsn LSN: next byte after last byte of WAL record for last change to this page pd_checksum Page checksum pd_flags Flag bits pd_lower Offset to start of free space pd_upper Offset to end of free space pd_special Offset to start of special space pd_pagesize_version Page size and layout version number information pd_prune_xid Oldest unpruned XMAX on page, or zero if none Field Description pd_lsn LSN: next byte after last byte of WAL record for last change to this page pd_checksum Page checksum pd_flags Flag bits pd_lower Offset to start of free space pd_upper Offset to end of free space pd_special Offset to start of special space pd_pagesize_version Page size and layout version number information pd_prune_xid Oldest unpruned XMAX on page, or zero if none
HEADER
HEADERArray of item identifiers pointing to the actual items
ItemID
ItemID
ItemID
ItemID
ItemID
ItemID
Tuple
Tuple
Tuple
Tuple
Tuple
TupleThe unallocated space. New item identifiers are allocated from the start of this area, new items from the endThe unallocated space. New item identifiers are allocated from the start of this area, new items from the end
Free Space
Free Space
Special
Specialpassword file format: host:port:db_name:user_name:passwordpassword file format: host:port:db_name:user_name:passwordpassword file format: host:port:db_name:user_name:password
.pgpass
.p…Subdirectory containing cluster-wide tables, such as pg_databaseSubdirectory containing cluster-wide tables, such as pg_database
global
globalSubdirectory containing transaction commit timestamp dataSubdirectory containing transaction commit timestamp data
pg_commit_ts
pg_commit_tsSubdirectory containing files used by the dynamic shared memory subsystem Subdirectory containing files used by the dynamic shared memory subsystem
pg_dynshmem
pg_dynshmemSubdirectory containing status data for logical decoding: mappings snapshots replorigin_checkpoint Subdirectory containing status data for logical decoding: mappings snapshots replorigin_checkpoint
pg_logical
pg_logicalSubdirectory containing multitransaction status data (used for shared row locks): members offsets Subdirectory containing multitransaction status data (used for shared row locks): members offsets
pg_multixact
pg_multixactSubdirectory containing LISTEN/NOTIFY status dataSubdirectory containing LISTEN/NOTIFY status data
pg_notify
pg_notifySubdirectory containing replication slot dataSubdirectory containing replication slot data
pg_repslot
pg_repslotSubdirectory containing information about committed serializable transactionsSubdirectory containing information about committed serializable transactions
pg_serial
pg_serialSubdirectory containing information about committed serializable transactionsSubdirectory containing information about committed serializable transactions
pg_snapshots
pg_snapshotsSubdirectory containing permanent files for the statistics subsystemSubdirectory containing permanent files for the statistics subsystem
pg_stat
pg_statSubdirectory containing temporary files for the statistics subsystemSubdirectory containing temporary files for the statistics subsystem
pg_stat_tmp
pg_stat_tmpSubdirectory containing subtransaction status dataSubdirectory containing subtransaction status data
pg_subtrans
pg_subtransSubdirectory containing state files for prepared transactionsSubdirectory containing state files for prepared transactions
pg_twophase
pg_twophaseSubdirectory containing transaction commit status data: 0000Subdirectory containing transaction commit status data: 0000
pg_xact
pg_xactSubdirectory containing per-database subdirectoriesSubdirectory containing per-database subdirectories
base
base
pgbadger
pgbadger
Extensions
Extensions
     pg_stat_statements, plpgsql , pgcrypto, postgres_fdw
sql_profiler, pg_prewarm, file_fdw, lo, hstore, system_stats
pg_stat_statements, plpgsql , pgcrypto, postgres_…Copies contents of full wal segments to a seperate filessytem as wal archives, these can be used for recovery,Copies contents of full wal segments to a seperate filessytem as wal archives, these can be used for recovery,
archiver
archiver
WAL Segments
WAL Segmentsonce WAL segments are full the ARHIVER Writes to these to WAL archives (if archive mode enabled)once WAL segments are full the ARHIVER Writes to these to WAL archives (if archive mode enabled)once WAL segments are full the ARHIVER Writes to these to WAL archives (if archive mode enabled)once WAL segments are full the ARHIVER Writes to these to WAL archives (if archive mode enabled)
WAL Archives
WAL Archivesdefault tablespaces: pg_global : PGDATA/global, cluster-wide tables and catalog objects pg_default : PGDATA/base , databases, schemas and other objects postgres also supports custom applicaton tablespacesdefault tablespaces: pg_global : PGDATA/global, cluster-wide tables and catalog objects pg_default : PGDATA/base , databases, schemas and other objects postgres also supports custom applicaton tablespacesdefault tablespaces: pg_global : PGDATA/global, cluster-wide tables and catalog objects pg_default : PGDATA/base , databases, schemas and other objects postgres also supports custom applicaton tablespacesdefault tablespaces: pg_global : PGDATA/global, cluster-wide tables and catalog objects pg_default : PGDATA/base , databases, schemas and other objects postgres also supports custom applicaton tablespaces
Tablespaces
Tablespaces
pg_global
pg_global
Application
Application
pg_default
pg_default
Replica 1
Replica 1Used in Logical Replication to replica a subset of the databaseUsed in Logical Replication to replica a subset of the database
Subscription
Subscription
Replica DB
Replica DB
Catalog
Catalog
Schema
Schema
Relation
Relation
Sequence
SequenceUsed in Logical Replication to replica a subset of the databaseUsed in Logical Replication to replica a subset of the database
Publication
Publication
Physical Or Logical
Physical Or Logical
Primary
Primary
Standby
Standbylaunches logical replication workers for each subscriptionlaunches logical replication workers for each subscription
logical replication launcher
logical replication apply worker
logical replication launcher…keeps a command history for psqlkeeps a command history for psqlkeeps a command history for psql
.psql_history
.p…user updateable configuration file for psqluser updateable configuration file for psqluser updateable configuration file for psql
.psqlrc
.p…
basebackup
basebackup
tablespace_map
t…
backup_label
b…
backup_manifest
b…
pg_wal.tar.gz
pg_wal.tar…
base.tar.gz
base.tar.gz
19017.tar.gz
19017.tar….
dump backups
dump backups
db.dmp
d…
db.sql
d…
global.sql
g…User backend process that talks to shared memoryUser backend process that talks to shared memory
Backend
BackendPGHOST: 127.0.0.1 PGPORT : 5432 PGUSER : postgres PGDATABASE : postgres PGDATA : /var/lib/pgsql/16/data parameter description listen_address address which server listens for connection *=all port port server listens on max_connections max concurrent connections server can support superuser_reserved_connections connection slots reserved for superusers reserved_connections slots reserved for user with pg_use_reserved_connection_role unix_socket_directory dir used for Unix Socket connection to server unix_socket_permission access permissions of Unix domain socket ”authentication_timeout ” max time to complete client authentication in secs ”row_security ” controls row security policy behaviour ”password_encryption ” algorithm to use to encrypt password ”ssl ” enable SSL connections ssl_ca_file file containing SSL server cert authority (CA) ssl_cert_file file containing SSL server cert ssl_key_file file containing SSL server private key ssl_ciphers list of SSL ciphers that maybe used for connections ssl_dh_params_file file for custom OpenSSL DH parameters
Users
Users
Utility Processes
Utility Processesstandby.signal signify the server should start as a replicastandby.signal signify the server should start as a replica
OID 17121
OID 17121standby.signal signify the server should start as a replicastandby.signal signify the server should start as a replica
recovery.conf
recovery.conf
standby.signal
standby.signal
Extension
Extension
Function
Function
View
View
Trigger
Trigger
Replica 2
Replica 2Used in Logical Replication to replica a subset of the databaseUsed in Logical Replication to replica a subset of the database
Subscription
Subscription
Replica DB
Replica DB
Catalog
Catalog
Schema
Schema
Relation
Relation
Sequence
Sequence
Physical Or Logical
Physical Or Logical
Standby
Standbystandby.signal signify the server should start as a replicastandby.signal signify the server should start as a replica
OID 17121
OID 17121standby.signal signify the server should start as a replicastandby.signal signify the server should start as a replica
recovery.conf
recovery.conf
standby.signal
standby.signal
Extension
Extension
Function
Function
View
View
Trigger
TriggerText is not SVG – cannot display