Install PostgreSQL 9 on Amazon Linux instance

In the previous post we spoke about how to build and configure Amazon EC2 Linux instance in terms of free tier offered by Amazon. Also we decided to use Amazon cloud platform as a staging environment where Tomcat 7 web-server will be installed. And now we are going to install PostgreSQL 9 server on the same Amazon EC2 Linux instance.

The process of installing PostgreSQL 9 could be described in several steps. After successfull installing it will be possible to connect to the database with pgAdmin tool for further configuration.

Change the root password and login as root

1. Initiate SSH session (as 'ec2-user') and connect to the Amazon Linux instance by it public DNS name.
2. Change the root password:

 sudo passwd root  

3. Login as root:

 su root  

Install PostgreSQL 9

1. Create new user postgres:

 useradd postgres  

2. Set the postgres password:

 passwd postgres  

3. Install PostgreSQL 9 server:

 yum install postgresql90 postgresql90-contrib postgresql90-devel postgresql90-server  

4. Set owner permissions:

 chown -hR postgres:postgres /var/lib/pgsql  

5. Initialize data base:

 service postgresql-9.0 initdb  

6. Start/Stop/Restart PostgreSQL 9 as a service.
start:

 service postgresql-9.0 start  

stop:

 service postgresql-9.0 stop  

restart:

 service postgresql-9.0 restart  

7. Add PostgreSQL 9 service to the autostart:

 chkconfig postgresql-9.0 on  

The default path of PostgreSQL 9 is: /var/lib/pgsql

Update PostgreSQL 9 configuration files

1. Add 5432 port to the security group associated with Amazon Linux instance using AWS Management Console.

2. Update /var/lib/pgsql/9.0/data/pg_hba.conf configuration file (the bottom of the file) to read:

 # TYPE   DATABASE    USER      CIDR-ADDRESS      METHOD  
 # "local" is for Unix domain socket connections only  
 local    all         all                         ident  
 # IPv4 local connections:  
 host     all         all        127.0.0.1/32     trust  
 host     all         all        0.0.0.0/0        trust                 
 # IPv6 local connections:  
 host     all         all        ::1/128          ident  

3. Update /var/lib/pgsql/9.0/data/postgresql.conf configuration file to read:

 listen_addresses = '*'  
 port = 5432  

Connect with pgAdmin

1. Download and install latest version of pgAdmin from the official site.
2. Add new connection to the database server.

3. Fill the New Server Registration form (for the Host name use Public DNS name of Amazon Linux instance) and press OK button.

4. New server appeared in the Object browser section.