Deploy and Configure a Postgres Server on CentOS7

By | August 11, 2017

A simple step by step guide on deploying Postgres and PgAdmin4

As we deploy our own cloud infrastructure, maintain our cross platform environments and support our customers we also create our own deployment guides. These guides provide step by step instructions which are the steps we have taken building the labs and the production environments including notes of potential issues and how to work around them providing valuable information to our engineers. It allows us to deploy these environments in a standard way that all engineers know.

QualTech Hosting Linux Services | QualTech360LinuxIf you are going to do a fresh installation of a Postgres database most likely you want to install the latest version of the database. You can access the rpm here https://yum.postgresql.org/.

The steps to install the rpm and install the server

  • yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
  • install the client packages: yum install postgresql96
  • install the server packages: yum install postgresql96-server
  • initialize the database and enable automatic start:

/usr/pgsql-9.6/bin/postgresql96-setup initdb

systemctl enable postgresql-9.6

systemctl start postgresql-9.6

PgAdmin4 Installation

If you installed the RPM for the posgresql 9.6 at the top of this aticle you should now be able to install pgadmin 4:QualTech Java Development Services | QualTech360Java

yum install pgadmin4-v1

On your gnome under Applications>Programming you should now be able to see pgAdmin 4.

First time configuration and Pgadmin access

After Postgres is installed it has to be initialized and the service setup to start as a service.

sudo -u postgres psql postgres

\password postgres

If this command doesn’t work see notes below.

Allowing local connections

QualTech Custom Software Development Services | QualTech360DevelopmentThe file pg_hba.conf governs the basic constraints underlying connection to PostgreSQL. By default, these settings are very conservative. Specifically, local connections are not allowed for the postgres user.

To allow this:

  1. As a super user, open /etc/postgresql/9.6/main/pg_hba.conf (Ubuntu) or /var/lib/pgsql/9.6/data/pg_hba.conf (Red Hat) in a text editor.
  2. Scroll down to the line that describes local socket connections. It may look like this:

local all all peer

  1. Change the peer method to md5

For more information on the various options, please see the PostgreSQL documentation on pg_hba.conf

  1. To allow connections using pgAdmin, find the line that describes local loopback connections over IPv6 and IPv4:

host all all ::1/128 ident

  1. Change the ident method to md5.QualTech IaaS | QualTech360Infrastructure
  2. Save and close the file.
  3. Restart PostgreSQL:

sudo service postgresql-9.6 restart

To test your connection using psql, run the following command:

psql -U postgres -W

You can download the guide here.

#QualTech360Care, #QualTech360Solutions, #QualTech360Linux, #QualTech360IaaS

Leave a Reply