How to Install PostgreSQL 9.2 on CentOS 6.3/RedHat EL6/Fedora

Standard

As a good measure install all updates to this CentOS 6 server. Before you continue further. This step is optional.

Image 001 Image 002

Postgres Repo:

pgdg is the repo that postgresql can be downloaded and installed from. Here is where you download it from. Identify which version of rpm you want to download for installing PostgreSQL database server. Right now I am picking PostgreSQL 9.2 for CentOS [CentOS 6 - x86_64] as this is the latest version at this time. Now that we have identified which version we want to download, right click on that hyperlink and click "copy link address".

Image 004

Login into your CentOS Server by SSH and run the following command to download it into your CentOS 6 Server.

wget http://yum.pgrpms.org/9.2/redhat/rhel-6-x86_64/pgdg-centos92-9.2-6.noarch.rpm

Image 008

If It fails and complains that you dont have wget utility installed on your machine, Install wget utility by running command

yum install wget

Image 006Image 007

Now that wget utility is installed, download the rpm from PostgreSQL rpm repository

Image 005 Image 009

Now install this rpm with this command.

rpm -ivh pgdg-centos92-9.2-6.noarch.rpm

Image 010

Now that we have installed the pgdg central PostgreSQL repo, we should have postgresql rpms show up in yum package manager search. Run the following command on command line to search for all postgres related available rpms.

yum search postgres

The following list should be generated from the search. Now install PostgreSQL client / Server programs.

Image 012

Run the following commands to install PostgreSQL server/client

yum install postgresql92 postgresql92-server

Image 014

Image 015

You now have successfully installed PostgreSQL database server / client on this CentOS Server.

Now create default data/tables into this newly build database server by running command:

service postgresql-9.2 initdb

[Service name is postgresql-9.2 or corresponding PostgreSQL generated init file under /etc/init.d/ directory]

Image 016

Start the PostgreSQL database server now.

service postgresql-9.2 start

Image 017

Make your database server auto starts when this CentOS server starts/restarts.

chkconfig postgresql-9.2 on

Image 018

Create an user other than default user for granting access to the database from outside world into this database server.

Login into database as postgres user, to do that run the following commands:

su postgres
psql -dpostgres

Above command some times lead to a PostgreSQL shared library issue. On some systems that have shared libraries (which most systems do) you need to tell your system how to find the newly installed shared libraries. If you did not encounter this error, please skip this step of adding path to /etc/profile file and move on to creating user.

psql: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory

Image 020

To fix this issue we need to add the following lines in your /etc/profile file all the way at the bottom of the file. First exit out as postgres user by typing exit on command line and add the following lines at the bottom on /etc/profile file as root user. Save and quit from here after adding those lines mentioned below.

LD_LIBRARY_PATH=/usr/pgsql-9.2/lib [Which ever path is relevant to your installation]
export LD_LIBRARY_PATH

Image 029

Restart your CentOS Server to get the new LD_LIBRARY_PATH variable set properly.

shutdown -r now

After the server is up and running, SSH in and run the following commands to create an user.

su – postgres

psql -dpostgres

Image 022

Now you are inside PostgreSQL database server, so create a super user for accessing database server from outside world and exit out of there.

CREATE role opensourcedbms LOGIN PASSWORD 'opensourcedbms' SUPERUSER;
\q

Image 024

Now we have created a super user for managing the database. We are almost done. To grant outside world access we need to make modifications to the database server configuration files and CentOS Server firewall rules [iptables]. First exit out as postgres user on your server and log back in as root user.

Make the changes in your database configuration file :

vi /var/lib/pgsql/9.2/data/postgresql.conf

Uncomment the line #listen_addresses = ‘localhost’ and change it to listen_addresses = ‘*’

Image 026

The second configuration file that you need to edit in order to grant access to outside world is below [Host based authentication file]:

vi /var/lib/pgsql/9.2/data/pg_hba.conf

Add a new line under IPv4 local connections all the way to the near bottom of the file. Make appropriate changes to network you want this new user to login from. If your subnet is 192.168.1.1/24 or 192.168.1.1/32, please use that or what ever is relevant in you case. This rule I added indicates the database server to accept connections for this user originating with in 192.168.1.1/24 sub-net network.

host all opensourcedbms 192.168.1.1/24 md5

Image 027

Now restart postgres server to get these changes in effect

service postgresql-9.2 restart

Finally open the default PostgreSQL database port in server firewall to connecting from outside world.

vi /etc/sysconfig/iptables

Add line:

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

Image 028

and now restart iptables

service iptables restart

Image 030

At this point you should be able to connect to this database server from clients outside this server like pgAdmin III [Download pgAdmin III and install on a host that you want to access this database from] as shown below:

Image 033

Image 016 Image 017

