Master PostgreSQL: Complete Introduction and Step‑by‑Step Installation Guide

PostgreSQL Introduction

PostgreSQL is a powerful open‑source object‑relational database system that extends SQL, offers strong reliability, ACID compliance, extensive data‑type support, advanced indexing, replication, and a vibrant extension ecosystem.

Why Choose PostgreSQL

Rich data types (primitive, structured, JSON/JSONB, geometric, custom)

Robust integrity constraints (UNIQUE, NOT NULL, primary/foreign keys, exclusion constraints)

High performance concurrency (MVCC, advanced indexes, parallel query, partitioning)

Reliable disaster recovery (WAL, asynchronous/synchronous/logic replication, PITR)

Strong security (GSSAPI, LDAP, SCRAM‑SHA‑256, row‑level security)

Extensible with PL/pgSQL, PL/Python, PL/Perl, PL/Java, and many third‑party extensions such as PostGIS

Internationalization and full‑text search support

PostgreSQL Installation

2.1 Host Initialization

2.1.1 Set Network Interface Name

For Rocky Linux 9/10, AlmaLinux 9/10, CentOS Stream 9/10, AnolisOS 23, OpenCloudOS 9 and similar systems, create a systemd link file to rename the primary NIC to eth0:

# mkdir -p /etc/systemd/network/
# touch /etc/systemd/network/70-eth0.link
# cat > /etc/systemd/network/70-eth0.link <<EOF
[Match]
MACAddress=00:0c:29:f8:60:8f
[Link]
Name=eth0
EOF

2.1.2 Modify NetworkManager Configuration

If NetworkManager manages the network, rename the connection profile and replace the interface name:

# mv /etc/NetworkManager/system-connections/ens160.nmconnection /etc/NetworkManager/system-connections/eth0.nmconnection
# sed -i.bak 's/ens160/eth0/' /etc/NetworkManager/system-connections/eth0.nmconnection

2.1.3 Configure Repository Mirrors

Replace the default mirror with a faster domestic source (example for Rocky Linux):

MIRROR=mirrors.aliyun.com
sed -i.bak -e 's|^mirrorlist=|#mirrorlist=|g' \
    -e 's|^#baseurl=http://dl.rockylinux.org/|baseurl=https://'${MIRROR}'/rockylinux|g' \
    /etc/yum.repos.d/[Rr]ocky*.repo

dnf clean all && dnf makecache

2.1.4 Disable Firewall

# systemctl disable --now firewalld

2.1.5 Disable SELinux

# setenforce 0
# sed -i 's/^SELINUX=.*/SELINUX=disabled/' /etc/selinux/config

2.1.6 Disable AppArmor (openSUSE)

# systemctl disable --now apparmor

2.1.7 Set Timezone

# timedatectl set-timezone Asia/Shanghai
# echo 'Asia/Shanghai' > /etc/timezone

2.1.8 Kernel Parameter Optimization

# cat > /etc/sysctl.conf <<EOF
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
EOF

sysctl -p
EOF

2.1.9 Resource Limits

# cat > /etc/security/limits.conf <<EOF
* - nofile 100000
* - nproc 100000
* - memlock 60000
EOF

2.2 Package Installation

2.2.1 Install from Distribution Repositories

On RHEL‑compatible systems (Rocky, AlmaLinux, CentOS, etc.):

# yum -y install postgresql-server
# postgresql-setup --initdb
# systemctl enable --now postgresql
# sudo -u postgres psql -c "SELECT version();"

On Ubuntu/Debian:

# apt update
# apt -y install postgresql
# systemctl enable --now postgresql
# sudo -u postgres psql -c "SELECT version();"

2.2.2 Install from Official PostgreSQL Repository

For RHEL‑compatible distributions:

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-10-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# dnf -qy module disable postgresql
# dnf install -y postgresql17-server
# /usr/pgsql-17/bin/postgresql-17-setup initdb
# systemctl enable --now postgresql-17
# sudo -u postgres psql -c "SELECT version();"

For Ubuntu:

# apt install -y curl ca-certificates
# install -d /usr/share/postgresql-common/pgdg
# curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc https://www.postgresql.org/media/keys/ACCC4CF8.asc
# . /etc/os-release
# echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt ${VERSION_CODENAME}-pgdg main" > /etc/apt/sources.list.d/pgdg.list
# sed -i.bak 's|apt.postgresql.org/pub|mirrors.aliyun.com/postgresql|g' /etc/apt/sources.list.d/pgdg.list
# apt update
# apt -y install postgresql-17
# systemctl enable --now postgresql
# sudo -u postgres psql -c "SELECT version();"

2.2.3 Install on openSUSE

Use the distribution packages:

# zypper install -y postgresql17-server
# systemctl enable --now postgresql
# sudo -u postgres psql -c "SELECT version();"

All commands above initialize the data directory, start the service, and verify the installation by displaying the PostgreSQL version.