Basic Usage PostgreSQL

Table of Contents

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
#

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.

Buat file .pgpass di home directory:
```
nano ~/.pgpass
```

Isi dengan format berikut:

hostname:port:database:username:password

Contoh:

*:*:*:user_name:yourstrongpassword

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)

Untuk melihat database tertentu.

SELECT
    datname,
    pg_get_userbyid(datdba) AS owner
FROM pg_database
WHERE datname LIKE '%develop%';

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 `pg_dump`
#

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

Format	Keterangan
`c`	Custom archive (direkomendasikan, dapat direstore dengan `pg_restore`)
`d`	Directory format
`t`	Tar archive
`p`	Plain SQL script (default)

Contoh backup dalam format custom.

pg_dump --file=testdb.dump --format=c -b -v testdb

Contoh backup dalam format SQL (Plain Text)

pg_dump --file=testdb.sql --format=p -v testdb

Contoh backup tanpa Owner dan ACL. Ini berguna jika backup akan direstore ke environment atau user yang berbeda.

pg_dump \
  -Fc \
  -Z 9 \
  --no-acl \
  --no-owner \
  -h localhost \
  -U myuser \
  --no-password \
  mydb \
  > mydb_backup.dump

Konversi Dump ke SQL Text
#

Untuk mengubah backup format custom (.dump) menjadi script SQL:

pg_restore \
  -v \
  --create \
  --no-owner \
  test.dump \
  > testdb.sql

Restore Database
#

Restore dari File SQL
#

Untuk file hasil backup format plain SQL (.sql):

psql -d roti < testdb.sql

Restore dari File Custom Dump
#

Untuk file hasil backup format custom (.dump):

pg_restore -d roti testdb.dump

Restore dengan Membersihkan Objek yang Sudah Ada
#

Opsi ini akan menghapus objek yang sudah ada sebelum melakukan restore.

pg_restore \
  -h localhost \
  -U myuser \
  --no-password \
  --clean \
  --if-exists \
  --no-owner \
  -d mydb \
  mydb_backup.dump

Catatan:

File backup format custom, directory, dan tar harus direstore menggunakan pg_restore.
File backup format plain SQL (.sql) harus direstore menggunakan psql.
Format custom (-Fc) umumnya paling fleksibel karena mendukung restore selektif, parallel restore, dan filtering objek.

Restore Database dengan Opsi Paralel
#

Jika file backup dibuat menggunakan format custom (-Fc) atau directory (-Fd), Anda dapat mempercepat proses restore dengan secara paralel menggunakan opsi -j.

Contoh restore menggunakan 4 worker:

pg_restore \
  -h localhost \
  -U myuser \
  -d mydb \
  -j 4 \
  mydb_backup.dump

Catatan:

-j 4 menjalankan 4 proses restore secara paralel.
Semakin banyak CPU core yang tersedia, semakin besar potensi peningkatan performa.
Opsi paralel hanya didukung untuk backup berformat custom (.dump) atau directory, dan tidak dapat digunakan pada backup format plain SQL (.sql).

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
#

Perintah	Deskripsi
`psql -d db -U user`	Terhubung ke database dengan user tertentu
`\x on`	Menampilkan hasil query dalam format yang lebih rapi (pretty mode)
`\c dbname`	Pindah koneksi ke database lain
`\l`	Menampilkan daftar semua database
`\dt`	Menampilkan daftar tabel di schema saat ini
`\dt+`	Menampilkan daftar tabel dengan informasi tambahan
`\d nama_tabel`	Menampilkan struktur sebuah tabel
`\d+ nama_tabel`	Menampilkan informasi detail sebuah tabel
`\dn`	Menampilkan semua schema
`\df`	Menampilkan semua fungsi
`\df+ nama_fungsi`	Menampilkan kode fungsi atau prosedur yang tersimpan
`\dv`	Menampilkan semua view
`\du`	Menampilkan semua role/user
`\g`	Menjalankan kembali perintah sebelumnya
`\s`	Menampilkan riwayat perintah
`\?`	Menampilkan semua perintah `psql` yang tersedia
`\h`	Bantuan untuk sintaks SQL (contoh: `\h ALTER TABLE`)
`\timing`	Menyalakan/menonaktifkan tampilan waktu eksekusi query
`\q`	Keluar dari `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;

Debug Log
#

Untuk mengatur tingkat log log_min_messages di PostgreSQL ke level debug1, jalankan perintah berikut:

ALTER SYSTEM SET log_min_messages = 'debug1';
SELECT pg_reload_conf();

Valid value: debug5, debug4, debug3, debug2, debug1, info, notice, warning, error, log, fatal, panic

Untuk memastikan perubahan telah diterapkan.

SHOW log_min_messages;

Login ke PostgreSQL #

Mengubah Password User #

Login Tanpa Password (Menggunakan .pgpass) #

Membuat User Baru #

Membuat Database Baru #

Melihat Daftar Database #

Menghapus Database & User #

Mengecek Database Extension #

Role Membership #

Privileges Management #

Memberikan Privileges #

Mengubah Owner #

Menghapus Privileges #

Export Database (Dump) #

Format Output pg_dump #

Konversi Dump ke SQL Text #

Restore Database #

Restore dari File SQL #

Restore dari File Custom Dump #

Restore dengan Membersihkan Objek yang Sudah Ada #

Restore Database dengan Opsi Paralel #

Restore Berdasarkan Item Tertentu #

Perintah psql #

Performa & Optimasi #

Debug Log #

Related