Instalasi Database Server PostgreSQL (Ubuntu)
Saturday, 17 June 2023
Bismillahirrahmanirrahim.
Di artikel kali ini akan dipaparkan proses instalasi Database Server Open Source yaitu PostgreSQL.
PostgreSQL merupakan salah satu database managemet system relasional atau disebut sebagai RDBMS selain dari MySQL/MariaDB yang umum digunakan.
PostgreSQL merupakan sistem basis data relasional-objek sumber terbuka yang ampuh, dengan lebih dari 35 tahun pengembangan aktif yang menghasilkan reputasi yang kuat terhadap keandalan, ketahanan fitur, dan performa. - postgresql.org
Instal PostgreSQL
Kunjungi situs web PostgreSQL untuk mendapatkan source code dan proses instalasi pada OS Ubuntu.
Membuat file konfigurasi repositori:
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 kunci penandatanganan repositori (signing key):
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Memperbaharui daftar paket (package list):
sudo apt-get update
Instal versi terbaru PostgreSQL:
sudo apt-get -y install postgresql
Untuk menginstal versi yang spesifik, gunakan postgre-<version>:
sudo apt-get -y install postgresql-12
Proses instalasi akan berlangsung hingga selesai. Berikut tampilan proses instalasi PostgreSQL:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
deepin-clone deepin-pw-check deepin-recovery-tool fbterm imageworsener libdumbnet1
libgtkmm-3.0-1v5 libllvm11 libmaxminddb0 libmspack0 libqtermwidget5-0 libsmi2ldbl
libutempter0 libutf8proc2 libwireshark-data libwireshark11 libwiretap8 libwscodecs2
libwsutil9 libx86-1 libxmlsec1 libxmlsec1-openssl live-system-com partclone
qtermwidget5-data squashfs-tools x11-apps x11-session-utils xbitmaps xinit
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
libllvm7 libpq5 postgresql-11 postgresql-client-11 postgresql-client-common
postgresql-common sysstat
Suggested packages:
postgresql-doc postgresql-doc-11 libjson-perl isag
The following NEW packages will be installed:
libllvm7 postgresql postgresql-11 postgresql-client-11 postgresql-client-common
postgresql-common sysstat
The following packages will be upgraded:
libpq5
1 upgraded, 7 newly installed, 0 to remove and 417 not upgraded.
Need to get 29.6 MB of archives.
After this operation, 115 MB of additional disk space will be used.
Get:1 https://community-packages.deepin.com/deepin apricot/main amd64 libllvm7 amd64 1:7.0.1-8 [13.0 MB]
Get:2 https://community-packages.deepin.com/deepin apricot/main amd64 libpq5 amd64 11.18-0+deb10u1 [173 kB]
Get:3 https://community-packages.deepin.com/deepin apricot/main amd64 postgresql-client-common all 200+deb10u4+rebuild [85.0 kB]
Get:4 https://community-packages.deepin.com/deepin apricot/main amd64 postgresql-client-11 amd64 11.18-0+deb10u1 [1,379 kB]
Get:5 https://community-packages.deepin.com/deepin apricot/main amd64 postgresql-common all 200+deb10u4+rebuild [225 kB]
Get:6 https://community-packages.deepin.com/deepin apricot/main amd64 postgresql-11 amd64 11.18-0+deb10u1 [14.0 MB]
Get:7 https://community-packages.deepin.com/deepin apricot/main amd64 postgresql all 11+200+deb10u4+rebuild [61.1 kB]
Get:8 https://community-packages.deepin.com/deepin apricot/main amd64 sysstat amd64 12.0.3.1-2+security [551 kB]
Fetched 29.6 MB in 4min 21s (113 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libllvm7:amd64.
(Reading database ... 264759 files and directories currently installed.)
Preparing to unpack .../0-libllvm7_1%3a7.0.1-8_amd64.deb ...
Unpacking libllvm7:amd64 (1:7.0.1-8) ...
Preparing to unpack .../1-libpq5_11.18-0+deb10u1_amd64.deb ...
Unpacking libpq5:amd64 (11.18-0+deb10u1) over (11.14-0+deb10u1) ...
Selecting previously unselected package postgresql-client-common.
Preparing to unpack .../2-postgresql-client-common_200+deb10u4+rebuild_all.deb ...
Unpacking postgresql-client-common (200+deb10u4+rebuild) ...
Selecting previously unselected package postgresql-client-11.
Preparing to unpack .../3-postgresql-client-11_11.18-0+deb10u1_amd64.deb ...
Unpacking postgresql-client-11 (11.18-0+deb10u1) ...
Selecting previously unselected package postgresql-common.
Preparing to unpack .../4-postgresql-common_200+deb10u4+rebuild_all.deb ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (200+deb10u4+rebuild) ...
Selecting previously unselected package postgresql-11.
Preparing to unpack .../5-postgresql-11_11.18-0+deb10u1_amd64.deb ...
Unpacking postgresql-11 (11.18-0+deb10u1) ...
Selecting previously unselected package postgresql.
Preparing to unpack .../6-postgresql_11+200+deb10u4+rebuild_all.deb ...
Unpacking postgresql (11+200+deb10u4+rebuild) ...
Selecting previously unselected package sysstat.
Preparing to unpack .../7-sysstat_12.0.3.1-2+security_amd64.deb ...
Unpacking sysstat (12.0.3.1-2+security) ...
Setting up postgresql-client-common (200+deb10u4+rebuild) ...
Setting up libpq5:amd64 (11.18-0+deb10u1) ...
Setting up postgresql-client-11 (11.18-0+deb10u1) ...
update-alternatives: using /usr/share/postgresql/11/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-common (200+deb10u4+rebuild) ...
supported-versions: WARNING! Unknown distribution: Deepin
/usr/share/postgresql-common/supported-versions: 66: /usr/share/postgresql-common/supported-versions: ID_LIKE: parameter not set
/usr/share/postgresql-common/supported-versions: 69: /usr/share/postgresql-common/supported-versions: ID_LIKE: parameter not set
Please submit this as a bug report to your distribution.
Adding user postgres to group ssl-cert
Creating config file /etc/postgresql-common/createcluster.conf with new version
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
en_us
Removing obsolete dictionary files:
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service.
Setting up libllvm7:amd64 (1:7.0.1-8) ...
Setting up sysstat (12.0.3.1-2+security) ...
Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Setting up postgresql-11 (11.18-0+deb10u1) ...
Creating new PostgreSQL cluster 11/main ...
/usr/lib/postgresql/11/bin/initdb -D /var/lib/postgresql/11/main --auth-local peer --auth-host md5
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/postgresql/11/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Makassar
selecting dynamic shared memory implementation ... posix
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:
pg_ctlcluster 11 main start
Ver Cluster Port Status Owner Data directory Log file
11 main 5432 down postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
update-alternatives: using /usr/share/postgresql/11/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (11+200+deb10u4+rebuild) ...
Processing triggers for man-db (2.8.5-2) ...
Processing triggers for libc-bin (2.28.21-1+deepin-1) ...
Koneksi ke PostgreSQL melalui psql
Proses instalasi PostgreSQL akan membuat user dengan nama postgres. Lakukan koneksi ke PostgreSQL menggunakan akun postgres dengan mengetikkan:
sudo -i -u postgres
Maka, akan berpindah ke user postgres, lihat prompt berikut:
postgres@fajri-PC:~$
Selanjutnya menggunakan psql untuk mengakses PostgreSQL dengan perintah:
psql
Maka, akses PostgreSQL akan dihadapkan pada prompt postgres:
postgres=#
Dari sini perintah-perintah query bisa dilakukan.
Contoh untuk melihat daftar database yang ada, gunakan perintah:
\list
// atau
\l
Output:
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=#