在 CentOS6.4 按裝 Postgresql on AWS

Install PostgreSQL 9.2.4 Database Server

• Step1

Add exclude to /etc/yum.repos.d/CentOS-Base.repo file [base] and [updates] sections:

[base]
...
exclude=postgresql*

[updates]
...
exclude=postgresql*

• Step2(大概是下載一些版本資訊吧)

## CentOS 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

• Step3

yum install postgresql92 postgresql92-server postgresql92-contrib

Install location(按裝位置)
/var/lib/pgsql/9.2

• Step4

Initialize db
su - postgres -c /usr/pgsql-9.2/bin/initdb

• Step5

Open /var/lib/pgsql/9.2/data/postgresql.conf file, and add/uncomment/modify following:
listen_addresses = '*'
port = 5432

• Step6

Modify PostgreSQL /var/lib/pgsql/9.2/data/pg_hba.conf (host-based authentication) file:
host all all     0.0.0.0/0 md5

• Step7

Start PostgreSQL Server and Autostart PostgreSQL on Boot
## Start PostgreSQL 9.2 ##
service postgresql-9.2 start
## OR ##
/etc/init.d/postgresql-9.2 start

## Start PostgreSQL 9.2 on every boot ##
chkconfig --levels 235 postgresql-9.2 on

• Step8

Create Test Database and Create New User

su postgres

createdb test

psql test

CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD '12345';

psql -h localhost -U testuser test

• Step9

Enable Remote Connections to PostgreSQL Server
vi /etc/sysconfig/iptables
Add following line before -A INPUT -j REJECT…(加在哪一行要看清楚)
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d 10.10.29.50  --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -p tcp -s 10.10.29.50 --sport 5432 -d 0/0 --dport 1024:65535 -m state --state ESTABLISHED -j ACCEPT

• Step10

Restart Iptables Firewall
service iptables restart
## OR ##
/etc/init.d/iptables restart

psql -h ec2-107-21-150-155.compute-1.amazonaws.com -U testuser -W test

參考來源：
Install PostgreSQL 9.2 on Fedora 19/18, CentOS/RHEL/SL 6.4/5.9