Postgresql 개발자가 알아야힐 필수사항

미디어위키 양식으로 변경해 달라는 요청이셨군요! 제가 이전에 제공했던 내용을 미디어위키 문법에 맞춰 다시 정리해드리겠습니다. 아래 내용은 MediaWiki 문법을 사용했기 때문에, 일반 텍스트 편집기에서는 마크업이 그대로 보일 것입니다. MediaWiki 엔진이 설치된 위키에 붙여넣으면 적절히 렌더링될 것입니다.

PostgreSQL 개발자가 많이 찾는 검색어

PostgreSQL 개발자들이 자주 찾는 검색어는 특정 명령어보다는 데이터베이스 관리, 성능 최적화, 쿼리 작성, 특정 기능 구현, 그리고 문제 해결과 관련된 내용이 주를 이룹니다. 핵심적인 검색어들은 다음과 같은 범주로 나눌 수 있습니다.

1. 쿼리 작성 및 최적화 📈

postgresql insert multiple rows

간략 설명: 여러 개의 행 데이터를 한 번의 쿼리로 데이터베이스에 효율적으로 삽입하는 방법입니다. 단일 INSERT 문을 여러 번 실행하는 것보다 네트워크 오버헤드를 줄여 성능을 향상시킵니다.
사용 예시:

-- VALUES 절을 이용한 다중 행 삽입
INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.00),
('Keyboard', 75.00);

-- COPY 명령어를 이용한 대량 삽입 (파일로부터)
-- CSV 파일 (e.g., products.csv) 내용:
-- Laptop,1200.00
-- Mouse,25.00
-- Keyboard,75.00
--
-- SQL 명령 (PostgreSQL 클라이언트에서 실행):
-- COPY products (name, price) FROM '/path/to/products.csv' DELIMITER ',' CSV;

사용법:
- INSERT INTO table_name (column1, column2) VALUES (value1_1, value1_2), (value2_1, value2_2), ...; 형식으로 사용합니다.
- COPY table_name (column1, column2) FROM 'file_path' DELIMITER 'delimiter' CSV; 명령어는 대량 데이터 로딩 시 매우 효율적이며, 서버 측에서 파일에 접근할 수 있어야 합니다.

postgresql upsert (ON CONFLICT)

간략 설명: 데이터가 이미 존재하는 경우(충돌 발생) 업데이트하고, 존재하지 않는 경우 새로 삽입하는 연산입니다. INSERT ... ON CONFLICT 구문을 사용하여 구현합니다. 이는 "UPDATE or INSERT"의 줄임말로, 데이터 무결성을 유지하면서 유연하게 데이터를 처리할 수 있도록 돕습니다.
사용 예시:

-- users 테이블 (id, username, email)이 있고 username이 UNIQUE 제약조건을 가진다고 가정
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100)
);

-- 새로운 사용자 삽입 (충돌 없음)
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

-- 기존 사용자 업데이트 (username 'alice' 충돌 발생, email 업데이트)
INSERT INTO users (username, email) VALUES ('alice', 'alice_new@example.com')
ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email;

-- 아무것도 하지 않음 (충돌 발생 시 무시)
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com')
ON CONFLICT (username) DO NOTHING;

사용법:
- INSERT INTO table_name (columns) VALUES (values) ON CONFLICT (conflict_target) DO UPDATE SET column1 = EXCLUDED.column1, ...;
- conflict_target은 충돌을 감지할 컬럼(예: UNIQUE 인덱스 또는 PRIMARY KEY)을 지정합니다.
- EXCLUDED.column_name은 충돌을 일으킨(제외된) 행의 값을 참조합니다.
- 충돌 시 아무 작업도 하지 않으려면 DO NOTHING을 사용합니다.

postgresql jsonb query

간략 설명: PostgreSQL의 JSONB 데이터 타입은 JSON 데이터를 이진 형태로 저장하여 효율적인 쿼리 및 인덱싱을 가능하게 합니다. ->, ->>, #>, @>, ? 등 다양한 연산자를 사용하여 JSONB 필드 내의 특정 값에 접근하고 쿼리할 수 있습니다.
사용 예시:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    details JSONB
);

INSERT INTO products (details) VALUES
('{"name": "Laptop", "specs": {"cpu": "i7", "ram_gb": 16, "storage": "512GB SSD"}, "tags": ["electronics", "portable"]}'),
('{"name": "Mouse", "specs": {"type": "wireless", "dpi": 1600}, "tags": ["accessories", "input"]}');

-- 'name' 키의 값 추출 (JSONB -> text)
SELECT details->>'name' AS product_name FROM products;

-- 중첩된 객체의 'cpu' 값 추출
SELECT details->'specs'->>'cpu' AS cpu_type FROM products WHERE details->'specs'->>'cpu' IS NOT NULL;

-- 'tags' 배열에 'electronics'가 포함된 제품 찾기
SELECT details->>'name' FROM products WHERE details->'tags' @> '["electronics"]'::jsonb;

-- 특정 키가 존재하는지 확인 (JSONB ? text)
SELECT details->>'name' FROM products WHERE details ? 'tags';

사용법:
- jsonb_column -> 'key': JSONB 객체에서 'key'에 해당하는 값을 JSONB 타입으로 반환합니다.
- jsonb_column ->> 'key': JSONB 객체에서 'key'에 해당하는 값을 TEXT 타입으로 반환합니다.
- jsonb_column #> '{path,to,key}': JSONB 객체에서 지정된 경로의 값을 JSONB 타입으로 반환합니다.
- jsonb_column #>> '{path,to,key}': JSONB 객체에서 지정된 경로의 값을 TEXT 타입으로 반환합니다.
- jsonb_column @> 'jsonb_value': jsonb_column이 jsonb_value를 포함하는지 확인합니다. (containment)
- jsonb_column ? 'key': jsonb_column에 'key'가 존재하는지 확인합니다.

