PostgreSQL 12 memiliki banyak fitur baru dan enhancement yang berguna. Berikut langkah instalasinya pada Ubuntu 18.04.

sudo apt-get -y install bash-completion wget

Tambahkan GPG key dan PostgreSQL 12 repository dengan perintah berikut.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list

Repository ini menambahkan beberapa package dan addon.

  • postgresql-client
  • postgresql
  • libpq-dev
  • postgresql-server-dev
  • pgadmin packages

Update APT repository index dan install package terkait PostgreSQL

sudo apt-get update
sudo apt-get -y install postgresql-12 postgresql-client-12

Periksa apakah Postgres service berjalan

sudo systemctl status postgresql

Jika berhasil akan muncul output seperti berikut:

user@host:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Mon 2019-09-30 10:45:27 CEST; 2 weeks 3 days ago
 Main PID: 2104 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 4915)
   CGroup: /system.slice/postgresql.service

Sep 30 10:45:27 host systemd[1]: Starting PostgreSQL RDBMS...
Sep 30 10:45:27 host systemd[1]: Started PostgreSQL RDBMS.

Sekarang anda bisa menjalankan perintah sudo su postgres agar bisa menjalankan psql.

sudo su postgres
psql

Outpu berikut akan muncul

postgres@host:$ psql
psql (12.0 (Ubuntu 10.10-0ubuntu0.18.04.1))
Type "help" for help.

Ganti password user postgres.

psql -c "alter user postgres with password 'StrongAdminP@ssw0rd'"

Buka PostgreSQL prompt menggunakan perintah:

$ psql

Output berikut akan muncul.

$ psql
psql (12.0 (Ubuntu 12.0-1.pgdg18.04+1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via sock
 Selesai!

Selanjutnya kita coba membuat database baru dan usernya.

postgres=# CREATE DATABASE mytestdb;
CREATE DATABASE
postgres=# CREATE USER mytestuser WITH ENCRYPTED PASSWORD 'MyStr0ngP@SS';
CREATE ROLE
postgres=# GRANT ALL PRIVILEGES ON DATABASE mytestdb to mytestuser;
GRANT

List  database:

postgres=# \l
                               List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |    Access privileges    
-----------+----------+----------+---------+---------+-------------------------
 mytestdb  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres           +
           |          |          |         |         | postgres=CTc/postgres  +
           |          |          |         |         | mytestuser=CTc/postgres
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres            +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

Connect ke database:

postgres-# \c mytestdb
You are now connected to database "mytestdb" as user "postgres".

Berikut kita buat user baru.

postgres@ubuntu:~$ createuser myuser --password
Password:
postgres@ubuntu:~$ createdb mydb -O myuser
postgres@ubuntu:~$ psql -l