If you have installed this in a virtual machine, make sure that you port forward (NAT Only) 5432 under your VM Settings > Network Settings > Advanced > Port forwarding to pass through this 5432 from main host to your VM to connect from outside VM.

Image 035

facebooktwittergoogle_plusredditpinterestlinkedinmail

About Prashanth Goriparthi

Sr. Database Administrator / Architect

32 Comments

  1. Pingback: How to do point in time recovery with PostgreSQL 9.2 : PITR in CentOS 6/Redhat EL6

  2. Pingback: Setup replication with Postgres 9.2 on CentOS 6/Redhat EL6/Fedora

  3. Pingback: Setup pgbouncer connection pooling for PostgreSQL on CentOS/RedHat/Fedora | Open source database management systems - PostgreSQL & MySQL

  4. Hi, Thanks for tutorial install postgre on Centos 5.9 x64 from installing to initdb but when i started the service [FAILED] do you have solution for this one? Thanks.

  5. Pingback: Running multiple PostgreSQL 9.2 Instances on one server in CentOS 6/RHEL 6/Fedora | Open source database management systems - PostgreSQL & MySQL

  6. One of the best article that I have ever found on the internet regarding Postgresql.

    Prashanth, can you please also write an article about, how to take the backup/ dump of Postgresql database and then restore it?

    Thanks, waiting for your reply.

  7. Thanks for the great tutorial!

    Is it possible for you to add some tutorial about connecting from ArcGIS for Server to postgresql installed on CentOS?

    Thank you

  8. I successfully got postgre installed and running. But was wondering why the webapp was not able to connect to the database. I found out that my localhost entry under /var/lib/pgsql/9.2/data/pg_hba.conf was set to ident instead of md5. Setting it to md5 fixed my problem. :-)

    Thanks again Prashanth Goriparthi for this useful article.

    Brian
    Philippines

  9. Hello, want to ask when i typed "service postgresql-9.3 initdb" the result is "initializing database: [FAILED]"

    i have search over the internet can’t find the resolve of my problem. I’m using centOS 5.10 [Final] x64. Thanks

  10. yum install postgresql92 postgresql92-server
    Setting up Install Process
    Resolving Dependencies
    –> Running transaction check
    —> Package postgresql92.x86_64 0:9.2.8-1PGDG.rhel6 will be installed
    –> Processing Dependency: postgresql92-libs = 9.2.8-1PGDG.rhel6 for package: postgresql92-9.2.8-1PGDG.rhel6.x86_64
    –> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql92-9.2.8-1PGDG.rhel6.x86_64
    —> Package postgresql92-server.x86_64 0:9.2.8-1PGDG.rhel6 will be installed
    –> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql92-server-9.2.8-1PGDG.rhel6.x86_64
    –> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql92-server-9.2.8-1PGDG.rhel6.x86_64
    –> Running transaction check
    —> Package postgresql92.x86_64 0:9.2.8-1PGDG.rhel6 will be installed
    –> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql92-9.2.8-1PGDG.rhel6.x86_64
    —> Package postgresql92-libs.x86_64 0:9.2.8-1PGDG.rhel6 will be installed
    –> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql92-libs-9.2.8-1PGDG.rhel6.x86_64
    –> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql92-libs-9.2.8-1PGDG.rhel6.x86_64
    —> Package postgresql92-server.x86_64 0:9.2.8-1PGDG.rhel6 will be installed
    –> Processing Dependency: libssl.so.10(libssl.so.10)(64bit) for package: postgresql92-server-9.2.8-1PGDG.rhel6.x86_64
    –> Processing Dependency: libcrypto.so.10(libcrypto.so.10)(64bit) for package: postgresql92-server-9.2.8-1PGDG.rhel6.x86_64
    –> Finished Dependency Resolution
    Error: Package: postgresql92-server-9.2.8-1PGDG.rhel6.x86_64 (pgdg92)
    Requires: libcrypto.so.10(libcrypto.so.10)(64bit)
    Error: Package: postgresql92-9.2.8-1PGDG.rhel6.x86_64 (pgdg92)
    Requires: libssl.so.10(libssl.so.10)(64bit)
    Error: Package: postgresql92-server-9.2.8-1PGDG.rhel6.x86_64 (pgdg92)
    Requires: libssl.so.10(libssl.so.10)(64bit)
    Error: Package: postgresql92-libs-9.2.8-1PGDG.rhel6.x86_64 (pgdg92)
    Requires: libcrypto.so.10(libcrypto.so.10)(64bit)
    Error: Package: postgresql92-libs-9.2.8-1PGDG.rhel6.x86_64 (pgdg92)
    Requires: libssl.so.10(libssl.so.10)(64bit)
    You could try using –skip-broken to work around the problem
    You could try running: rpm -Va –nofiles –nodigest

Leave a Reply


2 × one =