PostgreSQL adalah database relasional open-source yang powerful. Mari pelajari cara setup dan menggunakannya.

Install PostgreSQL

Ubuntu/Debian

# Update package list
sudo apt update
sudo apt install postgresql postgresql-contrib
Check status
sudo systemctl status postgresql
Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

Fedora/RHEL

# Install PostgreSQL
sudo dnf install postgresql-server postgresql-contrib
Initialize database
sudo postgresql-setup --initdb
Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

Initial Configuration

Access PostgreSQL

# Switch to postgres user
sudo -i -u postgres
Access PostgreSQL prompt
psql
Atau langsung
sudo -u postgres psql

Create Database and User

-- Create user
CREATE USER myuser WITH PASSWORD 'mypassword';
-- Create database
CREATE DATABASE mydb;
-- Grant privileges
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
-- Make user superuser (optional)
ALTER USER myuser WITH SUPERUSER;
-- List databases
\l
-- List users
\du
-- Exit
\q

Basic SQL Commands

Connect to Database

# Connect as specific user
psql -U myuser -d mydb -h localhost
Or with connection string
psql "postgresql://myuser:mypassword@localhost:5432/mydb"

Create Tables

-- Create table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    umur INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Create table with foreign key
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT,
user_id INTEGER REFERENCES users(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CRUD Operations

-- Insert data
INSERT INTO users (nama, email, umur)
VALUES ('Budi', '[email protected]', 25);
INSERT INTO users (nama, email, umur)
VALUES
('Ani', '[email protected]', 23),
('Citra', '[email protected]', 28);
-- Select data
SELECT 
 FROM users;
SELECT nama, email FROM users WHERE umur > 24;
SELECT  FROM users ORDER BY nama ASC;
SELECT * FROM users LIMIT 10 OFFSET 0;
-- Update data
UPDATE users SET umur = 26 WHERE nama = 'Budi';
-- Delete data
DELETE FROM users WHERE id = 1;

Advanced Queries

Joins

-- Inner join
SELECT users.nama, posts.title
FROM users
INNER JOIN posts ON users.id = posts.user_id;
-- Left join
SELECT users.nama, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;
-- Count with group
SELECT users.nama, COUNT(posts.id) as total_posts
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.nama;

Aggregate Functions

-- Count
SELECT COUNT(*) FROM users;
-- Sum, Avg, Min, Max
SELECT
SUM(umur) as total,
AVG(umur) as rata_rata,
MIN(umur) as termuda,
MAX(umur) as tertua
FROM users;
-- Group by
SELECT umur, COUNT(*) FROM users GROUP BY umur;

PostgreSQL Specific Features

JSON Support

-- Create table with JSON column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    metadata JSONB
);
-- Insert JSON data
INSERT INTO products (name, metadata)
VALUES ('Laptop', '{"brand": "Dell", "specs": {"ram": "16GB", "storage": "512GB"}}');
-- Query JSON
SELECT name, metadata->>'brand' as brand FROM products;
SELECT * FROM products WHERE metadata->>'brand' = 'Dell';
SELECT metadata->'specs'->>'ram' as ram FROM products;

Array Type

-- Create table with array
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    tags TEXT[]
);
-- Insert array
INSERT INTO articles (title, tags)
VALUES ('PostgreSQL Guide', ARRAY['database', 'sql', 'tutorial']);
-- Query array
SELECT 
 FROM articles WHERE 'database' = ANY(tags);
SELECT  FROM articles WHERE tags @> ARRAY['sql'];

User Management

Create Roles

-- Create role
CREATE ROLE readonlyuser WITH LOGIN PASSWORD 'password';
-- Grant read-only access
GRANT CONNECT ON DATABASE mydb TO readonlyuser;
GRANT USAGE ON SCHEMA public TO readonlyuser;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonlyuser;
-- For future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonlyuser;

Revoke Privileges

-- Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE mydb FROM someuser;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM someuser;
-- Drop user
DROP USER someuser;

Configuration

postgresql.conf

# Edit config file
sudo nano /etc/postgresql/16/main/postgresql.conf
Common settings
listen_addresses = 'localhost'  # atau '*' untuk remote
port = 5432
max_connections = 100
shared_buffers = 256MB
work_mem = 4MB

pg_hba.conf (Authentication)

# Edit authentication config
sudo nano /etc/postgresql/16/main/pg_hba.conf
Allow local connections
TYPE  DATABASE  USER  ADDRESS       METHOD
local   all       all                 peer
host    all       all   127.0.0.1/32  md5
host    all       all   ::1/128       md5
Allow remote (hati-hati!)
host    all       all   0.0.0.0/0     md5

Apply Changes

# Reload config
sudo systemctl reload postgresql
Restart service
sudo systemctl restart postgresql

Backup and Restore

Backup Database

# Backup single database
pg_dump -U postgres mydb > mydb_backup.sql
Backup with compression
pg_dump -U postgres -Fc mydb > mydb_backup.dump
Backup all databases
pg_dumpall -U postgres > all_databases.sql
Backup specific tables
pg_dump -U postgres -t users mydb > users_table.sql

Restore Database

# Restore from SQL file
psql -U postgres -d mydb < mydb_backup.sql
Restore from dump
pg_restore -U postgres -d mydb mydb_backup.dump
Create database then restore
createdb -U postgres newdb
pg_restore -U postgres -d newdb mydb_backup.dump

Useful Commands

psql Meta-Commands

-- List databases
\l
-- Connect to database
\c mydb
-- List tables
\dt
-- Describe table
\d users
-- List schemas
\dn
-- List functions
\df
-- Show current user
\conninfo
-- Execute SQL file
\i /path/to/file.sql
-- Export to CSV
\copy users TO '/tmp/users.csv' WITH CSV HEADER;
-- Help
\?

Performance Commands

-- Explain query plan
EXPLAIN SELECT * FROM users WHERE umur > 25;
EXPLAIN ANALYZE SELECT * FROM users WHERE umur > 25;
-- Create index
CREATE INDEX idx_users_umur ON users(umur);
CREATE INDEX idx_users_email ON users(email);
-- Check table size
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- Check database size
SELECT pg_size_pretty(pg_database_size('mydb'));

Kesimpulan

PostgreSQL adalah database yang powerful dengan banyak fitur advanced. Mulai dengan basic setup lalu explore fitur seperti JSON, arrays, dan full-text search.