Tag: Postgresql

Postgresql 11to13 버전 업데이트 도구

짐은 이걸 이용해서 11에서 13으로 업데이트를 하였다

https://hub.docker.com/r/tianon/postgres-upgrade
https://github.com/tianon/docker-postgres-upgrade

sudo docker run --rm -it -v "/현재데이터디렉토리/data:/var/lib/postgresql/11/data/" -v "/타겟디렉토리빈폴더/data:/var/lib/postgresql/13/data" tianon/postgres-upgrade:11-to-13

명령어 한 줄로 되게 만들어 놨다.
업그레이드 다운 받아서 실행 하는거 (별거 아니지만) 귀찮았는데 덕분에 쉽게 처리했다

11 부터 16까지 모든 버전 별로 제공 중

PostgreSQL 9.5 초기설정

기본설정은 버전이 변경되도 크게 바뀌지 않는다

설정파일

postgresql.conf

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

사용자가 늘어서 튜닝을 해야한다 하는상황이 아니라면 
이거말구 크게 바꿀게 없다.
listen_addresses = 'localhost'
listen_addresses = '*'
listen_addresses = '192.168.0.2,localhost'
listen_addresses = '127.0.0.1'

pg_hba.conf

  • https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
개발용이면 보통은 이거한줄 추가하지 않을까
host    all             all             127.0.0.1/0             md5
운영서버라면 서버 ip를 명시하면 되고

서버 범위 지정하는거면 ip주소 쓰는칸에
192.168.0.0/24

터미널접속

sudo -u postgres psql -U postgres -w

사용자 관리

사용자 목록보기
postgres=# \deu
postgres=# \du

create user ${my-user} with password '${my-password}';
create database ${my-database};
grant all privileges on database ${my-database} to ${my-user};

권한문제 나올 때
create user --superuser ${my-user}
alter role ${my-user} superuser;
관리자권한 줘버리는거나 껄쩍지근하기도 한데...
완료후 권한축소
alter role ${my-user} nosuperuser;

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

설치

sudo apt install -y postgresql postgis postgresql-9.5-dbg postgresql-common postgresql-9.5-postgis-2.2 postgresql-9.5-postgis-scripts

설치는 별거 손댈게 없다.

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

sudo apt install python3 python3-dbg python3-dev python3-dbf python3-pip
pip3 install psycopg2

설정

Postgresql

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

postgresql.conf

listen_addresses = 'localhost'

pg_hba.conf

postgres    all    all  0.0.0.0/24    md5

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

계정 설정

root 생성

sudo -u postgres psql -U postgres
alter user postgres with encrypted password 'dkfldk3$';
\q

Postgis

디비 생성후

create database gis_db;
\connect gis_db;
create extension 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

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

How to install PostGIS 2.0 on Ubuntu 12.10 (quantal) from source
Prerequisites

Several components are needed, which can either be built from source or installed from pre-built packages, as shown below.

Install prerequisite packages using:

sudo apt-get install build-essential postgresql-9.1 postgresql-server-dev-9.1 libgeos-c1 libxml2-dev libproj-dev libjson0-dev xsltproc docbook-xsl docbook-mathml
Optional package for raster support (this is required if you want to build the PostgreSQL extensions):

sudo apt-get install libgdal1-dev
Build PostGIS

wget http://download.osgeo.org/postgis/source/postgis-2.0.4.tar.gz
tar xfz postgis-2.0.4.tar.gz
cd postgis-2.0.4
PostGIS 2.0 can be configured to disable topology or raster components, using the configure flags --without-raster and/or --without-topology. The default is to build both. Note that raster is required for the extension installation method for PostgreSQL.

./configure
make
sudo make install
sudo ldconfig
sudo make comments-install
Lastly, enable the command-line tools to work from your shell:

sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/shp2pgsql
sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/pgsql2shp
sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/raster2pgsql
Spatially enabling a database

With PostgreSQL 9.1, there are two methods to add PostGIS functionality to a database: using extensions, or using enabler scripts.

PostGIS Extension for PostgreSQL

Spatially enabling a database using extensions is a new feature of PostgreSQL 9.1.

Connect to your database using pgAdmin or psql, and run the following commands. To add postgis with raster support:

CREATE EXTENSION postgis;
To add topology support, a second extension can be created on the database:

CREATE EXTENSION postgis_topology;
Enabler Scripts / Template

Enabler scripts can be used to either build a template, or directly spatially enable a database. This method is older than the extension method, but is required if the raster support is not built.

The following example creates a template, which can be re-used for creating multiple spatially-enabled databases. Or if you just want to make one spatially enabled database, you can modify the commands for your needs.

PostGIS:

sudo -u postgres createdb template_postgis
sudo -u postgres psql -d template_postgis -c "UPDATE pg_database SET datistemplate=true WHERE datname='template_postgis'"
sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis.sql
sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-2.0/spatial_ref_sys.sql
sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-2.0/postgis_comments.sql
with raster support:

sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-2.0/rtpostgis.sql
sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-2.0/raster_comments.sql
with topology support:

sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-2.0/topology.sql
sudo -u postgres psql -d template_postgis -f /usr/share/postgresql/9.1/contrib/postgis-2.0/topology_comments.sql
See also

 https://help.ubuntu.com/community/PostgreSQL

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 스크립트에서 수정해야된다

# Set defaults for configuration variables
PGENGINE=/usr/pgsql-9.3/bin
PGPORT=5432
#PGDATA=/var/lib/pgsql/9.3/data
PGDATA=/home/postgres/data
#PGLOG=/var/lib/pgsql/9.3/pgstartup.log
PGLOG=/home/postgres/data/pg_log/pgstartup.log
# Log file for pg_upgrade
#PGUPLOG=/var/lib/pgsql/data/$PGMAJORVERSION/pgupgrade.log
PGUPLOG=/home/postgres/data/$PGMAJORVERSION/pgupgrade.log

 

