Purpose

Provide guidance on installing and configuring a basic PostgreSQL database for use in a Small-scale Hipchat Data Center environment.

In this How To guide, we will be installing PostgreSQL version 9.5 on a CentOS 7 Linux server. 

Familiarity with installing and administering CentOS 7 using the Linux command line is recommended. 

Prepare

• Ensure the system on which PostgreSQL will be installed meets the Hardware Requirements for the Data Stores Node.
• Download and install CentOS 7 on the server using the minimal iso image.

• Install the PostgreSQL repository package using a command like the following. This package automatically configures the PostgreSQL yum repository on the server machine.  

  sudo yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm

  
  

  Postgres is continuously updated to fix bugs and improve stability. Before you run the command above, check the PostgreSQL Yum Repository page for the URL to the most recent release of PostgreSQL 9.5.

Install and Configure

1. Install the Postgres server packages from your yum repo:

   sudo yum install postgresql95-server postgresql95 -y

2. Run the following command to initialize the PostgreSQL database:

   sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb

3. Start PostgreSQL:

   sudo systemctl start postgresql-9.5.service

4. Configure PostgreSQL to start after every server reboot:

   sudo systemctl enable postgresql-9.5.service

5. Open the database ports on the server's firewall so that Postgres can be accessed from other systems:

   sudo firewall-cmd --permanent --zone=public --add-service=postgresql
   sudo firweall-cmd --reload

6. As the root user, open the /var/lib/pgsql/9.5/data/postgresql.conf file in a text editor.  

7. Find the following line:

   listen_addresses='localhost'

   ..and change it to:

   listen_addresses='*'

   Security note:

   This setting allows postgres to listen on all available interfaces. Your organization's security policies may not allow this. Learn more about configuring this parameter in the Postgres Documentation.

8. Locate the max_connections setting and change it to 1000:

   max_connections=1000

9. Save the changes to the file.
   
   

10. As the root user, open the /var/lib/pgsql/9.5/data/pg_hba.conf file in a text editor. 
    Modify this line:

    local   all    	       	all    	       	       	       	       	peer

    ..to be this:

    local   all    	       	all    	       	       	       	       	md5

    ...and add this line to the end of the file:

    host    all             all              0.0.0.0/0               md5

    The above modifications will allow PostgreSQL to be accessible from remote hosts. 

    Security note:

    The 0.0.0.0/0 CIDR notation in the configuration opens up PostgreSQL to accept connections from all networks. Confirm that this meets your organization's security policies before proceeding. You might need to adjust the CIDR notation so that only approved networks are allowed to connect to the database.

    Check the PostgreSQL Documentation for more information on how to configure the pg_hba.conf file.

11. Save the changes to the file.
    
    

12. Restart the service so that all of the above changes take effect:

    sudo systemctl restart postgresql-9.5.service

13. Verify that PostgreSQL is listening for connections on port 5432.  Using the above examples, this would be the expected output:

    ss -antlp | grep 5432
    LISTEN     0      128          *:5432                     *:*                   users:(("postgres",pid=40743,fd=3))
    LISTEN     0      128         :::5432                    :::*                   users:(("postgres",pid=40743,fd=4))

14. Finish setting up and configuring the Hipchat database according to the guidelines in the Small-Scale Setup Documentation.