Cara install dan setup PostgreSQL di RHEL 8 / Centos 7

Bagaimana cara menginstal manajemen basis data PostgreSQL di RHEL 8 atau Centos 7 menggunakan perintah? Bagaimana cara menginstal dan mengatur PostgreSQL di RHEL 8 server?

Pendahuluan: PostgreSQL adalah gratis dan open dari ORDBMS (sistem manajemen database objek-relasional). Ini adalah basis data sumber terbuka paling canggih di dunia. Halaman ini menunjukkan cara menginstal PostgreSQL di RHEL 8 dan mengkonfigurasi nya dari server database.

Cara install dan setup PostgreSQL di RHEL 8 / Centos 7

1. Buka terminal Window
2. Temukan version dari PostgreSQL yang kamu mau untuk di install:
   sudo yum module list | grep postgresql
3. Install the default, PostgreSQL version 10 on RHEL 8:
   sudo yum install @postgresql
4. Selanjtunya initialisasi PostgreSQL database baru di RHEL 8:
   sudo postgresql-setup --initdb

Mari kita lihat semua command.

Cara melihat semua list PostgreSQL application streams

Jalankan perintah yum

$ sudo yum module list | grep postgresql

Cara install dan menggunakan PostgreSQL di RHEL 8

In the previous step, we saw that RHEL 8 shipped with two Applications streams for PostgreSQL server. To install PostgreSQL 9.6, run:
$ sudo yum install @postgresql:9.6
The default is PostgreSQL 10, so running the following yum command installs the latest stable version:
$ sudo yum install @postgresql

Cara untuk initialisasi PostgreSQL database baru

The first action you perform after PostgreSQL server installation is to run the following command:
$ sudo postgresql-setup --initdb

Cara setup password untuk postgres account

Jalankan perintah passwd untuk setup password:
$ sudo passwd postgres
Contoh output:

Changing password for user postgres.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

Cara menjalankan/stop/restart PostgreSQL server

You need to use the systemctl command:
sudo systemctl start postgresql ## <-- start the server ##
sudo systemctl stop postgresql ## <-- stop the server ##
sudo systemctl restart postgresql ## <-- resstart the server ##
sudo systemctl status postgresql ## <-- get status of the server ##

How to enable the PostgreSQL server at boot time on RHEL 8

Again use the systemctl command as follows:
$ sudo systemctl enable postgresql

How do I log in using psql?

You need to use the psql command. It is a terminal-based front-end to PostgreSQL server. It enables you to type in queries interactively. The installation script created a user named postgres. That is the default account for default database and roles. Let us log in as postgres using the sudo command:
$ sudo -i -u postgres
Run it:
$ psql

At postgres=# prompt type \q to quit from the command-line interface to PostgreSQL server. Did you notice you logged into PostgreSQL without any password? Let us fix this by creating HBA config:
$ sudo vi /var/lib/pgsql/data/pg_hba.conf
Find lines that read as follows:

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

Replace ident with scram-sha-256:

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

Restart the postgresql server:
$ sudo systemctl restart postgresql

How to create a new PostgreSQL user account

First create a Linux user account named tom:
$ sudo useradd tom
$ sudo passwd tom
Sample outputs:

Changing password for user tom.
New password: 
Retype new password: 
passwd: all authentication tokens updated successfully.

The postgres account is nothing but an administrative user for PostgreSQL server. So log in as postgres:
$ sudo -i -u postgres
Run the following createuser command to creates a new PostgreSQL role for tom Linux user:
$ createuser --interactive

Create a new user account with password for new role:
$ createuser --interactive --pwprompt

Finally create a new database named jerry for tom user by log in as postgres admin user:
$ sudo -i -u postgres
$ createdb -O tom jerry

How do I connect to jerry database with tom user?

Simply run the following commands:
$ sudo -i -u tom
$ psql -d jerry -U tom

How to create a new table

Log in:
$ psql -d jerry -U tom
Type the following SQL to create demo table:

CREATE TABLE demo(
 id serial PRIMARY KEY,
 email VARCHAR (100) UNIQUE NOT NULL,
 name  VARCHAR (50) UNIQUE NOT NULL
);

CREATE TABLE demo(
id serial PRIMARY KEY,
email VARCHAR (100) UNIQUE NOT NULL,
name VARCHAR (50) UNIQUE NOT NULL
);

See info about the demo table:
\d
OR
\dt

Let us add some data to our table, run the following SQL:

INSERT INTO demo (id, email, name) VALUES (1, 'webmaster@cyberciti.biz', 'Vivek Gite');
INSERT INTO demo (id, email, name) VALUES (2, 'foo@bar.com', 'Foo Bar');
INSERT INTO demo (id, email, name) VALUES (3, 'roja@nixcraft.com', 'Roja T');
INSERT INTO demo (id, email, name) VALUES (4, 'marlena@gmail.net.in', 'Marlena John');

View data:

SELECT * FROM demo;

DELETE FROM demo WHERE id = 4;

UPDATE demo SET email = 'foo@gmail.com' WHERE id = 2;

Kesmipulan

Selamat. Anda berhasil mengatur server PostgreSQL di server RHEL 8. Anda juga belajar cara membuat pengguna, database, dan tabel. Untuk info lebih lanjut, lihat dokumentasi PostgreSQL resmi here.