PostgreSQL 9.2 on Centos 6.5

PostgreSQL Install Steps for a CentOS 6.5 Server running on a VM Ware Player VM.

These steps are only intended to be used for a Local PostgreSQL Install and was intended to be used to create an environment for a group of DBA’s to use while following a PostgreSQL Training Course.

Start the Centos VM created in the previous article, centos_65_install_vmware.

On the Centos VM open a terminal.

pg1

Use “ip addr” to get your VM’s IP Address. Make a note of this address for later.
pg2

Still in the terminal window install the PostgreSQL Server and contrib packages with the command lines below.

You need sudoers permissions on your Centos VM. This is a local VM, so I don’t recommend this for production use.
Get your username from the prompt, e.g. [user1@localhost ~]$

su root
enter the root password which is the one used during the Centos install steps.

Find the sudoers file
sudo find -type f -name sudoers

edit the file as below

nano /etc/sudoers
look for this part of the file:
## Allow root to run any commands anywhere
root ALL=(ALL) ALL

add the line below:
user1 ALL=(ALL) ALL

use ctrl+x to save and exit this file.

Now type “exit” to get back to the context of your user.

run the install with the line below. This may prompt you for your password.

sudo yum install http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm
it will prompt for a yes response

pg3

now run this:
sudo yum install postgresql92-server postgresql92-contrib

this will also prompt for a yes reply

pg4

Once the install completes it should look like this
pg5

Next initialize the PostgreSQL Cluster. This creates the postgres database and the root data structures.

sudo service postgresql-9.2 initdb
it should return the line below

Initializing database: [ OK ]

This starts the postgres server service at server start-up time
sudo chkconfig postgresql-9.2 on

You may need to start the postgresql server service now.

sudo service postgresql-9.2 start
it should return this message:
Starting postgresql-9.2 service: [ OK ]
[user1@localhost ~]$

Add a super user account which we will use later to connect to the server.
sudo su postgres

psql –dpostgres

at the # prompt run the statement below: (this sets the password to be “password”)
check the formatting of the single quotes too if you copy this code, it may replace them with “`”

CREATE ROLE “dbsuperuser” LOGIN ENCRYPTED PASSWORD ‘md56bac755f8285a72102dec34c5a91f1d5’
SUPERUSER
VALID UNTIL ‘infinity’;

\q

You should now be back at the prompt below:
bash-4.1$

Change to the directory “/var/lib/pgsql/9.2/data” and edit the pg_hba.conf use the IP address range you recorded earlier.
The example below will allow all hosts in the 192.168.n.n range to connect.
cd
cd 9.2
cd data
nano pg_hba.conf

add a new line below the localhost address as shown below, use the IP address from earlier.
# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 192.168.232.147/16 md5

save the changes with ctrl+x

type exit to get back to the user1 shell

Restart the postgresql service
sudo service postgresql-9.2 restart

This completes the PostgreSQL Server installation. The next steps cover installing the PGAdmin 3 GUI tool.

The following steps will show you how to install the PostgreSQL GUI too PGAdmin III.

In the terminal window run this, it will prompt for a yes. Once it completes should return Complete!
sudo yum install pgadmin3_9*

Open PGAdmin 3
pga1

Connect to the local server.
pga2

Enter an alias name in the Name field, and the IP address in the host field. Un check the save password box.
pga3

Click on “Fix It!” if it prompts for the tools below.
pga4

You should now be able navigate around the server with the GUI tool PG Admin 3.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s