postgresql index types

간략 설명: PostgreSQL은 다양한 인덱스 타입을 제공하며, 각각 특정 유형의 쿼리 및 데이터 구조에 최적화되어 있습니다. 가장 일반적인 것은 B-tree 인덱스이며, GIN, GiST, BRIN 등은 특수 목적에 사용됩니다. 적절한 인덱스 선택은 쿼리 성능에 큰 영향을 미칩니다.
주요 인덱스 타입:
- B-tree: 가장 일반적인 인덱스 타입으로, 등호(=), 범위(>, <, >= 등), LIKE ('prefix%' 형태) 쿼리 등에 최적화되어 있습니다. 모든 데이터 타입에 사용됩니다.
- GIN (Generalized Inverted Index): 다중 값을 포함하는 컬럼(예: 배열, JSONB)에서 특정 값이 존재하는지 검색하는 데 효율적입니다. 전문 검색(Full-Text Search)에도 사용됩니다.
- GiST (Generalized Search Tree): 지리 정보 시스템(GIS) 데이터, 풀 텍스트 검색, 범위 데이터 등 복잡한 데이터 타입 및 비표준 검색 연산자에 사용됩니다.
- BRIN (Block Range Index): 물리적 위치가 유사한 대량의 데이터에 유용하며, 매우 작고 효율적이지만 특정 순서로 저장된 데이터에 가장 잘 작동합니다.
사용 예시:

-- B-tree 인덱스 생성 (기본값)
CREATE INDEX idx_users_username ON users (username);

-- JSONB 컬럼에 GIN 인덱스 생성 (jsonb_path_ops는 JSONB 내의 키/값 검색 최적화)
CREATE INDEX idx_products_details_gin ON products USING GIN (details jsonb_path_ops);

-- 특정 문자열이 포함된 검색을 위한 GIN 인덱스 (text_pattern_ops는 LIKE '%%' 최적화)
-- CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));
-- 또는 B-tree for LIKE 'prefix%'
-- CREATE INDEX idx_users_email_prefix ON users (email text_pattern_ops);

사용법:
- CREATE INDEX index_name ON table_name (column_name [operator_class]);
- CREATE INDEX index_name ON table_name USING index_type (column_name [operator_class]);

postgresql explain analyze

간략 설명: EXPLAIN ANALYZE 명령어는 SQL 쿼리의 실제 실행 계획을 보여주고, 각 단계에 소요된 시간과 자원 사용량을 상세하게 분석합니다. 이를 통해 쿼리 성능 병목 지점을 정확히 파악하고 최적화 방안을 모색할 수 있습니다.
사용 예시:

-- 특정 쿼리의 실행 계획 및 통계 분석
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25 AND city = 'New York';

사용법:
- EXPLAIN ANALYZE your_sql_query;
- 결과는 각 노드(예: Seq Scan, Index Scan, Hash Join 등)의 실행 시간, 비용, 행 수 등을 보여줍니다.
- 읽는 법: 출력은 일반적으로 가장 안쪽(오른쪽)의 노드부터 시작하여 위쪽(왼쪽)으로 진행하며, 하위 작업부터 상위 작업까지의 흐름을 보여줍니다. actual time은 실제 소요 시간, rows는 실제 처리된 행 수입니다.

postgresql full text search

간략 설명: 대량의 텍스트 데이터 내에서 단어나 구문을 효율적으로 검색하는 기능입니다. 텍스트를 tsvector (토큰화된 문서)로 변환하고, 검색 쿼리를 tsquery로 변환하여 두 벡터 간의 일치를 찾습니다.
사용 예시:

-- 테이블 생성 및 데이터 삽입
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT
);

INSERT INTO documents (title, content) VALUES
('PostgreSQL Basics', 'PostgreSQL is a powerful, open source object-relational database system.'),
('Full Text Search in PG', 'Learn how to perform full-text search using tsvector and tsquery in PostgreSQL.');

-- 텍스트 검색을 위한 인덱스 생성 (GIN 인덱스 사용)
CREATE INDEX idx_documents_content_fts ON documents USING GIN (to_tsvector('english', content));

-- 전문 검색 쿼리 실행
SELECT title, content
FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & search');

-- 구문 검색
SELECT title FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'open <-> source'); -- 'open' 바로 뒤에 'source'가 오는 경우

사용법:
- to_tsvector('config', text_column): 텍스트를 검색 가능한 tsvector로 변환합니다. 'config'는 언어(예: 'english', 'korean')를 지정합니다.
- to_tsquery('config', 'query_string'): 검색어를 tsquery로 변환합니다. & (AND), | (OR), ! (NOT), <-> (인접) 등의 연산자를 사용할 수 있습니다.
- @@: tsvector와 tsquery 간의 일치를 확인하는 연산자입니다.

postgresql window functions

간략 설명: 쿼리 결과 집합 내에서 행 그룹에 대해 계산을 수행하는 함수입니다. OVER 절을 사용하여 윈도우(행 그룹)를 정의하며, 집계 함수(SUM, AVG), 순위 함수(ROW_NUMBER, RANK), 분석 함수(LAG, LEAD) 등으로 다양하게 활용됩니다. 각 행이 독립적으로 처리되면서도 그룹 내의 다른 행을 참조할 수 있어 복잡한 보고서나 분석 쿼리에 매우 유용합니다.
사용 예시:

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    region VARCHAR(50),
    sale_date DATE,
    amount NUMERIC(10, 2)
);

INSERT INTO sales (region, sale_date, amount) VALUES
('East', '2023-01-01', 100),
('West', '2023-01-01', 150),
('East', '2023-01-02', 120),
('West', '2023-01-02', 180),
('East', '2023-01-03', 90),
('West', '2023-01-03', 200);

-- 각 판매에 대한 지역별 판매 순위 (amount 기준)
SELECT
    region,
    sale_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) as rank_in_region
