PostgreSQL - 윈디하나의 솔라나라
|
오브젝트-관계형 데이터베이스관리시스템(object-relational database management system, ORDBMS)이다. 더 자세한 소개는 PostgreSQL: About을 참조하기 바란다.
char, varchar최대 길이가 10MiB (10 * 1024 * 1024)이다. (src/include/access/htup_details.h에 MaxAttrSize으로 정의되어있음) 특히 char(n), varchar(n), text타입에 대해 성능 차이가 없다. 또한 CLOB은 text를 사용하면 된다. PostgreSQL 에서는 1G 까지 지원된다.bytea 에 해당한다. 하지만 여느 DB와 조금 다르다. 8.4. Binary Data Types와 Chapter 34. Large Objects를 읽어보자.
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
CREATE TRIGGER check_update
BEFORE UPDATE ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE PROCEDURE check_account_update();
CREATE TRIGGER view_insert
INSTEAD OF INSERT ON my_view
FOR EACH ROW
EXECUTE PROCEDURE view_insert_row();
CREATE SEQUENCE serial START 101;
SELECT nextval('serial');
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
SELECT name, altitude
FROM cities
WHERE altitude > 500;
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
SELECT title
FROM pgweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;
CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 80
RAISE NOTICE 'Outer quantity here is %', outerblock.quantity; -- Prints 50
END;
RAISE NOTICE 'Quantity here is %', quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
postgres라는 롤 계정도 같이 생성되어있다. 하지만 이 문서에서는 최신 버전 바이너리를 사용한다. 솔라리스에 있는 롤 계정은 그대로 사용할 것이다.curses터미널 제어라이브러리를 새롭게 만든 라이브러리가 ncurses다. curses는 cursor optimizaion이라는 의미다. 일반적으로 terminfo데이터베이스를 사용한다. 솔라리스에는 curses는 제공하지만, ncurses는 기본 제공하지 않기 때문에 별도로 설치해야 한다.ncursesw는 ncurses의 와이드 캐릭터 버전이다. 쉽게 말하자면 UTF-8을 지원하는 라이브러리다. 솔라나라는 터미널 문자셋 기준이 UTF-8이기 때문에 ncursesw를 사용해야 한다.ncursesw를 설치하면 /usr/local/share/terminfo에 터미널 정보 데이터베이스가 설치된다. 솔라리스에는 /usr/share/lib/terminfo가 있는데, 이보다 더 많은 터미널을 지원한다.# wget https://invisible-island.net/archives/ncurses/ncurses-6.3.tar.gz # tar xvfz ncurses-6.3.tar.gz # cd ncurses-6.3 # ./configure --with-shared --enable-widec --disable-overwrite \ --enable-sp-funcs \ --enable-term-driver \ --enable-const \ --enable-ext-colors \ --enable-ext-mouse \ --enable-ext-putwin \ --enable-no-padding \ --enable-sigwinch 1) 2) ... ** Configuration summary for NCURSES 6.3 20211021: extended funcs: yes xterm terminfo: xterm-new bin directory: /usr/local/bin lib directory: /usr/local/lib include directory: /usr/local/include/ncursesw 3) man directory: /usr/local/share/man terminfo directory: /usr/local/share/terminfo ** Include-directory is not in a standard location # make # make install1)
--disable-overwrite파라메터 이후의 파라메터들은 확장용 파라메타다. UTF-8 지원이 가능한 ncursesw 으로 설치한다. 또한 솔라리스에서는 curses라이브러리와 같이 사용하기 위해 --disable-overwrite옵션을 붙인다.CFLAGS="-m64" LDFLAGS="-m64" CXXFLAGS="-m64" 옵션을 붙인다./usr/local/include/ncursesw이다. 꼭 기억하자. 빌드할 때 initscr32 심볼은 찾을 수 없다는 오류가 발생하면 대부분 라이브러리 헤더가 잘못 삽입되었을 때 발생한다.
# wget ftp://ftp.cwru.edu/pub/bash/readline-6.3.tar.gz # tar xvfz readline-6.3.tar.gz # cd readline-6.3 # ./configure --enable-multibyte # make # make install
windy@wl ~/src $ wget https://ftp.postgresql.org/pub/source/v15.0/postgresql-15.0.tar.bz2 windy@wl ~/src $ tar xvfj postgresql-15.0.tar.bz2 windy@wl ~/src $ cd postgresql-15.0 windy@wl ~/src/postgresql-15.0 # CFLAGS="-m64" LDFLAGS="-m64" \ ./configure \ --prefix=/usr/local/postgres/15-pgdg \ --enable-nls=ko \ --enable-thread-safety \ --enable-dtrace \ --with-blocksize=4 \ --with-wal-blocksize=4 \ --with-pam \ --with-libedit-preferred \ --with-openssl \ --with-system-tzdata=/usr/share/lib/zoneinfo \ --with-tclconfig=/usr/sfw/lib \ --with-python \ --with-libxml \ --with-libxslt \ --with-gssapi \ --without-zlib \ --without-readline windy@wl ~/src/postgresql-15.0 # gmake -j 4 ... All of PostgreSQL successfully made. Ready to install. windy@wl ~/src/postgresql-15.0 # gmake install ... PostgreSQL installation complete. windy@wl ~/src/postgresql-15.0 # cd contrib windy@wl ~/src/postgresql-15.0/contrib # gmake windy@wl ~/src/postgresql-15.0/contrib # gmake install
※ pgdg
는 PostgreSQL Global Development Group의 의미다.
PostgreSQL 사이트에서 제공했던 솔라리스 11용 바이너리는 솔라리스 11.3 SRU20 또는 솔라리스 11.4 이상을 필요로 한다. 11.2나 11.1 등 요구사항 이하의 솔라리스 11버전 에 설치해야 한다면, 소스설치하거나, 솔라리스 10용 바이너리를 사용할 수 있다.
PostgreSQL 12.4를 마지막으로 바이너리를 제공해주지 않는다. 각 OS 배포판의 패키지 매니저를 사용하자. 바이너리는 아래와 같이 설치할 수 있다.
windy@wl ~/src $ sudo useradd postgres 1) windy@wl ~/src $ sudo groupadd postgres 1) windy@wl ~/src $ wget https://ftp.postgresql.org/pub/old/binary/v12.4/solaris/solaris11/i386/postgresql-12.4-S11.i386-64.tar.bz2 windy@wl ~/src $ cd /usr/local/ windy@wl /usr/local $ sudo tar xvfj ~windy/src/postgresql-12.4-S11.i386-64.tar.bz2 2)1)
postgres그룹 및 계정을 생성한다.postgres계정으로 파일이 생성된다.
windy@wl ~ $ sudo groupadd -g 90 postgres windy@wl ~ $ sudo useradd -m -s /usr/bin/pfksh -c "PostgreSQL Reserved UID" -s /bin/bash -u 90 -g postgres postgrespostgreSQL의 바이너리 파일에 대해 소유자 및 그룹도 정해주자.
windy@wl ~ $ sudo chown -R postgres:postgres /usr/local/postgres
windy@wl ~ $ sudo su - postgres postgres@wl ~ $ echo $LANG ko_KR.UTF-8 postgres@wl:~$ cd /usr/local/postgres/15-pgdg/share/locale postgres@wl:/usr/local/postgres/15-pgdg/share/locale$ ln -s ko $LANG
PostgreSQL 서버를 실행하기에 앞서 initdb(1)을 사용해 Database Cluster
를 생성해야 한다. Database Cluster
는 저장장치에서 데이터를 저장할 영역을 의미한다. 아래의 예시에서는 /usr/local/postgres/11-pgdg/data디렉토리로 지정했다.
windy@wl ~ $ sudo su - postgres postgres@wl:~$ vi .profile ... export PATH=$PATH:/usr/local/postgres/15-pgdg/bin/64 export PGDATA=/usr/local/postgres/15-pgdg/data postgres@wl:~$ . .profile
이제 데이터를 초기화할 차례다.
postgres@wl:~$ initdb --data-checksum --auth=trust --locale=ko_KR.UTF-8 --pgdata=$PGDATA
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "ko_KR.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "ko_KR.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are enabled.
creating directory /usr/local/postgres/15-pgdg/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... sysv
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Seoul
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
pg_ctl -D /usr/local/postgres/15-pgdg/data -l logfile start
뭔가 잘못되었으면 $PGDATA디렉토리를 삭제하고 initdb(1)를 다시 실행하면 된다. 아래는 initdb(1)의 주요한 옵션에 대해 설명한다.
-A, --auth, --auth-host, --auth-local: 각각에 대해 인증 메소드 지정. 기본값은 trust. 추후 $PGDATA/pg_hba.conf파일을 수정해 변경 할 수 있다.-E, --encoding: 기본 인코딩.--locale: 기본 로케일.-D, --pgdata: 데이터 디렉토리.-U, --username: PostgreSQL에 사용할 슈퍼 유저 이름을 지정. 기본값은 initdb(1) 를 실행한 계정-W, --pwprompt: 슈퍼유저에 사용할 패스워드를 입력할 수 있는 프롬프트를 보임-X, --waldir: WAL을 저장할 디렉토리.--wal-segsize: WAL 세그먼트 크기. MiB 단위.-k, --data-checksums: 데이터 체크섬 활성화. 활성화할 경우 데이터 무결성과 성능에 큰 영향을 준다. (성능은 낮아지지만 데이터 크래시 등으로부터 무결성은 지켜진다. 한번 결정되면 데이터베이스를 삭제하고 다시 만들때까지 변경할 수 없다)데이터베이스를 초기화하면 $PGDATA/postgresql.conf파일과 $PGDATA/pg_hba.conf이 생성된다. 이 파일을 수정해 PostgreSQL의 설정을 변경할 수 있다.
기본적으로 로컬에서만 접속 가능하고, Superuser
속성을 가진, initdb(1) 를 실행한 시스템 계정의 이름과 동일한 이름을 가진 롤(위와 같이 했다면 postgres
이다. PostgreSQL 에서 롤은 사용자다)이 생성되어있다.
PostgreSQL 의 기본이 되는 설정 파일이다. 이 문서에 모든 설정 사항을 담을 수는 없어 필요하다고 생각하는 설정만을 담았다. 솔라리스 기준이며, 리눅스나 윈도에서는 매뉴얼을 참고해 설정하자. 서버 설정 방법 및 사용할 수 있는 파라메터는 Chapter 19. Server Configuration를 참고하자.
postgres@wl ~ $ vi $PGDATA/postgresql.conf listen_addresses = '*' shared_buffers=8000MB temp_buffers = 8MB work_mem=1MB maintenance_work_mem = 256MB wal_sync_method = fdatasync wal_sync_method = fsync synchronous_commit = false full_page_writes = off wal_buffers = 512 commit_delay = 10 checkpoint_segments = 128 #fsync=off #wal_writer_delay
성능 향상을 위한 튜닝에 대해서는 Tuning Your PostgreSQL Server를 읽어보자.
접속을 관리하는 pg_hba.conf파일을 보면 아래와 같이 세팅되어있다. 127.0.0.1/32과 ::1/128 및 유닉스 도메인 소켓을 통해 들어오는 모든 사용자를 신뢰하도록 설정되어있다. 따라서 이런 사용자들은 입력한 패스워드와 관계없이 인증한다.
postgres@wl ~ $ vi $PGDATA/pg_hba.conf # "local" is for Unix domain socket connections only # TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust
이를 바꾸려면 METHOD 부분의 trust부분을 다른 값(예를 들어 md5)으로 변경하고 PostgreSQL 을 재시작하면 된다.
외부에서 접속 가능하게 하려면 아래와 같이 추가하면 된다. 아래의 예에서는 모든 IPv4 IP 에서 패스워드를 사용해 인증할 수 있다.
windy@wl ~ $ sudo vi $PGDATA/pg_hba.conf host all all 0.0.0.0/0 md5
각 컬럼에서 사용 가능한 값은 아래와 같다. 자세한 사항은 Chapter 20. Client Authentication - 20.1. The pg_hba.conf File를 읽어보자.
pg_service)를 작성하자. 이 문서에서는 PostgreSQL 시작/종료가 필요한 경우 pg_service스크립트를 실행하도록 작성되어있다. (소스 배포 파일의 contrib/start-scripts/freebsd를 참고해 작성했다)
postgresql_pg_service.sh | (900 바이트) |
#!/bin/bash
. /etc/profile
. /export/home/postgres/.profile
USER=`id -un`
if [ "$USER" != "postgres" ]; then
echo "Must run as postgres user"
exit 1
fi
if [ ! -d "$PGDATA" ]; then
echo "Must set PGDATA and exists";
exit 1
fi
PGCTL=`which pg_ctl`
if [ ! -x "$PGCTL" ]; then
echo "Must set PATH of PostgreSQL binary (pg_ctl)"
exit 1
fi
case $1 in
start)
$PGCTL -D $PGDATA -l $PGDATA/logfile $1
;;
stop)
$PGCTL -D $PGDATA -l $PGDATA/logfile $1
;;
restart)
if [ -e "$PGDATA/postmaster.pid" ]; then
$PGCTL -D $PGDATA -l $PGDATA/logfile stop
fi
sleep 1
$PGCTL -D $PGDATA -l $PGDATA/logfile start
;;
status)
$PGCTL -D $PGDATA -l $PGDATA/logfile $1
;;
*)
echo "Usage: `basename $0` {start|stop|restart|status}"
exit 1
;;
esac
exit 0
postgres@wl ~ $ wget -O bin/pg_service https://www.solanara.net/contents/includes/postgresql_pg_service.sh postgres@wl ~ $ chmod +x bin/pg_service시작은 아래와 같이 할 수 있다.
windy@wl ~ $ sudo su postgres /export/home/postgres/bin/pg_service start
서버를 시작하기 위해 기다리는 중.... 완료
서버 시작됨
windy@wl ~ $ sudo cat $PGDATA/logfile
...
2019-10-29 00:00:00.000 KST [1860] 로그: 이제 데이터베이스 서버로 접속할 수 있습니다
windy@wl ~ $ netstat -na | grep 5432
*.5432 *.* 0 0 256000 0 LISTEN
*.5432 *.* 0 0 256000 0 LISTEN
...
종료는 아래와 같이 할 수 있다.
windy@wl ~ $ sudo su postgres /export/home/postgres/bin/pg_service stop 서버를 멈추기 위해 기다리는 중.... 완료 서버 멈추었음
PostgreSQL 데몬을 실행했으면, 이제 Database 와 Role, Schema 를 생성하자. 우선 PostgreSQL 에서 Database Cluster, Database, Role, Schema 의 의미는 아래와 같다.
CREATE DATABASE명령을 사용해 생성한다.CREATE SCHEMA명령을 사용해 생성한다.CREATE TABLE명령을 사용해 생성한다.CREATE TABLESPACE명령을 사용해 생성한다.CREATE ROLE명령을 사용해 생성한다.windy@wl ~ $ psql -U postgres postgres=# 1) \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=# CREATE ROLE windy WITH ENCRYPTED PASSWORD '12345' LOGIN; 2) CREATE ROLE postgres=# CREATE DATABASE windy OWNER windy ENCODING 'utf-8' TEMPLATE template0; 3) CREATE DATABASE postgres=# \connect windy You are now connected to database "windy" as user "postgres". windy=# SELECT current_database(); current_database ------------------ windy (1개 행) windy=# CREATE SCHEMA IF NOT EXISTS windy AUTHORIZATION windy; 4)1)
postgres=#에서 "postgres"는 현재 접속한 데이터베이스 이름을, "=" 는 프롬프트 모드를, "#"는 슈퍼유저를 의미한다. 슈퍼유저가 아닌 경우 ">"으로 표시된다. 프롬프트 형식은 psql - PostgreSQL Client Applications를 읽어보자.windy이다.
LOGIN속성을 주어 로그인 가능한 Role로 정의하자. 로그인할 Role 은 createuser(1)을 사용해서도 생성할 수 있다.windy이며 소유자는
windyRole 이다.
UTF-8인코딩을 사용하며, 이를 위해 template0을 사용했다. createdatabase(1)을 사용해서도 생성할 수 있다.이제 생성한 Role 을 사용해 로그인해보자. psql(1)을 사용해 접속하며 -U (사용자명 지정), -W(패스워드 사용 지정), -d(데이터베이스명 지정) 옵션을 사용해 접속해야 한다.
windy@wl ~ $ psql -U windy -W -d windy windy=> SHOW SERVER_ENCODING; 1) server_encoding ----------------- UTF8 (1개 행) windy=> SHOW LC_COLLATE; 2) lc_collate ------------- ko_KR.UTF-8 (1개 행) windy=> SHOW SEARCH_PATH; 3) search_path ----------------- "$user", public (1개 행) windy=> Ctrl+D windy@wl ~ $ psql 4) windy=>1) 인코딩 확인
$user의 의미는 접속한 Role 이름이다. 검색경로를 변경하려면 SET search_path TO 'windy,public'명령을 사용하자. 롤의 기본값을 변경하려면 ALTER ROLE windy SET search_path TO 'windy,public'명령을 사용할 수 있다.-U, -d와 옵션을 주지 않으면 두 옵션은 기본값으로 OS 계정이름을 사용한다. 여기서는 windy 계정으로 psql(1) 을 실행했기 때문에, 결과적으로 -U windy -d windy와 동일하다. 또한 pg_hba.conf 에 로컬(유닉스 도메인 소켓을 사용한 접속)에서는 패스워드 없이 접속 가능하도록 설정했기 때문에 (이 설정이 기본값이다) 결과적으로 psql만 실행해도 접속 된다.
\db명령 또는 pg_tablespace를 조회해 확인할 수 있다. PostgreSQL 이 초기화 되면 아래와 같이 2개의 Tablespace 가 $PGDATA에 생성된다.
postgres@wl ~ $ psql -U postgres
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+--------+-------------
pg_default | postgres | | | | 30 MB |
pg_global | postgres | | | | 623 kB |
(2 rows)
postgres=# SELECT * FROM pg_tablespace;
spcname | spcowner | spcacl | spcoptions
------------+----------+--------+------------
pg_default | 10 | |
pg_global | 10 | |
(2개 행)
테이블 스페이스를 생성하려면 먼저 디렉토리를 생성하고 디렉토리 소유자와 권한을 지정한다.
postgres@wl ~ $ mkdir -p -m 700 /export/home/postgres/tablespace/1
postgres@wl ~ $ psql
postgres=# CREATE TABLESPACE windyspace OWNER windy
LOCATION '/export/home/postgres/tablespace/1';
Database, Table, Index를 생성할 때, 사용할 Tablespace 를 지정할 수 있으며 사용자의 기본 Tablespace 를 지정할 수도 있다. 기본값은 pg_default이다. 아래에 명령 예제를 보였다. 자세한 설명은 Chapter 22. Managing Databases - 22.6. Tablespaces를 참조하자.
CREATE DATABASE windy OWNER windy TABLESPACE windyspace;CREATE TABLE foo(i int) TABLESPACE windyspace;ALTER ROLE windy SET default_tablespace TO 'windyspace';seq_page_cost: 일련의 순차적 인출의 일부인 디스크 페이지 페치 비용의 계획자 추정치. 기본값은 1.0random_page_cost: 순차적으로 가져 오지 않은 디스크 페이지 비용의 계획자 추정치. 기본값은 4.0effective_io_concurrency: PostgreSQL가 동시에 실행할 수있는 동시 디스크 I/O 작업 수. 기본값은 0(동시 실행 하지 않음)
seq_page_cost와 random_page_cost은 SQL Query Plan 의 Cost 를 계산할때 참조되며, 비율이 중요하다. 기본값으로 세팅하면, x4 로 되어있는 셈이다. SSD를 사용하는 경우 비순차 작업도 빠르기 때문에 이 값을 적당히 줄여준다.
effective_io_concurrency는 같은 테이블 스페이스에 물리적으로 디스크가 여러개 붙어있는 경우 동시작업을 해볼만 하다.
테이블 스페이스의 파라메터값은 아래와 같은 명령으루 수정할 수 있다.
ALTER TABLESPACE windyspace set ( seq_page_cost=1.0, random_page_cost=1.0 );
PostgreSQL 을 처음 접할때 가장 혼동되는 것중 하나가, 예시된 명령에는 사용자이름, 패스워드, DB이름이 없는데도 PostgreSQL 에 접속되는거 같은데, 도대체 어느 계정으로 어느 DB에 어떤 권한을 가지고 어떻게 접속하는지 명확하지 않기 때문이다. "DB계정으로 SSH 접속해서 psql 만 치면 들어가져요."라고 답변 받고 잘 사용하다가 외부에서 접속하려 할때 부랴부랴 접속 세팅하는 경우를 보곤 했다. 이 문서를 읽을때에도 혼동되지 않도록 프롬프트를 잘 살펴보면서 읽자.
PostgreSQL 의 이념이 그렇다. 세팅해놓고 커맨드에서는 마치 계정에 특화된 유닉스 커맨드를 사용하듯 DB를 사용하면 된다. 이를 위해 PostgreSQL은 여러가지 기능을 지원한다. PGDATABASE, PGHOST, PGPORT, PGUSER, PGPASSWORD 등의 환경변수를 설정해 기본으로 사용할 접속정보를 지정할 수 있기 때문에, 더더욱 현재 접속하고 있는 롤과 데이터베이스를 확인해야 한다. 사용할 수 있는 환경 변수에 대한 설명은 Chapter 34. libpq - C Library - 34.14. Environment Variables를 읽어보자.
별도의 설정이나 옵션을 주지 않으면 PostgreSQL 명령은 프로세스의 Effective UserID를 기준으로 기본 사용자와 기본 DB를 정한다. 쉽게 말하면 --dbname, --username의 파라메터값이 유닉스 계정 이름이 된다. 현재 접속한 유닉스 계정 이름은 id(1)을 사용해 알아낼 수 있다.
windy@wl ~ $ id -un windy
-U <USER NAME> -W -d <DATABASE NAME>옵션을 주면 된다. PostgreSQL 의 커맨드라인 유틸리티는 대부분 이렇게 작동한다.psql [OPTION]... [DBNAME [USERNAME]]일반 옵션:
-c, --command=COMMAND 하나의 명령(SQL 또는 내부 명령)만 실행하고 끝냄
-d, --dbname=DBNAME 연결할 데이터베이스 이름(기본 값: "유효사용자ID")
-f, --file=FILENAME 파일 안에 지정한 명령을 실행하고 끝냄
-l, --list 사용 가능한 데이터베이스 목록을 표시하고 끝냄
-v, --set=, --variable=NAME=VALUE
psql 변수 NAME을 VALUE로 설정
(예, -v ON_ERROR_STOP=1)
-V, --version 버전 정보를 보여주고 마침
-X, --no-psqlrc 시작 파일(~/.psqlrc)을 읽지 않음
-1 ("one"), --single-transaction
명령 파일을 하나의 트랜잭션으로 실행
-?, --help[=options] 이 도움말을 표시하고 종료
--help=commands psql 내장명령어(\문자로 시작하는)를 표시하고 종료
--help=variables 특별 변수들 보여주고, 종료
입출력 옵션:
-a, --echo-all 스크립트의 모든 입력 표시
-b, --echo-errors 실패한 명령들 출력
-e, --echo-queries 서버로 보낸 명령 표시
-E, --echo-hidden 내부 명령이 생성하는 쿼리 표시
-L, --log-file=FILENAME 세션 로그를 파일로 보냄
-n, --no-readline 확장된 명령행 편집 기능을 사용중지함(readline)
-o, --output=FILENAME 쿼리 결과를 파일(또는 |파이프)로 보냄
-q, --quiet 자동 실행(메시지 없이 쿼리 결과만 표시)
-s, --single-step 단독 순차 모드(각 쿼리 확인)
-S, --single-line 한 줄 모드(줄 끝에서 SQL 명령이 종료됨)
출력 형식 옵션:
-A, --no-align 정렬되지 않은 표 형태의 출력 모드
-F, --field-separator=STRING
unaligned 출력용 필드 구분자 설정(기본 값: "|")
-H, --html HTML 표 형태 출력 모드
-P, --pset=VAR[=ARG] 인쇄 옵션 VAR을 ARG로 설정(\pset 명령 참조)
-R, --record-separator=STRING
unaligned 출력용 레코드 구분자 설정
(기본 값: 줄바꿈 문자)
-t, --tuples-only 행만 인쇄
-T, --table-attr=TEXT HTML table 태그 속성 설정(예: width, border)
-x, --expanded 확장된 표 형태로 출력
-z, --field-separator-zero
unaligned 출력용 필드 구분자를 0 바이트로 지정
-0, --record-separator-zero
unaligned 출력용 레코드 구분자를 0 바이트로 지정
연결 옵션들:
-h, --host=HOSTNAME 데이터베이스 서버 호스트 또는 소켓 디렉터리
(기본값: "로컬 소켓")
-p, --port=PORT 데이터베이스 서버 포트(기본 값: "5432")
-U, --username=USERNAME 데이터베이스 사용자 이름(기본 값: "유효사용자ID")
-w, --no-password 암호 프롬프트 표시 안 함
-W, --password 암호 입력 프롬프트 보임(자동으로 처리함)
windy@wl ~ $ psql -n
windy=> CREATE TABLE x (
a varchar(10)
);
windy=> INSERT INTO x(a) VALUES ('가');
INSERT INTO x(a) VALUES ('A');
INSERT INTO x(a) VALUES ('1');
INSERT INTO x(a) VALUES ('다');
INSERT INTO x(a) VALUES ('똠');
INSERT INTO x(a) VALUES ('ㄱ');
INSERT INTO x(a) VALUES ('9');
INSERT INTO x(a) VALUES ('a');
INSERT INTO x(a) VALUES ('하');
INSERT INTO x(a) VALUES ('ㅏ');
INSERT INTO x(a) VALUES ('z');
INSERT INTO x(a) VALUES ('Z');
windy=> select * from x order by a;
a
----
1
9
a
A
z
Z
ㄱ
가
다
똠
하
ㅏ
(12 rows)
ORDER BY구문에서 정렬이 위와 다르게 나온다. 기본적으로 정렬은 소문자 a 이후 대문자 A 가 나오도록 해야한다..pgpass 파일을 생성해 놓으면 psql(1) 에서 자동으로 읽어와 패스워드를 입력해준다. 입력 형식은 hostname:port:database:username:password 이다. * 를 사용하면 전체라는 의미로 해석된다.
windy@wl ~ $ vi ~/.pgpass 127.0.0.1:5432:sample:user1:12345 windy@wl ~ $ chmod 600 ~/.pgpass windy@wl ~ $ psql -h 127.0.0.1-U user1 -d sample
PGPASSWORD 환경변수에 패스워드를 설정해 실행할 수 있다. 하지만 이 방법은 프로세스 유틸리티(예를 들어 ps(1))를 사용해 특정 프로세스의 환경변수를 읽어올 수 있기 때문에 권장하지 않는다.
windy@wl ~ $ PGPASSWORD='12345' pg_dump -h 127.0.0.1-U user1 -d sample
PostgreSQL에서 벤치마킹을 할 수 있는 툴이다. 소스 설치했다면 소스 배포판의 contrib/pgbench에서 별도로 설치해야 한다. 패키지로 설치했다면 번들되어있을 것이다. 툴에 대한 자세한 사용방법은 PostgreSQL Client Applications - pgbench을 읽어보자. PostgreSQL의 튜닝에 관련해서는 Performance Optimization - PostgreSQL wiki을 읽어보자.
기본적인 벤치 메소드는 TPC-B와 유사하다.
windy@wl ~/src/postgresql-9.4.1 $ cd contrib/pgbench windy@wl ~/src/postgresql-9.4.1/contrib/pgbench # make windy@wl ~/src/postgresql-9.4.1/contrib/pgbench # make install
-i옵션으로 먼저 테이블을 생성하고 초기화한다.
windy@wl ~ $ pgbench -i dropping old tables... 알림: "pgbench_accounts" 테이블 없음, 무시함 알림: "pgbench_branches" 테이블 없음, 무시함 알림: "pgbench_history" 테이블 없음, 무시함 알림: "pgbench_tellers" 테이블 없음, 무시함 creating tables... generating data... 100000 of 100000 tuples (100%) done (elapsed 0.50 s, remaining 0.00 s) vacuuming... creating primary keys... done.이제 벤치마크를 실행할 차례다.
windy@wl ~ $ pgbench -c 10 -j 10 -t 1000 starting vacuum...end. transaction type: <builtin: TPC-B (sort of)> scaling factor: 1 query mode: simple number of clients: 10 number of threads: 10 number of transactions per client: 1000 number of transactions actually processed: 10000/10000 latency average = 26.320 ms tps = 379.938441 (including connections establishing) tps = 380.571649 (excluding connections establishing)
postgres@wl ~ $ sudo su - postgres psql --dbname windy windy=# create extension dblink SCHEMA windy; CREATE EXTENSION아래와 같이 사용할 수 있다.
windy@wl ~ $ psql
windy=> select dblink_connect('MyDBLink', 'hostaddr=192.168.0.100 user=hana password=hana dbname=hana port=5432');
windy=> select * from dblink('MyDBLink', 'select user from userdb') as name (user varchar(100));
windy=> select dblink_disconnect('MyDBLink')
postgres@wl ~ $ psql
postgres=# CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION
postgres=# SELECT
encode(
encrypt(
convert_to('sample text', 'utf8'),
'key',
'aes'
),
'hex'
);
encode
----------------------------------
f859f8abea6d78a58871a0f986a468bf
(1개 행)
postgres=# SELECT
convert_from(
decrypt(
decode('f859f8abea6d78a58871a0f986a468bf','hex'),
'key',
'aes'
),
'utf8'
);
convert_from
--------------
sample text
(1개 행)
windy@wl ~ $ pg_dump
...
CREATE SCHEMA windy;
ALTER SCHEMA windy OWNER TO windy;
...
CREATE TABLE public.x (
a character varying(10)
);
ALTER TABLE public.x OWNER TO windy;
...
COPY public.x (a) FROM stdin;
가
A
...
스키마 생성부터, 테이블 생성, 소유자 변경 및 데이터까지 출력해주는 것을 확인할 수 있다. 이 출력을 그대로 파일로 저장하면 된다.
windy@wl ~ $ pg_dump > windy.sql
windy@wl ~ $ psql < windy.sql
pg_dump [options] [dbname]일반
-f, --file=파일이름 출력 파일 또는 디렉터리 이름
-F, --format=c|d|t|p 출력 파일 형식(사용자 지정, 디렉터리, tar,
일반 텍스트(초기값))
-j, --jobs=개수 덤프 작업을 병렬 처리 함
-v, --verbose 작업 내역을 자세히 봄
-V, --version 버전 정보를 보여주고 마침
-Z, --compress=0-9 출력 자료 압축 수위
--lock-wait-timeout=초 테이블 잠금 시 지정한 초만큼 기다린 후 실패
--no-sync fsync 작업 생략
-?, --help 이 도움말을 보여주고 마침
출력
-a, --data-only 스키마 빼고 자료만 덤프
-b, --blobs Large Object들도 함께 덤프함
-B, --no-blobs Large Object들을 제외하고 덤프함
-c, --clean 다시 만들기 전에 데이터베이스 개체 지우기(삭제)
-C, --create 데이터베이스 만드는 명령구문도 포함시킴
-E, --encoding=인코딩 지정한 인코딩으로 자료를 덤프 함
-n, --schema=SCHEMA 지정한 SCHEMA들 자료만 덤프
-N, --exclude-schema=SCHEMA 지정한 SCHEMA들만 빼고 모두 덤프
-o, --oids OID 포함해서 덤프
-O, --no-owner 일반 텍스트 형식에서
개체 소유권 복원 건너뛰기
-s, --schema-only 자료구조(스키마)만 덤프
-S, --superuser=NAME 일반 텍스트 형식에서 사용할 슈퍼유저 사용자 이름
-t, --table=TABLE 지정한 이름의 테이블들만 덤프
-T, --exclude-table=TABLE 지정한 테이블들만 빼고 덤프
-x, --no-privileges 접근 권한 (grant/revoke) 정보는 덤프 안 함
--binary-upgrade 업그레이드 유틸리티 전용
--column-inserts 칼럼 이름과 함께 INSERT 명령으로 자료 덤프
--disable-dollar-quoting $ 인용 구문 사용안함, SQL 표준 따옴표 사용
--disable-triggers 자료만 복원할 때 트리거 사용을 안함
--enable-row-security 로우 보안 활성화 (현재 작업자가 접근할 수
있는 자료만 덤프 함)
--exclude-table-data=테이블 해당 테이블 자료는 덤프 안함
--if-exists 객체 삭제 시 IF EXISTS 구문 사용
--inserts COPY 대신 INSERT 명령으로 자료 덤프
--load-via-partition-root 상위 테이블을 통해 하위 테이블을 로드함
--no-comments 코멘트는 덤프 안함
--no-publications 발행 정보는 덤프하지 않음
--no-security-labels 보안 라벨 할당을 덤프 하지 않음
--no-subscriptions 구독 정보는 덤프하지 않음
--no-synchronized-snapshots 병렬 작업에서 스냅샷 일관성을 맞추지 않음
--no-tablespaces 테이블스페이스 할당을 덤프하지 않음
--no-unlogged-table-data 언로그드 테이블 자료는 덤프하지 않음
--quote-all-identifiers 예약어가 아니여도 모든 식별자는 따옴표를 씀
--section=SECTION 해당 섹션(pre-data, data, post-data)만 덤프
--serializable-deferrable 자료 정합성을 보장하기 위해 덤프 작업을
직렬화 가능한 트랜잭션으로 처리 함
--snapshot=SNAPSHOT 지정한 스냅샷을 덤프 함
--strict-names 테이블이나 스키마를 지정했을 때 그 패턴에 맞는
객체가 적어도 하나 이상 있어야 함
--use-set-session-authorization
SET SESSION AUTHORIZATION 명령을 ALTER OWNER 명령
대신 사용하여 소유권 설정
연결
-d, --dbname=DBNAME 덤프할 데이터베이스 -h, --host=HOSTNAME 접속할 데이터베이스 서버 또는 소켓 디렉터리 -p, --port=PORT 데이터베이스 서버의 포트 번호 -U, --username=NAME 연결할 데이터베이스 사용자 -w, --no-password 암호 프롬프트 표시 안 함 -W, --password 암호 입력 프롬프트 보임(자동으로 처리함) --role=ROLENAME 덤프 전에 SET ROLE 수행
pg_restore [options] [files]일반
-d, --dbname=NAME 접속할 데이터베이스 이름 -f, --file=FILENAME 출력 파일 이름 -F, --format=c|d|t 백업 파일 형식 (지정하지 않으면 자동분석) -l, --list 자료의 요약된 목차를 보여줌 -v, --verbose 자세한 정보 보여줌 -V, --version 버전 정보를 보여주고 마침 -?, --help 이 도움말을 보여주고 마침리스토어
-a, --data-only 스키마는 빼고 자료만 입력함 -c, --clean 다시 만들기 전에 데이터베이스 개체 지우기(삭제) -C, --create 작업 대상 데이터베이스를 생성 -e, --exit-on-error 오류가 생기면 끝냄, 기본은 계속 진행함 -I, --index=NAME 지정한 인덱스 생성 -j, --jobs=NUM 여러 병렬 작업을 사용하여 복원 -L, --use-list=FILENAME 출력을 선택하고 해당 순서를 지정하기 위해 이 파일의 목차 사용 -n, --schema=NAME 해당 스키마의 개체들만 복원함 -N, --exclude-schema=NAME 해당 스키마의 개체들은 복원 안함 -O, --no-owner 개체 소유권 복원 건너뛰기 -P, --function=NAME(args) 지정한 함수 생성 -s, --schema-only 자료구조(스키마)만 생성 -S, --superuser=NAME 트리거를 사용하지 않기 위해 사용할 슈퍼유저 사용자 이름 -t, --table=NAME 복원할 객체 이름 (테이블, 뷰, 기타) -T, --trigger=NAME 지정한 트리거 생성 -x, --no-privileges 접근 권한(grant/revoke) 지정 안함 -1, --single-transaction 하나의 트랜잭션 작업으로 복원함 --disable-triggers 자료만 복원할 때 트리거 사용을 안함 --enable-row-security 로우 보안 활성화 --if-exists 객체 삭제 시 IF EXISTS 구문 사용 --no-comments 코멘트는 복원하지 않음 --no-data-for-failed-tables 만들 수 없는 테이블에 대해서는 자료를 덤프하지 않음 --no-publications 발행 정보는 복원 안함 --no-security-labels 보안 라벨을 복원하지 않음 --no-subscriptions 구독 정보는 복원 안함 --no-tablespaces 테이블스페이스 할당을 복원하지 않음 --section=SECTION 지정한 섹션만 복원함 섹션 종류: pre-data, data, post-data --strict-names 테이블이나 스키마를 지정했을 때 그 패턴에 맞는 객체가 적어도 하나 이상 있어야 함 --use-set-session-authorization SET SESSION AUTHORIZATION 명령을 ALTER OWNER 명령 대신 사용하여 소유권 설정연결
-h, --host=HOSTNAME 접속할 데이터베이스 서버 또는 소켓 디렉터리 -p, --port=PORT 데이터베이스 서버의 포트 번호 -U, --username=NAME 연결할 데이터베이스 사용자 -w, --no-password 암호 프롬프트 표시 안 함 -W, --password 암호 입력 프롬프트 보임(자동으로 처리함) --role=ROLENAME 복원 전에 SET ROLE 수행
$PGDATA에 지정된 위치 (이 문서에서는 /usr/local/postgres/11-pgdg/data)에 저장된다. 따라서 PostgreSQL 을 셧다운 하고 이 파일들을 백업하면 백업이 완료된다. 복원도 이 파일들을 복원하고 PostgreSQL을 시작하면 된다. 가장 쉽고 가장 빠르게 백업 및 복원할 수 있는 장점이 있지만, 데이터베이스를 셧다운 해야 한다는 단점이 있다.pg_default, pg_global 외에 생성한 테이블 스페이스가 있다면), 해당 테이블 스페이스에 지정된 디렉토리도 같이 백업받아야 한다. 테이블 스페이스는 위에서 언급했듯 \db명령으로 확인할 수 있다.postgres@wl ~ $ bin/pg_service stop postgres@wl ~ $ cd $PGDATA/.. postgres@wl /usr/local/postgres/11-pgdg $ tar cvfz postgresql_data.tar.gz data
postgres@wl ~ $ bin/pg_service stop postgres@wl ~ $ cd $PGDATA/.. postgres@wl /usr/local/postgres/11-pgdg $ tar xvfz postgresql_data.tar.gz data postgres@wl /usr/local/postgres/11-pgdg $ ~/bin/pg_service start
postgres@wl ~ $ mkdir -p /export/home/postgres/backup/archive postgres@wl ~ $ vi $PGDATA/postgresql.conf wal_level = replica archive_mode = on archive_command = 'test ! -f /export/home/postgres/backup/archive/%f && cp %p /export/home/postgres/backup/archive/%f' postgres@wl ~ $ bin/pg_service stop postgres@wl ~ $ bin/pg_service start
postgres@wl ~ $ pg_basebackup --format t --gzip -D /export/home/postgres/backup/base
/export/home/postgres/backup/base 디렉토리를 보면 두개의 tar.gz 파일 (base.tar.gz, pg_wal.tar.gz) 이 있다. 설정 파일과 현재까지의 WAL 백업 파일이다.
archive_command에 설정된 커맨드를 실행해 지정한 디렉토리로 복사한다.postgres@wl ~ $ bin/pg_service stop postgres@wl ~ $ rm -rf $PGDATA데이터 디렉토리를 생성하고, Base Backup 을 복원한다.
postgres@wl ~ $ mkdir -m 750 $PGDATA postgres@wl ~ $ gtar xvfz /export/home/postgres/backup/base/base.tar.gz -C $PGDATA postgres@wl ~ $ gtar xvfz /export/home/postgres/backup/base/pg_wal.tar.gz -C $PGDATA/pg_wal/리커버리 명령을 설정한 후 서버를 시작한다.
postgres@wl ~ $ vi $PGDATA/recovery.conf restore_command = 'cp /export/home/postgres/backup/archive/%f %p' #recovery_target_time = '2019-10-04 00:00:00 KST' postgres@wl ~ $ bin/pg_service start
PostgreSQL은 한국어용 전문검색(FTS, Full Text Search)모듈을 제공하지 않는다. 따라서 기본값인 simple
모듈이 선택된다. 이는 공백문자 단위로 형태소를 분석하기 때문에, 한국어에 그대로 적용하는 경우 불필요한 인덱스가 생성된다. 기본적으로 전문검색은 속도향상을 위해 문장에서 의미있는 형태소만을 추출해 인덱싱해야 한다.
일본어용 형태소 엔진인 mecab 을 한국어에 맞게 수정한 mecab-ko 을 사용해 PostgreSQL 에 한국어용 형태소 분석 기반 전문검색기를 설치하는 방법에 대해 설명한다. 이 단락은 textsearch_ko을 참고했다.
오픈소스 한글 전문검색 모듈인 mecab-ko 를 설치하자.
windy@wl ~/src $ wget https://bitbucket.org/eunjeon/mecab-ko/downloads/mecab-0.996-ko-0.9.2.tar.gz windy@wl ~/src $ tar xvfz mecab-0.996-ko-0.9.2.tar.gz windy@wl ~/src $ cd mecab-0.996-ko-0.9.2 windy@wl ~/src/mecab-0.996-ko-0.9.2 $ ./configure CC=gcc CXX=g++ CFLAGS="-m64" CXXFLAGS="-m64" windy@wl ~/src/mecab-0.996-ko-0.9.2 $ make windy@wl ~/src/mecab-0.996-ko-0.9.2 $ sudo make install
mecab-ko 에서 사용할 사전을 설치하자.
windy@wl ~/src $ wget https://bitbucket.org/eunjeon/mecab-ko-dic/downloads/mecab-ko-dic-2.1.1-20180720.tar.gz windy@wl ~/src $ tar xvfz mecab-ko-dic-2.1.1-20180720.tar.gz windy@wl ~/src $ cd mecab-ko-dic-2.1.1-20180720 windy@wl ~/src/mecab-ko-dic-2.1.1-20180720 $ ./autogen.sh windy@wl ~/src/mecab-ko-dic-2.1.1-20180720 $ ./configure windy@wl ~/src/mecab-ko-dic-2.1.1-20180720 $ make windy@wl ~/src/mecab-ko-dic-2.1.1-20180720 $ sudo make install
아래와 같이 테스트해보자.
windy@wl ~ $ mecab -d /usr/local/lib/mecab/dic/mecab-ko-dic mecab-ko-dic은 MeCab을 사용하여, 한국어 형태소 분석을 하기 위한 프로젝트입니다. mecab SL,*,*,*,*,*,*,* - SY,*,*,*,*,*,*,* ko SL,*,*,*,*,*,*,* - SY,*,*,*,*,*,*,* dic SL,*,*,*,*,*,*,* 은 JX,*,T,은,*,*,*,* MeCab SL,*,*,*,*,*,*,* 을 JKO,*,T,을,*,*,*,* 사용 NNG,행위,T,사용,*,*,*,* 하 XSV,*,F,하,*,*,*,* 여 EC,*,F,여,*,*,*,* , SC,*,*,*,*,*,*,* 한국어 NNG,*,F,한국어,Compound,*,*,한국/NNG/*+어/NNG/* 형태소 NNG,*,F,형태소,Compound,*,*,형태/NNG/*+소/NNG/* 분석 NNG,행위,T,분석,*,*,*,* 을 JKO,*,T,을,*,*,*,* 하 VV,*,F,하,*,*,*,* 기 ETN,*,F,기,*,*,*,* 위한 VV+ETM,*,T,위한,Inflect,VV,ETM,위하/VV/*+ㄴ/ETM/* 프로젝트 NNG,*,F,프로젝트,*,*,*,* 입니다 VCP+EF,*,F,입니다,Inflect,VCP,EF,이/VCP/*+ㅂ니다/EF/* . SF,*,*,*,*,*,*,* EOS ^C
PostgreSQL용 전문 검색 모듈을 설치하자.
windy@wl ~/src $ wget https://github.com/i0seph/textsearch_ko/archive/master.zip windy@wl ~/src $ unzip textsearch_ko-master.zip windy@wl ~/src $ cd textsearch_ko-master #C_INCLUDE_PATH=/usr/local/include LIBRARY_PATH=/usr/local/lib make USE_PGXS=1 install 으로 실행시켜야 하나 오류가 발생해 아래와 같이 풀어서 적용함 windy@wl ~/src/textsearch_ko-master $ gcc -m64 -fpic -I. -I./ -I/usr/local/postgres/11-pgdg/include/64/server -I/usr/local/postgres/11-pgdg/include/64/internal -I/usr/include/libxml2 -I/export/home/tmp/pg_build/proto-64/usr/include -c -o ts_mecab_ko.o ts_mecab_ko.c windy@wl ~/src/textsearch_ko-master $ gcc -m64 -G -o ts_mecab_ko.so ts_mecab_ko.o -L/usr/local/postgres/11-pgdg/lib/64 -L/export/home/tmp/pg_build/proto-64/usr/lib/64 -L/usr/sfw/lib/64 -L/usr/lib/64 -Wl,--as-needed -Wl,-R'/usr/local/postgres/11-pgdg/lib/64' -lmecab windy@wl ~/src/textsearch_ko-master $ /usr/bin/gmkdir -p '/usr/local/postgres/11-pgdg/lib/64' windy@wl ~/src/textsearch_ko-master $ /usr/bin/gmkdir -p '/usr/local/postgres/11-pgdg/share/contrib' windy@wl ~/src/textsearch_ko-master $ /usr/bin/ginstall -c -m 755 ts_mecab_ko.so '/usr/local/postgres/11-pgdg/lib/64/ts_mecab_ko.so' windy@wl ~/src/textsearch_ko-master $ /usr/bin/ginstall -c -m 644 .//ts_mecab_ko.sql .//uninstall_ts_mecab_ko.sql '/usr/local/postgres/11-pgdg/share/contrib/' # 여기까지
빌드한 모듈을 로드해 테스트해보자.
windy@wl ~ $ psql -f ts_mecab_ko.sql windy
...
windy@wl ~ $ psql -n
windy=> select * from mecabko_analyze('무궁화꽃이 피었습니다.');
word | type | part1st | partlast | pronounce | conjtype | conjugation | basic | detail | lucene
--------+------+---------+----------+-----------+----------+-------------+-------+---------------------+--------
무궁화 | NNG | | F | 무궁화 | Compound | | | 무궁/NNG/*+화/NNG/* | 무궁화
꽃 | NNG | | T | 꽃 | | | | | 꽃
이 | JKS | | F | 이 | | | | | 이
피 | VV | | F | 피 | | | | | 피
었 | EP | | T | 었 | | | | | 었
습니다 | EF | | F | 습니다 | | | | | 습니다
. | SF | | | . | | | | | .
(7개 행)
기본값인 simple
은 아래와 같이 형태소가 분석되어 나온다.
windy@wl ~ $ psql -n
windy=> select * from to_tsvector('무궁화꽃이 피었습니다.');
to_tsvector
-------------------------------
'무궁화꽃이':1 '피었습니다':2
(1개 행)
windy=> select * from to_tsvector('그래서, 무궁화꽃이 피겠는걸요?');
to_tsvector
------------------------------------------
'그래서':1 '무궁화꽃이':2 '피겠는걸요':3
(1개 행)
전문검색기를 로드한 상태에서 korean
으로 설정하면 아래와 같이 형태소가 분석되어 나온다.
windy@wl ~ $ psql -n
windy=> set default_text_search_config = korean;
SET
windy=> select * from to_tsvector('무궁화꽃이 피었습니다.');
to_tsvector
--------------------------
'꽃':2 '무궁화':1 '피':3
(1개 행)
windy=> select * from to_tsvector('그래서, 무궁화꽃이 피겠는걸요?');
to_tsvector
--------------------------
'꽃':2 '무궁화':1 '피':3
(1개 행)
root@wll:~# sudo apt install curl ca-certificates root@wll:~# sudo install -d /usr/share/postgresql-common/pgdg root@wll:~# sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc root@wll:~# sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' root@wll:~# sudo apt update root@wll:~# sudo apt -y install postgresql root@wll:~# sudo apt -y install postgresql-contrib최신버전으로 설치하며,
apt upgrade 명령을 사용해 해당 버전을 패치할 수 있다.
root@wll:~# vi /etc/postgresql/15/main/postgresql.conf listen_addresses = '*' max_connections = 500 root@wll:~# vi /etc/postgresql/15/main/pg_hba.conf host all all 0.0.0.0/0 scram-sha-256 root@wll:~# systemctl restart postgresql
root@wll:~# su - postgres postgres@wll:~$ psql CREATE ROLE sample WITH ENCRYPTED PASSWORD 'password' LOGIN; CREATE DATABASE sample OWNER sample ENCODING 'utf-8' TEMPLATE template0; \connect sample CREATE SCHEMA IF NOT EXISTS sample AUTHORIZATION sample; CREATE EXTENSION pgcrypto; CREATE EXTENSION dblink; postgres@wll:~$ psql -h localhost -U sample -W -d sample \l
PostgreSQL 메이저 버전이 업그레이드 된 경우에도, apt upgrade명령 실행시 패키지만 설치 될뿐 업그레이드 되지 않는다. 이런 경우 업그레이드 하는 방법에 대해 설명한다.
우선 현재 상태를 확인해야 한다. pg_lsclusters(1)명령을 사용하면 된다.
root@wll:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log 16 main 5433 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
15, 16 패키지가 설치되어있다. PostgreSQL 15버전이 최신버전일때 설치했고, apt upgrade 명령을 실행시켜서 우분투를 패치했으며, 이때 최신 패키지인 16 이 설치된 상태다. 현재 사용하고 있는 버전은 15 이다.
16 을 사용하고 있지 않지만, 어쨌든 online 상태이므로, pg_dropcluster(1)을 사용해 삭제한다.
root@wll:~# pg_dropcluster --stop 16 main root@wll:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
15 를 최신버전으로 업그레이드 한다. pg_upgradecluster(1)을 사용한다. 아래와 같은 형식으로 사용할 수 있다.
pg_upgradecluster [-v 신규버전] 구버전 클러스터명 [신규데이터디렉토리]
신규버전을 명시하지 않으면 시스템에 설치되어있는 가장 높은 버전으로 업그레이드 한다. 업그레이드 도중에는 데이터베이스를 사용할 수 없으며, 사용하고 있는 데이터베이스, 스키마, 테이블, 데이터가 많을 수록 업그레이드 시간이 오래걸린다.
root@wll:~# pg_upgradecluster -v 16 15 main
Stopping old cluster...
Restarting old cluster with restricted connections...
Notice: extra pg_ctl/postgres options given, bypassing systemctl for start operation
Creating new PostgreSQL cluster 16/main ...
/usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main --auth-local peer --auth-host scram-sha-256 --no-instructions --encoding UTF8 --lc-collate ko_KR.UTF-8 --lc-ctype ko_KR.UTF-8 --locale-provider libc
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
...
Upgrading database sample...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Starting upgraded cluster on port 5432...
Running finish phase upgrade hook scripts ...
vacuumdb: processing database "sample": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "sample": Generating default (full) optimizer statistics
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 15 main
Ver Cluster Port Status Owner Data directory Log file
15 main 5433 down postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
Ver Cluster Port Status Owner Data directory Log file
16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
아래와 같이 실행되어있음을 확인할 수 있다.
root@wll:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 15 main 5433 down postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log 16 main 5432 online postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log
업그레이드가 잘 되었는지 확인해 본 후 업그레이드가 잘 되었으면 구 클러스터는 삭제하자.
root@wll:~# pg_dropcluster 15 main
| RSS ATOM XHTML 5 CSS3 |
Copyright © 2004-2025 Jo HoSeok. All rights reserved. |