Install PostgreSQL 9.2 on Fedora 19/18, CentOS/RHEL/SL 6.4/5.9

This is guide, howto install PostgreSQL 9.2 (currently 9.2.4) database server on Fedora 19/18/17/16, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 6.4/6.3/6.2/6.1/6/5.9. This guide uses PostgreSQL own YUM repos, which are always up-to-date and stable releases are available instantly.

Note: If you are upgrading PostgresSQL (from earlier version), then make sure that you backup (dump and copy) your database and configs.

1. Install PostgreSQL 9.2.4 Database Server on Fedora 19/18/17/16, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 6.4/6.3/6.2/6.1/6/5.9

1.1 Change root user

su -
## OR ##
sudo -i

1.2 Exclude Fedora, CentOS, Red Hat (RHEL) and Scientific Linux (SL) own PostgreSQL Packages

This is important step to get PostgreSQL repository working properly. Exclude PostgreSQL packages from the repository of the distro.

Fedora

Add exclude to /etc/yum.repos.d/fedora.repo file [fedora] section:

[fedora]
...
exclude=postgresql*

Add exclude to /etc/yum.repos.d/fedora-updates.repo file [updates] section:

[updates]
...
exclude=postgresql*

CentOS

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

[base]
...
exclude=postgresql*
 
[updates]
...
exclude=postgresql*

Red Hat (RHEL)

Add exclude to /etc/yum/pluginconf.d/rhnplugin.conf file [main] section:

[main]
...
exclude=postgresql*

Scientific Linux (SL)

Add exclude to /etc/yum.repos.d/sl.repo file [sl] and [sl-security] sections:

[sl]
...
exclude=postgresql*
 
[sl-security]
...
exclude=postgresql*

1.3 Install PostgreSQL 9.2 Repository

Fedora

## Fedora 19 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-19-i386/pgdg-fedora92-9.2-6.noarch.rpm
 
## Fedora 19 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-19-x86_64/pgdg-fedora92-9.2-6.noarch.rpm
 
## Fedora 18 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-18-i386/pgdg-fedora92-9.2-6.noarch.rpm
 
## Fedora 18 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/pgdg-fedora92-9.2-6.noarch.rpm
 
## Fedora 17 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-17-i386/pgdg-fedora92-9.2-6.noarch.rpm
 
## Fedora 17 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-17-x86_64/pgdg-fedora92-9.2-6.noarch.rpm
 
## Fedora 16 - i686 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-16-i386/pgdg-fedora92-9.2-6.noarch.rpm
 
## Fedora 16 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/fedora/fedora-16-x86_64/pgdg-fedora92-9.2-6.noarch.rpm

CentOS

## CentOS 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm
 
## 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
 
## CentOS 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-centos92-9.2-6.noarch.rpm
 
## CentOS 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-centos92-9.2-6.noarch.rpm

Red Hat (RHEL)

## Red Hat Enterprise Linux 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-redhat92-9.2-7.noarch.rpm
 
## Red Hat Enterprise Linux 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
 
## Red Hat Enterprise Linux 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-redhat92-9.2-7.noarch.rpm
 
## Red Hat Enterprise Linux 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-redhat92-9.2-7.noarch.rpm

Scientific Linux (SL)

## Scientific Linux 6 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-sl92-9.2-8.noarch.rpm
 
## Scientific Linux 6 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm
 
## Scientific Linux 5 - i386 - 32-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-i386/pgdg-sl92-9.2-8.noarch.rpm
 
## Scientific Linux 5 - x86_64 - 64-bit ##
rpm -Uvh http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-sl92-9.2-8.noarch.rpm

1.4 Install PostgreSQL 9.2 with YUM

yum install postgresql92 postgresql92-server postgresql92-contrib

2. Configure PostgreSQL 9.2

2.1 Initialize Cluster with initdb Command

Here is multiple alternatives howto do this (like service postgresql-9.x initdb, /etc/init.d/postgresql-9.x initdb) and postgresql-setup initdb, so that’s why I use here universal PostgreSQL initdb method, which should work with Fedora 19/18/17/16, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 6.4/5.9:

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

2.2 Set PostgreSQL Server to Listen Addresses and Set Port

