postgres
Client Applicaiton/Process connects to Postmaster processpostmaster after authentication starts new postgres process and connects client app.
Any request for data first is checked in the shared buffer if not found it fetches the data from datafile.and buffers it for future use.
On updated any records, shared buffers / WAL buffers are manipulated first, then on commit WAL process writes and flushes all WAL records to WALFiles, if archiving is enabled , these will be written to archive log filesPeriodically background writer process checks the shared buffer for dirty pages and writes them to datafiles
Postmaster : when instance start this is the first process, at startup it does recovery initialized share memory, run background processes. Also creates backend process when there is connection request from the client process.
checkpointer : on checkpoint dirty buffer is written to datafile
writer : Writes dirty buffer to datafile
wal writer : writes WAL Buffer to WAL file
archiver : WAL files are copied to archive directory
stats collector : pg_stat_activity and table usage statistics information pg_stat_all_tables are collected.
logger : writes error message to log file
Autovaccuum launcher : carries vacuum operation on bloated tables, i.e release un-used space in the pages.
pga_hba.confThis is the Client Authentication Configuration File.It controls, hosts // which are allowed to connectclients // how to authenticateuser // which user can accesss which database.
Allow remote connectionshost all all 192.168.1.31/24 md5
#show config_file/var/lib/pgsql/12/data/postgressql.conf
#select name,setting from pg_settings;
#select distinct context from pg_settings
Context ------------------postmaster // restart required.superuser-backenduserinternalbackendsighupsuperuser
Password File .pgpass in the postgres user home directory contains password use during connection
$psql -U postgres // If password is found in home directory i.e ~/.pgpass it does not prompt for password.
Logical structure which maps on to physical location on disk
Every instance has two tablespaces1) pg_default tablespace : user data2) pg_global tablespace global data.
Search Path#show search_path"$user", public
#set search_path = "$user"
#revoce create on schema public from public;
pg_dump --helppg_dumpall --help
#pg_dump -v db1 > /backup/db1.sql#pg_dump -v -U postgres -W -F t db1 > /backup/db1.tar //with user postgres prompt for password format is tar file.
#pg_dumpall > /backup/alldb.sql
#pg_dump -d db1 -t table1 > /backup/t1.dmp
pg_restore#createdb -T template db1plsql db1 < /backup/db1.sql
Any request for data first is checked in the shared buffer if not found it fetches the data from datafile.and buffers it for future use.
On updated any records, shared buffers / WAL buffers are manipulated first, then on commit WAL process writes and flushes all WAL records to WALFiles, if archiving is enabled , these will be written to archive log filesPeriodically background writer process checks the shared buffer for dirty pages and writes them to datafiles
Postmaster : when instance start this is the first process, at startup it does recovery initialized share memory, run background processes. Also creates backend process when there is connection request from the client process.
checkpointer : on checkpoint dirty buffer is written to datafile
writer : Writes dirty buffer to datafile
wal writer : writes WAL Buffer to WAL file
archiver : WAL files are copied to archive directory
stats collector : pg_stat_activity and table usage statistics information pg_stat_all_tables are collected.
logger : writes error message to log file
Autovaccuum launcher : carries vacuum operation on bloated tables, i.e release un-used space in the pages.
Host based authentication
pga_hba.confThis is the Client Authentication Configuration File.It controls, hosts // which are allowed to connectclients // how to authenticateuser // which user can accesss which database.Host based authentication
Allow remote connectionshost all all 192.168.1.31/24 md5
Main Config File
Main Config File
postgressql.conf
postgressql.conf
#show config_file/var/lib/pgsql/12/data/postgressql.conf
#select name,setting from pg_settings;
#select distinct context from pg_settings
Context ------------------postmaster // restart required.superuser-backenduserinternalbackendsighupsuperuser
postgres password file.
Password File .pgpass in the postgres user home directory contains password use during connectionpostgres password file.
$psql -U postgres // If password is found in home directory i.e ~/.pgpass it does not prompt for password.
Tablespaces.
Logical structure which maps on to physical location on diskTablespaces.
Every instance has two tablespaces1) pg_default tablespace : user data2) pg_global tablespace global data.
Search Path#show search_path"$user", public
#set search_path = "$user"
#revoce create on schema public from public;
Backup / restore.
pg_dump --helppg_dumpall --helpBackup / restore.
#pg_dump -v db1 > /backup/db1.sql#pg_dump -v -U postgres -W -F t db1 > /backup/db1.tar //with user postgres prompt for password format is tar file.
#pg_dumpall > /backup/alldb.sql
#pg_dump -d db1 -t table1 > /backup/t1.dmp
pg_restore#createdb -T template db1plsql db1 < /backup/db1.sql