Postgres architecture diagram with explanation

Open source and very capable, PostgreSQL is an object-relational database system that builds upon the SQL language and incorporates numerous capabilities to securely store and handle even the most complex data demands. With almost 35 years of continuous development on the core platform, PostgreSQL began as part of the POSTGRES project at the University of California, Berkeley in 1986.

Process and Memory Architecture

To find the background process at OS level

bash-4.2$ ps -ef | grep postgres

postgres 1276 1 0 07:44 ? 00:00:00 /opt/PostgreSQL/10/bin/postgres -D /opt/PostgreSQL/10/data

postgres 1311 1276 0 07:44 ? 00:00:00 postgres: logger process

postgres 1335 1276 0 07:44 ? 00:00:00 postgres: checkpointer process

postgres 1336 1276 0 07:44 ? 00:00:00 postgres: writer process

postgres 1337 1276 0 07:44 ? 00:00:00 postgres: wal writer process

postgres 1338 1276 0 07:44 ? 00:00:00 postgres: autovacuum launcher process

postgres 1339 1276 0 07:44 ? 00:00:00 postgres: stats collector process

postgres 1342 1276 0 07:44 ? 00:00:00 postgres: bgworker: logical replication launcher

root 2522 2441 0 08:01 pts/0 00:00:00 su - postgres

postgres 2523 2522 0 08:01 pts/0 00:00:00 -bash

postgres 2688 1 0 08:11 ? 00:00:00 /usr/pgsql-14/bin/postgres -D /app01/cluster1

postgres 2689 2688 0 08:11 ? 00:00:00 postgres: logger

postgres 2691 2688 0 08:11 ? 00:00:00 postgres: checkpointer

postgres 2692 2688 0 08:11 ? 00:00:00 postgres: background writer

postgres 2693 2688 0 08:11 ? 00:00:00 postgres: walwriter

postgres 2694 2688 0 08:11 ? 00:00:00 postgres: autovacuum launcher

postgres 2695 2688 0 08:11 ? 00:00:00 postgres: stats collector

postgres 2696 2688 0 08:11 ? 00:00:00 postgres: logical replication launcher

database, postgresql, tutorial

How to create a database in postgresql

PostgreSQL simplified database creation, once initialized the databases cluster by default it create 3 database, postgres , templete0 and template1.

If you don't create any new database, all the objects will be created under default postgres database.

Follow below steps to create new database in postgresql.

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(3 rows)

- To create a new database.

postgres=# create database dbnew;

CREATE DATABASE

( Database use default tablespace pg_default.)

- To create a new database with user defained tablespace.

First create new tablespace.

postgres=# create tablespace new_ts location '/app01/new_ts';

CREATE TABLESPACE

postgres=# create database dbnewts with tablespace new_ts;

CREATE DATABASE

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(5 rows)

database, postgresql

psql error connection to server on socket

Issue :

-bash-4.2$ psql -U postgres -p 4441

psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.4441" fail ed: No such file or directory

Is the server running locally and accepting connections on that socket?

Solution :

- Check your database cluster state, generally we receive this issue only when our db cluster is down state.

Example

Start the database cluster

-bash-4.2$ ls -ltr

total 8

drwx------. 20 postgres postgres 4096 Feb 15 16:59 cluster2 / <your db instance>

drwx------. 20 postgres postgres 4096 Feb 15 18:53 cluster1 / <your db instance>

-bash-4.2$ pg_ctl -D /app01/cluster1/ start

pg_ctl: another server might be running; trying to start server anyway

waiting for server to start....2024-02-16 08:11:45.502 IST [2688] LOG: redirecting log output to logging collector process

2024-02-16 08:11:45.502 IST [2688] HINT: Future log output will appear in directory "log".

done

server started

-bash-4.2$ psql -U postgres -p 4441

psql (14.11)

Type "help" for help.

postgres=# show port;

port

------

4441

database, error, issue, postgresql, tutorial

How to change default port in postgresql

This article will help in changing the port number in PostgreSQL database.

Port numbers are using to connect the database remote server or application, each database having one unique port number, in PostgreSQL database the default port number is 5432, for security reasons organizations use different port number.

