PostgreSql

psql -h [호스트주소] -p [포트번호] -U [사용자명] -d [데이터베이스명]

# 원격 서버(IP: 192.168.1.50)의 5432 포트, postgres 계정으로 mydb 데이터베이스에 접속
psql -h 192.168.1.50 -p 5432 -U postgres -d mydb

psql "postgresql://[사용자명]:[비밀번호]@[호스트주소]:[포트번호]/[데이터베이스명]?옵션"

# 비밀번호가 mypassword123인 경우 예시 (전체 문자열을 따옴표로 감싸는 것이 안전합니다)
psql "postgresql://postgres:mypassword123@192.168.1.50:5432/mydb"

# SSL 연결이 필수인 클라우드 DB 접속 시 (예: Supabase 등)
psql "postgresql://postgres:password@db.supabase.co:5432/postgres?sslmode=require"

# 형식: 호스트주소:포트번호:데이터베이스명:사용자명:비밀번호
192.168.1.50:5432:mydb:postgres:mypassword123

chmod 0600 ~/.pgpass

psql -h 192.168.1.50 -U postgres -d mydb

export PGPASSWORD='mypassword123'
psql -h 192.168.1.50 -U postgres -d mydb

# postgresql.conf
listen_addresses = '*'  # 기본값인 'localhost' 대신 '*'로 되어 있어야 외부 접속 가능

# pg_hba.conf 예시 (모든 IP 호스트에 대해 md5/scram-sha-256 인증 허용)
host    all             all             0.0.0.0/0               scram-sha-256

-- 1. 전용 사용자(Role) 생성 (비밀번호 포함)
CREATE USER jace_user WITH PASSWORD 'secure_password_123';

-- 2. 데이터베이스 생성 (소유자를 위에서 만든 사용자로 지정)
CREATE DATABASE my_project_db OWNER jace_user;

-- 데이터베이스 삭제 (연결된 세션이 없어야 함)
DROP DATABASE my_project_db;

-- 만약 다른 프로세스가 접속 중이라 삭제가 안 된다면, 강제 접속 종료 후 삭제
DROP DATABASE my_project_db WITH (FORCE);

-- 특정 사용자에게 해당 데이터베이스의 모든 권한 부여
GRANT ALL PRIVILEGES ON DATABASE my_project_db TO jace_user;

-- 특정 스키마(예: public) 내의 테이블 조회/수정 권한만 부여할 때
GRANT USAGE ON SCHEMA public TO jace_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO jace_user;

-- 특정 테이블의 권한을 회수
REVOKE INSERT, UPDATE ON TABLE users FROM jace_user;

-- 새 컬럼 추가
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

-- 컬럼 데이터 타입 변경
ALTER TABLE users ALTER COLUMN phone_number TYPE VARCHAR(30);

-- 컬럼 이름 변경
ALTER TABLE users RENAME COLUMN phone_number TO phone;

-- 컬럼 삭제
ALTER TABLE users DROP COLUMN phone;

-- 인덱스 생성 (Non-blocking 방식으로 운영 중인 서비스에 영향 없이 생성)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- 인덱스 삭제
DROP INDEX CONCURRENTLY idx_users_email;

# 특정 데이터베이스를 SQL 스크립트 파일로 백업
pg_dump -h localhost -U postgres -d my_project_db > backup.sql

# 압축된 커스텀 포맷으로 백업 (대용량 DB 추천, 복구가 빠름)
pg_dump -h localhost -U postgres -F c -d my_project_db -f backup.dump

# 1. SQL 스크립트 파일(.sql)로 복구할 때
psql -h localhost -U postgres -d new_project_db -f backup.sql

# 2. 커스텀 포맷 파일(.dump)로 복구할 때
pg_restore -h localhost -U postgres -d new_project_db -v backup.dump

-- 1. 쓰레기 데이터를 정리하고 성능 통계를 최적화 (운영 중 사용 가능)
VACUUM ANALYZE users;

-- 2. 데이터베이스 전체를 대상으로 실행
VACUUM ANALYZE;

-- 3. 디스크 물리 공간까지 완전히 OS로 반환 (테이블에 락이 걸리므로 서비스 점검 시에만 사용)
VACUUM FULL users;

-- 현재 실행 중인 활성 쿼리 및 실행 시간 확인
SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT LIKE '%pg_stat_activity%';

-- 특정 프로세스(PID) 안전하게 종료 (실행 중인 쿼리만 취소)
SELECT pg_cancel_backend([확인한_PID]);

-- 특정 프로세스(PID) 강제 종료 (커넥션 자체를 끊음)
SELECT pg_terminate_backend([확인한_PID]);

CREATE TABLESPACE limited_space LOCATION '/mnt/db_limit_10g';

CREATE DATABASE restricted_db TABLESPACE limited_space;

-- 1. 현재 DB의 용량이 5GB(5120MB)를 넘었는지 확인하는 함수 생성
CREATE OR REPLACE FUNCTION check_db_size_limit() 
RETURNS TRIGGER AS $$
DECLARE
    current_db_size_mb NUMERIC;
    limit_size_mb NUMERIC := 5120; -- 제한하고 싶은 용량 (MB 단위)
BEGIN
    -- 현재 접속 중인 DB의 용량 계산
    SELECT pg_database_size(current_database()) / 1024 / 1024 INTO current_db_size_mb;

    IF current_db_size_mb > limit_size_mb THEN
        RAISE EXCEPTION '데이터베이스 용량 제한(%MB)을 초과하여 데이터를 추가할 수 없습니다.', limit_size_mb;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. 용량이 크게 늘어날 수 있는 주요 테이블에 트리거 적용
CREATE TRIGGER trg_limit_size
BEFORE INSERT OR UPDATE ON heavy_table
FOR EACH ROW EXECUTE FUNCTION check_db_size_limit();

#!/bin/bash
# 현재 DB 용량(Byte)을 가져와 기준치(예: 10GB = 10737418240)와 비교
CURRENT_SIZE=$(psql -At -c "SELECT pg_database_size('my_db');")
LIMIT_SIZE=10737418240

if [ "$CURRENT_SIZE" -gt "$LIMIT_SIZE" ]; then
    # 용량 초과 시, 해당 DB를 읽기 전용(Connection 제한 등)으로 변경하거나 일반 유저의 INSERT 권한 회수
    psql -c "ALTER DATABASE my_db CONNECTION LIMIT 0;"
    # 또는 특정 유저의 권한 박탈
    # psql -d my_db -c "REVOKE INSERT, UPDATE ON ALL TABLES IN SCHEMA public FROM jace_user;"
fi