Install Postgresql9.3 on CentOS

Install Postgresql Server 9.3

 

first, we need to add official postgresql repository for yum:

sudo yum install http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm

second, install by yum:

sudo yum install postgresql93-server postgresql93-contrib

after that, init database:

sudo service postgresql-9.3 initdb

finally, set postgresql service start automatically:

sudo chkconfig postgresql-9.3 on

And, startt the server:

sduo service postgresql-9.3 start

 

Configure Server for user access

change password for user postgres:

sudo passwd postgres

 

su as postgres, and log into pg sql server to set password of postgres on pgsql sever:

[Ivar@localhost ~]$ su - postgres
密码：
-bash-4.1$ psql
psql (9.3.4)
输入 "help" 来获取帮助信息.
postgres=# alter user postgres with password 'postgres'

Quit from user postgresql shell, and vim open postgresql.conf

sudo vim /var/lib/pgsql/9.3/data/postgresql.conf

change the value of lisen_address to '*", means server would lisent on all IP addresses:

# - Connection Settings -
listen_addresses = '*'                  # what IP address(es) to listen on;

 

save and open pg_hba.conf add the ip address or segment you would allow them to get connected on pgsql server:

sudo vim /var/lib/pgsql/9.3/data/pg_hba.conf

 

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                                    md5
# IPv4 local connections:
host    all             all             127.0.0.1/32                  md5
host    all             all             192.168.0.0/24            md5
host    all             all             192.168.84.0/24          md5
host    all             all             192.168.184.0/24        md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Note, here 192.168.0.0/24 means an IP segment from 192.168.0.1 to 192.168.0.255

/24 meas its mask is 255.255.255.0 (24 bits).

 

change fire wall policy for connection access from port 5432:

[Ivar@localhost ~]$ sudo /sbin/iptables -I INPUT -p tcp --dport 5432 -j ACCEPT
[Ivar@localhost ~]$ sudo service iptables save
iptables：将防火墙规则保存到 /etc/sysconfig/iptables：     [确定]
[Ivar@localhost ~]$ sudo service iptables restart
iptables：将链设置为政策 ACCEPT：filter                    [确定]
iptables：清除防火墙规则：                                 [确定]
iptables：正在卸载模块：                                   [确定]
iptables：应用防火墙规则：                                 [确定]
[Ivar@localhost ~]$ sudo service iptables status
表格：filter
Chain INPUT (policy ACCEPT)
num  target     prot opt source               destination        
1    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           tcp dpt:5432
2    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0           state RELATED,ESTABLISHED
3    ACCEPT     icmp --  0.0.0.0/0            0.0.0.0/0          
4    ACCEPT     all  --  0.0.0.0/0            0.0.0.0/0          
5    ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0           state NEW tcp dpt:22
6    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain FORWARD (policy ACCEPT)
num  target     prot opt source               destination        
1    REJECT     all  --  0.0.0.0/0            0.0.0.0/0           reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)
num  target     prot opt source               destination    

 

 

restart server

sudo service postgresql-9.3 restart

 

create user for your own, here mine is Ivar:

 

[Ivar@localhost ~]$ psql -h localhost  -U postgres
用户 postgres 的口令：
psql (9.3.4)
输入 "help" 来获取帮助信息.

postgres=# CREATE ROLE admin
postgres-#   NOSUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
CREATE ROLE
postgres=# CREATE USER 'Ivar' WITH PASSWORD 'password';
CREATE ROLE

 

logon by your own user and create test database

[Ivar@localhost ~]$ psql postgresql

postgres=# CREATE DATABASE test