FROM sales;

-- 각 지역의 전날 판매액 (LAG)
SELECT
    region,
    sale_date,
    amount,
    LAG(amount, 1, 0) OVER (PARTITION BY region ORDER BY sale_date) as prev_day_amount
FROM sales;

사용법:
- window_function() OVER ([PARTITION BY column1, ...] [ORDER BY column2 [ASC|DESC], ...])
- PARTITION BY: 결과 집합을 파티션(그룹)으로 나눕니다. 각 파티션은 독립적으로 처리됩니다.
- ORDER BY: 파티션 내의 행 순서를 정의합니다. 순위 함수나 분석 함수에 중요합니다.

postgresql common table expression (CTE) (WITH 절)

간략 설명: WITH 절은 복잡한 쿼리를 여러 개의 더 작고 읽기 쉬운 명명된 하위 쿼리(CTE)로 분해하는 데 사용됩니다. CTE는 한 번 정의되면 메인 쿼리 또는 다른 CTE 내에서 여러 번 참조될 수 있어 쿼리의 가독성과 재사용성을 높입니다.
사용 예시:

-- CTE를 사용하여 각 지역의 총 판매액을 계산한 후, 이를 기반으로 총 판매액이 특정 값 이상인 지역을 필터링
WITH RegionalSales AS (
    SELECT
        region,
        SUM(amount) as total_sales
    FROM sales
    GROUP BY region
)
SELECT
    region,
    total_sales
FROM RegionalSales
WHERE total_sales > 300;

-- 여러 CTE 체이닝 (하나의 CTE가 다른 CTE를 참조)
WITH CustomerOrders AS (
    SELECT customer_id, COUNT(*) AS num_orders
    FROM orders
    GROUP BY customer_id
),
HighValueCustomers AS (
    SELECT customer_id
    FROM CustomerOrders
    WHERE num_orders > 5
)
SELECT c.name, h.num_orders
FROM customers c
JOIN HighValueCustomers h ON c.id = h.customer_id;

사용법:
- WITH cte_name AS (SELECT ...), another_cte AS (SELECT ...) SELECT ... FROM cte_name ...;
- CTE는 메인 쿼리 또는 다른 CTE 내에서 테이블처럼 참조될 수 있습니다.
- CTE는 재귀적으로 자신을 참조할 수도 있습니다 (WITH RECURSIVE).

2. 데이터베이스 관리 및 설정 ⚙️

postgresql create user database

간략 설명: PostgreSQL에서 새로운 데이터베이스와 해당 데이터베이스에 접근할 수 있는 사용자를 생성하고, 필요한 권한을 부여하는 과정입니다. 보안과 관리 효율성을 위해 각 애플리케이션 또는 서비스에 전용 데이터베이스와 사용자를 할당하는 것이 일반적입니다.
사용 예시:

-- 1. 새 사용자 생성 (암호 설정)
CREATE USER myappuser WITH PASSWORD 'strong_password';

-- 2. 새 데이터베이스 생성 (소유자 지정)
CREATE DATABASE myapp_db OWNER myappuser;

-- 3. (선택 사항) 특정 사용자에게 데이터베이스의 모든 권한 부여
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myappuser;

-- 4. (선택 사항) 데이터베이스에 연결하여 해당 사용자에게 스키마 및 테이블 생성 권한 부여
--    (psql에서 'myapp_db'로 연결 후)
--    GRANT ALL PRIVILEGES ON SCHEMA public TO myappuser;
--    ALTER DEFAULT PRIVILEGES FOR USER myappuser IN SCHEMA public GRANT ALL ON TABLES TO myappuser;
--    ALTER DEFAULT PRIVILEGES FOR USER myappuser IN SCHEMA public GRANT ALL ON SEQUENCES TO myappuser;

사용법:
- CREATE USER username WITH PASSWORD 'password';
- CREATE DATABASE database_name OWNER username;
- GRANT privileges ON object TO user;

postgresql alter table add column

간략 설명: 이미 존재하는 테이블에 새로운 컬럼을 추가하거나, 기존 컬럼의 속성을 변경하는 DDL(Data Definition Language) 명령어입니다. 데이터베이스 스키마를 업데이트할 때 사용됩니다.
사용 예시:

-- products 테이블에 'stock_quantity' 컬럼 추가 (INTEGER 타입, 기본값 0)
ALTER TABLE products
ADD COLUMN stock_quantity INTEGER DEFAULT 0;

-- users 테이블의 'email' 컬럼 길이를 변경 (TEXT로 변경)
ALTER TABLE users
ALTER COLUMN email TYPE TEXT;

-- users 테이블의 'email' 컬럼에 NOT NULL 제약조건 추가
ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

-- products 테이블에 UNIQUE 제약조건 추가 (name 컬럼)
ALTER TABLE products
ADD CONSTRAINT unique_product_name UNIQUE (name);

사용법:
- ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];
- ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;
- ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL; / DROP NOT NULL;
- ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

postgresql backup restore (pg_dump, pg_restore)

간략 설명: PostgreSQL 데이터베이스의 데이터를 안전하게 보존하고, 필요할 때 다시 복원하는 방법입니다. pg_dump는 데이터베이스를 파일로 백업하고, pg_restore는 백업 파일을 데이터베이스로 복원합니다.
사용 예시:

# 1. 단일 데이터베이스 백업 (모든 데이터, 스키마, 인덱스 등)
pg_dump -U your_user -d your_database -Fp > your_database_backup.sql
# -U: 사용자, -d: 데이터베이스 이름, -Fp: plain text 형식 (SQL 스크립트)

# 2. 특정 테이블만 백업
pg_dump -U your_user -d your_database -Ft -t your_table_name > your_table_backup.tar
# -Ft: tar 형식 (커스텀 또는 디렉토리 형식을 선호)

