Install Postgresql-16

1. Đặc điểm chính

• Mã nguồn mở và miễn phí, nhưng có chất lượng tương đương hệ CSDL thương mại.
• Tuân thủ chuẩn SQL cao hơn nhiều hệ khác.
• Hỗ trợ ACID đầy đủ (Atomicity, Consistency, Isolation, Durability).
• Kiến trúc MVCC (Multiversion Concurrency Control) cho hiệu năng giao dịch cao, hạn chế lock.
• Mạnh về tính toàn vẹn dữ liệu (constraints, foreign keys, triggers…).

2. Tính năng nổi bật

• Hỗ trợ mở rộng: cho phép viết function bằng nhiều ngôn ngữ (PL/pgSQL, Python, JS…).
• Index đa dạng: B-tree, Hash, GiST, SP-GiST, GIN, BRIN.
• Hỗ trợ JSON/JSONB mạnh mẽ → dùng như database quan hệ + NoSQL.
• Replication:
  – Streaming replication (physical)
  – Logical replication
  – Partitioning hỗ trợ tốt
  – Full-text search tích hợp sẵn.
  – Hỗ trợ stored procedures, triggers, views, materialized views.

3. Khi nào nên dùng PostgreSQL

• Ứng dụng cần dữ liệu mạnh, xuyên suốt, đáng tin cậy
• Data analytics, GIS (PostGIS)
• API backend, microservices
• Hệ thống nghiệp vụ lớn cần transaction chính xác
• Hệ thống cần xử lý JSON lớn và phức tạp

4. Điểm mạnh

• Ổn định, hiệu năng cao, dễ mở rộng.
• Tính năng SQL rất đầy đủ.
• Tốt cho cả mô hình quan hệ và bán cấu trúc (JSON).
• Cộng đồng mạnh, nhiều extension chất lượng.
• Khả năng tùy biến và lập trình bên trong CSDL cao.

5. Hạn chế

• Cấu hình ban đầu mặc định khá an toàn nhưng chưa tối ưu hiệu năng.
• Việc scale-out không đơn giản bằng một số NoSQL.
• Logical sharding cần thêm công cụ (Citus, YugabyteDB…).

6. Cài đặt

echo "vm.nr_hugepages = 2048" >> /etc/sysctl.conf
sysctl -p

#!/bin/bash -ex
SETUP_DIR=/opt/setup
PACKAGE=postgresql-16.1
addgroup postgres
adduser --home /home/postgres --uid 5000 --gid 5000 --disabled-password postgres
apt install build-essential flex bison libreadline6-dev zlib1g-dev libossp-uuid-dev uuid pkg-config libicu-dev
cd $SETUP_DIR
if [ ! -f ${SETUP_DIR}/$PACKAGE.tar.bz2 ]
then
	wget https://ftp.postgresql.org/pub/source/v16.1/$PACKAGE.tar.bz2
fi
tar -xjvf $PACKAGE.tar.bz2
cd $SETUP_DIR/$PACKAGE
cat <<EOF >runconfigure.sh
#!/bin/bash
set -e
set -u
set -o pipefail
./configure \
    --prefix=/opt/postgres \
    --with-uuid=ossp
EOF

chmod +x runconfigure.sh
./runconfigure.sh
make -j 8
make install

cd $SETUP_DIR/$PACKAGE
cat <<EOF | sudo tee runcontrib.sh
#!/bin/bash
set -u
set -e
set -o pipefail
export BASEDIR=$SETUP_DIR/$PACKAGE/contrib
build_contrib() {
    cd \$BASEDIR/\$1
    make
    make install
}
# contrib modules
build_contrib dblink
build_contrib uuid-ossp
build_contrib pageinspect
build_contrib pg_buffercache
build_contrib pg_freespacemap
build_contrib pg_prewarm
build_contrib pgrowlocks
build_contrib pg_stat_statements
build_contrib pgstattuple
# contrib binaries 
build_contrib oid2name
EOF
chmod +x runcontrib.sh 

runcontrib.sh
mkdir -p /opt/postgres/{pgdata,pgscript,pglog}
chown -R postgres:postgres /opt/postgres
su - postgres
cd /opt/postgres/bin
./initdb --pgdata=/opt/postgres/pgdata --encoding=UTF8 --no-locale
# pg_ctl -D /opt/postgres/pgdata -l logfile start