Database administrators having full access on servers and they can able to modify the port number of PostgreSQL database anytime.

Step 1 : Connect to the database.

-bash-4.2$ psql -U postgres -p 2221

psql (14.11)

Type "help" for help.

Step 2 : Verify the current PORT number of daabase

postgres=# select name,setting,reset_val from pg_settings where name='port';

name | setting | reset_val

------+---------+-----------

port | 2221 | 2221

Step 3 : Connect to the database and fond configurations file location.

postgres=# \conninfo

You are connected to database "postgres" as user "postgres" via socket in "/run/postgresql" at port "2221".

postgres=# show config_file;

config_file

---------------------------------

/app01/cluster1/postgresql.conf

Step 4 : Go to config file and edit the parameter

-bash-4.2$ vi /app01/cluster1/postgresql.conf

port = 4441 # (change requires restart)

Step 5 : To reflect the new port number database cluster need to be restarted.

-bash-4.2$ pg_ctl -D /app01/cluster1/ stop

waiting for server to shut down.... done

server stopped

-bash-4.2$ pg_ctl -D /app01/cluster1/ start

waiting for server to start....2024-02-15 18:53:43.697 IST [24477] LOG: redirecting log output to logging collector process

2024-02-15 18:53:43.697 IST [24477] HINT: Future log output will appear in directory "log".

done

server started

Step 6 : Connect to the database with new port number and verify.

-bash-4.2$ psql -U postgres -p 4441

psql (14.11)

Type "help" for help.

postgres=# select name,setting,reset_val from pg_settings where name='port';

name | setting | reset_val

------+---------+-----------

port | 4441 | 4441

(1 row)

database, postgresql, tutorial

How to describe view in postgresql

In every RDBMS database there are default dictionary read only tables to contain the metadata of complete database. These data dictionary views helps to find out database default structure and application objects information.

In PostgreSQL database also there are user defined objects as well as default tables and views which got created during the db cluster installation.

to use those views we need to now the columns of tables/ data dictionary views.

Example : To verify database information there is view called pg_database.

\d <dictionary view> describe view in postgresql

postgres=# \d pg_database;

Table "pg_catalog.pg_database"

Column | Type | Collation | Nullable | Default

---------------+-----------+-----------+----------+---------

oid | oid | | not null |

datname | name | | not null |

datdba | oid | | not null |

encoding | integer | | not null |

datcollate | name | | not null |

datctype | name | | not null |

datistemplate | boolean | | not null |

datallowconn | boolean | | not null |

datconnlimit | integer | | not null |

datlastsysoid | oid | | not null |

datfrozenxid | xid | | not null |

datminmxid | xid | | not null |

dattablespace | oid | | not null |

datacl | aclitem[] | | |

Indexes:

"pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"

"pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"

Tablespace: "pg_global"

postgres=# \d pg_tablespace;

Table "pg_catalog.pg_tablespace"

Column | Type | Collation | Nullable | Default

------------+-----------+-----------+----------+---------

oid | oid | | not null |

spcname | name | | not null |

spcowner | oid | | not null |

spcacl | aclitem[] | | |

spcoptions | text[] | C | |

Indexes:

"pg_tablespace_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"

"pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"

Tablespace: "pg_global"

database, postgresql

User defined database creation in postgresql

Database creation in PostgreSQL

In PostgreSQL there are different methods to install the database like using yum install we can create database in default location and other one is user defined method database creation.

The disadvantage with default method is, software and database will be created under the /usr and /var/lib and those mount points not contain much space.

Below User defined database creation in postgresql have more control for administrators to manage.

Follow below steps to create database in PostgreSQL.

Step1 : Install software using below yum repository

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server

Step 2 : Create a Directory where mount point having sufficient space and copy bash profile to the newly created directoy.

[root@server1 ~]# mkdir /app01
[root@server1 ~]# cp /root/.bash_profile /app01/

Step 3 : Open the bash profile under newly created directory and specify the PostgreSQL binaries path.

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH=/usr/pgsql-14/bin:$PATH
export PATH