# 3. 데이터베이스 복원 (새 데이터베이스 생성 후)
# 먼저 빈 데이터베이스 생성: CREATE DATABASE new_database OWNER your_user;
psql -U your_user -d new_database < your_database_backup.sql

# 4. tar 또는 custom 형식 백업 파일 복원 (pg_restore 사용)
pg_restore -U your_user -d new_database your_table_backup.tar

사용법:
- pg_dump [options] dbname > filename: 데이터베이스를 SQL 스크립트 또는 다른 형식으로 백업합니다.
- pg_restore [options] filename: pg_dump로 생성된 비-plain text 형식의 백업을 복원합니다.
- psql -d dbname < filename: plain text (SQL 스크립트) 백업을 복원합니다.

postgresql autovacuum tuning

간략 설명: PostgreSQL은 MVCC(Multi-Version Concurrency Control) 아키텍처를 사용합니다. 데이터가 업데이트되거나 삭제될 때 즉시 공간이 해제되지 않고 "죽은 튜플"(dead tuples)로 남게 됩니다. VACUUM은 이 죽은 튜플 공간을 회수하여 재사용 가능하게 하고, 트랜잭션 ID 랩어라운드(wraparound) 문제를 방지합니다. autovacuum은 이 작업을 자동으로 수행하도록 설정된 백그라운드 프로세스입니다. 적절한 튜닝은 데이터베이스 성능과 안정성에 필수적입니다.
주요 설정 (postgresql.conf):
- autovacuum = on: (기본값) 자동 VACUUM 활성화.
- log_autovacuum_min_duration = -1: 자동 VACUUM 로깅 임계값 (0은 모든 VACUUM 작업 로깅, -1은 로깅 비활성화). 튜닝 시 유용.
- autovacuum_max_workers = 3: 동시에 실행될 수 있는 자동 VACUUM 워커의 최대 개수.
- autovacuum_vacuum_scale_factor = 0.2: 테이블 크기 대비 VACUUM이 실행되는 기준 (예: 테이블의 20%가 변경되면).
- autovacuum_vacuum_threshold = 50: VACUUM이 실행되는 최소 변경 행 수.
- autovacuum_analyze_scale_factor = 0.1: 테이블 크기 대비 ANALYZE가 실행되는 기준.
- autovacuum_analyze_threshold = 50: ANALYZE가 실행되는 최소 변경 행 수.
사용 예시 (튜닝은 서버 설정 파일에서 이루어짐):

-- 특정 테이블의 autovacuum 설정 변경 (테이블 수준 설정)
ALTER TABLE my_large_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_vacuum_threshold = 1000);

-- autovacuum 관련 통계 확인
SELECT relname, last_autovacuum, last_autoanalyze, autovacuum_count, analyze_count
FROM pg_stat_all_tables
WHERE schemaname = 'public';

사용법:
- postgresql.conf 파일을 직접 수정하거나, ALTER SYSTEM SET parameter = 'value'; 명령어를 사용합니다. 변경 후에는 PostgreSQL 서버를 재시작하거나 pg_ctl reload로 설정을 다시 로드해야 합니다.
- ANALYZE는 옵티마이저가 최적의 쿼리 계획을 세우는 데 필요한 통계 정보를 업데이트합니다.

postgresql conf file location

간략 설명: postgresql.conf는 PostgreSQL 서버의 주요 설정 파일입니다. 데이터베이스의 동작, 성능, 보안, 로깅 등 거의 모든 측면을 제어하는 파라미터들이 여기에 정의되어 있습니다. 이 파일의 위치는 운영체제 및 설치 방식에 따라 다릅니다.
일반적인 위치 및 확인 방법:
- Linux/macOS (패키지 관리자를 통해 설치 시): /etc/postgresql/<version>/main/postgresql.conf 또는 /var/lib/pgsql/data/postgresql.conf
- Windows: PostgreSQL 설치 디렉토리 내의 data 폴더 (예: C:\Program Files\PostgreSQL\<version>\data\postgresql.conf)
- 확인 방법 (SQL 쿼리): PostgreSQL 클라이언트(psql 등)에 연결한 후 다음 쿼리를 실행하여 현재 설정 파일의 경로를 확인할 수 있습니다.

SHOW config_file;

주요 설정 파라미터 (간략 설명):
- listen_addresses: 서버가 수신할 IP 주소 (기본값 'localhost' - 외부 연결 허용 안 함).
- port: 서버가 수신할 포트 번호 (기본값 5432).
- max_connections: 동시 연결 가능한 최대 클라이언트 수.
- shared_buffers: 데이터베이스 캐시로 사용될 공유 메모리 양. 성능에 매우 중요.
- work_mem: 복잡한 정렬, 해시 테이블 작업 등에 사용되는 메모리 양.
- maintenance_work_mem: VACUUM, 인덱스 생성 등에 사용되는 메모리 양.
- wal_level: WAL(Write-Ahead Log)의 상세 수준. 복제 및 백업에 영향.
- log_destination, logging_collector, log_filename: 로깅 설정.

3. 에러 해결 및 문제 진단 🚨

postgresql "FATAL: password authentication failed for user"

간략 설명: 이 에러는 PostgreSQL 데이터베이스에 연결을 시도할 때 사용자 이름이나 비밀번호가 올바르지 않거나, 클라이언트의 IP 주소에서 해당 사용자의 접속이 허용되지 않았을 때 발생합니다. 이는 가장 흔한 연결 오류 중 하나입니다.
주요 원인 및 해결 방법:

잘못된 사용자 이름 또는 비밀번호:
1. 입력한 사용자 이름과 비밀번호가 정확한지 다시 확인합니다.
2. psql에서 \du 명령어를 사용하여 사용자와 역할을 확인할 수 있습니다.
pg_hba.conf 설정:
1. PostgreSQL은 pg_hba.conf 파일을 사용하여 어떤 사용자가 어떤 데이터베이스에 어떤 IP 주소에서 어떤 인증 방식으로 연결할 수 있는지 정의합니다.
2. 위치: postgresql.conf 파일과 동일한 디렉토리에 있습니다 (SHOW hba_file;로 확인 가능).
3. 변경 예시:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5  -- 로컬 연결 허용 (암호화된 암호)
# host    all             all             0.0.0.0/0               md5  -- 모든 IP에서 연결 허용 (보안에 주의!)

