Cara Mengelola Database di PostgreSQL: Panduan Lengkap untuk Pengguna Linux VPS

PostgreSQL (biasanya disebut Postgres) adalah salah satu sistem manajemen basis data relasional open-source yang paling powerful dan kaya fitur yang tersedia saat ini. Dipercaya oleh tim startup dan enterprise, PostgreSQL unggul dalam menangani query kompleks, dataset besar, dan arsitektur aplikasi yang scalable. Baik Anda membangun platform SaaS, toko e-commerce, atau backend API yang intensif data, PostgreSQL memberikan reliability dan performance yang dibutuhkan proyek Anda.

Menjalankan PostgreSQL di lingkungan VPS Hosting berkinerja tinggi — dengan penyimpanan NVMe SSD, akses root penuh, dan perlindungan DDoS bawaan — memberikan Anda kontrol lengkap atas infrastruktur basis data tanpa overhead solusi cloud terkelola. Panduan ini memandu Anda melalui setiap tugas manajemen PostgreSQL yang penting, dari akses awal dan pembuatan basis data hingga izin pengguna, backup, dan pengerasan keamanan.

Prasyarat

Sebelum melanjutkan, pastikan Anda memiliki:

• VPS Linux yang menjalankan Ubuntu, Debian, atau CentOS dengan PostgreSQL terinstal
• Akses root atau sudo ke server Anda
• Keakraban dasar dengan command line Linux

Jika Anda belum menyiapkan lingkungan server Anda, VPS Control Panels AlexHost membuat stack Anda dikonfigurasi dengan cepat.

1. Mengakses Antarmuka Baris Perintah PostgreSQL

Semua tugas manajemen PostgreSQL dimulai di antarmuka baris perintah (CLI), juga dikenal sebagai psql. Secara default, PostgreSQL membuat pengguna sistem bernama postgres selama instalasi, dan akun ini digunakan untuk autentikasi dengan mesin basis data.

Langkah 1: Beralih ke Pengguna Sistem PostgreSQL

sudo -i -u postgres

Langkah 2: Luncurkan CLI PostgreSQL

psql

Setelah terhubung, Anda akan melihat prompt interaktif:

postgres=#

Ini mengkonfirmasi Anda berada di dalam lingkungan PostgreSQL dan siap untuk menjalankan perintah SQL dan meta-command.

> Pro Tip: Anda juga dapat terhubung langsung tanpa beralih pengguna dengan menjalankan sudo -u postgres psql dari sesi shell reguler Anda.

2. Membuat Basis Data Baru

Basis data adalah wadah tingkat atas untuk semua tabel, indeks, dan data tersimpan Anda. Gunakan pernyataan CREATE DATABASE untuk menyediakan basis data baru.

Sintaks

CREATE DATABASE database_name;

Contoh

CREATE DATABASE my_database;

Ini membuat basis data bernama my_database yang dimiliki oleh role PostgreSQL yang sedang aktif.

Verifikasi Basis Data Dibuat

Gunakan meta-command l untuk mencantumkan semua basis data di server:

l

Anda akan melihat tabel yang menampilkan nama basis data, pemilik, encoding, dan privilege akses.

3. Membuat dan Mengelola Pengguna Basis Data

Manajemen pengguna yang tepat sangat penting untuk keamanan basis data. Daripada memberikan semua aplikasi akses di bawah akun superuser postgres, Anda harus membuat pengguna khusus dengan izin yang terbatas.

Buat Pengguna Baru

CREATE USER username WITH PASSWORD 'your_secure_password';

Contoh

CREATE USER dbuser WITH PASSWORD 'StrongP@ssword123';

Berikan Akses Penuh ke Basis Data Tertentu

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Contoh

GRANT ALL PRIVILEGES ON DATABASE my_database TO dbuser;

Ini memberikan dbuser akses baca dan tulis lengkap ke my_database, termasuk kemampuan untuk membuat dan menghapus tabel.

Cantumkan Semua Pengguna

Untuk melihat semua role dan pengguna yang ada:

du

4. Terhubung ke Basis Data

Setelah basis data ada, Anda perlu terhubung ke basis data sebelum Anda dapat membuat tabel atau menjalankan query terhadapnya.

Beralih ke Basis Data

c my_database

Prompt akan diperbarui untuk mencerminkan basis data yang aktif:

my_database=#

Anda sekarang beroperasi dalam my_database dan dapat menjalankan semua pernyataan DDL dan DML terhadapnya.

5. Membuat dan Mengelola Tabel

Tabel adalah unit struktural inti dari basis data relasional apa pun. Setiap tabel mendefinisikan skema — satu set kolom bernama dengan tipe data dan batasan tertentu.

Buat Tabel

CREATE TABLE table_name (
    column1 data_type PRIMARY KEY,
    column2 data_type,
    column3 data_type
);