Open /var/lib/pgsql/9.2/data/postgresql.conf file, and add/uncomment/modify following:

listen_addresses = '*'
port = 5432

If you want just localhost setup, then use following:

listen_addresses = 'localhost'
port = 5432

Or if you want use specific ip, then use following:

listen_addresses = '192.1.2.33'
port = 5432

2.3 Set PostgreSQL Permissions

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

# Local networks
host	all	all	        xx.xx.xx.xx/xx	md5
# Example
host	all	all     	10.20.4.0/24	md5
# Example 2
host	test	testuser	127.0.0.1/32	md5

You can find more examples and full guide from PostgreSQL pg_hba.conf manual.

2.4 Start PostgreSQL Server and Autostart PostgreSQL on Boot

Fedora 19/18/17/16

## Start PostgreSQL 9.2 ##
systemctl start postgresql-9.2.service
 
## Start PostgreSQL 9.2 on every boot ##
systemctl enable postgresql-9.2.service

CentOS/Red Hat (RHEL)/Scientific Linux (SL) 6.4/5.9

## 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

2.5 Create Test Database and Create New User

Change to postgres user

su postgres

Create test database (as postgres user)

createdb test

Login test database (as postgres user)

psql test

Create New “testuser” Role with Superuser and Password

CREATE ROLE testuser WITH SUPERUSER LOGIN PASSWORD 'test';

Test Connection from localhost (as Normal Linux User)

psql -h localhost -U testuser test

3. Enable Remote Connections to PostgreSQL Server –> Open PostgreSQL Port (5432) on Iptables Firewall

3.1. Edit /etc/sysconfig/iptables file

vi /etc/sysconfig/iptables

2. Add following line before -A INPUT -j REJECT…

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

3.3 Restart Iptables Firewall

Fedora 19/18/17/16

systemctl restart iptables.service

CentOS/Red Hat (RHEL)/Scientific Linux (SL) 6.4/5.9

service iptables restart
## OR ##
/etc/init.d/iptables restart

3.4 Test remote connection

psql -h dbserver_name_or_ip_address -U testuser -W test

Note: You have to allow remote connections on pg_hba.conf, check step 2.3 and PostgreSQL pg_hba.conf manual.

Follow If Not True Then False Updates!