cd /opt/postgres/pgdata
sed -i '/#listen_addresses /a listen_addresses='2.2.2.77'' postgresql.conf
sed -i '/#shared_buffers /a shared_buffers = 8GB' postgresql.conf
sed -i '/#temp_buffers /a temp_buffers = 80MB' postgresql.conf
sed -i '/#work_mem /a work_mem = 10MB'  postgresql.conf
sed -i '/dynamic_shared_memory_type/#dynamic_shared_memory_type'  postgresql.conf
sed -i '/#dynamic_shared_memory_type /a dynamic_shared_memory_type=mmap'  postgresql.conf
sed -i '/#maintenance_work_mem /a maintenance_work_mem = 2GB' postgresql.conf
sed -i '/#shared_preload_libraries = '' /a shared_preload_libraries = 'pg_stat_statements'' postgresql.conf
sed -i '/#wal_buffers /a wal_buffers = 16MB' postgresql.conf
sed -i '/#max_wal_size /a max_wal_size = 3GB' postgresql.conf
sed -i '/#random_page_cost /a random_page_cost = 1.0' postgresql.conf
sed -i '/#effective_cache_size /a effective_cache_size = 16GB'  postgresql.conf
sed -i '/#logging_collector /a logging_collector = on' postgresql.conf
sed -i '/#log_truncate_on_rotation /a log_truncate_on_rotation = on' postgresql.conf
sed -i '/#log_rotation_size /a log_rotation_size = 100' postgresql.conf
sed -i '/#log_min_messages /a log_min_messages = debug1' postgresql.conf
sed -i '/#log_directory /a log_directory = '/opt/postgres/pglog'' postgresql.conf
sed -i '/#log_filename /a log_filename = 'postgresql-%Y-%m-%d_%H.log'' postgresql.conf
sed -i '/#log_min_duration_statement /a log_min_duration_statement = 2000'
sed -i '/#autovacuum_vacuum_cost_limit /a autovacuum_vacuum_cost_limit = 2000' postgresql.conf
sed -i '/#idle_in_transaction_session_timeout /a idle_in_transaction_session_timeout = 10000' postgresql.conf
exit
cat <<EOF | sudo tee /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL 16.1
# This unit can only run after the network is up and running
# (that is, the network target has run)
After=network.target

[Service]
# PostgreSQL is a traditional UNIX daemon that forks a child,
# and the initial process exits
Type=forking
# Wait 120 seconds on startup and shutdown to consider the process
# correctly started up or shut down.
TimeoutSec=120
# The UNIX user and group to execute PostgreSQL as
User=postgres
Group=postgres

# Set the PGROOT environmental variable for PostgreSQL
Environment=PGROOT=/opt/postgres

# If StandardOutput= or StandardError= are set to syslog, journal or kmsg,
# prefix log lines with "postgres"
SyslogIdentifier=postgres

# Let systemd know where PostgreSQL keeps its pid file
PIDFile=/opt/postgres/pgdata/postmaster.pid

# Command used to start PostgreSQL
ExecStart=/opt/postgres/bin/pg_ctl -s -D \${PGROOT}/pgdata start -w -t 120
# Command used to reload PostgreSQL
ExecReload=/opt/postgres/bin/pg_ctl -s -D \${PGROOT}/pgdata reload
# Command used to stop PostgreSQL
ExecStop=/opt/postgres/bin/pg_ctl -s -D \${PGROOT}/pgdata stop -m fast

# Use the lowest allowable setting for the OOM killer; this should
# actually disable the OOM killer for PostgreSQL
OOMScoreAdjust=-1000

[Install]
# This unit is part of target multi-user
WantedBy=multi-user.target
EOF

cat EOF<< sudo tee /home/postgres/enable-extensions.sql
create extension "uuid-ossp";
create extension pg_buffercache;
create extension pg_stat_statements;
create extension pgrowlocks;
create extension pgstattuple;
create extension pg_freespacemap;
create extension pg_prewarm;
create extension pageinspect;
EOF
su - postgres
/opt/postgres/bin/psql \
    -d template1 \
    -f /home/postgres/enable-extensions.sql

host replication replicator 2.2.2.45/32 trust
host replication replicator 2.2.2.46/32 trust
#host replication replicator 2.2.2.44/32 trust
host replication replicador all md5
host replication replicador 0.0.0.0/0 md5
host all postgres 127.0.0.1/32 trust
host all postgres 2.2.2.45/32 md5
host all postgres 2.2.2.46/32 md5
#host all postgres 2.2.2.35/32 md5
host all postgres 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5