PostgreSQL Tutorial

#PG Install & Uninstall

#PG Install
sudo apt update
sudo apt install postgresql postgresql-contrib
#sudo apt install postgresql-14 postgresql-contrib-14

#Pg Uninstall
#sudo apt remove --purge postgresql postgresql-contrib
#sudo apt autoremove

#PG Info:

psql -V
/usr/lib/postgresql/15/bin/postgres -V
SELECT version();
pg_config --version
postgres --version
SHOW SERVER_VERSION;

#PG Service Management:

sudo systemctl enable postgresql
#sudo systemctl disable postgresql

sudo systemctl status postgresql
sudo systemctl start postgresql
sudo systemctl stop postgresql
sudo systemctl restart postgresql

#PG DB DATA location:

/var/lib/postgresql/<version>/main [Linux]
C:\Program Files\PostgreSQL\<version>\data [Win]

Postgresql Configuration: Most Important [2] Files:

Debian: /etc/postgresql/<version>/main/postgresql.conf
RHEL: /var/lib/pgsql/<version>/data/postgresql.conf
Windows: C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

-1 postgresql.conf:
-2 pg_hba.conf:
>>systemctl restart postgresql
>>ss -tulpan | grep 5432

#1 .postgresql.conf
Control of performance, connection settings, security, logging, storage management, etc.

#2 .pg_hba.conf [Host-Based Authentication]
The user determines the IP address and authentication methods.

Getting Started with PostgreSQL:

#Connecting Postgresql:

getent passwd postgres
sudo su postgres
sudo -u postgres psql
psql
#psql -h 192.168.5.50 -p 5432 -d Devs -U postgres [Remote Connections]

#Commands:

#List Roles:
\du
select rolname from pg_roles;

#List Schema:
\dn

#Create and Delete Role:
CREATE ROLE new_role_name; [Create Role]
DROP ROLE role_name; [Delete Role]

#Assigning Roles to User:
GRANT DB_admins TO faruk;

#Create & Delete a User Role:
CREATE USER: creates a user with login authority by default.
CREATE ROLE: creates a nologin user.

#PostgreSQL Authorization Management:

PG access privileges are used to determine which operations users and roles can perform on specific database objects.

-SELECT: Permission to read data in a table or view.
-INSERT: Permission to add new data to a table.
-UPDATE: Permission to change table data.
-DELETE: Permission to delete data from a table.
-TRUNCATE: Permission to quickly empty a table.
-REFERENCES: Permission to use foreign keys.
-TRIGGER: Permission to create triggers in a table.
-CREATE: Permission to create a new schema or table.
-CONNECT: Permission to connect to a database.
-EXECUTE: Permission to run functions.
-TEMPORARY: Permission to create temporary tables.
-USAGE: Permission to access schemas or data types.
-ALL PRIVILEGES: Covers all privileges, provides full access.

#Privileges:
*GRANT ALL PRIVILEGES ON DATABASE Devs TO faruk;
*REVOKE ALL PRIVILEGES ON DATABASE Devs FROM faruk;
*----------

#User Attributes:

LOGIN
SUPERUSER
CREATEDB
CREATEROLE
REPLICATION LOGIN
PASSWORD
INHERIT

#VACUUM:

SHOW autovacuum;

#VACUUM; [Normal]
#VACUUM FULL; [Full]

#Index:

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

#Clustering, Replication, HA:

Database Cluster: One or more databases on the same PostgreSQL server.
Redundant Cluster: Structure where multiple servers work together.

#PG Replication Management:
-Logical Replication
-Streaming Replication
-Failover
-Load Balancing
-Automatic Switchover
-Cascade Replication
-Patroni
-Barman
-PgLoader
-Repmanager
-Pgpool
-PAF
-PgBouncer
-PgPool-II
-Postgresql Citus
-Postgres-XL
-Bucardo
---------

#Locking:

-----------

#Tuning:

https://pgtune.sainth.de
https://pgtune.leopard.in.ua
https://postgresqlco.nf

#Monitoring

-ELK
-Pgwatch
-Pg_activity
-Zabbix
-PostgreSQL AWR
-Grafana

#Backup and Restore:

*Explained in a different article.

#PostgreSQL WAL: (Write-Ahead Logging)

/var/lib/postgresql/<version>/main/pg_wal

#REPORTING AND LOGGING:

/var/log/postgresql/
sudo tail -f /var/log/postgresql/postgresql-*.log

#Required Downloads:

https://www.pgadmin.org/download
https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
https://sourceforge.net/projects/dvd-rental/files/DVDRentalSource.zip/download

#Docs:

https://tubitak-bilgem-yte.github.io/pg-yonetici
https://berkanyiildirim.github.io/pg-yonetici
https://cheatsheets.zip/postgres
https://quickref.me/postgres
https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546
https://gist.github.com/apolloclark/ea5466d5929e63043dcf