Contoh Praktis: Tabel Catatan Karyawan

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      NUMERIC(10, 2)
);

Di sini, SERIAL secara otomatis menambah employee_id pada setiap baris baru, dan NOT NULL menegaskan bahwa setiap karyawan harus memiliki nama.

6. Menyisipkan, Menanyakan, Memperbarui, dan Menghapus Data

Sisipkan Catatan

INSERT INTO employees (name, department, salary)
VALUES ('Jane Smith', 'Engineering', 72000);

Tanyakan Semua Catatan

SELECT * FROM employees;

Filter Hasil dengan Klausa WHERE

SELECT name, salary FROM employees WHERE department = 'Engineering';

Perbarui Catatan yang Ada

UPDATE employees
SET salary = 78000
WHERE name = 'Jane Smith';

Hapus Catatan Tertentu

DELETE FROM employees
WHERE name = 'Jane Smith';

> Best Practice: Selalu gunakan klausa WHERE dengan pernyataan UPDATE dan DELETE. Menghilangkannya akan mempengaruhi setiap baris dalam tabel.

7. Mengelola Akses Basis Data dan Keamanan

Keamanan adalah perhatian kelas satu dalam penerapan PostgreSQL produksi apa pun. Prinsip privilege minimal — memberikan pengguna hanya izin yang benar-benar mereka butuhkan — secara dramatis mengurangi permukaan serangan Anda.

Cabut Semua Privilege dari Pengguna

REVOKE ALL PRIVILEGES ON DATABASE my_database FROM dbuser;

Berikan Akses Baca-Saja ke Tabel Tertentu

GRANT SELECT ON TABLE employees TO dbuser;

Ini memungkinkan dbuser untuk menanyakan tabel employees tetapi mencegah penyisipan, pembaruan, atau penghapusan apa pun.

Berikan Izin DML Tertentu

GRANT SELECT, INSERT, UPDATE ON TABLE employees TO dbuser;

Rekomendasi Keamanan Tambahan

• Gunakan password yang kuat dan unik untuk setiap pengguna basis data
• Nonaktifkan akses jarak jauh untuk superuser postgres di pg_hba.conf
• Aktifkan koneksi SSL untuk mengenkripsi data dalam transit — pasangkan ini dengan Sertifikat SSL terpercaya di server Anda
• Audit privilege pengguna secara teratur menggunakan du dan dp
• Jaga PostgreSQL tetap diperbarui untuk menambal kerentanan yang diketahui

8. Backup dan Restore Basis Data

Backup reguler tidak dapat dinegosiasikan untuk basis data produksi apa pun. PostgreSQL menyediakan utilitas pg_dump dan psql untuk alur kerja backup dan restoration yang mudah.

Backup Basis Data ke File SQL

pg_dump my_database > my_database_backup.sql

Ini mengekspor skema basis data lengkap dan data sebagai skrip SQL teks biasa.

Backup dalam Format Terkompresi (Direkomendasikan untuk Basis Data Besar)

pg_dump -Fc my_database > my_database_backup.dump

Format kustom (-Fc) menghasilkan file biner terkompresi dan mendukung restoration paralel.

Restore Basis Data dari Backup SQL

psql my_database < my_database_backup.sql

Restore dari Backup Format Kustom

pg_restore -d my_database my_database_backup.dump

Otomatisasi Backup dengan Pekerjaan Cron

Jadwalkan backup harian dengan menambahkan entri cron:

crontab -e

0 2 * * * pg_dump my_database > /var/backups/postgres/my_database_$(date +%F).sql

Ini menjalankan backup setiap hari pada pukul 2:00 pagi dan menambahkan tanggal ke nama file untuk versioning yang mudah.

9. Menghapus Tabel dan Basis Data

Ketika tabel atau basis data tidak lagi diperlukan, gunakan perintah DROP untuk menghapusnya secara permanen.

Hapus Tabel

DROP TABLE table_name;

Contoh

DROP TABLE employees;

Hapus Tabel Hanya Jika Ada (Sintaks Lebih Aman)

DROP TABLE IF EXISTS employees;

Hapus Basis Data

Anda harus memutuskan sambungan dari basis data target sebelum menghapusnya. Beralih ke basis data default postgres terlebih dahulu:

c postgres

Kemudian hapus target:

DROP DATABASE my_database;

> Peringatan: DROP DATABASE tidak dapat dibalikkan. Selalu verifikasi Anda memiliki backup terkini sebelum menjalankan perintah ini dalam produksi.

10. Referensi Meta-Command PostgreSQL yang Berguna

CLI psql mencakup satu set meta-command yang kaya (diawali dengan ) yang menyederhanakan navigasi dan inspeksi:

11. Keluar dari CLI PostgreSQL

Ketika Anda selesai dengan sesi Anda, keluar dengan bersih menggunakan:

q

Ini mengembalikan Anda ke