Postgresql

Docker로 셋업하기

Stored Procedure

• Postgresql은 session 별로 prepared statements를 관리하고 shared query plan cache 도 지원하지 않음. 그래서 connection 별로 cache가 지원되기에, connection pool 을 이용해야 함

psql

NAME="Amazon Linux"
VERSION="2"
ID="amzn"
ID_LIKE="centos rhel fedora"
VERSION_ID="2"
PRETTY_NAME="Amazon Linux 2"
ANSI_COLOR="0;33"
CPE_NAME="cpe:2.3:o:amazon:amazon_linux:2"
HOME_URL="https://amazonlinux.com/"
SUPPORT_END="2025-06-30"

drop database <database_name>
create database <database_name>
\dt # list tables
\l # list databases

Backup and restore(pg_dump, psql)

# backup data
pg_dump --host localhost --port 11543 -U gateway_user -t gateway.ais_blclst 
-O -a -E UTF8 postgres > dump.sql

# backup schema of database
pg_dump --host localhost --port 11543 -U gateway_user -O -E UTF8 <database> 
> dump.sql

## reload
psql -d newdb -f db.sql

• -O : To make a script that can be restored by any user, but will give that user ownership of all the objects, specify -O

• -a : Dump only the data, not the schema (data definitions).

• -E : Create the dump in the specified character set encoding

• -t pattern : Dump only tables with names matching pattern

• -s : Dump only the object definitions (schema), not data.

• -n pattern : Dump only schemas matching pattern

partitioning table backup

• 위 이미지에서 보듯이 ais_ship_data_hist의 데이터가 ais_ship_data_hist테이블에 있는 것이 아니라 각각의 파티션 테이블에 존재함. 그래서 pg_dump를 해당 테이블들을 다 명시해주어야 데이터 dump 까지 진행됨

pg_dump --host localhost --port 11543 -U gateway_user -v -O -a -E UTF8 
-t gateway.ais_ship_data_hist 
-t gateway.pais_ship_data_hist_230309 
-t gateway.pais_ship_data_hist_230310 
-t gateway.pais_ship_data_hist_230311 
-t gateway.pais_ship_data_hist_230312 
-t gateway.pais_ship_data_hist_230313 
-t gateway.pais_ship_data_hist_230314 
-t gateway.pais_ship_data_hist_230315 
-t gateway.pais_ship_data_hist_230316 postgres > /data/ais_ship_data_hist.sql

쿼리

SELECT datname FROM pg_database -- 전체 데이터베이스 조회

SELECT datname FROM pg_database WHERE datistemplate = false 
-- 사용자가 생성한 데이터베이스만 조회

select nspname from pg_catalog.pg_namespace -- 현재 db의 전체 스키마 조회
select tablename from pg_tables -- 전체 테이블 조회

-- max 함수는 여러 행에 대해서 최대값 찾는 것이고, greatest 함수는 여러 열에 대해서 최대값 찾음 
SELECT greatest(values...);

// ALTER TABLE

/// 컬럼 추가
ALTER TABLE cars ADD column <columnname> VARCHAR(255);

/// 컬럼 삭제
ALTER TABLE townhall_reservations DROP COLUMN organization_id;

/// 제약조건 삭제
ALTER TABLE townhall_reservations DROP CONSTRAINT fk_townhall_org_id;

/// 제약조건 추가
ALTER TABLE orders 
    ADD CONSTRAINT fk_city_id FOREIGN KEY (city_id) REFERENCES city (id);
    
// 인덱스 삭제
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

// 인덱스 조회
SELECT * FROM pg_indexes WHERE tablename = 'your_table'; 

// 인덱스 생성
CREATE UNIQUE INDEX CONCURRENTLY name ON table_name (column_name);
ALTER TABLE equipment ADD CONSTRAINT <constraint_name> UNIQUE USING INDEX <index_name>;

//현재 시퀀스 값 조회
SELECT currval('시퀀스명');
SELECT last_value FROM 시퀀스명;

// 시퀀스 값 업데이트
ALTER SEQUENCE 시퀀스명 restart with 시퀀스값;

TroubleShooting

hostname:port:database:username:password

'user's log' -- 안됨
'user''s log' # 가능

E'user\'s log' # 가능