1. pg_hba.conf 파일을 수정한 후에는 PostgreSQL 서버를 재시작하거나 pg_ctl reload 명령어로 설정을 다시 로드해야 합니다.
사용자 권한 문제:
1. 사용자가 연결하려는 데이터베이스에 CONNECT 권한이 없을 수도 있습니다. GRANT CONNECT ON DATABASE your_database TO your_user; 명령어로 권한을 부여할 수 있습니다.

사용법: pg_hba.conf 파일을 텍스트 편집기로 열어 적절한 연결 규칙을 추가하거나 수정합니다. 변경 후에는 PostgreSQL 서버를 재시작해야 합니다.

postgresql "ERROR: relation does not exist"

간략 설명: 이 에러는 쿼리에서 참조하는 테이블, 뷰, 시퀀스 등과 같은 "릴레이션"이 현재 데이터베이스 또는 스키마에 존재하지 않을 때 발생합니다. 이는 일반적으로 오타, 잘못된 스키마 지정, 또는 객체가 실제로 생성되지 않았을 때 나타납니다.
주요 원인 및 해결 방법:

오타:
1. 가장 흔한 원인입니다. 테이블, 컬럼, 스키마 이름에 오타가 없는지 철자를 다시 확인합니다.
잘못된 스키마:
1. PostgreSQL은 기본적으로 public 스키마를 사용하지만, 다른 스키마에 객체가 있는 경우 명시적으로 스키마 이름을 지정해야 합니다 (예: myschema.my_table).
2. 현재 세션의 검색 경로(search_path)를 확인하세요: SHOW search_path;
3. 검색 경로에 스키마를 추가할 수 있습니다: SET search_path TO myschema, public;
객체 미생성:
1. 해당 테이블이나 객체가 실제로 데이터베이스에 생성되었는지 확인합니다.
2. \dt (테이블 목록), \dv (뷰 목록) 등 psql 명령어를 사용하여 객체 존재 여부를 확인할 수 있습니다.
대소문자 문제:
1. PostgreSQL은 기본적으로 식별자(테이블, 컬럼 이름)를 소문자로 변환합니다. 만약 식별자를 큰따옴표(")로 묶어 대소문자를 유지했다면, 쿼리할 때도 정확히 큰따옴표로 묶어 대소문자를 맞춰야 합니다 (예: "MyTable").

postgresql connection refused

간략 설명: 이 에러는 클라이언트가 PostgreSQL 서버에 연결을 시도했지만, 서버가 해당 연결 요청을 거부했음을 의미합니다. 서버가 실행되고 있지 않거나, 잘못된 포트로 연결을 시도하거나, 방화벽 문제 등 다양한 이유로 발생할 수 있습니다.
주요 원인 및 해결 방법:

PostgreSQL 서버 미실행:
1. 가장 흔한 원인입니다. PostgreSQL 서버 프로세스가 실행 중인지 확인합니다.
2. Linux: sudo systemctl status postgresql (또는 해당 배포판의 서비스 관리 명령어)
3. Windows: 서비스 관리자에서 PostgreSQL 서비스 확인
잘못된 호스트/포트:
1. 클라이언트에서 연결하려는 호스트 주소(IP)와 포트 번호가 PostgreSQL 서버의 listen_addresses 및 port 설정과 일치하는지 확인합니다.
2. postgresql.conf 파일에서 listen_addresses와 port 설정을 확인하세요. listen_addresses = '*'는 모든 IP에서 연결을 허용합니다 (보안에 주의).
방화벽:
1. 서버 또는 클라이언트 측 방화벽이 PostgreSQL의 기본 포트(5432) 또는 설정된 포트의 통신을 차단하고 있을 수 있습니다.
2. 방화벽 규칙을 추가하여 PostgreSQL 포트를 허용해야 합니다.
pg_hba.conf 설정:
1. FATAL: password authentication failed 에러와 유사하게, pg_hba.conf 파일에 클라이언트의 IP 주소로부터의 연결을 허용하는 규칙이 없으면 연결이 거부될 수 있습니다. host 규칙이 올바르게 설정되었는지 확인합니다.
네트워크 문제:
1. 클라이언트와 서버 간의 네트워크 연결 자체에 문제가 있을 수 있습니다. ping 명령어로 서버에 도달 가능한지 확인해보세요.

postgresql logs location

간략 설명: PostgreSQL은 서버의 활동, 오류, 경고, 쿼리 실행 정보 등을 로그 파일에 기록합니다. 이 로그 파일들은 문제 진단, 성능 분석, 보안 감사 등에 매우 중요합니다. 로그 파일의 위치와 로깅 설정은 postgresql.conf 파일에서 구성합니다.
로그 위치 확인 및 설정:
- 로그 위치 확인 (SQL 쿼리):

SHOW log_directory; -- 로그 파일이 저장되는 디렉토리
SHOW log_filename;  -- 로그 파일 이름 형식 (예: 'postgresql-%Y-%m-%d_%H%M%S.log')

- postgresql.conf 주요 로깅 설정:
  - log_destination = 'stderr' 또는 'csvlog': 로그 출력 대상 (표준 에러 출력, CSV 파일 등). csvlog는 분석에 편리한 CSV 형식으로 기록합니다.
  - logging_collector = on: 로그를 별도의 파일로 수집할지 여부. 일반적으로 on으로 설정합니다.
  - log_directory = 'log': 로그 파일이 저장될 디렉토리 (상대 경로는 데이터 디렉토리 기준).
  - log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log': 로그 파일의 이름 형식.
  - log_min_duration_statement = -1: 특정 시간(밀리초) 이상 걸린 모든 쿼리를 로깅합니다. 성능 문제 진단에 매우 유용합니다. (0은 모든 쿼리 로깅, -1은 비활성화).
  - log_statement = 'none': 모든 쿼리 로깅 여부 ('none', 'ddl', 'mod', 'all').
  - log_connections = on, log_disconnections = on: 연결 및 연결 해제 이벤트 로깅.
사용법:
- 로그 파일은 해당 서버의 파일 시스템에서 직접 접근하여 텍스트 편집기나 로그 분석 도구로 확인합니다.
- log_min_duration_statement를 설정하여 느린 쿼리를 식별하고 최적화하는 데 활용합니다.

4. 고급 기능 및 확장 🚀

postgresql stored procedure function

간략 설명: PostgreSQL은 데이터베이스 내에서 실행되는 코드 블록인 함수(Functions)를 지원합니다. 다른 RDBMS의 저장 프로시저와 유사하게 사용될 수 있습니다. PL/pgSQL과 같은 절차 언어로 작성되며, 복잡한 로직을 데이터베이스 계층에서 처리하여 네트워크 왕복 횟수를 줄이고 보안을 강화할 수 있습니다.
함수 vs 프로시저 (PostgreSQL 11+에서 PROCEDURE 도입):
- 함수 (Functions): 주로 값을 반환하며, SELECT 문 내에서 호출될 수 있습니다. 트랜잭션 내에서 실행되며, COMMIT 또는 ROLLBACK을 직접 호출할 수 없습니다.
- 프로시저 (Procedures): (PostgreSQL 11부터) 값을 반환하지 않거나 여러 개의 OUT 매개변수를 통해 반환할 수 있으며, CALL 명령어로 호출됩니다. 트랜잭션을 직접 제어(COMMIT, ROLLBACK)할 수 있습니다.
사용 예시 (PL/pgSQL 함수):

-- 간단한 함수 생성 (두 숫자를 더하는 함수)
CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

-- 함수 호출
SELECT add_numbers(5, 7);

-- 사용자 생성 및 관련 정보 삽입 (트랜잭션 제어 예시 - 프로시저에서 더 일반적)
CREATE OR REPLACE FUNCTION create_user_with_log(p_username VARCHAR, p_email VARCHAR)
RETURNS INT AS $$
DECLARE
    new_user_id INT;
BEGIN
    INSERT INTO users (username, email) VALUES (p_username, p_email) RETURNING id INTO new_user_id;
    INSERT INTO user_logs (user_id, action) VALUES (new_user_id, 'user created');
    RETURN new_user_id;
END;
$$ LANGUAGE plpgsql;

-- 프로시저 생성 (PostgreSQL 11 이상)
CREATE OR REPLACE PROCEDURE update_product_price(product_id INT, new_price NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE products SET price = new_price WHERE id = product_id;
    COMMIT; -- 프로시저 내에서 트랜잭션 커밋
END;
$$;

-- 프로시저 호출
-- CALL update_product_price(1, 1250.00);

사용법:
- CREATE FUNCTION function_name(param_list) RETURNS return_type LANGUAGE plpgsql AS $$...$$;
- CREATE PROCEDURE procedure_name(param_list) LANGUAGE plpgsql AS $$...$$;
- 함수는 SELECT function_name(...)으로 호출, 프로시저는 CALL procedure_name(...)으로 호출합니다.

postgresql extensions

간략 설명: PostgreSQL은 확장을 통해 핵심 기능에 새로운 데이터 타입, 함수, 연산자 등을 추가하여 기능을 강화할 수 있습니다. 이는 모듈식 아키텍처 덕분이며, 특정 요구사항에 맞춰 데이터베이스를 커스터마이징하고 성능을 개선하는 데 매우 유용합니다.
주요 확장 예시:
- PostGIS: 지리 정보 시스템(GIS) 기능을 추가하여 공간 데이터 저장, 쿼리, 분석을 가능하게 합니다. (예: 위도, 경도 기반 검색)
- pg_stat_statements: 서버가 실행한 모든 쿼리에 대한 통계를 수집하여 가장 느린 쿼리나 가장 자주 실행되는 쿼리를 식별하는 데 도움을 줍니다. 쿼리 성능 분석에 필수적입니다.
- uuid-ossp: UUID(Universally Unique Identifier)를 생성하는 함수를 제공합니다. 분산 시스템에서 고유한 ID를 생성할 때 유용합니다.
- dblink: 다른 PostgreSQL 데이터베이스에 연결하여 쿼리를 실행할 수 있도록 합니다.
- hstore: 키-값 쌍을 저장하는 데이터 타입을 제공합니다. JSONB의 전신 격이며, JSONB보다 가벼운 키-값 저장이 필요할 때 사용될 수 있습니다.
- pg_repack: 온라인 상태에서 테이블과 인덱스를 재구성하여 팽창(bloat)을 제거하고 성능을 복구합니다.
설치 및 사용 예시 (pg_stat_statements):

postgresql.conf 수정: shared_preload_libraries = 'pg_stat_statements'를 추가하고 서버 재시작.
데이터베이스에서 확장 생성:

CREATE EXTENSION pg_stat_statements;

사용 예시 (가장 많이 실행된 쿼리 확인):

SELECT query, calls, total_time, mean_time, min_time, max_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

사용법:
- 대부분의 확장은 CREATE EXTENSION extension_name; 명령어로 데이터베이스 내에서 활성화됩니다. 일부 확장은 postgresql.conf에서 shared_preload_libraries 설정을 변경하고 서버를 재시작해야 할 수 있습니다.

postgresql foreign data wrapper (FDW)

간략 설명: FDW는 PostgreSQL이 외부 데이터 소스(다른 데이터베이스, CSV 파일, 웹 서비스 등)에 연결하여 마치 로컬 테이블처럼 데이터를 쿼리할 수 있도록 하는 기능입니다. 이는 데이터 통합 및 연합 쿼리에 매우 유용합니다.
사용 예시 (다른 PostgreSQL 서버의 데이터에 접근):

FDW 확장 설치:

CREATE EXTENSION postgres_fdw;

외부 서버 정의:

CREATE SERVER foreign_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'foreign_db_host', port '5432', dbname 'foreign_database');

사용자 매핑 생성 (외부 서버에 연결할 때 사용할 인증 정보):

CREATE USER MAPPING FOR current_user -- 또는 특정 사용자
    SERVER foreign_server
    OPTIONS (user 'foreign_user', password 'foreign_password');

외부 테이블 임포트 또는 직접 생성:

-- 특정 스키마의 모든 테이블 임포트
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server INTO local_schema;

-- 또는 특정 외부 테이블에 대한 FOREIGN TABLE 생성
CREATE FOREIGN TABLE foreign_products (
    id INT,
    name VARCHAR(100),
    price NUMERIC(10, 2)
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'products');

쿼리 실행 (로컬 테이블처럼 사용):

SELECT * FROM foreign_products WHERE price > 1000;

사용법:
- CREATE EXTENSION foreign_data_wrapper_name;
- CREATE SERVER server_name FOREIGN DATA WRAPPER fdw_name OPTIONS (...);
- CREATE USER MAPPING FOR user SERVER server_name OPTIONS (...);
- CREATE FOREIGN TABLE table_name (...) SERVER server_name OPTIONS (...);

5. 클라이언트 및 ORM 🛠️

python psycopg2 connect

간략 설명: psycopg2는 Python에서 PostgreSQL 데이터베이스에 연결하고 SQL 쿼리를 실행하기 위한 가장 널리 사용되는 어댑터(라이브러리)입니다. SQL 쿼리를 직접 작성하고 실행하여 데이터베이스와 상호작용할 수 있습니다.
설치 방법:

pip install psycopg2-binary

사용 예시:

import psycopg2
from psycopg2 import Error

try:
    # 데이터베이스 연결
    connection = psycopg2.connect(
        user="your_user",
        password="your_password",
        host="127.0.0.1",
        port="5432",
        database="your_database"
    )

    cursor = connection.cursor()

    # 쿼리 실행 예시: 테이블 생성
    create_table_query = """
    CREATE TABLE IF NOT EXISTS employees (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        hire_date DATE DEFAULT CURRENT_DATE
    );
    """
    cursor.execute(create_table_query)
    connection.commit() # 변경사항 커밋
    print("Table 'employees' created successfully.")

    # 쿼리 실행 예시: 데이터 삽입
    insert_query = """
    INSERT INTO employees (name) VALUES ('John Doe');
    INSERT INTO employees (name, hire_date) VALUES (%s, %s);
    """
    cursor.execute(insert_query, ('Jane Smith', '2023-01-15'))
    connection.commit()
    print("Data inserted successfully.")

    # 쿼리 실행 예시: 데이터 조회
    select_query = "SELECT id, name, hire_date FROM employees;"
    cursor.execute(select_query)
    records = cursor.fetchall() # 모든 결과 가져오기
    print("\nRecords from employees table:")
    for row in records:
        print(f"ID: {row[0]}, Name: {row[1]}, Hire Date: {row[2]}")

except Error as e:
    print(f"Error while connecting to PostgreSQL or executing query: {e}")
finally:
    # 연결 종료
    if connection:
        cursor.close()
        connection.close()
        print("PostgreSQL connection closed.")

사용법:

psycopg2.connect()로 데이터베이스에 연결합니다.
connection.cursor()로 커서 객체를 생성합니다.
cursor.execute(sql_query, [parameters])로 SQL 쿼리를 실행합니다. 매개변수는 SQL 인젝션 방지를 위해 항상 플레이스홀더(%s)와 튜플로 전달합니다.
connection.commit()으로 변경사항을 데이터베이스에 반영하고, connection.rollback()으로 변경사항을 되돌릴 수 있습니다.
cursor.fetchall(), cursor.fetchone(), cursor.fetchmany()로 쿼리 결과를 가져옵니다.
작업 완료 후 cursor.close()와 connection.close()로 자원을 해제합니다.

nodejs pg connect

간략 설명: pg는 Node.js에서 PostgreSQL 데이터베이스에 연결하고 비동기적으로 쿼리를 실행하기 위한 클라이언트 라이브러리입니다. 콜백, 프로미스, async/await 패턴을 모두 지원하여 Node.js 환경에 적합합니다.
설치 방법:

npm install pg

사용 예시 (async/await):

// app.js
const { Client } = require('pg');

async function connectAndQuery() {
    const client = new Client({
        user: 'your_user',
        host: '127.0.0.1',
        database: 'your_database',
        password: 'your_password',
        port: 5432,
    });

    try {
        await client.connect(); // 데이터베이스 연결
        console.log('Connected to PostgreSQL database');

        // 테이블 생성 (CREATE TABLE IF NOT EXISTS)
        const createTableQuery = `
            CREATE TABLE IF NOT EXISTS products (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                price NUMERIC(10, 2)
            );
        `;
        await client.query(createTableQuery);
        console.log('Table "products" ensured.');

        // 데이터 삽입
        const insertQuery = 'INSERT INTO products (name, price) VALUES ($1, $2) RETURNING id;';
        const resInsert = await client.query(insertQuery, ['Laptop', 1200.50]);
        console.log(`Inserted product with ID: ${resInsert.rows[0].id}`);

        // 데이터 조회
        const selectQuery = 'SELECT * FROM products WHERE price > $1;';
        const resSelect = await client.query(selectQuery, [100]);
        console.log('\nProducts (price > 100):');
        resSelect.rows.forEach(row => {
            console.log(`ID: ${row.id}, Name: ${row.name}, Price: ${row.price}`);
        });

    } catch (err) {
        console.error('Database operation failed:', err);
    } finally {
        await client.end(); // 연결 종료
        console.log('PostgreSQL connection closed.');
    }
}

connectAndQuery();

사용법:

new Client(config)로 클라이언트 인스턴스를 생성합니다. config 객체에 연결 정보를 포함합니다.
client.connect()로 데이터베이스에 연결합니다.
client.query(sql_query, [values])로 SQL 쿼리를 실행합니다. 매개변수는 $1, $2와 같은 플레이스홀더를 사용하고 배열로 전달합니다.
client.end()로 연결을 종료합니다.
풀링(const { Pool } = require('pg');)을 사용하여 여러 클라이언트 연결을 효율적으로 관리할 수도 있습니다.

java jdbc postgresql

간략 설명: JDBC(Java Database Connectivity)는 Java 애플리케이션이 다양한 관계형 데이터베이스와 상호작용하기 위한 표준 API입니다. PostgreSQL의 경우 PostgreSQL JDBC Driver (PgJDBC)를 사용하여 연결합니다. JDBC는 SQL 쿼리를 직접 실행하여 데이터베이스 작업을 수행합니다.
설치 방법:

PgJDBC 드라이버 JAR 파일을 다운로드합니다. PostgreSQL JDBC Driver 공식 페이지에서 다운로드할 수 있습니다.
다운로드한 JAR 파일을 프로젝트의 클래스패스에 추가합니다. (Maven/Gradle 사용 시 pom.xml 또는 build.gradle에 의존성 추가)

- Maven:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.3</version> </dependency>

사용 예시:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class PgJdbcExample {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/your_database";
        String user = "your_user";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to the PostgreSQL database successfully!");

            // 1. 테이블 생성
            String createTableSQL = "CREATE TABLE IF NOT EXISTS employees ("
                                  + "id SERIAL PRIMARY KEY,"
                                  + "name VARCHAR(100) NOT NULL,"
                                  + "salary NUMERIC(10, 2)"
                                  + ");";
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createTableSQL);
                System.out.println("Table 'employees' ensured.");
            }

            // 2. 데이터 삽입 (PreparedStatement 사용 - SQL 인젝션 방지)
            String insertSQL = "INSERT INTO employees (name, salary) VALUES (?, ?);";
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                pstmt.setString(1, "Alice");
                pstmt.setDouble(2, 60000.00);
                pstmt.executeUpdate();

                pstmt.setString(1, "Bob");
                pstmt.setDouble(2, 75000.50);
                pstmt.executeUpdate();
                System.out.println("Data inserted successfully.");
            }

            // 3. 데이터 조회
            String selectSQL = "SELECT id, name, salary FROM employees WHERE salary > ?;";
            try (PreparedStatement pstmt = conn.prepareStatement(selectSQL)) {
                pstmt.setDouble(1, 70000.00);
                try (ResultSet rs = pstmt.executeQuery()) {
                    System.out.println("\nEmployees with salary > 70000:");
                    while (rs.next()) {
                        int id = rs.getInt("id");
                        String name = rs.getString("name");
                        double salary = rs.getDouble("salary");
                        System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
                    }
                }
            }

        } catch (SQLException e) {
            System.err.println("Database error occurred: " + e.getMessage());
        }
    }
}

