[PoStGrEsQl] 기초 학습

 

PostgreSQL 17 버젼에 대한 기초 학습을 진행 하면서, 기록으로 남겨 두는 문서.

 

 

 

1. 원격 접근 가능 설정

pg_hba.conf 파일을 백업 해 두고 수정 한다.

host         all          all          127.0.0.1/32          scram-sha-256

위와 같은 부분을 찾아 아래와 같이 수정 한 뒤, 서비스 재시작

host         all          all          0.0.0.0/0             scram-sha-256

 

2. 서버 관련 설정

postgresql.conf 파일을 백업 해 두고 수정 한다. 항목들의 적당한 값으로 수정 한다.

port=5432

max_connections = 1000

shared_buffers = 128MB

temp_buffers=32MB

work_mem=16MB

 

3. 사용자 추가 및 삭제

-- 사용자 추가
create user <생성하고자 하는 계정> password '<계정에 대한 비밀번호>' superuser;

create user freecatz password 'test1234%' superuser;


-- 사용자 계정 목록
select * from pg_user;


-- 사용자 삭제
drop user <삭제 하고자 하는 계정>;

drop user freecatz;

 

4. 데이터베이스 생성

-- 형식
create database <생성할 데이터베이스 명칭> owner <소유자>;

-- 사용 예
create database db_freecatz owner freecatz;

 

5. 테이블 생성 및 삭제

-- 테이블 생성
create unlogged table if not exists tb_test (
	column_a smallserial
	, column_b serial
	, column_c bigserial
	, column_d char(1) not null default 'Y'
	, column_e varchar(100) default null
	, column_f text default null
	, column_g integer generated always as identity
	, column_h smallInt  default '0'
	, column_i bigInt  default '9999'
	, column_j numeric(14, 4)  default '0.0000'
	, column_k date default current_date check(column_k > current_date + intervaL '19 year')
	, column_l timestamp default current_timestamp
	, primary key(column_a, column_l)
);

-- 테이블 및 컬럼 설명 
comment on table tb_test is '테스트용 테이블';
comment on column tb_test.column_a is 'smallserial 타입 컬럼(자동 증가) : 1 ~ 32,767';
comment on column tb_test.column_b is 'serial 타입 컬럼(자동 증가) : 1 ~ 2,147,483,647';
comment on column tb_test.column_c is 'bigserial 타입 컬럼(자동 증가) : 1 ~ 9,223,372,036,854,775,807';
comment on column tb_test.column_d is 'char 타입 컬럼';
comment on column tb_test.column_e is 'varchar 타입 컬럼';
comment on column tb_test.column_f is 'text 타입 컬럼';
comment on column tb_test.column_g is 'integer 타입 컬럼(자동 증가) : -2,147,483,648 ~ 2,147,483,648';
comment on column tb_test.column_h is 'smallInt 타입 컬럼 : -32,768 ~ 32,767';
comment on column tb_test.column_i is 'bigInt 타입 컬럼 : -9,223,372,036,854,775,807 ~ 9,223,372,036,854,775,807';
comment on column tb_test.column_j is 'numeric 타입 컬럼 : 소숫점 표현 가능';
comment on column tb_test.column_k is 'date 타입 컬럼 : 년월일';
comment on column tb_test.column_l is 'timestamp 타입 컬럼 : 년월일시분초';

-- 테이블 삭제
drop table tb_test;

 

 

6. 플러그인 관련

-- 설치된 플러그인 목록
select * from pg_extension;

-- 설치 가능한 플러그인 목록
select * from pg_available_extensions;

-- uuid_generate_v4() 사용 하기 위한 플러그인 설치
create extension if not exists "uuid-ossp" schema public;

-- 다른 서버의 postgresql 을 마운트 하기위해 postgres_fdw 플러그인 설치
create extension if not exists postgres_fdw schema public;

 

7. postgres_fdw 를 이용한 다른 서버의 postgresql 연결(dblink 아님)

-- 서버 개체 생성
create server <서버 이름> foreign data wrapper postgres_fdw options (
  host '<서버 아이피>',
  port '<서버 포트>',
  dbname '<데이터베이스 이름>'
);

create server my_test_server foreign data wrapper postgres_fdw options (
  host '192.168.0.1',
  port '5432',
  dbname 'db_test'
);


-- 사용자 매핑 생성
create user mapping for <내 계정> server <서버 개체명> options(
  user '<원격 서버 계정>',
  password '<원격 서버 계정의 비밀번호>'
);

create user mapping for freecatz server my_test_server options(
  user 'freecatz',
  password 'test1234%'
);


-- 스키마 생성
create schema if not exists <스키마 명칭>;

create schema if not exists test_schema;


-- 스키마 삭제
drop schema if exists <스키마 명칭>;

drop schema if exists test_schema;