Step 4 : Change ownership for binaries and database directories.

[root@server1 ~]# chown -R postgres:postgres /app01/

[root@server1 ~]# chown -R postgres:postgres /usr/pgsql-14/

Step 5 : Open the password file under /etc location and change user home location.

vi /etc/password
postgres:x:1001:1001:PostgreSQL:/app01:/bin/bash

Step 6 : Now, login as postgres user and verify bashprofile and psql connectivity and version.

[root@server1 ~]# su - postgres

-bash-4.2$ pwd

/app01

-bash-4.2$ initdb --version

initdb (PostgreSQL) 14.11

Step 7 : Create a Directory under /app01/ for new cluser database and create database using initdb utility.

-bash-4.2$ mkdir cluster1

-bash-4.2$ initdb -D cluster1/

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".

The default database encoding has accordingly been set to "UTF8".

The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory cluster1 ... ok

creating subdirectories ... ok

selecting dynamic shared memory implementation ... posix

selecting default max_connections ... 100

selecting default shared_buffers ... 128MB

selecting default time zone ... Asia/Kolkata

creating configuration files ... ok

running bootstrap script ... ok

performing post-bootstrap initialization ... ok

syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the option -A, or

--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D cluster1/ -l logfile start

-bash-4.2$ ls -ltr

total 4

drwx------. 19 postgres postgres 4096 Feb 15 13:38 cluster1

-bash-4.2$ pwd

/app01

-bash-4.2$ cd cluster1/

-bash-4.2$ ls -ltr

total 56

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_twophase

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_snapshots

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_serial

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_notify

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_dynshmem

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_commit_ts

-rw-------. 1 postgres postgres 3 Feb 15 13:38 PG_VERSION

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_tblspc

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_stat_tmp

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_stat

drwx------. 2 postgres postgres 6 Feb 15 13:38 pg_replslot

drwx------. 4 postgres postgres 36 Feb 15 13:38 pg_multixact

-rw-------. 1 postgres postgres 28764 Feb 15 13:38 postgresql.conf

-rw-------. 1 postgres postgres 88 Feb 15 13:38 postgresql.auto.conf

-rw-------. 1 postgres postgres 1636 Feb 15 13:38 pg_ident.conf

-rw-------. 1 postgres postgres 4789 Feb 15 13:38 pg_hba.conf

drwx------. 2 postgres postgres 18 Feb 15 13:38 pg_xact

drwx------. 3 postgres postgres 60 Feb 15 13:38 pg_wal

drwx------. 2 postgres postgres 18 Feb 15 13:38 pg_subtrans

drwx------. 2 postgres postgres 4096 Feb 15 13:38 global

drwx------. 5 postgres postgres 41 Feb 15 13:38 base

drwx------. 4 postgres postgres 68 Feb 15 13:38 pg_logical

Step 8 : If default PORT number want change, open postgresql configuration file and change the post number as like below and save the file.

-bash-4.2$ vi postgresql.conf

port = 2221 # (change requires restart)

Step 9 : Start the database using pg_ctl.

-bash-4.2$ ps -ef | grep postgre

root 4717 2248 0 13:36 pts/0 00:00:00 su - postgres

postgres 4718 4717 0 13:36 pts/0 00:00:00 -bash

postgres 4832 4718 0 13:41 pts/0 00:00:00 ps -ef

postgres 4833 4718 0 13:41 pts/0 00:00:00 grep --color=auto postgre

-bash-4.2$ pg_ctl -D /app01/cluster1/ start

waiting for server to start....2024-02-15 13:42:14.812 IST [4849] LOG: redirecting log output to logging collector process

2024-02-15 13:42:14.812 IST [4849] HINT: Future log output will appear in directory "log".

done

server started

-bash-4.2$ ps -ef | grep postgres

root 4717 2248 0 13:36 pts/0 00:00:00 su - postgres

postgres 4718 4717 0 13:36 pts/0 00:00:00 -bash

postgres 4849 1 0 13:42 ? 00:00:00 /usr/pgsql-14/bin/postgres -D /app01/cluster1 >> Postmaster - Main supervisor process in postgresql

