Install-PostgreSQL

Published onesixx on

 

설치

https://wikidocs.net/7369
https://zetawiki.com/wiki 설치

기설치여부 확인

➜  dpkg -l|grep postgres

or

~|⇒ aptitude show postgresql | grep State
State: not installed
또는
State: installed

설치

https://www.postgresql.org/download/linux/ubuntu/
http://yallalabs.com/linux/how-to-install-and-use-postgresql-10-on-ubuntu-16-04/

Add repository 

sudo vi /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

Import the repository signing key

➜  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
➜  sudo apt update 

Ver10.1 설치

➜  sudo apt install postgresql-10

===========================

~|⇒ sudo apt install postgresql-9.5
....
Suggested packages:
  postgresql-doc locales-all postgresql-doc-9.5 libdbd-pg-perl isag
The following NEW packages will be installed:
  libpq5 postgresql postgresql-9.5 postgresql-client-9.5 postgresql-client-common postgresql-common
  postgresql-contrib-9.5 sysstat
...

Creating new cluster 9.5/main ...
  config /etc/postgresql/9.5/main
  data   /var/lib/postgresql/9.5/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5432
Setting up postgresql (9.5+173) ...
Setting up postgresql-contrib-9.5 (9.5.7-0ubuntu0.16.04) ...
Setting up sysstat (11.2.0-1ubuntu0.1) ...

Creating config file /etc/default/sysstat with new version
update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode
Processing triggers for libc-bin (2.23-0ubuntu7) ...
Processing triggers for systemd (229-4ubuntu17) ...
Processing triggers for ureadahead (0.100.0-19) ...

설치확인

~|⇒ dpkg -l | grep postgres
ii postgresql                9.5+173               all    object-relational SQL database (supported version)
ii postgresql-9.5            9.5.7-0ubuntu0.16.04  amd64  object-relational SQL database, version 9.5 server
ii postgresql-client-9.5     9.5.7-0ubuntu0.16.04  amd64  front-end programs for PostgreSQL 9.5
ii postgresql-client-common  173                   all    manager for multiple PostgreSQL client versions
ii postgresql-common         173                   all    PostgreSQL database-cluster manager
ii postgresql-contrib        9.5+173               all    additional facilities for PostgreSQL (supported version)
ii postgresql-contrib-9.5    9.5.7-0ubuntu0.16.04  amd64  additional facilities for PostgreSQL

 PostgreSQL 관리자 계정 postgres가 생성되었음

~|⇒ cat /etc/passwd | grep postgres
postgres:x:122:129:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

상태 확인

~|⇒ /etc/init.d/postgresql status
● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since 화 2017-08-22 14:50:21 KST; 20h ago
  Process: 1598 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
 Main PID: 1598 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postgresql.service

로컬호스트(127.0.0.1)에서만 접속 가능

~|⇒ sudo netstat -tnlp | grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      818/postgres    

 

 초기설정

psql 관리툴 접속 

⇒ sudo -u postgres psql
psql (9.5.7)
Type "help" for help.

or

⇒ sudo su - postgres
⇒ psql

패스워드 설정

postgres 계정의 패스워드 변경

postgres=# ALTER USER postgres with encrypted password '1';
ALTER ROLE

종료

postgres=# \q

 

설정파일  

설정파일 (/etc/postgresql/9.5/main/ 폴더내)

⇒ ls -l /etc/postgresql/9.5/main/

-rw-r--r-- 1 postgres postgres   315  8월  2 17:22 environment
-rw-r--r-- 1 postgres postgres   143  8월  2 17:22 pg_ctl.conf
-rw-r----- 1 postgres postgres  4641  8월  2 17:22 pg_hba.conf
-rw-r----- 1 postgres postgres  1636  8월  2 17:22 pg_ident.conf
-rw-r--r-- 1 postgres postgres 21441  8월  2 17:22 postgresql.conf
-rw-r--r-- 1 postgres postgres   378  8월  2 17:22 start.conf

