Module2_System_architecture

Question 1

what is the first process that starts when you start postgresql?

Answer 1

postmaster and is a superviser for all other postgres processes

Question 2

what are the functions of postmaster process?

Answer 2

• it starts all other postgresql processes and restarts them if they die.
• it listens to new connections and setups up a new dedicated process per user connection

Question 3

what 3 items sit in shared memory

Answer 3

shared buffers, wal buffer and process array

Question 4

what is background writer?

Answer 4

ensure enough buffer supply available in the memory and sync from time to time dirty data blocks to data files

Question 5

what does the checkpointer?

Answer 5

checkpointer writes the buffers by default every 5 min (checkpoint timeout parameter) to data files or
when WAL is full Parameter (MAX_WAL_SIZE)

Question 6

What does the WAL Writer?

Answer 6

ensuring data wall buffers are synced with disk at commit.

Question 7

what does the logger processes do?

Answer 7

Routes log messages to syslog, eventlog or log files

Question 8

what does archiver proces do?

Answer 8

Copy the switched wal file to an archived wall destination. not started by default.

Question 9

what does autovacuum LAUNCHER process do?

Answer 9

autolauncher proces manages and starts autovacuum processes that do maintaince tasks like vacuming and analyse.

Question 10

what does stats collector proces do?

Answer 10

collect stats and store them in the DB

Question 11

what does logical replication process do?

Answer 11

Launces the processes to perform logical replication based on the data he retrieved from WAL segments.

Question 12

what does the autovacuum WORKER process

Answer 12

recover free space for reuse.

Question 13

How is shared buffer used for READING:

Answer 13

If a new user connection asks for data that is present in the shared buffer the user will get the data fast because it already lookup once before. If the query is not executed before it will be fetched from disk and loaded into shared data (slow) and results can be given to the user. The result of that query will stay in memory so the next request will be fast as well.

Question 14

How is shared buffer used for WRITING:

Answer 14

When user alters data and does a commit it will be written in the shared buffer ONLY! after a checkpoint (because of time default 5 min or when WAL is full) data will be written to disk by checkpointer proces.

Question 15

What is background writer cleaning scan

Answer 15

BGWriter performing cleaning scans to ensure there is enough supply of clean buffers.

Question 16

what is Write Ahead Logging (WAL)

Answer 16

WAL is used to track transactions. when user commits data it will be written from wal buffer inside shared memory to WAL file on disk by the WAL Writer. It will only give the user the commit succeeded after it’s written in transaction log (WAL file).

Question 17

Transaction log archiving.

Answer 17

Archiver process starts process to copy the switched WAL (PG_WAL log files) to a archiver destination for PIR. The archiver runs the ARCHIVE_COMMAND on the OS the copy the files.

Question 18

where is the altered data before commit?

Answer 18

uncommited updates are in memory

Question 19

where is the altered data after commit?

Answer 19

WAL buffers are writen to disk (wal file) and shared buffers are markerd as commited.

Question 20

where is the altered data after checkpoint?

Answer 20

altered data pages are written from shared memory to data files.

Question 21

what process acts as the listener?

Answer 21

postmaster and listens on 1 port only

Question 22

what happens after Postmaster listenens to a new connection

Answer 22

it will start a new dedicated processes voor that connection and perform authentication. after autentication is succes Postmaster will handover that new proceses to the requesting user.

Question 23

statement proces has 3 functions

Answer 23

Parsing the query, optimizing (find plan with lowest cost) and executed based on the optimized plan .

Question 24

what is a cluster?

Answer 24

collection of databases managed by one instance with a seperate data directory, TCP Port and set of processes.

Question 25

what is the default INSTALLATION directory op postgres?

Answer 25

/usr/pgsql-VERSION

has BIN, LIB en SHARE

Question 26

what is the default DATA directory op postgres?

Answer 26

/var/lib/pgsql/VERSION/data

Question 27

what is in the GLOBAL directory?

Answer 27

cluster wide database objects like Data dictionary and user information. database information

Question 28

What is in the BASE directory?

Answer 28

Data of the databases. a directory per database.

Question 29

what is in the PG_TBLSC directory?

Answer 29

Symbolink links to tablespace location.

Question 30

what is in the pg_wal directory?

Answer 30

the location of WAL files.

Question 31

what is in the LOG directory?

Answer 31

error files

Question 32

What is the pg_ident.conf file?

Answer 32

This file controls PostgreSQL user name mapping. It maps external
user names to their corresponding PostgreSQL user names. Records
are of the form:

Question 33

what is the order of loading config files when starting instance.

Answer 33

first loads the postgresql.conf and then postgresql.auto.conf on top of it.

Question 34

what are the postmaster configuation files?

Answer 34

postmaster. pid where it stores the postmaster ID

postmaster. opts where it loads the postmaster startup OPTIONS

Question 35

how many files are table or index

Answer 35

every table or index has its own datafile and gets default 1GB chunk.

Question 36

what is a tablespace

Answer 36

is a directory.

Question 37

how can i find the file on disk that matches my table

Answer 37

pg_class.relfilenode
select pg_relation_filepath ('TABLE_NAME');

Question 38

what are 5 things in a page?

Answer 38

page header: General info about page,pointers to free space,24 bytes long
row/index pointers: 4 bytes per item, array of offset/lengt pointing to the actual rows/index entries
free space: unallocated free space
row/index entry: actual data or index entry
special: index access method specific data (empty in ordinary tables)