본문 바로가기

728x90

데이터베이스/Postgresql65

postgresql + pgpool-ii 셋팅 리눅스에서 도커를 이용하여 postgresql 서버 2대를 구성하여 고가용서 ha 를 구성한다. 도커 허브에 접속하여 centos-8 stream을 다운받아서 서버2대를 구성하고 테스트 https://hub.docker.com/r/twistedbytes/centos8-stream/tags Docker hub.docker.com /* 도커 허브에서 이미지를 다운 받는다 */ poman pull twistedbytes/centos8-stream:latest 맨밑에 docker.id 선택하여 이미지 다운 받는다. /* 도커 이미지 확인 */ podman images /* 네트워크 생성 */ podman network create --subnet 192.168.0.0/24 --gateway 192.168.0.1.. 2023. 2. 9.

postgis 확장모듈 소스 컴파일 설치 설치운영체제 centos8 stream 버전 postgis를 설치하기 위해서는 순서대로 의존성을 설치후 마지막에 설치를 하여야 한다. 설치 버전 정보 1. postgresql-15.0 2. geos-3.8.0 3. proj-6.2.1 4. cgal-4.14.2 5. sfcgal-v1.3.7 6. gdal-3.5.3 7. postgis-3.3.1 직접 소스를 컴파일 해서 설치 하는 경우 os배포판과 분리 하기 위해서 각 패지지 설치 경로를 만든다. 1. geos 소스 컴파일 설치 ./configure --prefix=/apps/geos make all make install 2. proj6.6 소스 컴파일 설치 sqlite 의존성 패키지를 먼저 설치한다. yum install sqlite-devel.x86.. 2023. 2. 5.

[PostgreSql] 오라클 INSTR = POSITION 오라클 위치 찾기 SELECT INSTR('가나다라마바사', '다') FROM dual; POSTGRESQL 위치찾기 같은 기능 select position('다' in '가나다라마바사') 2022. 6. 30.