pg_hba.conf

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

# "local" is for Unix domain socket connections only
###trust로하자 편하게
local   all             all                                     trust

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
## 아무데서나 접속하게해준다는 뜻. 하나하나 설정해주는게 보안에 좋다. 내 경우는 방화벽도 있고 암호도 있으니까 그냥 ip는 풀어놨다
host    all             all             0.0.0.0/0               md5

# IPv6 local connections:
host    all             all             ::1/128                 ident

# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident
## 리플레케이션 유저를 설정. trust 또는 md5로 보통 쓴다
host    replication     testuser            192.168.0.102/0         trust

postgresql.conf

listen_addresses = '*'
port = 5432
wal_level = hot_standby
archive_mode = on
archive_command = 'cp %p /home/postgres/data/pg_archive/%f'
max_wal_senders = 3
wal_keep_segments = 8

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_basebackup -h 192.168.0.101 -p 5432 -U testuser -D /home/postgres/data --xlog --checkpoint=fast --progress
# chown postgres:postgres /home/postgres -R
# mkdir /home/postgres/data/pg_archive

pg_hba.conf

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

host    all             all             0.0.0.0/0               md5

postgresql.conf

listen_addresses = '*'
port = 5432
wal_level = hot_standby 
archive_mode = on
archive_command = 'cp %p /home/postgres/data/pg_archive/%f'
max_wal_senders = 2
wal_keep_segments = 8

recoverty.conf

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

restore_command = 'cp /home/postgres/data/pg_archive/%f %p'
archive_cleanup_command = 'pg_archivecleanup /home/postgres/data/pg_archive %r'
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.101 port=5432 user=testuser password=testpass1234'
#trigger_file = '/tmp/pgsql.trigger'

주의할점

로그확인을 잘 하자.

 

 

참고한 페이지

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
나머지는 진짜 참고만 했고 첫번째 링크에 솔루션이 있다.

테이블 두개…

CREATE TABLE alarm_info
(
  alarm_id bigserial PRIMARY KEY,
  alarm_name character varying(20),
  created_at timestamp without time zone DEFAULT now(),
  updated_at timestamp without time zone DEFAULT now(),
  description text,
  admin_ids character varying(255),-- 3,6,7
)

CREATE TABLE admin_info
(
  admin_id bigserial NOT NULL PRIMARY KEY,
  admin_username character varying NOT NULL,
  admin_email character varying NOT NULL,
  admin_password character varying,
  created_at timestamp with time zone NOT NULL,
)

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

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

SELECT	admin_ids
FROM	alarm_info

SELECT	regexp_split_to_array(admin_ids,',')
FROM	alarm_info

SELECT	cast(regexp_split_to_array(admin_ids,',') as integer[])
FROM	alarm_info

SELECT	cast(regexp_split_to_array(admin_ids,',') as integer[])
FROM	alarm_info

select (ARRAY[1, 2])

SELECT * FROM admin_info
WHERE	admin_id = ANY(3,4)


SELECT
  a.admin_id as id,
  a.admin_username as username,
  a.admin_email as email,
  a.admin_phone as phone,
  a.admin_name as name,
  n.subject as subject,
  n.message
FROM
(
SELECT
  cast(regexp_split_to_array(admin_ids,',') as bigint[]) as ids,
  alarm_name as subject,
  alarm_description as message
FROM
  alarm_info
) n, admin_info a
where
  a.admin_id = any(n.ids)

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

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

로그 규칙:

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

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

CREATE TABLE log_upload1
(
  regdate timestamp without time zone NOT NULL,
  filename character varying(100) NOT NULL,
  filenamepath character varying(255),
  logmessage character varying(255),
  PRIMARY KEY (regdate, filename)
);

CREATE TABLE log_upload2
(
  regdate timestamp without time zone NOT NULL,
  filename character varying(100) NOT NULL,
  logmessage character varying(255),
  filenamepath character varying(255),
  CONSTRAINT log_upload2_pkey PRIMARY KEY (filename)
);
CREATE INDEX log_upload2_idx
  ON log_upload2
  USING btree
  (regdate);

CREATE TABLE log_upload3
(
  id bigserial NOT NULL,
  regdate timestamp without time zone NOT NULL,
  filename character varying(100) NOT NULL,
  logmessage character varying(255),
  filenamepath character varying(255),
  PRIMARY KEY (id)
);
CREATE INDEX log_upload3_idx
  ON log_upload3
  USING btree
  (regdate);

CREATE TABLE log_upload4
(
  id bigserial NOT NULL,
  regdate timestamp without time zone NOT NULL,
  filename character varying(100) NOT NULL,
  logmessage character varying(255),
  filenamepath character varying(255),
  PRIMARY KEY (id)
);
CREATE INDEX log_upload4_idx
  ON log_upload4
  USING btree
  (regdate, filename);

– 데이터 누적 입력 테스트

1번 테이블

INSERT INTO log_upload1
(regdate, filename, filenamepath, logmessage)
select 
now(),
md5(random()::text)||nextval('test1'),
md5(random()::text)||currval('test1'),
'no message'
FROM generate_series(1,1500000);

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번 테이블

INSERT INTO log_upload3
(regdate, filename, filenamepath, logmessage)
select 
now(),
md5(random()::text)||nextval('test3'),
md5(random()::text)||currval('test3'),
'no message'
FROM generate_series(1,1500000);

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

4번 테이블

INSERT INTO log_upload4
(regdate, filename, filenamepath, logmessage)
select 
now(),
md5(random()::text)||nextval('test4'),
md5(random()::text)||currval('test4'),
'no message'
FROM generate_series(1,1500000);

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

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

 

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