Installation over Oracle Linux

• Version of the Linux Distro which I am using ,

cat /etc/os-release
NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://github.com/oracle/oracle-linux"
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9

• Install the repository RPM,

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

• Install PostgreSQL,

sudo yum install -y postgresql13-server

Optionally initialize the database and enable automatic start

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb [TBD]
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13


initdb

• This file will create all DB related files , directories & Configuration files.

• Also creation of system catalogs --> what is this ? --> It will store all indexes , roles , etc. --> you can see list of catalogue using below statement.

postgres=# select relname from pg_catalog.pg_class where relkind='r';

sudo -i -u postgres psql

• By default a user , a role and a database will be created.
• All the above 3 will be created in same name "postgres".
• Then how to check the roles ?

postgres=# \du

• Now see the list of databases, ( these 3 are created by postgres by default with the help of template1.

postgres=# \l

• If you try to create a new DB , then it will follow the "template1", its like the blueprint.
• Then why template0 ? --> you can edit template1 but not template0 ( you can't change and its not editable ).
• so in short template0 --> NOT EDITABLE & template1 --> EDITABLE.

WAL

• WRITE AHEAD LOG.
• If the database gets crashed , with this help we can retrieve the data.

Roles

• Librarian , Library member and visitor all these are roles.

• Lets create a dummy role and test ,

• You can see "Cannot login" , that means you can't login as it's not assigned.

• creating one more ROLE,

• Assigning the permissions or roles,

• Here you can't see 'CANNOT LOGIN'.
• If a ROLE can login then he/she is the user.
• User can inherits the parents also.

How it works ?

Postmaster

• All connection request goes first here. ( whatever client ask , server will respond )
• Follows client server architecture.

WAL Writer

• It will be write in a notebook , to keep the track of all.

Checkpoint

• It periodically checks and responed.

Auto Vaccum

• It periodically checks.

Stats Collector

• Gathers all stats Eg., if a table is getting more request then it will collect the information and try to keep it in cache to respond very fast and also check whether index is there for the table.

Archiver

• To backup the data.

Reference

https://www.postgresql.org/download/linux/redhat/ --> This site will provide steps to install in different distro's.

Notes

1. "template0" is called pristine.

Questions

1. what command initialize a PostgresSQL database cluster? initdb
2. which role is automatically created during PostgresSQL installation ? postgres
3. what is the purpose of template0 in PostgresSQL ? Pristine , unmodified template.
4. which PostgresSQL template database is used as the default for creating new databases ? template1
5. how to connect PostgresSQL interactive terminal ? psql
6. List all databases ? \l
7. Architecture of PostgresSQL ? Client Server