nahos

PostgreSQL에서 아래와 같이 LIKE를 사용하여 검색을 하면 대소문자 구분을 하여, 결과를 가져오지 못하는 경우

SELECT * FROM notice WHERE title LIKE '%test%'

LIKE 대신 ILIKE 를 사용하면 된다.

SELECT * FROM notice WHERE title ILIKE '%test%'

PostgreSQL에  PostGIS 3.4 설치

Rocky Linux 9

PostGIS 3.4 기준 아래의 링크를 확인하면, 

PostgreSQL 12 ~ 16  버전의 경우 필수 패키지 버전 확인이 가능하다.

https://postgis.net/docs/manual-3.4/postgis_installation.html

필수 패키지 버전을 참고하여, 아래의 링크에서 다운로드

proj 6.1 이상 https://proj.org/ (proj-9.4.0)
GEOS 3.6 이상 https://libgeos.org (geos-3.12.1)
GDAL 3 https://gdal.org/download.html (gdal-3.4.3)
JSON-C 0.9이상 https://github.com/json-c/json-c/releases/ (json-c-0.15)
LibXML2 2.5 이상  https://gitlab.gnome.org/GNOME/libxml2/-/releases (2.12.1)

#아래의 패키지는 필수라고 나와있으나, 설치 링크 연결도 안되고 없어도 설치가 되는 것 확인
PostgreSQL+JIT, LLVM 6이상 https://trac.osgeo.org/postgis/ticket/4125 (설치 안함 - 링크 연결안됨)

- SQLite 버전 업데이트 (PROJ9.4 설치시 sqlite 3.11 버전 이상 필요)

SQLite 버전이 3.11보다 높은경우 다음 단계로 넘어가도록 하자

#SQLite 버전 확인 명령어
sqlite3 --version

