Sabtu, 22 Juni 2024

PSQL-training ha

Setup PostgreSQL

https://github.com/IDN-Training/postgresql-training/tree/main/module-2-setup-postgresql

Installation

Install package postgresql

# 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

Verify package installed

apt list --installed | grep postgresql

Verify service activate

systemctl status postgresql

Verify port listen

ss -tulpn

Verify user postgres was created

cat /etc/passwd | grep postgres

Verify log postgresql database

tail -f /var/log/postgresql/postgresql-16-main.log

Verify data directory

ls /var/lib/postgresql/16/main/

Verify file configuration

ls /etc/postgresql/16/main

Access Databases

Login with user postgres

su - postgres

Connect database

psql -d postgres -U postgres

Create password user postgres

ALTER USER postgres PASSWORD 'postgres';

Access from user root

psql -h localhost -d postgres -U postgres -p 5432

Set Environtmental Variable

Locate psql command

cd /usr/lib/postgresql/16/bin/

Define env variable on bash_profile

vim $HOME/.bashrc

Set this

PATH=/usr/lib/postgresql/16/bin:$PATH
export PGDATA=/var/lib/postgresql/16/main/
export PGUSER=postgres
export PGPORT=5432
export PGDATABASE=postgres

Load profile

source $HOME/.bashrc

Verify

pg_ctl status

==================================================

PostgreSQL Administration

User Tools - Command Line Interfaces

Download sample.sql file and place on postgres directory

https://github.com/IDN-Training/postgresql-training/blob/main/sample/training_data.sql

Load dump sample.sql file

psql -f training_data.sql -d postgres -U postgres

Access database

psql

Information command

# 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;

Reload a Database Cluster

pg_ctl -D training/ -l /var/lib/postgresql/training.log reload

Stopping a Database Cluster

# 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

Security and Authentication Settings

vim /etc/postgresql/16/main/postgresql.conf

postgresql.conf

superuser_reserved_connections = 10
authentication_timeout = 10s

Write Ahead Log Settings

vim /etc/postgresql/16/main/postgresql.conf

postgresql.conf

max_wal_size = 1GB
min_wal_size = 80MB

Log Settings

vim /etc/postgresql/16/main/postgresql.conf

postgresql.conf

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)
);

Create table course

CREATE TABLE training_schema.courses (
    id_course SERIAL PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    id_instructor INTEGER REFERENCES training_schema.instructors(id_instructor)
);

Create table materials

CREATE TABLE training_schema.materials (
    id_material SERIAL PRIMARY KEY,
    material_name VARCHAR(100) NOT NULL,
    description TEXT,
    id_course INTEGER REFERENCES training_schema.courses(id_course)
);

Create table registrations

CREATE TABLE training_schema.registrations (
    id_registration SERIAL PRIMARY KEY,
    id_participant INTEGER REFERENCES training_schema.participants(id_participant),
    id_course INTEGER REFERENCES training_schema.courses(id_course),
    registration_date DATE DEFAULT CURRENT_DATE
);

Check tables

\dt

Insert data into tables participants

INSERT INTO training_schema.participants (name, email, phone) VALUES
('Budi', 'andi@gmail.com', '123456789'),
('Andi', 'budi@gmail.com', '987654321');

Insert data into tables instructors

INSERT INTO training_schema.instructors (name, email, phone, specialization) VALUES
('Rafi Riadi', 'rafi.riadi@gmail.com', '111222333', 'Data Science'),
('John Doe', 'jane.doe@gmail.com', '444555666', 'Machine Learning');

Insert data into tables courses

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

Test connection

psql -h 172.23.x.x -U admin -p 5432 -d training_db

Row Level Security

Create user non superuser

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;

Check privileges

psql -h localhost -d postgres -U andi -p 5432 -c "select * from training_schema.courses;"
psql -h localhost -d postgres -U budi -p 5432 -c "select * from training_schema.courses;"

Check status row level security on tables

