Tag Archives: postgresql

PostgreSQL 9.5 초기설정

기본설정은 버전올라가도 크게 변하지 않는다.

 

파일 설정

postgresql.conf

https://www.postgresql.org/docs/9.5/static/runtime-config-connection.html

pg_hba.conf

https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

터미널 접속

psql -u postgres psql -U postgres -W

사용자 관리

 

GIS 분석 인프라 구성 – Ubuntu16.04, Postgresql9.5, PostGIS2.2

설치

설치는 별거 손댈게 없다.

서버에서 스크립트를 돌릴 일이 있을 수 있으니

설정

Postgresql

원격접속 가능하게 설정 – 필요한 경우

postgresql.conf

pg_hba.conf

개발테스트용이 아닌 서비스용이라면 초기 설정할 때만 오픈해놨다가 접근권한을 127.0.0.1이나 허용된 서버만으로 제한하도록 한다.

계정 설정

root 생성

Postgis

디비 생성후

postgis 설치가 완료되면

gis_db.public.spatial_ref_sys 테이블이 생성된다.

확인

select * from pg_catalog.pg_tables;

초기 데이터 준비

spatial_ref_sys에 표준 좌표계는 다 입력되어 있는편인데

공공기관에서 제공해주는 변태좌표계에 필요한 설정을 넣어준다.

id값이 충돌하지 않도록 주의.

 

GIS 너무 쉬운거 아니야?

아직 지옥문을 막 열었을 뿐.. 문이 닫히기 전에 돌아가라

 

RAW데이터를 수집해야한다.

 

공공기관의 데이터

정권이 바뀔때마다 지들 기분날 때 마다 데이터의 제공주체나 생산주체 담당자가 수시로 바뀌니 그때그때 확인을 해 봐야한다.

데이터 포맷 정보가 없는 경우도 많고 통일성도 없다.

따져도 소용없다. 그 사람들도 문제의식은 가지고 있는데.. 이게 장관급에서 프로젝트가 내려오지 않으면 어쩔 수 없는 부분이 있다. 여러 기관이 얽히고 얽혀서

데이터 제공 기관

행정표준코드 관리시스템 : https://www.code.go.kr/jsp/index.jsp

환경공간정보서비스 : https://egis.me.go.kr
생태자연도

국립생태원 : http://www.nie.re.kr/

국토환경정보센터 : http://www.neins.go.kr

국토지리원 : http://www.ngii.go.kr
수치지도, DEM(래스터)

국가공간정보포털 : http://www.nsdi.go.kr
연속지적도, 개별공시지가, 토지등급, 건물정보 …

주소, 행정동, 지적도, 건물정보, 등 넣어줘야하는데

국가교통정보센터 : http://www.its.go.kr/

교통정보공개서비스 : http://openapi.its.go.kr

기상청 : http://www.kma.go.kr

NASA 수치 데이터 : 한국 데이터는 없다고도 하고 잘 못 찾겠다.

Ubuntu12.04 postgresql with postgis

현재 우분투 버전 13.04 postgis설치시에 apt-get 기본패키지로 postgresql 9.1과 그에 딸린  postgis설치하면 제대로 동작을 하지않는다.

정확한 패키지명.. postgresql-9.1-postgis

 

2.x버전을 설치해야 제대로 동작한다고한다.

관련 페이지.

http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1210src

http://docs.geonode.org/en/latest/tutorials/admin/install/install_postgis.html

http://postgis.net/docs/postgis_installation.html#make_install_postgis_extensions

삭제되는경우를 대비하여 복사.

CentOS6 Postgresql 9.3 replication[현재안됨]

서버두개 Master Slave인 경우

대략적인 설정을 하고 확인한 것은.  pg의 레플리케이션은 파일을 Master서버에서 파일을 퍼와서 Slave서버에 바로 배치입력시키는 과정을 자동화해놓은 정도의 수준인 것처럼 보인다.

Master

