[PostgreSQL - 12.3 install]

 Yum 이용 설치

[root@localhost ~]# sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

[root@localhost ~]# sudo yum install -y postgresql12-server sudo /usr/pgsql-12/bin/postgresql-12-setup initdb

[root@localhost ~]# sudo systemctl enable postgresql-12

[root@localhost ~]# sudo systemctl start postgresql-12

 

 

 Compile 이용 설치

 

■ 다음의 3개 패키지가 사전에 설치되어 있어야 합니다. (CentOS7, RHEL7, Fedora22 기준)

① gcc

② readline-devel

③ zlib-devel

[root@localhost ~]# yum -y install gcc readline-devel zlib-devel make

 

 

 

■ 해당 URL에서 tar 파일 다운

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

PostgreSQL: File Browser

[root@localhost ~]# tar -zxvf postgresql-12.3.tar.gz

[root@localhost ~]# groupadd postgres

[root@localhost ~]# useradd -g postgres postgres

 

 

■ postgreSQL 설치

[root@localhost postgresql-12.3]# cd postgresql-12.3/

[root@localhost postgresql-12.3]# ./configure --prefix=/mnt/pgsql/12       => DB 홈 경로 기입

[root@localhost postgresql-12.3]# make && make install

 

 

■ 데이터 디렉토리 추가/소유자 할당

[root@localhost postgresql-12.3]# mkdir -p /mnt/pgsql/12/data/
[root@localhost postgresql-12.3]# chown -R postgres.postgres /mnt/pgsql/

 

 

■ 데이터베이스 클러스터 생성

[root@localhost postgresql-12.3]# su - postgres


[postgres@localhost ~]$ cd /mnt/pgsql/12/bin


[postgres@localhost bin]$ ./initdb  -D /mnt/pgsql/12/data/    => 데이터디렉토리 경로 기입
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".
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 disabled.

fixing permissions on existing directory /home/postgres/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... ROK
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    ./pg_ctl -D /home/postgres/pgsql/data/ -l logfile start

 

 

■ DB 기동

[postgres@localhost bin]$ ./pg_ctl -D /mnt/pgsql/12/data/ -l /mnt/pgsql/12/pgsql.log start
waiting for server to start.... done
server started


[postgres@localhost bin]$ ps -ef | grep post
root      5344     1  0 10:55 ?        00:00:00 /usr/libexec/postfix/master -w
postfix   5361  5344  0 10:55 ?        00:00:00 qmgr -l -t unix -u
root     23421 12580  0 17:00 pts/0    00:00:00 su - postgres
postgres 23422 23421  0 17:00 pts/0    00:00:00 -bash
postfix  23728  5344  0 17:24 ?        00:00:00 pickup -l -t unix -u
postgres 23803     1  0 17:30 ?        00:00:00 /mnt/pgsql/12/bin/postgres -D /mnt/pgsql/12/data
postgres 23805 23803  0 17:30 ?        00:00:00 postgres: checkpointer   
postgres 23806 23803  0 17:30 ?        00:00:00 postgres: background writer   
postgres 23807 23803  0 17:30 ?        00:00:00 postgres: walwriter   
postgres 23808 23803  0 17:30 ?        00:00:00 postgres: autovacuum launcher   
postgres 23809 23803  0 17:30 ?        00:00:00 postgres: stats collector   
postgres 23810 23803  0 17:30 ?        00:00:00 postgres: logical replication launcher   
postgres 23811 23422  0 17:30 pts/0    00:00:00 ps -ef
postgres 23812 23422  0 17:30 pts/0    00:00:00 grep --color=auto post

 

■ bash_profile 변경

[root@localhost ~]# su - postgres
[postgres@localhost ~]$ vi ~/.bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin
POSTGRES_HOME=/mnt/pgsql/12
LD_LIBRARY_PATH=/mnt/pgsql/12/lib
PGDATA=$POSTGRES_HOME/data
MANPATH=$MANPATH:$POSTGRES_HOME/man

PATH=$POSTGRES_HOME/bin:$PATH

export PATH
export POSTGRES_HOME
export PGDATA
export MANPATH
export LD_LIBRARY_PATH


[postgres@localhost ~]$ source ~/.bash_profile
[postgres@localhost ~]$ mkdir -p /mnt/pgsql/12/man

 

■ postgresql.conf 수정

postgres$ vi /mnt/pgsql/12/data/postgresql.conf 
...
listen_addresses = 'localhost' → '*' 로 변경

 

■ database 위치

select oid, datname from pg_database;

 

 

■ 테이블 스페이스 위치

select oid, spcname from pg_tablespace;

 

 

 

■ kim 테이블 정보

select oid, relname, reltablespace, relfilenode from pg_class where relkind='r' and relname= 'kim';