PostgreSQL Compile Build Getting Started & Quick deployment

Posted on May 16, 2024

PostgreSQL: The World's Most Advanced Open Source Relational Database, this writings take compile build from source code and getting started quickly.

Compile Build

# debian
apt install libsystemd-dev libicu-dev zlib1g-dev flex
# fedora
dnf install systemd-devel libicu-devel zlib-devel flex

git clone --recurse-submodules --depth 1 --branch=REL_16_2 [email protected]:postgres/postgres.git

cd postgres

./configure --with-pgport=5432 \
  --prefix=/usr/local/pgsql16 \
  --with-systemd \
  --with-blocksize=8 \
  --with-segsize=1 \
  --with-wal-blocksize=8 \
  --without-readline \
  --datadir=/databases/pgsql16/init
make
make install

# all entension
cd contrib
make
make install 
# Specify one like hstore
cd contrib/hstore
make
make install

Initialize Database


useradd -s /bin/bash -m postgres
chown -R postgres:postgres /databases/pgsql16/
sudo su - postgres
/usr/local/pgsql16/bin/initdb -D /databases/pgsql16/data

Start PostgreSQL

/lib/systemd/system/pgsql16d.service

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
# RequiresMountsFor=/databases

[Service]
Type=notify
User=postgres
Group=postgres
RuntimeDirectory=postgresql
RuntimeDirectoryMode=2755
ExecStart=/usr/local/pgsql16/bin/postgres -D /databases/pgsql16/data -k /run/postgresql/
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

systemctl enable --now pgsql16d.service
# /usr/local/pgsql16/bin/pg_ctl -D /databases/pgsql16/data -l logfile start/stop

# local psql client: /usr/local/pgsql16/bin/psql
sudo -Su postgres /usr/local/pgsql16/bin/psql -h /run/postgresql
# show dbs
sudo -Su postgres /usr/local/pgsql16/bin/psql -c "\l" -h /run/postgresql
sudo -Su postgres /usr/local/pgsql16/bin/psql -c "SELECT * from pg_stat_activity" -h /run/postgresql

# EXTENSION
sudo -u postgres /usr/local/pgsql16/bin/psql exampledb -h /run/postgresql
CREATE EXTENSION hstore;

User & Database Manage

-- sudo -Su postgres psql 
CREATE USER usr1 WITH PASSWORD 'debian';
ALTER USER usr1 WITH SUPERUSER;
ALTER USER usr1 WITH NOSUPERUSER;

-- create database
CREATE DATABASE exampledb OWNER usr1;
GRANT ALL PRIVILEGES ON DATABASE exampledb TO usr1;

-- delete database
DROP DATABASE exampledb;

-- delete users
DROP ROLE usr1;

-- command
\l -- list all database
\c miniflux -- change to database "miniflux"
\d -- list all tables
\d users -- show table "users" create
\q -- quit

Server configure

Listen in all interfaces:

/databases/pgsql16/data/postgresql.conf

listen_addresses = '*'

Database access control

/databases/pgsql16/data/pg_hba.conf

# local login exampledb use usr1
local   exampledb       usr1                                   md5
# login with remote exampledb usr1
host    exampledb       usr1         0.0.0.0/0                 md5
# login with remote all user & dbs
host    all             all           0.0.0.0/0                 md5

Host client

# dnf install postgresql
# apt install postgresql-client
psql -U usr1 -d exampledb -h <xx.xx.xx.xx> -p 5432 -W