postgres 4850 4849 0 13:42 ? 00:00:00 postgres: logger

postgres 4852 4849 0 13:42 ? 00:00:00 postgres: checkpointer

postgres 4853 4849 0 13:42 ? 00:00:00 postgres: background writer

postgres 4854 4849 0 13:42 ? 00:00:00 postgres: walwriter

postgres 4855 4849 0 13:42 ? 00:00:00 postgres: autovacuum launcher

postgres 4856 4849 0 13:42 ? 00:00:00 postgres: stats collector

postgres 4857 4849 0 13:42 ? 00:00:00 postgres: logical replication launcher

postgres 4866 4718 99 13:42 pts/0 00:00:00 ps -ef

postgres 4867 4718 0 13:42 pts/0 00:00:00 grep --color=auto postgres

Step 10 : Login into the database and using postgres user and verify all the file.

-bash-4.2$ psql -U postgres -p 2221

psql (14.11)

Type "help" for help.

postgres=# select datname from pg_database;

datname

-----------

postgres

template1

template0

(3 rows)

postgres=# \l+

List of databases

-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------

(3 rows)

postgres=# \db+

List of tablespaces

------------+----------+----------+-------------------+---------+--------+-------------

(2 rows)

postgres=# select * from pg_tablespace;

oid | spcname | spcowner | spcacl | spcoptions

------+------------+----------+--------+------------

1663 | pg_default | 10 | |

1664 | pg_global | 10 | |

(2 rows)

postgres=# \du+

List of roles

Role name | Attributes | Member of | Description

-----------+------------------------------------------------------------+-----------+-------------

postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |

postgres=# select * from pg_user;

----------+----------+-------------+----------+---------+--------------+----------+----------+-----------

postgres | 10 | t | t | t | t | ******** | |

(1 row)

postgres=# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+----------------------+------------------------

public | postgres | postgres=UC/postgres+| standard public schema

| | =UC/postgres |

(1 row)

postgres=# create database mdb;

CREATE DATABASE

postgres=#

postgres=# \l+ mdb

List of databases

------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------

mdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 8625 kB | pg_default |

(1 row)

postgres=# select oid,datname from pg_database;

oid | datname

-------+-----------

14487 | postgres

16384 | mdb

1 | template1

14486 | template0

(4 rows)

postgres=# \c mdb

You are now connected to database "mdb" as user "postgres".

mdb=# \dS+

List of relations

------------+---------------------------------+-------+----------+-------------+---------------+------------+-------------

(134 rows)

database, postgresql, tutorial

psql postgresql shortcut commands.

psql / postgresql shortcut commands.

Unlike oracle database, postgresql / psql interactive terminal to work with the postgresql database, most of the data you can retrieve from the database using below shortcuts instead of writing big sql queries.

Below psql short commands will help to explore more.

postgres=# \? - To get the all the psql shortcut commands use " \? "

It display all the psql shortcut commands

General

\copyright show PostgreSQL usage and distribution terms

\crosstabview [COLUMNS] execute query and display results in crosstab

\errverbose show most recent error message at maximum verbosity

\g [FILE] or ; execute query (and send results to file or |pipe)

\gexec execute query, then execute each value in its result

\gset [PREFIX] execute query and store results in psql variables

\gx [FILE] as \g, but forces expanded output mode

\q quit psql

\watch [SEC] execute query every SEC seconds

Help

\? [commands] show help on backslash commands

\? options show help on psql command-line options

\? variables show help on special variables

\h [NAME] help on syntax of SQL commands, * for all commands

Query Buffer

\e [FILE] [LINE] edit the query buffer (or file) with external editor

\ef [FUNCNAME [LINE]] edit function definition with external editor

\ev [VIEWNAME [LINE]] edit view definition with external editor

\p show the contents of the query buffer

\r reset (clear) the query buffer

\s [FILE] display history or save it to file

\w FILE write query buffer to file

Input/Output

\copy ... perform SQL COPY with data stream to the client host

\echo [STRING] write string to standard output

\i FILE execute commands from file

\ir FILE as \i, but relative to location of current script

