How to set up a basic PostgreSQL database for Hipchat Data Center
Reference configuration only
This article provides an example configuration to help you set up your Hipchat Data Center deployment, however third-party software might require extra configuration work to function in your environment. Atlassian provides best-effort to assist you with your deployment, but does not directly support these components.
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
Install the Postgres server packages from your
yum
repo:sudo yum install postgresql95-server postgresql95 -y
Run the following command to initialize the PostgreSQL database:
sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
Start PostgreSQL:
sudo systemctl start postgresql-9.5.service
Configure PostgreSQL to start after every server reboot:
sudo systemctl enable postgresql-9.5.service
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
- As the root user, open the
/var/lib/pgsql/9.5/data/postgresql.conf
file in a text editor. 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.
Locate the
max_connections
setting and change it to1000
:max_connections=1000
- Save the changes to the file.
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.- Save the changes to the file.
Restart the service so that all of the above changes take effect:
sudo systemctl restart postgresql-9.5.service
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))
- Finish setting up and configuring the Hipchat database according to the guidelines in the Small-Scale Setup Documentation.