[PostgreSql] 오라클 WM_CONCAT와 같은 기능 /* POSTGRES 오라클 WM_CONCAT 와 같은기능 */ SELECT ARRAY_TO_STRING(ARRAY_AGG(CONCAT ORDER BY CONCAT ),',') AS AR FROM ( select '문자열합치기1' AS CONCAT UNION ALL select '문자열합치기2' AS CONCAT UNION ALL select '문자열합치기3' AS CONCAT ) A /* 오라클 WM_CONCAT */ SELECT WM_CONCAT(CONCAT) AS CONCAT FROM ( select '문자열합치기1' AS CONCAT FROM DUAL UNION ALL select '문자열합치기2' AS CONCAT FROM DUAL UNION ALL select '문자열합치기3' AS CONCA.. 2022. 6. 30.

[PostgreSql] 특정문자를 짤라서 컬럼으로 변경 /* 오라클에서 특정문자를 기준으로 짤라서 컬럼으로 변경 */ SELECT TRIM(REGEXP_SUBSTR('COLUMN1,COLUMN2,COLUMN3', '[^,]+', 1, LEVEL)) AS SPLIT_CNSTNT_ID ,LEVEL FROM DUAL CONNECT BY INSTR('COLUMN1,COLUMN2,COLUMN3', ',', 1, LEVEL - 1) > 0; /* POSTGRESQL 특정문자를 기준으로 짤라서 컬럼으로 변경 */ select (REGEXP_SPLIT_TO_ARRAY('COLUMN1,COLUMN2,COLUMN3',','))[LEVEL] AS SPLIT_CNSTNT_ID, LEVEL from (select 'COLUMN1,COLUMN2,COLUMN3' as P_CNSTNT_.. 2022. 6. 30.

[PostgreSql] pg_cron job schedule 등록방법 /* pg_cron이 설치가 안되어 있는 경우 * https://github.com/citusdata/pg_cron 사이트에 가서 소스를 다운받아서 컴파일 설치를 한다. * git clone https://github.com/citusdata/pg_cron * cd pg_cron * postgresql이 설치된 bin 경로를 설정한다. */ 리눅스에 .bash_profile 열어서 경로 설정 LANG=ko_KR.UTF-8 PGHOME=/home/passdata/apps/postgresql-13.4 PGDATA=/home/passdata/passdata/pgdata PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin LD_LIBRARY_PATH=/$PGHOME/lib .. 2022. 6. 29.

[PostgreSql] 파티션 자동 생성 프로시저 PostgreSql에서 파티션 자동생성 프로시저 입니다. 오라클과 달리 PosrgreSql에서는 파티션을 나누면 그 자체가 서브 테이블로 생성이 됩니다. 서브테이블에 바로 인서트 해도 되고 서브테이블을 그냥 삭제 해 버리면 파티션도 자동으로 그냥 삭제 처리가 됩니다. CREATE OR REPLACE PROCEDURE PROC_AUTO_PART_CREATE( OUT_RESULT_CD INOUT VARCHAR, OUT_SQLERR INOUT VARCHAR ) LANGUAGE plpgsql AS $procedure$ DECLARE V_SQL VARCHAR(4000); C_PART RECORD; BEGIN FOR C_PART IN select 'CREATE TABLE public.part_' || A.PDATE.. 2022. 6. 28.

[PostgreSql] 프로시저 생성 방법 DBeaver 툴 사용해서 생성 Function메뉴에서 마우스 오른쪽 버튼 클릭하여 Create New Function 메뉴 선택하면 생성창이 뜬다. Type에서 procedure 선택하고 Language에서 plpgsql선택후 확인 눌러서 생성하면 기본 템플릿이 작성된다. CREATE OR REPLACE PROCEDURE public.pn_test() LANGUAGE plpgsql AS $procedure$ declare /* 변수 선언 */ v_test varchar(100); v_date timestamp; BEGIN /* 로직 구현 */ /*변수 초기값 셋팅 */ v_date := now(); END; $procedure$ 2022. 6. 28.

[PostgreSql] 함수 function 생성 방법 DBeaver 툴을 이용하여 기본 템플릿 functin 생성하는 방법 create New Function 클릭하면 입력창이 뜬다. 확인 버튼 클릭 기본 템플릿 파일이 생성 된다. function은 리턴값이 존재하고 프로시저는 리턴값이 있을수도 있고 없을수도 있다. postgresql은 특이하게 function안에 commit나 rollback구문을 넣으면 프로시저나,함수 호출시 에러가 발생한다. 오라클에서는 문제가 안된다. 사실 function안 또는 프로시저안에 commit문이나 rollback을 넣는것 자체가 문제다. 원자성이 훼손된다. 프로그램에서 호출시에 전체가 성공해야 commit이 실행되어야 하는데 프로시저안에 중간 중간 다 박아 넣으면 전체가 성공해야 commit처리가 되어야 하는데 그냥 성.. 2022. 6. 28.

[PostgreSql] 오라클에서 postgresql 로 이관시 타입변환 정리 오라클에서 MERGE INTO 문은 postgresql에서는 UPSERT 문과 비슷하지만 다르다. 오라클에서는 한번에 조회해서 update나 insert처리가 되지만 postgresql에서는 한건씩 비교해서 같으면 업데이트 다르면 인서트 처리를 한다. 건수가 많지 않을경우에는 cursor돌려서 처리하면 되지만 몇백만건 이상 될때는 속도 차이가 많이 난다. FOR 루프로 100만건 정도 된다고 하면 한건싹 루프 돌면서 처리가 되어서 시간이 오래 걸림 FOR ROW_DATA IN ( SELECT KEY,TEST1,TEST2 FROM 조회_테이블명 ) LOOP WITH UPSERT AS ( UPDATE 테이블명 SET TEST1 = ROW_DATA.TEST1 , TEST2 = ROW_DATA.TEST2 , R.. 2022. 6. 28.

[PostGreSql] 문자인지 숫자인지 체크하는 함수 postgresql에는 문자인지 숫자인지 체크하는 함수가 없다. 인터넷 검색하다가 못 찾아서 그냥 함수 하나 만들었다. CREATE OR REPLACE FUNCTION is_numeric ( p_string varchar ) RETURNS numeric AS $$ DECLARE V_NEW_NUM BOOLEAN; BEGIN SELECT p_string ~ E'^[-+]?\\d*\\.?\\d+(?:[eE][-+]?\\d+)?$' into V_NEW_NUM; IF (V_NEW_NUM) THEN RETURN 1; ELSE RETURN 0; END IF; END; $$ LANGUAGE 'plpgsql' /* 사용방법 함수 호출해서 1이면 숫자 0이면 문자다 */ select is_numeric('-3.2') 2022. 6. 24.

[PostgreSql] 설치후 초기 데이터 구축 1. 사용자 계정 생성 create user test password 'test2021' superuser; 2. 데이터 베이스 생성 create database naqs owner test; 3. 테이블 스페이스 저정될 폴더를 먼저 생성한다. mkdir -p /postgre_db/com_dat mkdir -p /postgre_db/com_idx 소유권을 변경한다. chown -R postgrs:postgres /postgre_db 4. 테이블 스페이스 생성 CREATE TABLESPACE naqscom_tbl owner test LOCATION '/postgre_db/com_dat'; CREATE TABLESPACE naqscom_idx owner test LOCATION '/postgre_db/com.. 2021. 12. 14.

[PostgreSql] 리눅스 postgresql 소스 컴파일 설치 https://www.postgresql.org/ftp/source/v13.4/ PostgreSQL: File Browser www.postgresql.org postgresql 버전 13.4 centos 에 소스 컴파일 설치 리눅스에서 postgresql 설치할 사용자 계정을 root에서 생성한다. adduser passdata 사용자 암호생성 passwd passdata 새암호: 새암호 재입력: postgresql 사이트에서 다운받은 postgresql-13.4.tar.gz 파일을 생성된 사용자 계정 홈디렉토리에 전송한다. 압축을 푼다 : tar -zxvf postgresql-13.4.tar.gz cd postgresql-13.4 ■ 설치할 디렉토리 생성 mkdir -p /apps/postgresql.. 2021. 12. 14.

[PostgreSql] 데이터 이관시 트리거 비활성후 테이블 데이터 삭제 /* 데이터 마이그레이션의 경우 모든 트리거 비활성 하는 방법 */ /* 비활성 시키고 데이터 삭제 */ SET session_replication_role = 'replica'; SELECT 'delete from ' || tablename || ';' as de FROM pg_catalog.pg_tables where schemaname = '스키마명'; SELECT 'select * from ' || tablename || ';' as se FROM pg_catalog.pg_tables where schemaname = '스키마명'; /* 데이터 삭제후 활성 */ 마이그레이션 후 모두 다시 활성화 SET session_replication_role = 'origin' ; 2021. 11. 9.

[PostgreSql] 패스워드 없이 백업 및 복원 백업을 하기 위해서는 먼저 홈디렉토리에 패스워드 파일을 생성해야 한다. vi .pgpass 아래의 내용 추가 192.168.1.42:5432:*:아이디:패스워드 권한변경 chmod 0600 .pgpass /* 패스워드 없이 스키마 별로 백업 */ pg_dump --file "/var/lib/pgsql/test.dump" --host "192.168.1.42" --port "5432" --username "아이디" --no-password --verbose --format=c --section=pre-data --section=data --section=post-data --column-inserts --encoding "UTF8" --schema "스키마명" "db명" /* 패스워드 없이 스키마 별로 복.. 2021. 11. 9.

[PostgreSql] postgresql 13 확장팩 설치 /* 확장팩 확인 */ select * from pg_catalog.pg_available_extensions; PostgreSQL에서는 extension이라는 기능을 제공합니다. extension을 통해서 제3자의(외부) 프로그램을 plug-in 형태로 PostgreSQL에 연동할 수 있습니다. extension을 사용해서 PostgreSQL에서 제공하지 않는 추가적인 기능을 사용할 수 있습니다. 설치하는 방법은 PostgreSQL을 yum을 통해 설치했느냐, 또는 source를 통해 설치했느냐에 따라 다릅니다. 만약 PostgreSQL을 source를 통해 설치를 했다면, source 디렉토리에 contrib 디렉토리가 있습니다. 그 디렉토리에 들어가서, 아래 명령어를 통해 설치 하시면 됩니다. 만약.. 2021. 11. 8.

[PostgreSql] 윈도우 db백업 및 리눅스 데이터 복원 리눅스 접속 su - postgres Postgresql 터미널 접속 psql -U postgres /* 사용자 계정 생성 */ create user test password 'test2021' superuser; /* 데이터 베이스 생성 */ create database test_db owner test; /* 테이블 스페이스 저정될 폴더를 먼저 생성한다.*/ mkdir -p /postgre_db/test_dat mkdir -p /postgre_db/test_idx chown -R postgrs:postgres /postgre_db /* 테이블 스페이스 생성 */ CREATE TABLESPACE test_tbl owner test LOCATION '/postgre_db/test_dat'; CREATE .. 2021. 11. 8.

[PostgreSQL] 날짜 범위 검색 PostgreSQL Date & Time Function 해당 날짜의 데이터 select count(watt_max) from tbl_test_watt3_sm2ch_min where to_char(regdate, 'YYYY-MM-DD') = '2016-10-17' 소요시간 : 124초 (150만건) (하루: 20*60*60*24 = 1,728,000) 이렇게 하면 망함! 해당 날짜의 데이터 select count(watt) from tbl_test_watt_lsh where regdate >= date '2016-10-17' and regdate = current_date and regdate '2016-10-17 07:40:00' AND to_char(regdate , 'YYYY-MM-DD HH24;M.. 2021. 10. 1.

[PostgreSQL] lock 확인,삭제 Lock 테이블 확인 select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc; Lock 삭제 select pg_cancel_backend(pid); 2021. 9. 29.

테이블의 스키마와 owner 변경하기 -- 특정 테이블의 owner 와 소속 schema 는 아래 명령으로 쉽게 변경할수 있습니다. ALTER TABLE 현소유자 OWNER TO 새소유자; ALTER TABLE 테이블명 SET SCHEMA 새로운_스키마명 -- 참고로 특정 유저 소유의 모든 오브젝트들을 다른 유저 소속으로 바꾸고 싶다면 아래명으로 간단히 처리할수 있습니다. REASSIGN OWNED BY 현OWNER TO 새OWNER; 2021. 9. 29.

이전 1 2 3 4 다음

728x90

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

티스토리툴바