SELECT schemaname, tablename, reloftype, relrowsecurity, relforcerowsecurity
FROM pg_tables
JOIN pg_class ON pg_tables.tablename = pg_class.relname
WHERE schemaname = 'training_schema' AND tablename = 'courses';

Enable row level security

ALTER TABLE training_schema.courses ENABLE ROW LEVEL SECURITY;

Create policy

CREATE POLICY participant_policy ON training_schema.courses
FOR SELECT USING (id_course = current_setting('app.current_participant')::INTEGER);

Assign policy to user

alter role andi set app.current_participant = '1';
alter role budi set app.current_participant = '2';

Verify policy

psql -h localhost -d postgres -U andi -p 5432 -c "select * from training_schema.courses;"
psql -h localhost -d postgres -U budi -p 5432 -c "select * from training_schema.courses;"

Data Encryption

Install pgcrypto

CREATE EXTENSION pgcrypto;

Create table user

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
   email TEXT NOT NULL UNIQUE,
   password TEXT NOT NULL
);

Using pgcrypto

INSERT INTO users (email, password) VALUES (
  'rafiryd@mail.com', 
  crypt('katasandi123', gen_salt('md5')) 
);

Query data

select * from users;

============================

PostgreSQL Backup and Recovery

Logical Backup and Recovery

Logical Backup

using pg_dump

# Backup selected database
pg_dump -f training_db.sql -U postgres training_db

# Backup schema
pg_dump -n training_schema -f training_schema.sql -U postgres training_db

# Backup data only
pg_dump -a --insert --disable-triggers -f training_data.sql -U postgres training_db

# Backup table
pg_dump -t training_schema.courses -f training_courses.sql -U postgres training_db

# Backup full database and compressed format
pg_dump -Ft -f training_db.tar -U postgres training_db

using pg_dumpall

# Backup full database Cluster
pg_dumpall > main_cluster.sql

Logical Recovery

Using psql

# Create database first
createdb -O admin training_db;

# Restore training_db database
psql -f training_db.sql training_db;

# Restore all databases
psql -f main_cluster.sql

Using pg_restore

# Restore training_db database
pg_restore -d training_db training_db_full.tar

Physical Backup and Recovery

Physical Backup and Recovery mode

Offline Mode

# Backup
tar -cf backup-main-cluster.tar /var/lib/postgresql/16/main

# Restore
tar -zxvf backup-main-cluster.tar

Online Backup

# Config Archiving WAL files
vim /etc/postgresql/16/main/postgresql.conf

# postgresql.conf
wal_level = replica

# Base Backup Using pg_basebackup Tool
pg_basebackup -h localhost -D backup_db

# Verify Backup
pg_verifybackup backup_db/

Archiving WAL

Create Archive Directory

Create archive directory

mkdir -p /backup/wal/main

Change ownership directory

chown -R postgres:postgres /backup/

Enable Config Archive WAL

Edit config

vim /etc/postgresql/16/main/postgresql.conf

postgresql.conf

archive_mode = on 
archive_command = 'cp %p /backup/wal/main/%f'

check archiving

ls /backup/wal/main

==================================

Monitoring and Maintenance

Monitoring and Admin Tools

pg_stat_activity

Create database

CREATE DATABASE test_pg_stat_activity;
\c test_pg_stat_activity

Create table

CREATE TABLE test_table (id SERIAL PRIMARY KEY, data TEXT);

Insert data to table

INSERT INTO test_table (data)
SELECT md5(random()::text)
FROM generate_series(1, 1000);

Monitoring activity using pg_stat_activity

SELECT pid, usename, state, query, query_start
FROM pg_stat_activity;

Create new session

psql -U postgres -d test_pg_stat_activity

Test query

BEGIN;
SELECT pg_sleep(10);

Monitoring activity using pg_stat_activity

SELECT pid, usename, state, query, query_start
FROM pg_stat_activity
WHERE state = 'active';

Kill process query

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active'
AND query = 'SELECT pg_sleep(10);';

pg_stat_statements

