Postgresql CLI Cheat Sheet

Postgresql

Postgresql adalah salah satu produk Open Source dan Database Management System yang banyak digunakan. Postgresql mampu menghandle berbagai data dari yang sederhana seperti teks  hingga data  yang kompleks seperti raster peta.

Selain itu, postgresql juga memiliki versi replikasi, cluster dan peningkatan pooling koneksi database seperti PostgresXL, PG-Pool II, Slony, Bucardo dan masih banyak lagi tool cluster pada postgresql. Bisa cek disini

Keseharian, hampir setiap hari saya perlu berinteraksi dengan postgresql. Dan nggak jarang saya lupa perintah apa yang harus digunakan untuk keperluan ini dan itu. Karena itu saya dokumentasikan kedalam blog ini. Berikut adalah kepek-an (cheat sheet) yang biasa saya gunakan saat mengelola postgresql :

Administrasi User

Membuat user baru

CREATE ROLE [nama_user];

Membuat user baru non superuser

CREATE ROLE [nama_user] PASSWORD '[password_user]' LOGIN;

 

 Membuat user baru dengan contoh atribute/hak akses lengkap

CREATE ROLE [nama_user] PASSWORD '[password_user]' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

Mengganti password user baru dengan contoh atribute lengkap

ALTER ROLE [user] WITH PASSWORD '[password_baru]';

Menambahkan user 

ALTER USER [user] WITH SUPERUSER;

Menampilkan daftar user postgresql

SELECT u.usename AS "User Name" FROM pg_catalog.pg_user u;

Administrasi Database dan Tabel
Membuat database

CREATE DATABASE [nama_database] owner [nama_owner];

Menggandakan database

CREATE DATABASE [database_baru] WITH TEMPLATE [sumber_database[ OWNER [nama_owner];

Mengubah kepemilikan database

ALTER DATABASE [nama_database] OWNER TO [nama_user_baru];

Menambahkan kolom baru ke tabel

ALTER TABLE [nama_tabel] ADD COLUMN [nama_kolom] [tipe_data];

Mengubah nama kolom pada tabel

ALTER TABLE [nama_tabel] RENAME COLUMN [nama_kolom_lama] TO [nama_kolom_baru];

Mengubah kepemilikan tabel

ALTER TABLE [nama_tabel] OWNER TO [nama_user_baru];

Mengubah password user

ALTER USER [nama_user] PASSWORD '[password_disini]'

Manage Extension
Menampilkan daftar extensi yang terinstall pada sistem postgresql

SELECT * FROM pg_available_extensions;

Mengaktifkan ekstensi pada sebuah database (pastikan sudah terhubung dengan database yang diingikan)

CREATE EXTENSION [nama_extension];

Menghapus ekstensi pada sebuah database (pastikan sudah terhubung dengan database yang diingikan)

DROP EXTENSION [nama_extension];

Mengubah Locale
Secara default postgresql di inisialisasi dengan locale en_US.utf8, salah satu cara untuk mengubahnya adalah mengubah database template1 yang merupakan template saat membuat database menjadi locale yang di inginkan. pertama  ubah terlebih dahulu config agar template1 bukan database template

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';

 kemudian drop database template1

DROP DATABASE template1;

 kemudian buat kembali database template1 dengan ctype dan locale yang berbeda, misalnya C

CREATE DATABASE template1 LC_COLLATE 'C' LC_CTYPE 'C'  TEMPLATE = template0 ENCODING = 'UTF8';

selanjutnya vacuum database template1

VACUUM FREEZE; 

selanjutnya buat database baru dengan dengan perintah berikut

CREATE DATABASE sample;

kemudian jika ingin mengembalikan config database template1  adalah sebuah template, bisa menjalankan perintah berikut

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

Miscellaneous
Menghitung jumlah record pada sebuah tabel atau view

SELECT COUNT(*) FROM [nama_table_atau_view];

Menampilkan daftar statistik koneksi database

SELECT * FROM  pg_stat_activity;

Eksekusi query langsung dari shell

psql -U postgres -h [url_ip_host] -p [port_postgres] -d [nama_database] -c "SELECT kolom  FROM nama_tabel WHERE kolom = 'value_kolom'"

Versi dan Configurasi Postgres
Menampilkan lokasi file konfigurasi postgresql

SHOW config_file;

Mengetahui konfigurasi postgresql dengan query

SELECT * FROM pg_settings;

Mengetahui versi postgresql dengan query

SELECT version();

Backup dan Restore
Backup tabel menggunakan metode dump (langsung dari shell)

pg_dump --host [url_atau_ip_server] --encoding=utf8 --no-owner --username=[username_postgres] --password -t [nama_tabel] [nama_database] > [nama_hasil_dump_tabel].sql

Backup database menggunakan metode dump (langsung dari shell)

NAMA_FILE=file.dump 

NAMA_DATABASE=mydatabase 

pg_dump -v -Fc -Z 9 --file=$NAMA_FILE $NAMA_DATABASE -U postgres 

-Fc     adalah format file yang dihasilkan, c adalah custom format dari postgresql
-Z 9 adalah level kompresi. semakin tinggi maka file yang dihasilkan semakin kecil

Restore tabel menggunakan metode dump (pastikan extension, relasi, role dan komponen lain sudah di konfigurasi sama seperti server sumber dump file)

psql --host [url_atau_ip_server] --username=[username_postgres] [database_target] -f [nama_dump_tabel].sql

Restore database dari file dump (langsung dari shell) :
disarankan untuk menghapus database yang ada terlebih dahulu dengan perintah :

NAMA_DATABASE=mydatabase 

dropdb $NAMA_DATABASE

Jika file berupa plain teks :

psql -d [nama_database] -U [username_superuser] -p [port_postgresql] -f [file.sql]

Jika file yang digunakan adalah custom format

NAMA_FILE=file.dump

pg_restore -C -Fc -j 8 -d $NAMA_DATABASE $NAMA_FILE 

- j 8 adalah jumlah job/worker yang digunakan saat proses restore.
- C adalah perintah untuk membuat database baru sebelum restore di proses


shortcut alias :

\l menampilkan daftar database

\dt  menampilkan daftar tabel

\dx menampilkan daftar ekstensi yang terpasang pada database

\dv menampilkan daftar view yang ada pada database

\du melihat daftar hak akses user pada database
\d [nama_tabel]  menampilkan deskripsi kolom tabel

\c [nama_database]mengganti koneksi ke sebuah database

\z menampilkan hak akses pada tabel

\q keluar dari postgres

\password [nama_user] mengganti password user

Semoga bermanfaat. :)

referensi :

• https://stackoverflow.com/a/15335318
• https://stackoverflow.com/a/74371
• https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/