Cara Setup PostgreSQL Database di Linux

# Update package list
sudo apt update

# Install PostgreSQL
sudo apt install postgresql postgresql-contrib

# Check status
sudo systemctl status postgresql

# Start service
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 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

# Switch to postgres user
sudo -i -u postgres

# Access PostgreSQL prompt
psql

# Atau langsung
sudo -u postgres psql

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

# Connect as specific user
psql -U myuser -d mydb -h localhost

# Or with connection string
psql "postgresql://myuser:mypassword@localhost:5432/mydb"

-- 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
);

-- Insert data
INSERT INTO users (nama, email, umur)
VALUES ('Budi', 'budi@email.com', 25);

INSERT INTO users (nama, email, umur)
VALUES
    ('Ani', 'ani@email.com', 23),
    ('Citra', 'citra@email.com', 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;

-- 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;

-- 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;

-- 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;

-- 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'];

-- 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 ALL PRIVILEGES ON DATABASE mydb FROM someuser;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM someuser;

-- Drop user
DROP USER someuser;

# 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

# 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

# Reload config
sudo systemctl reload postgresql

# Restart service
sudo systemctl restart postgresql

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

-- 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
\?

-- 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'));