Install and Configure PostgreSQL with pgAdmin

PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. People will usually comparing PostgreSQL with MySQL, and there are several key factors which let us choose PostgreSQL over MySQL:

• Single storage engine – no hassle to choose which storage engine like MySQL
• PostgreSQL aims for SQL standards compliance (the current standard is ANSI-SQL:2008)
• High level of data integrity demanded by a serious transactional database application, the current generation PostgreSQL performs better than MySQL
• Open source and run on GPL license – no licensing cost

I will be using standard CentOS 6 with and yum installer. Variable as below:

OS: Centos 6.2 64bit
Server IP: 192.168.0.1
Client IP: 192.168.0.100
Root user: postgres
Password: Bf44Ks#1

1. Install PostgreSQL using yum:

$ yum install -y postgresql*

2. Initialize the server database:

$ service postgresql initdb

3. By default, PostgreSQL will be installed under /var/lib/pgsql/data. Open the configuration file at /var/lib/pgsql/data/postgresql.conf using text editor and enable following line:

listen_addresses = '*'

4. Open client authentication configuration file at /var/lib/pgsql/data/pg_hba.conf to configure how user can access PostgreSQL via network or locally. User postgres is the super-user and I need to allow this user to be access from my Windows 7 laptop using pgAdminIII client. I need to add following line as below:

host    all    postgres    192.168.0.0/24       md5

Explanation: host = access via network, all = can access all database, postgres = user that I want to use to access, 192.168.0.0/24 =  network address which the user used to access this server, md5 = authentication method

5. Start and enable the PostgreSQL service:

$ service postgresql start
$ chkconfig postgresql on

6. Now we need to reset the user postgres password so we can use md5 authentication when connecting through client later. We need to change to user postgres environment by ‘su’ command and then access the PostgreSQL database:

$ su - postgres
-bash-4.1$ psql
psql (8.4.9)
Type "help" for help.
 
postgres=#

Then run following command:

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'Bf44Ks#1';
postgres=# \q

After quit from psql, reload the server:

$ psql_ctl reload

7. Add following line into /etc/sysconfig/iptables to allow port 5432 for PostgreSQL communication using text editor. Make sure you put the line before any REJECT (-j REJECT) rules:

-A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

Dont forget to restart IPtables:

$ service iptables restart

8. Now lets download and install pgAdminIII, a client which run on Windows for PostgreSQL database server at http://www.postgresql.org/ftp/pgadmin3/release/v1.8.4/win32/. Once downloaded, unzip the compress file and run the installer. Follow the wizard by accepting default value and complete the installation.
9. Lets connect to the server and run the pgAdminIII client. It usually located under Windows > All Programs > pgAdmin III 1.8 > pgAdmin III. Enter required information as screenshot below:

Done! You may now manage your PostgreSQL server remotely!

 from http://blog.secaserver.com/2012/01/linux-install-configure-postgresql-with-pgadmin/