# Postgresql Recipes

## Cài đặt
#### HĐH Ubuntu/Linux
Offical URL: https://www.postgresql.org/download/linux/ubuntu/

# 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 PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
$ sudo apt-get -y install postgresql-12 postgresql-client-12 postgresql-contrib-12

#### HĐH Window / MacOS
Download version tương ứng tại https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

## Đăng nhập/Đăng xuất
#### Đăng nhập - Sử dụng Ubuntu-terminal

$ sudo -i -u postgres
$ psql
hoặc
$ sudo -i -u postgres psql

#### Đăng xuất - Sử dụng Ubuntu-terminal

$ \q
hoặc Ctrl + D

## Thao tác với Databases
### Show Databases:

=# \l

### Create Database:

=# CREATE DATABASE db_name;

### Delete Database:

=# DROP DATABASE IF EXISTS db_name;

### Chọn Database để thao tác:

=# \c db_name;

### Tạo mới User, set password & gán quyền SUPERUSER cho user:

=# CREATE USER db_user;
=# ALTER USER db_user with encrypted password 'password';
hoặc
=# CREATE USER db_user WITH PASSWORD 'password';

### Show Users List:

=# SELECT usename FROM pg_user;

### Gán Users thành Superuser:

=# ALTER USER db_user WITH SUPERUSER;

*Note: bước này gán db_user thành Superuser, thường sử dụng khi user cần quyền tạo extension trên hệ thống hoặc một số tác vụ khác...*
### Delete User:

=# DROP USER db_user;

### Gán ALL PRIVILEGES cho User theo Database:

=# GRANT ALL PRIVILEGES ON DATABASE db_name TO db_user;

### Drop all database trong schema

=# DROP SCHEMA public CASCADE;
=# CREATE SCHEMA public;
=# GRANT ALL ON SCHEMA public TO postgres;
=# GRANT ALL ON SCHEMA public TO public;

## Xử lý các case đặc biệt
### Ngắt kết nối từ bên ngoài vào database

=# REVOKE CONNECT ON DATABASE target_db FROM public;

### Đổi tên database trong psql
1. Ngắt kết nối từ bên ngoài vào database
2. Execute command

=# ALTER DATABASE target_db RENAME TO new_name;

### Xử lý khi gặp "ERROR: duplicate key violates unique constraint"
Note: trường hợp này gặp khi key_sequence của table trong postgres bị trùng với dữ liệu đã có
Ví dụ:
- key_sequence sinh ra để khi insert 1 row data, key của row đó sẽ bằng key_sequence, sau đó key_sequence++
- tuy nhiên bằng cách nào đó (developer cố ý gán giá trị cho key hay import data từ excel), có 1 row trong table đã có giá trị key bằng giá trị hiện tại của key_sequence => báo lỗi **"ERROR: duplicate key violates unique constraint"**

=# SELECT MAX(primary_key) FROM target_table;
=# SELECT nextval('primary_key_sequence'); # check value primary_key_sequence
cuối cùng:
=# SELECT setval('primary_key_sequence', (SELECT MAX(primary_key) FROM target_table)+1);

eg:

=# SELECT setval('shop_id_seq', (SELECT MAX(id) FROM shop)+1) from shop;

*Hiểu đơn giản cách này set key_sequence = (max_value của primary_key) + 1*
### DROP DATABASE khi gặp "ERROR: database "sms_test" is being accessed by other users"
1. Ngắt kết nối từ bên ngoài vào database
2. Run command

=# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'target_db';

### Restore file .sql
**Trỏ đến path /usr/bin/psql rồi link đến file là xong**
> Note: với trường hợp server là CentOS
cần export file với định dạng .sql rồi upload file đó lên server
> Change owner file sang postgres
> Tạo database và user, password đăng nhập giống hệt file đã export
> sudo sang root chạy câu lệnh psql là xong:

