Basic Usage PostgreSQL

Jika sebelumnya telah menginstall PostgreSQL, pada panduan ini akan menjelaskan cara dasar penggunaan dan pengelolaan database PostgreSQL.

Login

#

Login sebagai user postgres

su - postgres

Lalu untuk mengakses PostgreSQL gunakan perintah psql

$ psql
psql (15.4)
Type "help" for help.

postgres=#

Atau bisa langsung tanpa perlu masuk ke user postgres

# psql -U postgres
psql (15.4)
Type "help" for help.

postgres=#

Change Password User

#

Untuk mengubah password user.

ALTER USER user_name WITH PASSWORD 'new_password';

Anda juga dapat mengatur agar dapat login tanpa memasukan password dengan cara membuat file .pgpass.

nano .pgpass

*:*:*:user_name:yourstrongpassword

Creating User

#

Buat user test untuk akses ke DB testdb

createuser -e test --role=testdb -P

CREATE ROLE test WITH PASSWORD 'test' LOGIN IN ROLE testdb;

Selanjutnya buat user testdb dengan attribut Cannot login

createuser -e --no-login testdb

CREATE ROLE testdb NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN;

Creating Database

#

Untuk membuat database baru dengan nama testdb.

createdb -O testdb testdb

CREATE DATABASE testdb OWNER testdb

List database dengan perintah

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    | testdb   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
(4 rows)

Test akses database dengan user test

$ psql -U test -d testdb
Password for user test:
psql (15.4)
Type "help" for help.

testdb=>

Untuk menghapus database.

dropdb testdb

Untuk menghapus user.

dropuser -e test

Check Database Extension

#

Install extension amcheck di semua database

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

Lalu untuk cek kerusakan/corruption database gunakan perintah.

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

Role Membership

#

Untuk menambahkan user ke group role.

GRANT roti TO test;

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                                               | {}

Untuk menambahkan privileges (GRANT) pada semua sequence di database schema public postgres.

GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO username

Untuk menambahkan privileges (GRANT) pada semua table di database schema public postgres.

GRANT ALL ON ALL TABLES IN SCHEMA public TO username

Untuk mengubah owner table.

ALTER table public.namatables owner to namaowner

Untuk mengubah owner sequence.

ALTER sequence public.namasequence owner to namaowner

Untuk menghapus privileges (REVOKE) pada semua sequence di database schema public postgres.

REVOKE ALL ON ALL SEQUENCES IN SCHEMA public TO username

Untuk menghapus privileges (REVOKE) pada semua table di database schema public postgres.

REVOKE ALL ON ALL TABLES IN SCHEMA public TO username

SQL Dump

#

Untuk export database menjadi file gunakan perintah.

pg_dump testdb > testdb.dump

Gunakan opsi -F, --format=c|d|t|p untuk menetukan format output file.

pg_dump -F c testdb > test.dump

Untuk mengubah format file dump menjadi text.

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

Lalu untuk import ke database lain bisa dengan perintah.

psql roti < testdb.dump

Restore Database

#

Tidak jauh beda dengan import database, hanya perintah yang digunakan adalah pg_restore

Export database menjadi file.

pg_dump -Fc testdb > testdb.dump

Lalu untuk restore ke database gunakan perintah.

pg_restore -d roti testdb.dump

Bisa juga restore berdasarkan item seperti berikut.

pg_restore -l testdb.dump

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 yang ingin Anda import.

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

Lalu restore ke database.

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

Cheat Sheet

#

Connect to a specific database

\c database_name;

List all databases in the PostgreSQL database server

\l

List all schemas

\dn

List all stored procedures and functions

\df

List all views

\dv

Lists all tables in a current database

\dt

Or to get more information on tables in the current database

\dt+

Get detailed information on a table

\d+ table_name

Show a stored procedure or function code

\df+ function_name

Show query output in the pretty-format

x on

List all users

\du

Performance

#

Show the query plan for a query

EXPLAIN query;

Show and execute the query plan for a query

EXPLAIN ANALYZE query;

Collect statistics

ANALYZE table_name;