우분투 PostgreSQL 설치

우분투 PostgreSQL 서버 설치
우분투 postgres 설치

1 확인[ | ]

root@zetawiki:~# aptitude show postgresql | grep State
State: not installed

2 설치[ | ]

root@zetawiki:~# apt-get install postgresql
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  libpq5 postgresql-9.3 postgresql-client-9.3 postgresql-client-common
  postgresql-common
Suggested packages:
  oidentd ident-server locales-all postgresql-doc-9.3
The following NEW packages will be installed:
  libpq5 postgresql postgresql-9.3 postgresql-client-9.3
  postgresql-client-common postgresql-common
0 upgraded, 6 newly installed, 0 to remove and 145 not upgraded.
Need to get 889 kB/3,685 kB of archives.
After this operation, 15.5 MB of additional disk space will be used.
Do you want to continue? [Y/n]

Get:1 http://us.archive.ubuntu.com/ubuntu/ trusty-updates/main libpq5 amd64 9.3.11-0ubuntu0.14.04 [80.6 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu/ trusty-updates/main postgresql-client-common all 154ubuntu1 [25.4 kB]
... (생략)
Creating new cluster 9.3/main ...
  config /etc/postgresql/9.3/main
  data   /var/lib/postgresql/9.3/main
  locale en_US.UTF-8
  port   5432
update-alternatives: using /usr/share/postgresql/9.3/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
 * Starting PostgreSQL 9.3 database server                                                    [ OK ] 
Setting up postgresql (9.3+154ubuntu1) ...
Processing triggers for libc-bin (2.19-0ubuntu6.6) ...

3 확인 2[ | ]

root@zetawiki:~# dpkg -l | grep postgres
ii  postgresql                            9.3+154ubuntu1                          all          object-relational SQL database (supported version)
ii  postgresql-9.3                        9.3.11-0ubuntu0.14.04                   amd64        object-relational SQL database, version 9.3 server
ii  postgresql-client-9.3                 9.3.11-0ubuntu0.14.04                   amd64        front-end programs for PostgreSQL 9.3
ii  postgresql-client-common              154ubuntu1                              all          manager for multiple PostgreSQL client versions
ii  postgresql-common                     154ubuntu1                              all          PostgreSQL database-cluster manager

root@zetawiki:~# cat /etc/passwd | grep postgres
postgres:x:111:121:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

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

root@zetawiki:~# /etc/init.d/postgresql status
9.3/main (port 5432): online

root@zetawiki:~# netstat -tnlp | grep postgres
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      23680/postgres

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

4 패스워드 설정[ | ]

postgres 계정의 패스워드 변경

root@zetawiki:~# sudo -u postgres psql template1
could not change directory to "/root": Permission denied
psql (9.3.11)
Type "help" for help.

template1=# 
template1=# ALTER USER postgres with encrypted password 'P@ssw0rd';
ALTER ROLE
template1=# \q
root@zetawiki:~#

5 접속 IP대역 설정[ | ]

★ 여기서는 테스트를 위해 모든 IP를 허용하지만, 실제로는 접근을 허용할 IP만으로 한정하는 것이 권장됨

root@zetawiki:~# ps -ef | grep postgresql.conf | grep -v grep
postgres 30050     1  0 12:47 ?        00:00:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf

→ 설정파일은 /etc/postgresql/9.3/main/postgresql.conf

설정파일 편집

root@zetawiki:~# vi /etc/postgresql/9.3/main/postgresql.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
listen_addresses = '*'

→ 모든 IP(*)에서 5432 포트 접근 가능하도록 설정^[1]

hba설정 파일^[2] 편집 (1줄 추가)

root@zetawiki:~# vi /etc/postgresql/9.3/main/pg_hba.conf

host    all             all             0.0.0.0/0               md5

→ 모든 IP(0.0.0.0/0)에서 모든 DB(all)에 대해 모든 계정(all)으로 로그인 허용^[1]

PostgreSQL 재시작 후 LISTEN 포트 확인

root@zetawiki:~# /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server                                                  [ OK ]

root@zetawiki:~# netstat -tnlp | grep postgres
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      32093/postgres  
tcp6       0      0 :::5432                 :::*                    LISTEN      32093/postgres

6 원격지에서 접속 테스트[ | ]

다른 서버에서 PostgreSQL 서버로 로그인이 되는지 확인해보자.
사전작업: 리눅스 psql 설치

[root@zeta01 ~]# psql -h 135.79.246.80 -U postgres template1
Password for user postgres:

psql (8.4.20, server 9.3.11)
WARNING: psql version 8.4, server version 9.3.
         Some psql features might not work.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

template1=#

7 같이 보기[ | ]

8 참고[ | ]

↑ ^1.0 ^1.1 권장되는 설정은 아니고 실제 접속할 서버 IP로 제한하는 것이 바람직하다.
↑ 호스트 기반 인증(host-based authentication)

[not-recommended-1] 1.0 ^1.1 권장되는 설정은 아니고 실제 접속할 서버 IP로 제한하는 것이 바람직하다.

[2] 호스트 기반 인증(host-based authentication)

[1]

[2]