enable pg_stat_statements on config file

vim /etc/postgresql/16/main/postgresql.conf

postgresql.conf

shared_preload_libraries = 'pg_stat_statements'

restart and load configuration

systemctl restart postgresql

Create database

CREATE DATABASE test_pg_stat_statements;
\c test_pg_stat_statements;

enable pg_stat_statements on database

CREATE EXTENSION pg_stat_statements;

Create table

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';

check table size - before vacuum

SELECT pg_size_pretty(pg_total_relation_size('data_table'));

check performance query - before vacuum

EXPLAIN ANALYZE SELECT * FROM data_table WHERE id = 5000000;

delete 20% data on table

DELETE FROM data_table WHERE id % 5 = 0;

use vacuum on table employees

VACUUM data_table;
VACUUM FULL data_table;

check table size - after vacuum

SELECT pg_size_pretty(pg_total_relation_size('data_table')); 

=============================================

PostgreSQL High Availability

Replication

Streaming Replication Asynchronous

Create database cluster on primary node

Initialize Primary Database

initdb -D /var/lib/postgresql/primary_db

Change config connection setting

vim primary_db/postgresql.conf

postgresql.conf

listen_addresses = 'localhost'
port = 5401

Start service

pg_ctl -D /var/lib/postgresql/primary_db/ -l /var/log/postgresql/primary_db.log start

Access database

psql -p 5401

Create password postgres

ALTER USER postgres PASSWORD 'postgres';

Create user admin

create role admin login password 'password';
alter role admin superuser;

Config access control user admin

vim primary_db/pg_hba.conf

pg_hba.conf

host    all             admin             0.0.0.0/0            md5

Restart service

pg_ctl -D /var/lib/postgresql/primary_db/ -l /var/log/postgresql/primary_db.log restart

Access database

psql -U admin -d postgres -p 5401 -h localhost

Create sample data

create database test_replica;
\c test_replica;

create table

create table tb_data(id int primary key, name varchar);

insert data on table

insert into tb_data values(generate_series(1,10),'data'||generate_series(1,10));

show data on table

select * from tb_data;

Setup replication on primary node

Config Enable Replication

vim primary_db/postgresql.conf

postgresql.conf

# config wal replica
wal_level = replica

# config archive mode
archive_mode = on
archive_command = 'cp %p /backup/wal/primary/%f'

# config cluster name
cluster_name = 'psql-nama'

Create directory archiving

mkdir -p /backup/wal/primary

Create user replication

createuser --replication -P replica -p 5401

Config access control user replication

vim primary_db/pg_hba.conf

pg_hba.conf

host    replication             replica             172.23.x.x/20            md5
host    all             replica             172.23.x.x/20            md5

Restart service

pg_ctl -D /var/lib/postgresql/primary_db/ -l /var/log/postgresql/primary_db.log restart

Setup replication on replica node

Download backup database from primary to a replica server

pg_basebackup -R -h 172.23.x.x -U replica --port 5401 -D replica_db -P

Config Enable Replication

vim replica_db/postgresql.conf

postgresql.conf

# change listen and port
listen_addresses = '*' 
port = 5401

# config wal replica
wal_level = replica

# config archive mode
archive_mode = on
archive_command = 'cp %p /backup/wal/replica/%f'

# config standby
primary_conninfo = 'user=replica host=172.23.x.x password=password port=5401 application_name=psql1-nama'
hot_standby = on

# config cluster name
cluster_name = 'psql2-nama'

Create directory archiving

mkdir -p /backup/wal/replica

Start database

pg_ctl -D replica_db/ -l /var/log/postgresql/replica_db.log start

Verify Replication

Expanded display

\x

Show status replication on primary

select * from pg_stat_replication;

Show status replication on replica

select * from pg_stat_wal_receiver;

Insert data to table

insert into tb_data values(generate_series(11,20),'data'||generate_series(11,20));

show data on table

select * from tb_data;

Streaming Replication Synchronous

Setup replication on primary node

Config Enable Replication