사용법:

DriverManager.getConnection() 메서드를 사용하여 데이터베이스에 연결합니다.
Connection 객체에서 Statement 또는 PreparedStatement 객체를 생성합니다. PreparedStatement는 SQL 인젝션 방지 및 성능 향상에 권장됩니다.
execute(), executeUpdate() (DML), executeQuery() (SELECT) 메서드를 사용하여 SQL 쿼리를 실행합니다.
ResultSet 객체를 통해 쿼리 결과를 반복하고 데이터를 가져옵니다.
try-with-resources 문을 사용하여 Connection, Statement, ResultSet 객체가 자동으로 닫히도록 관리합니다.

django postgresql settings

간략 설명: Django는 웹 개발 프레임워크이며, settings.py 파일에서 데이터베이스 연결을 구성합니다. PostgreSQL을 사용하려면 DATABASES 설정에서 엔진, 이름, 사용자, 비밀번호, 호스트, 포트 등을 명시해야 합니다.
설치 방법 (PostgreSQL 어댑터):

pip install psycopg2-binary # 또는 psycopg2

사용 예시 (settings.py):

# settings.py

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql', # PostgreSQL 엔진 지정
        'NAME': 'your_django_db',                  # 데이터베이스 이름
        'USER': 'your_db_user',                    # 데이터베이스 사용자 이름
        'PASSWORD': 'your_db_password',            # 데이터베이스 비밀번호
        'HOST': 'localhost',                       # 데이터베이스 호스트 (IP 주소 또는 도메인)
        'PORT': '5432',                            # PostgreSQL 기본 포트
    }
}

# DATABASES = {
#     'default': {
#         'ENGINE': 'django.db.backends.postgresql_psycopg2', # 레거시 버전용
#         # ... 기타 설정 ...
#     }
# }

사용법:

Django 프로젝트의 settings.py 파일을 엽니다.
DATABASES 딕셔너리 내에서 'default' 키에 대한 설정을 위와 같이 수정합니다.
PostgreSQL 데이터베이스와 사용자를 미리 생성해야 합니다 (위의 postgresql create user database 항목 참조).
설정 후 python manage.py makemigrations 및 python manage.py migrate 명령어를 실행하여 Django 모델을 기반으로 데이터베이스 스키마를 생성합니다.
배포 환경에서는 민감한 정보(비밀번호)를 settings.py에 직접 넣는 대신 환경 변수나 별도의 설정 파일을 통해 관리하는 것이 좋습니다 (예: os.environ.get('DB_PASSWORD')).