$ sudo chown -R postgres. /home/longdh/sp_test.sql;
$ sudo -u postgres psql sms_test < '/home/longdh/sp_test.sql';

### Path trỏ tới pg_dump trên MacOS

/Library/PostgreSQL/10/bin/pg_dump

### Tìm path của pg_dump trên centos 7

$ find / -name pg_dump -type f 2>/dev/null

### Restart Postgres trên MacOS

$ sudo su postgres -c "/Library/PostgreSQL/12/bin/pg_ctl restart -D /Library/PostgreSQL/12/data"

### Gặp lỗi Error: pg_config executable not found khi cài pip
Lỗi này do thiếu file pg_config ở trong máy chủ Ubuntu/Debian

$ sudo apt-get install libpq-dev

## Duplicate a Postgres database trên localhost
1. KILL ALL EXISTING CONNECTION FROM ORIGINAL DB (SOURCE_DB)

=# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'SOURCE_DB' AND pid <> pg_backend_pid();

2. CLONE DATABASE TO NEW ONE(TARGET_DB)

=# CREATE DATABASE TARGET_DB WITH TEMPLATE SOURCE_DB OWNER USER_DB;

## Uninstall - Postgres trên Linux
1. Check list postgres đang tồn tại trên hệ thống

$ dpkg -l | grep postgres

2. Xóa bọn nó bằng lệnh purge remove

$ sudo apt-get --purge remove pgdg-keyring postgresql-12 postgresql-client-12 postgresql-client-common postgresql-common

## Postgres với Docker
Một cách sử dụng Postgresql thuận tiện hơn và không phải cài đặt phức tạp là thông qua Docker, ở đây là guide hướng dẫn start một docker container
Vấn đề:
* Cần cấu hình để có thể truy cập postgres trong docker container từ máy local (do mặc định khi khởi chạy postgres chỉ cho kết nối từ trong container)
* Cần chỉ định port để truy cập đến postgres (mapping local port với container port)
* Cần lưu lại data tại máy local để khi container khởi động lại không bị mất dữ liệu
1. Pull images

$ docker pull postgres

Check lại bằng **docker images**
2. Tạo file postgresql.conf và thêm dòng **listen_addresses = '*'**

$ echo "listen_addresses = '*'" > postgresql.conf

3. Khởi chạy container

$ docker run --name local-postgres -p 5434:5432 -v "$PWD/postgresql.conf":/etc/postgresql/postgresql.conf -v "$PWD/data":/var/lib/postgresql/data -e POSTGRES_PASSWORD=secret123 postgres

Trong đó:
* **-p 5434:5432** mapping local_port:5434 ~ container_port:5432
* **-v "$PWD/postgresql.conf":/etc/postgresql/postgresql.conf** sử dụng file postgresql.conf đã tạo ở bước 2 để làm file config của container
* **-v "$PWD/data":/var/lib/postgresql/data** mapping thư mục data ở local với thư mục data của container => data được lưu ở local
4. Check lại connect trên local với
DB_NAME = postgres
DB_USER = postgres
DB_PASSWORD = secret123
DB_HOST = localhost
DB_PORT = 5434

## Postgres với Docker-compose
Bao gồm 2 file và .docker sẽ để docker tự tạo
.
├── .docker
├── .env
├── docker-compose.yaml
└── postgresql.conf

docker-compose.yaml

version: "3.4"

services:
  db:
    image: postgres:13.1
    volumes:
      - $PWD/postgresql.conf:/etc/postgresql/postgresql.conf
      - $PWD/.docker/db/data:/var/lib/postgresql/data
    expose:
      - "5432"
    environment:
      POSTGRES_DB: ${DB_NAME}
      POSTGRES_USER: ${DB_USER}
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    ports:
      - "${DB_MAPPING_PORT}:5432"
    restart: unless-stopped

postgresql.conf

listen_addresses = '*'

.env

DB_NAME = your_db
DB_USER = your_db
DB_PASSWORD = your_db
DB_MAPPING_PORT = 5432