-- 기타 다른 개체들이 이 개체에 의존하고 있어...
drop schema if exists test_schema cascade;




-- 스키마에 서버 등록
-- 참고 : import 이후, 연결된 서버에 생성된 테이블은 조회 불가.
import foreign schema public from server <서버 개체명> into <스키마 명칭>;

import foreign schema public from server my_test_server into test_schema;


-- 스키마 삭제
drop schema if exists test_schema cascade;

 

8. 기타 쿼리

-- 버젼 확인
select version();

-- 서버 아이피 확인
select inet_server_addr();

-- 사용중인 데이터베이스 확인
select current_database();

-- 클라이언트 로그 레벨 확인(function 개발시 사용한다고 한다)
SHOW client_min_messages;

-- 클라이언트 로그 레벨 변경(function 개발시 사용한다고 한다)
SET client_min_messages = 'debug'; -- debug, log, info, notice(기본값), warning

-- 현재 시간 확인
select to_char(now(), 'YYYY-MM-DD HH24:MI:SS.MS');

-- 문자열 채우기
select lpad(generate_series(1, 100)::varchar, 4, '0');

-- 문자열 분리
select split_part(current_timestamp::varchar , '-', 1);

-- 벌크 데이터 만들때 쓸 수 있을 듯...
select concat('TEST', lpad(generate_series(1, 100)::varchar, 4, '0'));

-- 세자리 마다 콤마
select to_char(1234567890.123, 'FM999,999,999,999.99');

-- 일 더하기
select to_char(current_date + interval '19 day', 'YYYY-MM-DD');

-- 월 더하기
select to_char(current_date + interval '19 month', 'YYYY-MM-DD');

-- 년 더하기
select to_char(current_date + interval '19 year', 'YYYY-MM-DD');

-- 날짜 차이
select current_date  - '1981-07-17'::date as diff_date;  -- 1,234
select age(now()::date, '1981-07-17'::date) as diff_date; -- 12 years 3 mons 4 days

-- 설정 확인
select * from pg_settings;

-- 런타임 파라메터 출력
show all;

-- 세션 조회
select * from pg_stat_activity;

-- 세션 종료
select pg_cancel_backend ( PID );

-- 세션 강제 종료
select pg_terminate_backend ( PID );




-- 특정 인덱스만 리빌드
reindex index <인덱스 명칭>;
reindex index idx_tb_test;

-- 테이블에 종속된 인덱스 모두다 리빌드
reindex table <테이블 명칭>;
reindex table tb_test;

-- 특정 스키마의 전체 인덱스 리빌드
reindex schema <스키마 명칭>;
reindex schema public;

-- 데이터베이스 전체 인덱스 리빌드
reindex database <데이터베이스 명칭>;
reindex database db_test;


-- vacuum = 디스크 공간 반환. DB 사용량이 없을때 할 것을 권장.

-- 테이블 단위 vacuum
vacuum <테이블 명칭>; 
vacuum tb_test;

vacuum full analyze;  -- DB 전체 
vacuum verbose analyze;  -- DB 전체 간단히

vacuum analyze <테이블 명칭>; -- 해당 테이블만 간단히
vacuum analyze tb_test;

vacuum full <테이블 명칭>; -- 특정 테이블
vacuum full tb_test;

-- vacuum, analyze 이력 확인
select relname
     , last_vacuum
     , last_autovacuum
     , last_analyze
     , last_autoanalyze
  from pg_stat_user_tables
 order by relname
 
 
 -- 특정 테이블의 autovacuum 제외(성능 테스트를 위해 하는 경우에 사용한다고..)
 alter table <테이블 명칭> set (autovacuum_enabled = false);
 alter table tb_test set (autovacuum_enabled = false);
 
 
 
 
 -- postgresql 15 버젼 이상 부터는 merge into 가 된다고 하는데, 난 안되서...
 
 -- merge into 대체 쿼리 첫번째 예제(두번째 예제 보다 이게 마음에 든다)
 insert into tb_test ( column_a,  column_b, column_c, column_d, column_e )
              values ( '1234A',  '값 B', '값 C', '값 D', current_timestamp)
 on conflict (column_a) do 
 update set column_b = '값 B-1'
          , column_c = '값 C-1'
          , column_d = '값 D-1' 
          , column_e = current_timestamp


-- merge into 대체 쿼리 두번째 예제
with upsert as (
     update tb_test set 
            column_b = '값 B-1'
          , column_c = '값 C-1'
          , column_d = '값 D-1' 
          , column_e = current_timestamp
      where column_a = '1234A'
      returning *
)
insert into tb_test ( column_a,  column_b, column_c, column_d, column_e )
             select '1234A',  '값 B', '값 C', '값 D', current_timestamp
where not exists (select * from tb_test);