Install PostgreSQL 9.3 on Fedora 20/19, CentOS/RHEL/SL 7/6.6/5.11

This is guide, howto install PostgreSQL 9.3 (currently 9.3.5) database server on Fedora 20/19/18, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7/6.6/5.11. 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.3.5 Database Server on Fedora 20/19/18, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7/6.6/5.11

1.1 Change root user

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:

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

CentOS

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

Red Hat (RHEL)

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

Scientific Linux (SL)

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

1.3 Install PostgreSQL 9.3 Repository

Fedora

CentOS 7/6.6/5.11

Red Hat (RHEL) 7/6.6/5.11

Scientific Linux (SL) 7/6.6/5.11

1.4 Install PostgreSQL 9.3 with YUM

2. Configure PostgreSQL 9.3

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 20/19/18, CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7/6.5/5.11:

2.2 Set PostgreSQL Server to Listen Addresses and Set Port

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

If you want just localhost setup, then use following:

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

2.3 Set PostgreSQL Permissions

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

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 20/19/18 and CentOS/Red Hat (RHEL)/Scientific Linux (SL) 7

CentOS/Red Hat (RHEL)/Scientific Linux (SL) 6.5/5.11

2.5 Create Test Database and Create New User

Change to postgres user

Create test database (as postgres user)

Login test database (as postgres user)

Create New “testuser” Role with Superuser and Password

Test Connection from localhost (as Normal Linux User)

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

3.1. CentOS/Red Hat (RHEL) 6.5/5.11

3.1.1 Edit /etc/sysconfig/iptables file:

3.1.2 Add following INPUT rule:

3.1.3 Restart Iptables Firewall:

3.2. Fedora and CentOS/Red Hat (RHEL) 7

3.2.1 Add New Rule to Firewalld

3.2.2 Restart firewalld.service

4. Test remote connection

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

Share this post

59 Comments

  1. 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?

    Reply
    • 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?

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

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

      Reply
  3. 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:

    Then as this testuser, you can create the db:

    Reply
  4. great tutorial. thanks a lot

    Reply
  5. 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.

    Reply
  6. … go to enterprise db site ,
    Download oss version , chmod to execute , execute … that ‘s all
    Wat else ?

    PS by the way , you have pgadmin installed

    fed 20 x 64

    PPS : i’m just switching from win7 to linux , happy to find at least a software that i can install just with a “click”

    Reply
  7. oups… i mean : what else (sic)

    Reply

Submit 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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">