ip : 192.168.0.101
username : testuser
password : testpass1234
권한 : replication

아이디 비밀번호가 같을 필요는 없지만 편의상 같ㄱ ㅔ한다

Slave
ip : 192.168.0.102
username : testuser
password : testpass1234

 

Master서버 설정

데이터 경로

자동설치의 경우 /etc/init.d/postgresql-0.3 스크립트에서 수정해야된다

 

pg_hba.conf

local trust, 외부접속설정. replication설정을 주의

postgresql.conf

pg인증방식

pg_hba.conf
host all all 127.0.0.1/32 trust
host all all 127.0.0.1/32 md5

local all postgres peer
local all postgres md5

Slave서버

pg_hba.conf

노예는 이것만 설정한다. 사용만 하면되니까… 마스터에서 접근은 안한다.

postgresql.conf

recoverty.conf

다른쪽들은 복제할 때 다 됐을테니 따로 안해도 된다… 이것만 새로 생성

주의할점

로그확인을 잘 하자.

 

 

참고한 페이지

http://mixellaneous.tistory.com/1007
https://www.digitalocean.com/community/articles/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps
http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
http://histlinux.egloos.com/1227710
http://mixellaneous.tistory.com/1007
http://dba.stackexchange.com/questions/16781/postgresql-9-1-hot-backup-error-the-database-system-is-starting-up

 

Postgresql – ,로 구분된 키값으로 조인

다음 사이트들을 참고해서 해겷했다.
http://stackoverflow.com/questions/8584967/split-comma-separated-column-data-into-additional-columns
http://stackoverflow.com/questions/11730777/postgres-not-in-array
http://stackoverflow.com/questions/10738446/postgresql-select-rows-where-column-array
나머지는 진짜 참고만 했고 첫번째 링크에 솔루션이 있다.

테이블 두개…

admin_ids가 문제다. 배열이 아닌 문자열형태로 ,구분자로 저장…
m-n조인을 하는것도 좋지만… 편의를 위해서 저렇게 하는 경우가 자주 있다.

이런경우에 조인을 하려면…
설명 생략하고 테스트과정을 순차적으로 다 써놓는다.
나야 보면 생각나겠지만… 다른사람들도 순서대로 실행시켜보면 뭔지 알 수 있을거다. 아마…

내용 변경했으니까 그냥 막 올려도 되겠지??? 아닌가…

RDBMS(Postgresql)를 이용한 log 테이블 생성시 참고

로그 규칙:

