Lewati ke isi

Instalasi PostgreSQL pada sistem operasi Linux

Persiapan

Sistem operasi yang didukung sampai saat ini oleh PostgreSQL diantaranya adalah:

  1. CentOS & RHEL: 6, 7, & 8
  2. Debian: 8, 9, & 10
  3. Ubuntu: 16.04, 18.04, & 20.04 (LTS)

Paket / repositori instalasi PostgreSQL

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql

sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
> /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
sudo apt-get update

Proses

Instalasi PostgreSQL

Via paket repositori

yum install -y postgresql12-server
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable --now postgresql-12

dnf install -y postgresql12-server
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable --now postgresql-12

sudo apt-get install -y postgresql-12
/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable --now postgresql-12
Verifikasi versi hasil instalasi 
psql --version

psql (PostgreSQL) 12.3
Lihat log inisiasi DB 
cat /var/lib/pgsql/12/initdb.log

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/12/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Jakarta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /   usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data/ -l logfile start

Konfigurasi - akses pengguna & pangkalan data

Variabel 
PENGGUNA_PD_APL="pengguna_pd_apl"
NAMA_PD_APL="nama_pd_apl"
HOS_PELADEN_PD="10.20.30.101"
HOS_PELADEN_APL="10.20.30.201"

Pengguna baru

Membuat pengguna baru lalu isikan kata sandi bagi pengguna untuk mengakses pangkalan data

sudo -u postgres createuser --createdb --pwprompt --echo $PENGGUNA_PD_APL

sudo -u postgres psql -c "SELECT usename FROM pg_user;"

    usename
----------------
postgres
$NAMA_PD_APL
(2 rows)

Pangkalan data baru

Membuat pangkalan data baru dan memberikan kepemilikannya kepada pengguna yang sebelumnya sudah dibuat

sudo -u postgres createdb $NAMA_PD_APL --owner=$PENGGUNA_PD_APL

Mengubah antarmuka jaringan peladen PostgreSQL agar dapat diakses secara jarak jauh dari peladen aplikasi

grep listen_addresses /var/lib/pgsql/12/data/postgresql.conf
# 
cp -v /var/lib/pgsql/12/data/postgresql.conf{,.`date +%Y%m%d%H%M`}
sed --in-place "s/^#listen_addresses = .*/listen_addresses = '${HOS_PELADEN_APL}'/g" \
   /var/lib/pgsql/12/data/postgresql.conf

Memberikan izin bagi pengguna…

grep -v -E "^#|^$" /var/lib/pgsql/12/data/pg_hba.conf
echo "host ${NAMA_PD_APL} ${PENGGUNA_PD_APL} ${HOS_PELADEN_APL}/24 md5" >> /var/lib/pgsql/12/data/pg_hba.conf

Memuat ulang layanan peladen PostgreSQL

systemctl restart postgresql

Penyelesaian