postgresql은 강력한 mvcc를 지원하고 엔진과 관리툴이 철저히 분리되어 있다.
이 때문에 하드웨어에 의존적인 순수한 속도와 규율적인 제약을 탈피할 수 있고
OS종속에서도 벗어날수 있으며 도커 컨테이너에도 자유롭게 올라가고 서버엔진과
클라이언트를 분리해서 클라이언트쪽에서 관리작업 (백업,복구)을 진행할수 있는등
혁신적인 일이 벌어진다. (어설픈 스크립트 백업이런거 아님) 또한 실시간 메모리 범위
변동이나 update나 insert시 RAM에서 우선처리하고 응답한후 스토리지에는 지연쓰기를 설정할 수 있는 레디스 db속도급 synchronous_commit = off 기능등 활용범위가
다양하고 자료형도 그래프관련 자료나 기하학관련 AI에서 쓰이는 자료형이나
json이나 xml처리등을할 수 있으며 지구상에서 sql표준을 가장 잘 구현하고 표준에 부합하는
데이터베이스다. 이런 혁신적인 구조 때문에 차세대 db엔진으로 많은 사랑을 받고 있다.
그래서 운영하는 시스템도 mssql에서 postgresql로 마이그레이션을 진행하였다.
먼저 이관 순서는 다음과 같이 진행하여야 한다. (한PC에서 두커넥션다 존재)
1. 기존 mssql자료에 접근할수 있는 로컬환경구축 (로그인과 알멩이가 같이 있어야함)
2. 새로운 postgresql에 접근할수 있는 로컬환경구축 (로그인과 껍데기 db준비)
3. sling으로 양쪽을 전부 접속해서 마이그레이션하는 명령실행
sling은 리눅스 기반이라 윈도우에서는 wsl을 이용하여 우분투를 설치한후
명령을 실행하는 방식을 취해야한다. 명령줄은 다음과 같다. (상세내역 아래참조)
./sling run --src-conn 'sqlserver://아이디:비밀번호@아이피:포트?database=DB명&encrypt=false&TrustServerCertificate=true' --src-stream 'dbo.*' --tgt-conn 'postgresql://아이디:비밀번호@아이피:포트/DB명?sslmode=disable' --tgt-object 'public.{stream_table}'
4. postgresql에 public 스키마로 백업된 데이터를 확인하고
내 입맛에 맞게 테이블을 alter작업 (이부분이 시간이 가장 많이 걸림.. 뷰나 프로시저도 한번은 거쳐야하므로 이때 이관한다. 복원된
테이블을 변경하고 인덱스도 다시 잡아줌 등등 sql파일에서 작업해서 처리할것)
5. 입맛대로 변경된 postgresql에서 db를 백업한다. (pg_dump 처리로 파일로 export)
6. 운영할 postgresql이 설치된 서버 pc에서 5번 파일로 restore 작업을 진행한다..
[마이그레이션 유의사항 - 리눅스에서 진행할것]
# 슬라잉(sling-cli) 최신 버전 다운로드
wget https://github.com/slingdata-io/sling-cli/releases/latest/download/sling_linux_amd64.tar.gz
# 3. 압축 풀기
tar -xzvf sling_linux_amd64.tar.gz
# 슬라잉을 이용하여 마이그레이션 작업
./sling run --src-conn 'sqlserver://아이디:비밀번호@아이피:포트?database=DB명&encrypt=false&TrustServerCertificate=true' --src-stream 'dbo.*' --tgt-conn 'postgresql://아이디:비밀번호@아이피:포트/DB명?sslmode=disable' --tgt-object 'public.{stream_table}'
[시간관련처리]
현재시간 | NOW() or CURRENT_TIMESTAMP | 현재시간을 추출함
형식변환(날짜) | NOW()::date | '2026-04-17' 형태만 남김
형식변환(파싱) | '2024-05-12 08:30:00'::timestamp | 문자열을 날자로변환
시간차이 | EXTRACT(EPOCH FROM (후-전))/3600 |
두시간사이 차이를 초로 반환해서 /3600을 하니까 시간단위로 바뀐것
시간더하기 | NOW() + INTERNAL '9 MINUTE' | 특정 시간의 합산처리
특정부분추출 | EXTRACT(HOUR FROM NOW()) | 시간에서 지정값을 뽑음
※ 시간(timstamp)을 문자로 변환하는것은 TO_CHAR() 하나로 끝남
TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS.MS') | 지정 스타일로 변환
[기간조회 공식은 같이 먹힘]
WHERE (sb::timestamp <= pe:timestamp) AND (se::timestamp >= pb::timestamp)
포스트그리SQL만은 기간조회가 아예 함수로도 있음
WHERE (SB, SE) OVERLAPS (@PB, @PE)
[백업 및 복원] - 명령파일 pg_dump.exe 같은걸로 실행해야함
[백업]
pg_dump -h <원격서버_IP> -p <포트번호> -U <권한있는계정> -d <db_이름> -F c -b -v -f "저장할_파일명.dump"
[클린복원]
pg_restore -h <원격서버 IP> -p <포트번호> -U <권한있는계정> -d <db_이름> -c -v "파일명.dump"
테이블만 지정할때는 -t 옵션으로 지정해서 처리해야함..
[프로그래밍 방식] - DO $$로 시작해서 BEGIN .. END $$;로 감싸는 구조임
예를 들어 '2017-05-23 14:23:32' 라는 문자를 파싱해서 timestamp로 만들고
그걸 문자열 6시간과 int 변수인 초값으로 더하고 그 결과를 다시 초로 환산해서
임시테이블로 바꿔서 select하는 예시
do $$
declare
input_str text := '2017-05-23 14:23:32';
target_date timestamp;
result_seconds integer;
add_sec integer := 30;
begin
create temp table if not exists temp_tb (
ori_time text,
cvt_time timestamp,
calc_sec integer
);
truncate temp_tb;
--
target_date := input_str::timestamp;
target_date := target_date + interval '6 hours';
target_date := target_date + (add_sec || ' minutes')::interval;
result_seconds := extract(epoch from target_date)::integer;
--
insert into temp_tb (ori_time, cvt_time, calc_sec)
values (input_str, target_date, result_seconds);
end $$;
--
select ori_time, cvt_time, calc_sec from temp_tb limit 1;
[또는 with와 as의 임시 테이블을 이용하는 방법]
WITH
var_ttb AS (SELECT '2017-05-23 14:23:32' AS input_str, 30 AS add_sec),
cal_ttb AS (SELECT input_str, add_sec, input_str::timestamp + INTERVAL '6 hours' AS base_date FROM var_ttb),
res_ttb AS (SELECT input_str, base_date + (add_sec || ' minutes')::interval AS target_date
FROM cal_ttb)
--
SELECT input_str AS ori_time, target_date AS cvt_time, EXTRACT(EPOCH FROM target_date)::integer AS calc_sec FROM res_ttb;
[접속한 db의 모든 테이블 목록보기]
SELECT * FROM information_schema.tables WHERE table_schema = 'public';
[자동증가 id]
자동증가 id의 경우 자료형을 integer로 해서 초기값과 증가값을 가지는 경우..
묻지도 따지지도 말고 serial 자료형으로 처음부터 등록해야함.
자동증감기라는 오브젝트가 아예 별도로 있어서 뒤로 당겨주는 역할을 별도로 하기때문에..
자동증가 당겨주기는
SELECT setval(pg_get_serial_sequence('mytable', 'id'), COALESCE(max(id), 0)) FROM mytable;
로 처리하면 되는데
자료형을 바꺼버리면 아이디가 새로 부여된다.. 굳이 아이디가 바껴도 상관없다면..
ALTER TABLE mytable DROP COLUMN id;
ALTER TABLE mytable ADD COLUMN id SERIAL PRIMARY KEY;
이렇게 할수도 있지만 id의 값이 바껴버림 (주의)
[pgAdmin4 에서 전체 테이블 create 스크립팅 처리]
데이터베이스 -> 백업 -> 형식(Plain) -> 데이터 옵션(객체종류: Only schemas)
-> 파일경로지정(*.sql) -> 백업버튼
[*.sql 파일처리]
*.sql에 대량의 insert문이나 혹은 내용에 select가 있어서 결과셋을 헤더가 있는 csv파일로
출력할때는 다음처럼 psql 명령을 이용한다. 원래는 콘솔창에서 접속해서 쿼리작업을 위한 용도
이지만 이렇게 처리해도됨.
psql -h 127.0.0.1 -p 5432 -U postgres -d parking_db -v ON_ERROR_STOP=1 -f "D:/input.sql"
이때 결과셋을 출력하는 내용이 input.sql 파일에 다음과 같이 있다면
\copy (SELECT user_no, user_name FROM myuser) TO 'D:/output.csv' WITH CSV HEADER ENCODING 'UTF-8';
실행결과셋이 csv로 출력된다.
메인: 대패삼겹살(150g), 우유(300~400ml), 찬밥(1공기)
풍미: 체다치즈(1~2장), 고춧가루(0.5~1큰술), 양파(1/2개), 다진 마늘(1큰술)
간: 간장(1큰술), 굴소스(0.5큰술), 후추 듬뿍
STEP 1: 고기 굽기 및 마이야르 반응
달군 팬에 대패삼겹살을 먼저 굽습니다.
Tip: 고기가 바삭해질 정도로 구워야 기름(라드)이 충분히 나오고 식감이 좋습니다.
다 구워진 고기는 따로 접시에 덜어둡니다.
STEP 2: 고추기름 베이스 만들기
팬에 남은 삼겹살 기름에 다진 마늘과 양파를 넣고 볶습니다.
양파가 투명해지면 고춧가루를 넣고 타지 않게 30초 정도만 살짝 볶아 매콤한 향을 입힙니다.
STEP 3: 리조또 빌드업
우유를 붓고 끓어오르면 찬밥을 넣습니다.
밥알이 우유를 머금어 몽글몽글해질 때까지 중불에서 저어가며 끓입니다.
STEP 4: 간 맞추기 및 치즈 투입
간장과 굴소스를 넣어 기본 간을 합니다.
소스가 어느 정도 꾸덕해지면 체다치즈 1~2장을 넣습니다.
치즈가 녹으면서 농도가 급격히 진해지니 이때 농도를 잘 체크
STEP 5: 병합 및 마무리
덜어두었던 구운 대패삼겹살을 넣고 가볍게 섞습니다.
그릇에 담은 뒤 후추를 팍팍 뿌려 완성.