일정시간간격으로 파일을 수신하는 로그를 기록하는 테이블
1일 50000개 가량의 데이터 누적
로그 보관기한 3개월
1개월 150`00000개

빠른 검색을 위한 인덱스와 키값을 지정.. 각 키값에 따라 데이터가 누적되는 성능과 검색성능비교 테스트
테스트 pc 사양 : CentOS 6.3, i5 센디브릿지, 8G Ram, 7200rpm짜리 1기가 하드..이정도?정확히 모름
하드웨어는 공용이라서 속도 측정에 오차가 발생할 가능성이 높음

– 데이터 누적 입력 테스트

1번 테이블

1회 : 27752ms
2회 : 28085
3회 : 29445
4회 : 28864
5회 : 29634
6회 : 27742

 

2번 테이블 (1번과 같은 쿼리)

1회 : 43306
2회 : 35274
3회 : 33770
4회 : 46349
5회 : 44384
6회 : 44153

3번 테이블

1회 : 21605
2회 : 22451
3회 : 22075
4회 : 20701
5회 : 24611
6회 : 25346

4번 테이블

1회 : 39211
2회 : 35934
3회 : 37053
4회 : 38488
5회 : 38954
6회 : 35096

여기까지 결론.
인덱스 두개자리는 시간이 오래 걸린다. 데이터 누적에 따라 느려지는건 별로 없다. 바이너리 인덱스라서
속도차이는 조금 있지만 신경쓸수준은 아니니 검색에 적합한 방식을 사용하면 될듯하다.

 

검색 쿼리도 확인을 해 봐야되는데.. 테스트 데이터를 만들어낸다음에 해? 보지않을 것 같다. 만약에 하게되면 해야될것. -샘플데이터 -검색쿼리

 

Postgresql FullTextSearch – 2017수정

디비에서 간단하게 문자열 검색을 할 때의 쿼리는 다음과 같다.

데이터 양 적거나/조회량이 적거나 해서 속도가 큰 문제가 되지 않을때는 이렇게 해도 충분하다.

 

like검색을 하면 무조건 full scan이 발생한다. 데이터가 늘어날수록 속도가 엄청나게 느려진다.

 

PostgreSQL Full Text Indexing

RDBMS에서도 문자열 검색이 필요할 때가 있는데
검색은 보통 루씬에 의존하는 방향으로 트렌드가 변하다 보니
FullTextSearch 쪽 기술은 싹이 트기도 전에 말라버려서…
영어쪽 기술은 충분히 지원되지만 한국어 지원은 잘 안된다.

인덱싱

인덱싱을 하면 한 문장이 형태소나 띄어쓰기 등 정해진 기준대로 단어를 분리해서 인덱스를 잡는다.

원래 문장 : ‘피자 맛있다. 햄버거는 몸에 안좋다.’

tsvector1=’피자’:1 ‘맛있다’:2 ‘햄버거는’:3 ‘ 몸에’:4:’안좋다’

tsvector2=’피자’:1 ‘맛있다’:2 ‘햄버거’:3 ‘ 몸’:4:’안좋다’ 5 ‘는’:3 ‘에’

형태소 분석기가 돌아간다면 tsvector2의 결과가 나오겠지만 별다른 설정없이 처리하면 1과같은 결과가 나오게 된다. 이 경우에는 ‘햄버거’를 검색하면 결과가 안 나올 수 있다.

‘서울특별시 종로구 흥인동’ 과 같은 원문이라면 별다른 처리 없이 인덱싱을 해도 검색에 많은 도움이 된다.

한개 컬럼만 인덱싱

gin, gist 인덱스를 두개 다 만든다.

여러컬럼 인덱싱 – tsvector 컬럼 추가

여러컬럼을 동시에 검색해야 하는 경우가 많으니.. 두번째 방법이 주로 사용된다.

검색

참고사이트

Hibernate nativesql 사용시 발생하는 문제

Exception String : org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [coalesce] during auto-discovery of a native-sql query

환경 : Postgresql, Hibernate 4.x, Java7, Spring3.1 … etc

상황 : 복잡한 조인을 사용해서 DB의 데이터를 조회해야하는 상황..HQL은 익숙하지 않고 Criteria로 하기에는 불편한 상황이라서 native sql을 사용하려고 했는데….

pgadmin에서 돌렸을 때 아무 문제도 없는 상황인데 session.createSQLQuery(queryString)을 사용하면 문제가 발생했다.

 

처리 :

alias문제… coalesce 함수를 두번 사용했는데 두개가 동일한 alias name으로 인식된 것 같다. pagadmin에서는 문제가 없었다.  as iteam1, as item2와 같이 두개에 다른 이름을 부여하니 문제없이 정상 동작했다.

 

 

Postgresql Database 시간함수

http://www.postgresql.org/docs/9.1/static/functions-datetime.html

간단한 예제…

part는 hour만 가져온다. 값이 20이 된다.

date_trunc는 timestamp형태를 유지한다. 시간 밑으로는 절삭한다.

 

 

Postgresql SQL update query – column value to another column

한컬럼의 값을 다른 컬럼으로 복사하기

데이터 이동 방향
dept_name = dept_code

 

create query

update query

 

한 테이블을 값을 이용해서 다른테이블 업데이트하기

셀렉트 쿼리처럼 해서 사용하면된다.

다른테이블의 값을 조인해서 다른 테이블 업데이트하기