Basic Usage PostgreSQL

Setelah Anda menginstall PostgreSQL, Anda bisa menggunakan command-line tool psql untuk membantu terhubung ke database, memeriksa skema, menjalankan query, atau membuat user dan database baru.

Berikut adalah beberapa perintah psql yang sering digunakan

Login ke PostgreSQL

#

Login sebagai user postgres:

su - postgres

Lalu jalankan:

psql

Contoh hasil:

psql (15.4)
Type "help" for help.

postgres=#

Atau, langsung login tanpa masuk ke user postgres terlebih dahulu:

psql -U postgres

Mengubah Password User

#

Untuk mengubah password user PostgreSQL:

ALTER USER user_name WITH PASSWORD 'new_password';

Login Tanpa Password (Menggunakan .pgpass)

#

Anda dapat mengatur agar login tidak perlu memasukkan password setiap kali, dengan membuat file .pgpass.

1. Buat file .pgpass di home directory:

   nano ~/.pgpass
   

2. Isi dengan format berikut:

   hostname:port:database:username:password
   

   Contoh:

   *:*:*:user_name:yourstrongpassword
   

3. Atur permission agar hanya user Anda yang bisa membaca file tersebut:

   chmod 600 ~/.pgpass
   

Membuat User Baru

#

Contoh: membuat user us_app dengan password untuk mengakses database testdb.

createuser -e us_app -P

Atau menggunakan SQL:

CREATE ROLE us_app WITH LOGIN PASSWORD 'supersecret' CREATEDB CREATEROLE REPLICATION BYPASSRLS;

Membuat Database Baru

#

Untuk membuat database testdb dengan owner us_app:

createdb -O us_app testdb

Atau menggunakan SQL:

CREATE DATABASE testdb OWNER us_app;

Test login menggunakan user us_app ke database testdb:

psql -U us_app -d testdb

Jika berhasil, akan tampil output seperti berikut:

Password for user us_app:
psql (15.4)
Type "help" for help.

testdb=>

Melihat Daftar Database

#

Gunakan perintah \l di dalam psql:

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 testdb    | us_app   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)

Menghapus Database & User

#

Hapus database:

dropdb testdb

Hapus user:

dropuser -e us_app

Mengecek Database Extension

#

Install extension amcheck di semua database (kecuali database bawaan: postgres, template0, template1):

for DB in $(psql -t -c "SELECT datname FROM pg_database WHERE datname NOT IN ('postgres', 'template0', 'template1')"); do
  psql -d $DB -c "CREATE EXTENSION IF NOT EXISTS amcheck"
done

Cek kerusakan/corruption database dengan:

/usr/pgsql-15/bin/pg_amcheck --all

Role Membership

#

Menambahkan user ke group role:

GRANT roti TO test;

Cek daftar roles:

postgres=# \du
                                     List of roles
 Role name |                         Attributes                         |   Member of
-----------+------------------------------------------------------------+--------------- 
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 roti      | Cannot login                                               | {}
 test      |                                                            | {testdb,roti}
 testdb    | Cannot login                                               | {}

Privileges Management

#

Memberikan Privileges

#

• Semua sequence di schema public:

GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO username;

• Semua table di schema public:

GRANT ALL ON ALL TABLES IN SCHEMA public TO username;

Mengubah Owner

#

• Table:

ALTER TABLE public.namatable OWNER TO namaowner;

• Sequence:

ALTER SEQUENCE public.namasequence OWNER TO namaowner;

Menghapus Privileges

#

• Semua sequence di schema public:

REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM username;

• Semua table di schema public:

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM username;

Export Database (Dump)

#

Export database testdb menjadi file dump:

pg_dump testdb > testdb.dump

Format Output

#

Gunakan opsi -F, --format=c|d|t|p untuk menentukan format file:

• c → custom archive (bisa di-restore dengan pg_restore)
• d → directory format
• t → tar archive
• p → plain SQL script (default)

Contoh:

pg_dump -F c testdb > test.dump

Konversi Format Dump ke SQL Text

#

pg_restore -v -C -O test.dump > testdb-text.sql

Import ke Database Lain

#

psql roti < testdb.dump

Restore Database

#

Export database ke file custom format:

pg_dump -Fc testdb > testdb.dump

Restore ke database lain:

pg_restore -d roti testdb.dump

Restore Berdasarkan Item Tertentu

#

List isi file dump:

pg_restore -l testdb.dump

Contoh output:

3; 2615 2200 SCHEMA - public pasha
1861; 0 0 COMMENT - SCHEMA public pasha
1862; 0 0 ACL - public pasha
317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha
...

Buat file testdb.list, lalu tambahkan baris item yang ingin di-restore:

317; 1247 17715 TYPE public composite pasha
319; 1247 25899 DOMAIN public domain0 pasha

Restore hanya item tersebut ke database:

pg_restore -L testdb.list -d roti testdb.dump

Perintah psql

#

Performa & Optimasi

#

• Menampilkan rencana eksekusi query:

EXPLAIN query;

• Menjalankan query sekaligus menampilkan rencana eksekusi:

EXPLAIN ANALYZE query;

• Mengumpulkan/memperbarui statistik untuk query planner:

ANALYZE nama_tabel;