vim primary_db/postgresql.conf

postgresql.conf

# config sync
synchronous_commit = on
synchronous_standby_names = '*'

Restart service

pg_ctl -D /var/lib/postgresql/primary_db/ -l /var/log/postgresql/primary_db.log restart

Setup replication on replica node

Config Enable Replication

vim replica_db/postgresql.conf

postgresql.conf

# config sync
synchronous_commit = on
synchronous_standby_names = '*'

Start database

pg_ctl -D /var/lib/postgresql/replica_db/ -l /var/log/postgresql/replica_db.log restart

Verify Replication

Expanded display

\x

Show status replication on primary

select * from pg_stat_replication;

Show status replication on replica

select * from pg_stat_wal_receiver;

Add data

# insert data on table 
insert into tb_data values(generate_series(21,30),'data'||generate_series(21,30));

# show data on table
select * from tb_data;

Logical Replication

Setup replication on primary node

Config Enable Replication

vim logical_primary_db/postgresql.conf

postgresql.conf

# change listen and port
listen_addresses = '*'
port = 5403

# config wal replica
wal_level = logical

Create user for replication

createuser --superuser -P dba -p 5403

Config access control user replication

vim logical_primary_db/pg_hba.conf

pg_hba.conf

host    all             dba             10.10.10.0/24            md5

Restart service

pg_ctl -D /var/lib/postgresql/logical_primary_db/ restart -l /var/log/postgresql/logical_primary_db.log

Set publication

CREATE PUBLICATION testpub FOR TABLE tb_data_1;

Setup replication on replica node

Config Enable Replication

vim logical_replica_db/postgresql.conf

postgresql.conf

# change listen and port
listen_addresses = '*'
port = 5403

# config wal replica
wal_level = logical

Start service

pg_ctl -D /var/lib/postgresql/logical_replica_db/ start -l /var/log/postgresql/logical_replica_db.log

Create subscription to connect publication

CREATE SUBSCRIPTION testsub CONNECTION ‘host=10.10.10.156 port=5402 user=dba password=password dbname=postgres’ PUBLICATION testpub;

Verify Replication

Expanded display

\x

Show status replication on primary

select * from pg_stat_replication;

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;

Config access control user replication

vim pg_hba.conf

pg_hba.conf

host    all             pgpool             10.10.10.0/24            md5

Restart service

pg_ctl -D /var/lib/pgsql/data/primary_db/ restart -l /var/log/postgresql/primary_db.log

Config pgpool.conf

vim /etc/pgpool2/pgpool.conf

pgpool.conf

backend_clustering_mode = 'streaming_replication'
listen_addresses = '*'
port = 5400

backend_hostname0 = '10.10.10.82'
backend_port0 = 5441
backend_weight0 = 0
backend_data_directory0 = '/var/lib/pgsql/data/primary_db/'
backend_application_name0 = 'psql01'

backend_hostname1 = '10.10.10.194'
backend_port1 = 5441
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/data/replica_db/'
backend_application_name1 = 'psql02'

log_statement = on
log_per_node_statement = on

sr_check_user = 'replica'
sr_check_password = 'password'

health_check_period = 10
health_check_user = 'pgpool'
health_check_password = 'password'

Add credential plain-text PostgreSQL to pool_passwd

pgpool:password

Or encrypt password using AES

# Create decrypt key
su - postgres
echo 'some string key' > ~/.pgpoolkey
chmod 600 ~/.pgpoolkey

# Encrypt password using pg_enc tool
su - postgres
pg_enc -m -k ~/.pgpoolkey -u pgpool -p
cat /etc/pgpool2/pool_passwd

Start pgpool

systemctl start pgpool2.service

Check log

journalctl -f -u pgpool2.service

Verify

psql -U pgpool --port 5400 -c "show pool_nodes;" postgres
psql -U pgpool --port 5400 -c "show pool_processes;" postgres
psql -U pgpool --port 5400 -c "show pool_health_check_stats;" postgres

Test data

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
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)