postgresql.conf : PostgreSQL configuration file
pg_hba.conf : PostgreSQL Client Authentication Configuration File
pg_ctl.conf    :  Automatic pg_ctl configuration
pg_ident.conf : PostgreSQL User Name Maps
start.conf : Automatic startup configuration

# FILE LOCATIONS
# CONNECTIONS AND AUTHENTICATION
# RESOURCE USAGE (except WAL)
# WRITE AHEAD LOG
# REPLICATION
# QUERY TUNING
# ERROR REPORTING AND LOGGING
# RUNTIME STATISTICS
# AUTOVACUUM PARAMETERS
# CLIENT CONNECTION DEFAULTS
# LOCK MANAGEMENT
# VERSION/PLATFORM COMPATIBILITY
# ERROR HANDLING
# CONFIG FILE INCLUDES
# CUSTOMIZED OPTIONS

postgresql.conf 

– 모든 IP(*)에서 5432 포트 접근 가능하도록 설정

|⇒ sudo vi postgresql.conf
# CONNECTIONS AND AUTHENTICATION
listen_addresses = '*'

pg_hba.conf

– 모든 IP(0.0.0.0/0)에서 모든 DB(all)에 대해 모든 계정(all)으로 로그인 허용

|⇒ sudo vi pg_hba.conf
# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0            md5

PostgreSQL 재시작 후 LISTEN 포트 확인

|⇒ sudo netstat -tnlp | grep postgres
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      25560/postgres  
tcp6       0      0 :::5432                 :::*                    LISTEN      25560/postgres

추가 설정

사용자 매핑 목록

postgres=# \deu+
      List of user mappings
 Server | User name | FDW Options 
--------+-----------+-------------
(0 rows)

Role 목록

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

데이터베이스 목록

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

데이터베이스 추가

postgres=# CREATE DATABASE testdb;
CREATE DATABASE

사용자 생성

postgres=# CREATE ROLE egg WITH LOGIN PASSWORD '1';
CREATE ROLE

Role 부여 

postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO egg;
GRANT
postgres=# ALTER ROLE egg SET client_encoding TO 'utf8';
ALTER ROLE
postgres=# ALTER ROLE egg SET default_transaction_isolation TO 'read committed';
ALTER ROLE
postgres=# ALTER ROLE egg SET timezone TO 'UTC';
ALTER ROLE
postgres=# 

joe 사용자로 testdb 데이터베이스에 연결한다.

|⇒ sudo -u postgres psql -h 127.0.0.1 -U egg testdb

CF> 명시적으로 호스트 접속 부분을 쓰지 않으면, psql: FATAL: Peer authentication failed for user "egg"같이 에러가 발생한다. 

|⇒ sudo -u postgres psql -U egg testdb

이때는  /etc/postgresql/9.5/main/ 폴더내에 pg_hba.conf 파일에서, [PostgreSQL Client Authentication Configuration File]

⇒ ls -l /etc/postgresql/9.5/main/

-rw-r--r-- 1 postgres postgres   315  8월  2 17:22 environment
-rw-r--r-- 1 postgres postgres   143  8월  2 17:22 pg_ctl.conf
-rw-r----- 1 postgres postgres  4641  8월  2 17:22 pg_hba.conf
-rw-r----- 1 postgres postgres  1636  8월  2 17:22 pg_ident.conf
-rw-r--r-- 1 postgres postgres 21441  8월  2 17:22 postgresql.conf
-rw-r--r-- 1 postgres postgres   378  8월  2 17:22 start.conf

 md5 방법으로 접속 가능 사용자를 지정한다.

|⇒ sudo vi pg_hba.conf 
# 추가
local   all             egg                                     md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

이렇게 예외 사용자를 추가하기 위해서는 반드시 윗줄에 적어야 한다.

적용을 위한 재시작

|⇒ /etc/init.d/postgresql restart

 또는

|⇒ sudo service postgresql restart

 

 

 

Categories: Postgresql

onesixx

Blog Owner

Leave a Reply

Your email address will not be published.