\o [FILE] send all query results to file or |pipe

\qecho [STRING] write string to query output stream (see \o)

Conditional

\if EXPR begin conditional block

\elif EXPR alternative within current conditional block

\else final alternative within current conditional block

\endif end conditional block

Informational

(options: S = show system objects, + = additional detail)

\d[S+] list tables, views, and sequences

\d[S+] NAME describe table, view, sequence, or index

\da[S] [PATTERN] list aggregates

\dA[+] [PATTERN] list access methods

\db[+] [PATTERN] list tablespaces

\dc[S+] [PATTERN] list conversions

\dC[+] [PATTERN] list casts

\dd[S] [PATTERN] show object descriptions not displayed elsewhere

\dD[S+] [PATTERN] list domains

\ddp [PATTERN] list default privileges

\dE[S+] [PATTERN] list foreign tables

\det[+] [PATTERN] list foreign tables

\des[+] [PATTERN] list foreign servers

\deu[+] [PATTERN] list user mappings

\dew[+] [PATTERN] list foreign-data wrappers

\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions

\dF[+] [PATTERN] list text search configurations

\dFd[+] [PATTERN] list text search dictionaries

\dFp[+] [PATTERN] list text search parsers

\dFt[+] [PATTERN] list text search templates

\dg[S+] [PATTERN] list roles

\di[S+] [PATTERN] list indexes

\dl list large objects, same as \lo_list

\dL[S+] [PATTERN] list procedural languages

\dm[S+] [PATTERN] list materialized views

\dn[S+] [PATTERN] list schemas

\do[S] [PATTERN] list operators

\dO[S+] [PATTERN] list collations

\dp [PATTERN] list table, view, and sequence access privileges

\drds [PATRN1 [PATRN2]] list per-database role settings

\dRp[+] [PATTERN] list replication publications

\dRs[+] [PATTERN] list replication subscriptions

\ds[S+] [PATTERN] list sequences

\dt[S+] [PATTERN] list tables

\dT[S+] [PATTERN] list data types

\du[S+] [PATTERN] list roles

\dv[S+] [PATTERN] list views

\dx[+] [PATTERN] list extensions

\dy [PATTERN] list event triggers

\l[+] [PATTERN] list databases

\sf[+] FUNCNAME show a function's definition

\sv[+] VIEWNAME show a view's definition

\z [PATTERN] same as \dp

Formatting

\a toggle between unaligned and aligned output mode

\C [STRING] set table title, or unset if none

\f [STRING] show or set field separator for unaligned query output

\H toggle HTML output mode (currently off)

\pset [NAME [VALUE]] set table output option

(NAME := {border|columns|expanded|fieldsep|fieldsep_zero|

tuples_only|unicode_border_linestyle|

unicode_column_linestyle|unicode_header_linestyle})

\t [on|off] show only rows (currently off)

\T [STRING] set HTML <table> tag attributes, or unset if none

\x [on|off|auto] toggle expanded output (currently off)

Connection

\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}

connect to new database (currently "postgres")

\conninfo display information about current connection

\encoding [ENCODING] show or set client encoding

\password [USERNAME] securely change the password for a user

Operating System

\cd [DIR] change the current working directory

\setenv NAME [VALUE] set or unset environment variable

\timing [on|off] toggle timing of commands (currently off)

\! [COMMAND] execute command in shell or start interactive shell

Variables

\prompt [TEXT] NAME prompt user to set internal variable

\set [NAME [VALUE]] set internal variable, or list all if no parameters

\unset NAME unset (delete) internal variable

Large Objects

\lo_export LOBOID FILE

\lo_import FILE [COMMENT]

\lo_list

\lo_unlink LOBOID large object operations

database, postgresql, tutorial

Database Administrator Tutorial

Postgres architecture diagram with explanation

How to create a database in postgresql

psql error connection to server on socket

How to change default port in postgresql

How to describe view in postgresql

User defined database creation in postgresql

Database creation in PostgreSQL

Follow below steps to create database in PostgreSQL.

psql postgresql shortcut commands.

psql / postgresql shortcut commands.

Search

Labels