우분투 PostgreSQL 설치

우분투 PostgreSQL 서버 설치

우분투 postgres 설치

1 확인

Copy

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

2 설치

Copy

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]

Copy

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

Copy

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

Copy

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

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

Copy

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

Copy

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 계정의 패스워드 변경

Copy

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대역 설정

Copy

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

• 설정파일 편집

Copy

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

Copy

#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(*)에서 접속가능하도록 설정

• PostgreSQL 재시작 후 LISTEN 포트 확인

Copy

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

Copy

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 같이 보기

• CentOS PostgreSQL 설치
• 우분투 PostgreSQL 클라이언트 설치
• 우분투 MySQL 설치
• 리눅스 psql
• sudo: unable to execute /usr/bin/psql: Permission denied

7 참고 자료

• https://help.ubuntu.com/lts/serverguide/postgresql.html