-- sqlite tar 압축해제 및 설치 (https://www.sqlite.org/download.html)

tar -xvf sqlite-autoconf-3450300.tar.gz
cd ./sqlite-autoconf-3450300
./configure 
make
make install
sqlite3 --version

-- 환경변수 설정(버전확인시 기존 설치 버전으로 나오기때문에 필요)

vi /etc/profile

export LD_LIBRARY_PATH=/usr/local/lib

source /etc/profile

-- SQLite 버전 확인하여 아래와 같은 버전으로 나오면 완료

sqlite3 --version
3.45.3 2024-04-15 13:34:05

- CMake 설치 (PROJ9.4 설치시 cmake 명령어 필요)

CMake가 설치되어 있다면, 다음 단계로 넘어가도록 하자

#cmake 버전 확인 명령어
cmake --version

-- cmake tar 압축해제 및 설치 (https://cmake.org/download/)

tar -xvf cmake-3.29.3.tar.gz
cd ./cmake-3.29.3
./bootstrap --prefix=/usr/local
make
make install

cmake --version

- PROJ 설치

-- proj tar 압축해제 및 설치 (https://proj.org/en/9.4/download.html)(https://proj.org/en/9.4/install.html)

tar -xvf proj-9.4.0.tar.gz
cd ./proj-9.4.0
mkdir ./build
cd ./build
#외부인터넷이 안되는 환경이므로 BUILD_TESTING=OFF 옵션 적용 
cmake -DBUILD_TESTING=OFF -DCMAKE_C_COMPILER=gcc -DCMAKE_CXX_COMPILER=g++ ..
cmake --build .
cmake --build . --target install
#삭제 방법 cmake --build . --target uninstall

PROJ 설치 실패로 인한 추가 설치, 아래명령어로 확인후 버전에 맞게 설치

rpm -qa|grep libtiff 
rpm -qa|grep libcurl

- GEOS 설치

--geos tar 압축해제 및 설치 (https://libgeos.org/usage/download/)

tar -xvf geos-3.12.1.tar.bz2
cd ./geos-3.12.1
mkdir ./_build
cd ./_build
cmake -DCMAKE_BUILD_TYPE=Release -DCMAKE_INSTALL_PREFIX=/usr/local -DCMAKE_C_COMPILER=gcc -DCMAKE_CXX_COMPILER=g++ ..
make
ctest
make install

- GDAL 설치

-- gdal tar 압축해제 및 설치 (https://gdal.org/download.html / https://gdal.org/development/building_from_source.html)

tar -xvf gdal-3.4.3.tar.gz
cd ./gdal-3.4.3
./configure --with-sqlite3=/usr/local/bin/sqlite3
make
make install

gdalinfo --version

- JSON-C 설치

-- json-c tar 압축해제 및 설치 (https://s3.amazonaws.com/json-c_releases/releases/index.html)

tar -xvf json-c-0.15.tar.gz
cd ./json-c-0.15
mkdir ./build
cd ./build
cmake -DCMAKE_INSTALL_PREFIX=/usr/local ..
make
make install

- LibXML2 설치

-- libxml2가 이미 설치되어있어서, libxml2-devel만 버전에 맞게 설치 

rpm -qa|grep libxml2

- POSTGIS 설치 

-- postgis  tar -xvzf postgis-3.4.2.tar.gz (https://postgis.net/development/source_code/)

tar -xvzf postgis-3.4.2.tar.gz
cd ./postgis-3.4.2
./configure --with-pgconfig=/home/postgres/pgsql/bin/pg_config --with-raster --without-protobuf --with-jsondir=/usr/local

make
make install

(pg_config 파일은 postgresql 설치 경로에 위치)
(protobuf를 사용하지 않으므로  ' --without-protobuf ' 옵션 사용)

(위에서 json-c 수동 설치 했을경우, ' -DCMAKE_INSTALL_PREFIX=/usr/local ' 옵션을 사용했기때문에

' --with-jsondir=/usr/local '  옵션으로 경로로 잡아줘야 json-c 위치를 찾음)

- Extensions 추가 설치

-- POSTGIS

cd extensions/postgis
make clean
make && make install
cd ..

-- POSTGIS_TOPOLOGY

cd postgis_topology
make clean
make && make install

외부 네트워크가 불가능한 상황에서 PostgreSQL 15를 설치하는 방법

Rocky Linux 9

- EPEL 저장소 설치 (https://pkgs.org/download/epel-release)

OS 버전에 맞는 EPEL 을 다운받아 설치해주도록하자

rpm -ivh epel-release-9-7.el9.noarch.rpm

- PostgreSQL Source 파일 다운로드

아래의 URL을 접속하여, 원하는 버전의 tar파일을 다운로드

( 여기서는 postgresql-15.1.tar.gz  다운로드)

https://www.postgresql.org/ftp/source/

- 필수 패키지 확인 및 설치

이 부분이 가장 시간이 많이  소요되며, 외부 인터넷이 안되는 불편함을 느끼는 부분

(아래의 링크를 통해 필수 패키지 버전 확인이 가능하다.)

https://www.postgresql.org/docs/15/install-requirements.html

rpm -qa|grep 명령어를 사용하여, 기존에 설치되어 있는 패키지 및 버전 확인하여 다운로드하여 설치

(추가로 필요한 패키지가 있는지 모르겠지만, 아래 정도만으로 설치 완료)

rpm -qa|grep glibc
rpm -qa|grep make
rpm -qa|grep gcc
rpm -qa|grep gcc-c++
rpm -qa|grep readline
rpm -qa|grep readline-devel
rpm -qa|grep zlib
rpm -qa|grep zlib-devel
rpm -qa|grep autoconf
rpm -qa|grep openssl
rpm -qa|grep openssl-devel
rpm -qa|grep uuid
rpm -qa|grep gettext
rpm -qa|grep gettext-devel

- root 접속

su -

- 유저 생성

groupadd postgres
useradd -g postgres -d /home/postgres postgres
passwd postgres

#유저 확인
cat /etc/passwd

#유저 디렉토리 변경
usermod -d /home/postgres postgres

- PATH 설정

vi /etc/profile

export PG_HOME=/home/postgres/pgsql
export PATH=$PG_HOME/bin:$PATH
export PGDATA=$PG_HOME/data
export LD_LIBRARY_PATH=$PG_HOME/lib

source /etc/profile

- tar 압축 해제 & 설치

https://www.postgresql.org/docs/15/install-procedure.html

tar -xvf postgresql-15.1.tar.gz
cd postgresql-15.1
./configure --prefix=/home/postgres/pgsql --enable-depend --enable-nls=utf-8 -with-openssl --with-ossp-uuid

make world
make check
make install-docs
make install-world

- PostgreSQL 초기화 (initdb)

cd /home/postgres/pgsql
mkdir ./data

cd /home/postgres/pgsql/bin
initdb -E utf-8 -D /home/postgres/pgsql/data

- 설정 파일 수정

postgresql.conf

vi /home/postgres/pgsql/data/postgresql.conf

#아래 내용 수정
#listen addresses ='localhost'
->
listen addresses ='*'

pg_hba.conf

vi /home/postgres/pgsql/data/pg_hba.conf

#아래 내용 추가
host    all             all             0.0.0.0/0               trust
host    all             all             all                     trust

- PostgreSQL 실행

cd /home/postgres/pgsql/bin
pg_ctl start -D /home/postgres/pgsql/data

- PostgreSQL 접속

cd /home/postgres/pgsql/bin
psql
\password postgres
\q

기존에 설치되어 있던 postgresql 을 삭제 해야하는 경우가 있다.

- root 접속

(상황에 따라 sudo 명령어 사용)

su -

- postgresql 종료

systemctl stop postgresql-15.service

- 서비스 상태 보기

systemctl list-unit-files postgresql*

- 서비스 비활성화

systemctl disable postgresql-15

- postgresql 홈 경로 삭제

(보통 /var/lib/pgsql 경로에 있으나, 확인 후 삭제)

rm -rf /var/lib/pgsql

- postgres 계정 삭제

userdel postgres
groupdel postgres

- 설치된 패키지 확인

yum list installed *postgres*

or

rpm -qa|grep postgres

- 설치된 패키지 삭제

yum remove *postgres*

or

rpm -e [package명]

마지막으로 설치된 패키지 확인시 안보이면 완료

Every derived table must have its own alias

 mysql 쿼리 작성중 에러 발생

에러의 원인은 서브 쿼리에 이름을 지정해 주지 않아서 발생

서브 쿼리 뒤에 as 를 사용하여 이름을 지정해주면 된다.

ex)  select name, email, phone from(

select ~~~

) as sub