[Ubuntu] install PostgreSQL

Published on

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

설치

0. 기설치여부 확인

~|⇒  dpkg -l|grep postgres
또는 
~|⇒ aptitude show postgresql | grep State

1. 설치

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
or
~|⇒ sudo apt install postgresql-9.5

2. 설치확인

~|⇒ dpkg -l | grep postgres

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

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

3. 상태 확인

~|⇒ /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

 초기설정

psql 관리툴 접속 

⇒ sudo -u postgres psql
or 
⇒ sudo su - postgres
⇒ psql

패스워드 설정

postgres 계정의 패스워드 변경

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

psql 종료

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
or
|⇒ sudo service postgresql restart
Categories: Postgresql
Tags:

onesixx

Blog Owner

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments

Related Posts

Postgresql

PostgreSQL Array

http://www.postgresqltutorial.com/postgresql-arrayhttps://www.timescale.com Timeserise database kj

Postgresql

regex in sql

  filename이라는 컬럼에서 날짜형식이 일치(포함)하는 행의 날짜별 row수 구하기 SELECT REGEXP_MATCHES(filename, '[0-9]{4}-[0-9]{2}-[0-9]{2}'), count(*) FROM TEST_Table GROUP BY 1 filename이라는 컬럼에서 날짜만 뽑아서 비교 select * from TEST_Table where SUBSTRING(filename, '\d{4}-\d{2}-\d{2}') = '2017-08-19'     http://www.postgresqltutorial.com/postgresql-regexp_matches/ Read more…

Postgresql

List all databases and tables

  databases SELECT datname FROM pg_database WHERE datistemplate = false;     tables SELECT table_name FROM information_schema.tables where table_catalog='tablespace' and table_schema='public' and table_type='BASE TABLE' ORDER BY 1;    

0
Would love your thoughts, please comment.x
()
x