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

How to see a list of all available PostgreSQL versions or application streams

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

Install and setup PostgreSQL on RHEL 8
Installing and use PostgreSQL on RHEL 8 using application streams (click to enlarge)

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

How to create a new PostgreSQL database cluster on RHEL 8
Creating a new PostgreSQL database cluster on RHEL 8

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

Start stop restart PostgreSQL server on RHEL 8
Start and enable the PostgreSQL server

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

Login to PostgreSQL Databases
Validating installation of PostgreSQL database server

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

How to create a new PostgreSQL user or role on RHEL 8
Define a new PostgreSQL user account named tom

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

Creating user with password on PostgreSQL
Creating user with password on PostgreSQL

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 connect to postgresql with the new user
Connecting to PostgreSQL server with the new user named tom for jerry database

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
);

See info about the demo table:
\d
OR
\dt
How to create a new PostgreSQL table on RHEL 8
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 some data:

DELETE FROM demo WHERE id = 4;

Update data:

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

 

How to add, query, delete and Update data in a table
How to add, query, delete and Update data in a table (click to enlarge)

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.


Di tulis oleh: