# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Update the package lists:
sudo apt update
# Install the latest version of PostgreSQL:
# If you want a specific version, use 'postgresql-16' or similar instead of 'postgresql'
sudo apt -y install postgresql
# List all databases
\l
# List all schemas
\dn
# Use schema
set search_path to training_schema;
# List all table
\dt
# Describe course table
\d participants
# Describe the course table including description
\d+ participants
# List all tablespaces
\db
# Execute SQL Statement
select * from participants;
# Execute SQL Statement, to save output to a file
\o participants_data.txt
SELECT * FROM participants;
\o
# history
\s
# show current connection
\conninfo
# show port
show port;
# show data directory
show data_directory;
Database Clusters
Login postgres user
su postgres
Create a Database Cluster
initdb -D training
Change port listen on Database Clusters Config
vim training/postgresql.conf
postgresql.conf
port = 5433
Starting a Database Cluster
# start database cluster
pg_ctl -D training/ -l /var/log/postgresql/training.log start
# check status database cluster
pg_ctl -D training/ status
Connecting To a Database Cluster
# access database cluster
psql -p 5433
# show current connection
\conninfo
# show port
show port;
# show data directory
show data_directory;
# stop database cluster
pg_ctl -D training/ stop
# check status database cluster
pg_ctl -D training/ status
View Cluster Control Information
pg_controldata training/
Configuration
View and Change Server Parameters
show all;
show port;
show work_mem;
show data_directory;
show config_file;
set work_mem = 8096;
alter system set work_mem=8192;
select pg_reload_conf();
Connection Settings
vim /etc/postgresql/16/main/postgresql.conf
postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 200
log_directory = 'log'
log_min_duration_statement = 5000
log_connections = on
Creating and Managing Databases
Databases
Check database
\l
Create database
create database training;
Alter database
ALTER DATABASE training
RENAME TO training_db;
Copy database
create database training_db_new with template training_db;
Using database
\c training_db;
Drop database
drop database training_db_new;
Users and Roles
Create role
create role admin login password 'password';
Alter role
alter role admin superuser;
Check role
\du
Change owner database
alter database training_db owner to admin
Connecting database use new user
psql -h localhost -U admin -p 5432 -d training_db
Tablespace
Check tablespace
\db
Create directory for tablespace
mkdir /data
chown postgres:postgres /data
Connecting database use new user
psql -h localhost -U admin -p 5432 -d training_db
Create tablespace
create tablespace data location '/data';
Using tablespace
create database training_data tablespace data;
Drop tablespace
drop tablespace data;
Databases Schema
Create schema
CREATE SCHEMA training_schema;
Check schema
\dn
Check current schema
SELECT current_schema();
Show search path
SHOW search_path;
Add new schema to search path
set search_path to training_schema;
Table
Create table participants
CREATE TABLE training_schema.participants (
id_participant SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15)
);
Create table instructors
CREATE TABLE training_schema.instructors (
id_instructor SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
specialization VARCHAR(100)
);
INSERT INTO training_schema.courses (course_name, description, start_date, end_date, id_instructor) VALUES
('Data Science 101', 'Introduction to Data Science', '2024-07-01', '2024-07-15', 1),
('Machine Learning Basics', 'Fundamentals of Machine Learning', '2024-08-01', '2024-08-10', 2);
Insert data into tables materials
INSERT INTO training_schema.materials (material_name, description, id_course) VALUES
('Introduction Slides', 'Slides for introduction', 1),
('Machine Learning Overview', 'Overview of Machine Learning', 2);
Insert data into tables registrations
INSERT INTO training_schema.registrations (id_participant, id_course) VALUES
(1, 1),
(2, 2);
Query data from tables participants
SELECT * FROM training_schema.participants;
Query data from tables instructors
SELECT * FROM training_schema.instructors;
Query data from tables courses
SELECT * FROM training_schema.courses;
Query data from tables materials
SELECT * FROM training_schema.materials;
Query data from tables registrations
SELECT * FROM training_schema.registrations;
Data
Update data on tables
UPDATE training_schema.participants
SET phone = '1122334455'
WHERE name = 'Budi';
UPDATE training_schema.instructors
SET specialization = 'Deep Learning'
WHERE name = 'Jhon Doe';
Delete data
DELETE FROM training_schema.registrations
WHERE id_participant = 2 AND id_course = 2;
Create View
CREATE VIEW training_schema.participant_courses AS
SELECT
p.name AS participant_name,
c.course_name,
r.registration_date
FROM
training_schema.registrations r
JOIN training_schema.participants p ON r.id_participant = p.id_participant
JOIN training_schema.courses c ON r.id_course = c.id_course;
Select view
SELECT * FROM training_schema.participant_courses;
Database Security
Host Based Access Control
show hba file and config
show hba_file;
select rule_number,type,database,user_name,address,netmask,auth_method;
config file hba to allow user admin remote access
vim /etc/postgresql/16/main/pg_hba.conf
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
host training_db admin 0.0.0.0/0 md5
create user andi with password 'andi123';
create user budi with password 'budi123';
grant privileges to user
grant connect on database postgres to andi, budi;
grant usage on schema training_schema to andi, budi;
grant select on all tables in schema training_schema to andi, budi;
CREATE TABLE test_table (id SERIAL PRIMARY KEY, data TEXT);
Insert data into table
INSERT INTO test_table (data)
SELECT md5(random()::text)
FROM generate_series(1, 1000);
execution query
SELECT * FROM test_table WHERE id = 1;
SELECT COUNT(*) FROM test_table;
UPDATE test_table SET data = 'updated' WHERE id = 1;
DELETE FROM test_table WHERE id = 2;
execution query more complex
SELECT * FROM test_table WHERE data LIKE 'a%';
SELECT id, COUNT(*) FROM test_table GROUP BY id;
analytics statistic query use pg_stat_statements
SELECT * FROM pg_stat_statements;
show most frequent running queries
SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;
show most time consuming queries
SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
Dashboard postgresql with pgAdmin
Setup the repository
# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Install pgAdmin
sudo apt install pgadmin4
Setup webserver
sudo /usr/pgadmin4/bin/setup-web.sh
Access from browser
http://172.23.x.x/pgadmin4
Maintenance
Vacuum
Create database test_vacuum
CREATE DATABASE test_vacuum;
\c test_vacuum
Create table
CREATE TABLE data_table (id SERIAL PRIMARY KEY, data TEXT);
Insert data into table 10.000.000 line
INSERT INTO data_table (data)
SELECT md5(random()::text)
FROM generate_series(1, 10000000);
Show statistic table
SELECT relname AS table_name,
n_live_tup AS live_tuples,
n_dead_tup AS dead_tuples,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'data_table';
Shows the status of subscription when using logical replication
pg_stat_subscription
Show status replication on replica
select * from pg_stat_wal_receiver;
Add data
# insert data on table
insert into tb_data_1 values(generate_series(11,20),'data'||generate_series(11,20));
# show data on table
select * from tb_data_1;
Loadbalancing
Install pgpool-ll
# Create the file repository configuration
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update the package lists
sudo apt-get update
# Install the latest version of Pgpool-II. Specify the specific version of PostgreSQL which you are using, e.g. postgresql-16-pgpool2
sudo apt-get -y install pgpool2 libpgpool2 postgresql-16-pgpool2
Create user to monitor health check
CREATE ROLE pgpool WITH LOGIN password 'password';
GRANT pg_monitor TO pgpool;
insert into tb_data_1 values(generate_series(21,30),'data'||generate_series(21,30));
update tb_data_1 set name = 'test2' where id = 11;
delete from tb_data_1 where id < 20;
select * from tb_data_1
