Installing and setting up Postgres with Centos 7

The following will outline how to quickly (and securely) get postgres and up and running.

We'll firstly need to add the epel repositories with:

sudo yum install epel-release

and then install postgres with:

sudo yum install postgresql postgresql-server postgresql-contrib

Initialise the database with:

postgresql-setup initdb

Let's firstly ensure that only local applications can access the server:

sudo vi /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local        all                        postgres                                            peer
local        all                        all                                                     peer
host         all                        all                  127.0.0.1/32               md5
host         all                        all                  ::1/128                       md5

The first line permit permit unix domain socket access for the postgres user - while the second and third line provide network access to localhost IPv4 and IPv6.

Enable and start the service:

sudo systemctl enable postgresql
sudo service start postgresql

Unlike MySQL / MariaDB - postgres by default uses 'peer' accounts - that are simply local user accounts that have been mapped with a corresponding postgres account.

By default postgres create a user called 'postgres' which we can use to access it's CLI:

sudo su - postgres

psql

quit the console using '\q'

We will then create a new user for our application (from the shell) with:

createuser --interactive

(ensuring the user is NOT a super admin, however can create databases.)

and set a password for the user:

psql

\password <username>
\q

We can this test this from shell with:

psql -U <username> -h localhost -W --dbname=postgres

Note: We haven't created a database for the user yet - so we use the 'postgres' database above.

Once logged in we can create our down database with:

CREATE DATABASE <database-name>;