Installing Postgresql Centos 7

You can check which version of Centos you are running by:

cat /etc/redhat-release

Output

CentOS Linux release 7.2.1511 (Core)

Install the repository for the latest stable version:

Add "exclude=postgresql*" to code blocks [base] and [updates] sections in /etc/yum.repos.d/CentOS-Base.repo to avoid outdated yum packages.

Then Install the latest version repo RPMs for Centos 7.

Get the link for the latest stable version at http://yum.postgresql.org/repopackages.php
yum localinstall <link from the comment above>

As of this writing version PostgreSQL 9.5 for Centos 7.

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

For more information you can check postgres documentation{: target=”_blank” }

List available packages:

yum update
yum list postgres*

Install PostgreSQL 9.5 server and contrib

yum install postgresql95 postgresql95-server postgresql95-libs postgresql95-contrib postgresql95-devel

Initialise db for first use (one time only)

/usr/pgsql-y.x/bin/postgresqlyx-setup initdb

e.g:

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

Output

Initializing database ... OK

If you want PostgreSQL to start automatically when the OS starts:

chkconfig <name> on

e.g:

chkconfig postgresql-9.5 on

Output

Note: Forwarding request to 'systemctl enable postgresql-9.5.service'.
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.5.service to /usr/lib/systemd/system/postgresql-9.5.service.

You can check a list of services, type:

systemctl list-unit-files

To control the database service

where <command> can be:

start : start the database
stop : stop the database
restart : stop/start the database; used to read changes to core configuration files
reload : reload pg_hba.conf file while keeping database running

E.g:

service postgresql-9.5 start

Output

Redirecting to /bin/systemctl start  postgresql-9.5.service

Add firewall permissions

firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --permanent --add-port=80/tcp
firewall-cmd --reload

if you get bash: firewall-cmd: command not found

Two possible options:

Your PATH does not contain /usr/bin
firewall-cmd is not installed

Install:

yum install firewalld

To enable firewalld, run the following command as root:

systemctl enable firewalld

To start firewalld, run the following command as root:

systemctl start firewalld

To check the status of firewalld, run the following command as root:

systemctl status firewalld

Run the following command to make PostgreSQL work if SELinux is enabled on your system.

setsebool -P httpd_can_network_connect_db 1

You may not be able login to PostegreSQL if you didn’t run the above command.

Access PostgreSQL command prompt:

The default database name and database user are “postgres”. Switch to postgres user to perform postgresql related operations:

su - postgres

To login to postgresql, enter the command:

psql

Output

psql (9.5.4)
Type "help" for help.
postgres=#

Check the version to confirm:

SELECT version();

Output

                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)

Set “postgres” user password:

postgres=# \password postgres 
Enter new password: 
Enter it again: 
postgres=# \q

Output

Enter new password: 
Enter it again:

Create New User and Database

For example, let us create a new user called “demouser” with password “demo”, and database called “demodb”.

Switch to postgres user:

su - postgres

Create user demouser.

$ createuser demouser

Create database:

$ createdb demodb

Now, we need to login to the psql prompt, and set password and Grant access to the database demodb for the user demouser:

$ psql

Output

psql (9.5.4)
Type "help" for help.

Set password for demouser:

postgres=# alter user demouser with encrypted password 'demo';

Output

ALTER ROLE

Add privileges to demouser on the database demodb:

postgres=# grant all privileges on database demodb to demouser;

Output

GRANT

Delete Users and Databases

To delete the database, switch to postgres user:

su - postgres

If you are already at the postgres prompt, go back to bash.

postgres=# \q

Output

-bash-4.2$

Enter command to delete the database:

$ dropdb demodb

Enter command to delete the user:

$ dropuser demouser

Since we’ve set password for the default user postgres we need to change some settings.

Configure PostgreSQL-MD5 Authentication. MD5 authentication requires the client to supply an MD5-encrypted password for authentication. To do that, edit /var/lib/pgsql/9.5/data/pg_hba.conf file:

vim /var/lib/pgsql/9.5/data/pg_hba.conf

Add or Modify the lines as shown below:

 TYPE  DATABASE        USER            ADDRESS                 METHOD

 # "local" is for Unix domain socket connections only
local   all             all                                     md5
 # IPv4 local connections:
host    all             all             127.0.0.1/32            md5
 # IPv6 local connections:
host    all             all             ::1/128                 md5

Restart postgresql service to apply the changes:

systemctl restart postgresql-9.5

linux