57 Comments

  1. thank you very much for sharing instructions on how to install postgresql 9.2

  2. Awesome write-up. Got me up and running quickly. Thanks.

  3. Works without a hitch! I am using Scientific Linux 6.3. Thanks.

  4. Thanks for the instructions. Worked perfectly on Centos 6.3 Server.

  5. Works fine on Centos. Thanks a lot. Your Webpage is awesome.

  6. Great guide. I just had to add this command `export PATH=/usr/pgsql-9.2/bin:$PATH` before firing up `createdb test`. I am using CentOS 6.3

  7. Great site, and this latest for postgresql good as usual. But would be a big help to get pgadmin3 latest to run with 9.2: other distros (ubuntu via .deb) have got 1.16 running, but my Fedora 17 won’t upgrade to this – yum will still only install 1.14 which is not compatible with 9.2

    Your assistance appreciated!

    Best
    Bogo

    • Hi Bogo,

      Nice to hear that you get PostgreSQL up and running. :)

      Personally I use just psql client, but looks like pgadmin3 1.16 found also from PostgreSQL own repos, try following to install latest pgadmin3 1.16:

      yum install pgadmin3_92
  8. Thanks, JR – that worked great. Many thanks.

    • Excellent! You are welcome! :)

  9. Fantastic & easy write-up… helped to get the DB UP quickly.
    Thanks Bro..

  10. I am using Fedora 17, but in the step 3.4 “Test Remote Connection” –> psql: could not translate host name “dbserver_name_or_ip_address” to address: Name or service not known.
    help me please.

    • Hi jonax,

      You have to replace dbserver_name_or_ip_address with your own ip address or hostname.

      • Thanks.

  11. Hi,

    Tried to follow your guide but when i got to “createdb” it says createdb command not found. But i see postgresql running in the service.

    Tried to do a simple “psql” and it says command not found too :(

    • Hi Rude,

      Could you first run following command:

      updatedb

      And post output of following commands:

      rpm -qa postgresql\*
       
      locate postgresql psql createdb
  12. Oh thx found it :) Didn’t have locate on the distrib. Kind of new to Centos :) Thank you

  13. Very nice guide.

    Have you ever installed PGADMIN3_92 in RHEL5 X86_64.
    I tried and got this:

    Error: Missing Dependency: libSDL-1.2.so.0 is needed by package wxGTK-2.8.12-1.el5.i386 (postgresql)
    Error: Missing Dependency: libpq.so.5 is needed by package pgadmin3_92-1.16.1-1.rhel5.i386 (postgresql)
    Error: Missing Dependency: libgstinterfaces-0.10.so.0 is needed by package wxGTK-2.8.12-1.el5.i386 (postgresql)

  14. I get [FAILED] when trying to start psql either via /etc/init.d or service postgresql-9.2 start.

    I also tried:

    ‘/usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start’

    after su’ing to postgres & that worked but if I then CTRL-d back to root, postgres stops (I ended up just leaving it running in one terminal).

    Any help appreciated…

  15. In fact, after running:

    ‘usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start’

    it is not running because when I try to ‘createdb test, I get:

    ‘could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket “/tmp/.s.PGSQL.5432″?’

    I forgot to mention this is on 64-bit CentOS 6.3…

  16. One last item for debugging this. This is from psql’s log:

    ‘LOG: end-of-line before authentication method
    CONTEXT: line 90 of configuration file “/var/lib/pgsql/9.2/data/pg_hba.conf”
    FATAL: could not load pg_hba.conf’

    I subsequently read the docs & Googled the problem but tried switching between md5 & trust but it hasn’t alleviated the problem. It still doesn’t start…

  17. After spending a couple of hours trying to start the server, I cut my losses & removed it with yum & rm’d the relevant directories.

    I then re-installed it, edited the ‘/var/lib/pgsql/9.2/data/postgresql.conf’ file as above but left the ‘/var/lib/pgsql/9.2/data/pg_hba.conf’ file alone (this was the one that had caused the problems).

    I then, as the postgres user, ran ‘/usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start’ & the server this time started & I was able to create the test db.

    There are a lot of posts on Google concerning the ‘pg_hba.conf’ file & a fair few had problems similar to mine so I’ll have to the RTFM a little more.

    Sorry for the noise…

    • wow man, thanks for this noise. I was having serious troubles until you gave that command

      usr/pgsql-9.2/bin/pg_ctl -D /var/lib/pgsql/9.2/data -l logfile start

      Thanks to the op as well! Very helpful post.

  18. Thank you so much for this!!!!

  19. Hi!! First Thanx for the post.
    So, I’m using Fedora 17 x64 and I ran every command in this post. But I stoped sn

    Systemclt start postgresql-9.2 server
    It’s giving me this error on
    systemclt status

    http://pastebin.com/S4HG7MUE
    Could You please help me/?

  20. *Correcting*

    Hi!! First Thanx for the post.
    So, I’m using Fedora 17 x64 and I ran every command in this post. But I stoped sn
    Systemclt start postgresql-9.2.service
    It’s giving me this error on
    systemclt status
    http://pastebin.com/S4HG7MUE
    Could You please help me/?

    • Hi Vinny,

      Could you post more info from your postgresql log files?

  21. I have a problem with “Initialize Cluster with initdb Command” step. When I enter su - postgres -c /usr/pgsql-9.2/bin/initdb I don’t know the password for postgres user. I’m on Fedora 18 64bit, btw.

    • Hi Arnes,

      Updated my comment:

      Simply run that command as root user. It will ask postgres password if you run it as normal user.

      Is it working then?

      • I’ve set my postgres user’s password with root access. It’s working now.

        • Hi Arnes,

          Okay, nice to hear that you got it working!

  22. https://github.com/Enelar/psql9.2_deploy

    First few steps automated.

    • Hi Enelar,

      Looks nice, thanks! :)

      • Step forward through 2.4.
        Further steps are too `personal`;)

        GL, and thanks for your manual – used it more than 12 times.

  23. Thank you very much. Very simple.

  24. Hi JR.

    little bit confuse in

    2.3 Set PostgreSQL Permissions
    # Local networks
    host all all xx.xx.xx.xx/xx md5
    # Example
    host all all 10.20.4.0/24 md5
    # Example 2
    host test testuser 127.0.0.1/32 md5

    can you pls. explain your example, I really need to know it.
    shoul I change my own settings at /var/lib/pgsql/9.2/data/pg_hba.conf..
    mine is look like:

    # TYPE DATABASE USER ADDRESS METHOD

    # “local” is for Unix domain socket connections only
    local all all trust
    # IPv4 local connections:
    host all all 127.0.0.1/32 trust
    # IPv6 local connections:
    host all all ::1/128 trust
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local replication postgres trust
    #host replication postgres 127.0.0.1/32 trust
    #host replication postgres ::1/128 trust

    thank you JR.

    • Hi labradorg13,

      Do you want to use your PostgreSQL installation just locally (from localhost)?

      • Hi JR, I want to use for local only.

        • Hi labradorg13,

          Okay, then your config is enough.

          If you want to enable users password when connection to PostgreSQL DB, then change trust to md5 and create users with passwords. :)

          • Hi, JR after following you said that my config is enough.
            I completely follow the steps from 2.3 Set PostgreSQL Permissions up to 3.3 Restart Iptables Firewall but at the last step 3.4 Test remote connection…
            I got the error …. [root@rpidvoproxy rpidavao]# psql -h dbserver_name_or_ip_address -U testuser -W test
            Password for user testuser:
            psql: could not translate host name “dbserver_name_or_ip_address” to address: Name or service not known
            [root@rpidvoproxy rpidavao]#

            I follow your steps in creating 2.5 Create Test Database and Create New User with-out any errors.

            • Hi again labradorg13,

              You have to use real server name or ip address, this should be on your case localhost or 127.0.0.1.

  25. Fedora 19
    1.3 Install PostgreSQL 9.2 Repository
    You will find the repository over there:
    http://yum.postgresql.org/9.2/fedora/

  26. I could not make it work, so I followed another tutorial:
    fedora-fr
    There is just one line to change:
    the pg_hba.conf file is located at /var/lib/pgsql/data/pg_hba.conf

    • Hi ludovic,

      I updated this guide also working with Fedora 19, just tested this on Fedora 19 and it worked as it should be.

  27. Thanks. Works like a charm. Best of a kind,

  28. When i execute this command. Postgresql doesn’t start
    systemctl start postgresql-9.2.service
    Following error is throw

    Job for postgresql-9.2.service failed. See ‘systemctl status postgresql-9.2.service’ and ‘journalctl -xn’ for details.

    Fedora 19
    I have done all step above as mention above.

    • This is a security issue. I have solved this problem by doing

      # chmod -R 700 /var/lib/pgsql/data
      # chown -R postgres /var/lib/pgsql/data

  29. Hello, want to ask when i service postgresql-9.3 initdb the result is “Initializing database: [FAILED]”

    any solving problem for this one?

  30. Thanks, very useful!

  31. Very good!

  32. Dear all, when i installed and create user and database and also connect that database and user from my front end application. and when i create second user and database then postgres asking password. I never set password for postgres user and database. and why postgres asking for password?

    • Hi Ashwin,

      Could you post/check what you have in your pg_hba.conf file(s)?

      Maybe you use trust for other db and example md5 for other db?

  33. Problem has been resolved, I have replaced trust instead md5 for local, like
    local all all trust
    then postgres not asking for password…

    • Excellent! Nice to hear that you got it working!

  34. Great and complete tutorial! I did the install in 5 minutes. Thanks for that.
    I also like to create a user with createuser:
    As postgres user, do:

    createuser -d -P testuser

    Then as this testuser, you can create the db:

    createdb -E Unicode test

  35. great tutorial. thanks a lot

  36. Excellent as usual (Centos 6.5). Only suggestion would be a concrete example for pg_hba.conf:

    host all all 0.0.0.0/0 trust

    For those on ‘safe’ networks and/or will worry about security later.

Add Comment Register



Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>