Step by step installation of PostgreSQL 9.4 on CentOS 7


 1200px-Postgresql_elephant.svg.png
First, we will install CentOS 7 on Virtualbox.
You can download iso image of CentOS 7 from https://www.centos.org/
Let’s create our virtual machine:l

1
2
3
4
5
6
Then go to the settings of new created virtual machine and change network and other settings like bellow:
7

Add Downloaded CentOS 7 disc image to cdrom
8

9

Start Virtual Machine and begin installation:

10

11
12

14
15

16
17

18
19
20
21

22
23
24
25
26
27
28
29

30

31.PNG

Install guest additions and reboot system:

32

Then edit /etc/hosts file and add IP address and hostname.

Check sshd service if it not works disable change SELinux config and the check again

[root@posgresql ~]# systemctl status sshd.service

Edit SELinux
Yo can  set permissive SELINUX or make PosgreSQL work if SELinux enabled
When you configure SELinux as permissive, it gives you a warning message instead of actually prohibiting the action. To change SELinux’s behavior to the permissive mode you need to edit the configuration file.

[root@posgresql ~] vi /etc/SELinux/config
SELINUX=permissive

[root@posgresql ~]# systemctl status sshd.service
● sshd.service - OpenSSH server daemon
 Loaded: loaded (/usr/lib/systemd/system/sshd.service; enabled; vendor preset: enabled)
 Active: active (running) since Sun 2017-04-16 07:14:14 +04; 5min ago
 Docs: man:sshd(8)
 man:sshd_config(5)
 Main PID: 966 (sshd)
 CGroup: /system.slice/sshd.service
 └─966 /usr/sbin/sshd -D

Apr 16 07:14:14 posgresql.localdomain systemd[1]: Starting OpenSSH server daemon...
Apr 16 07:14:14 posgresql.localdomain sshd[966]: Server listening on 0.0.0.0 port 22.
Apr 16 07:14:14 posgresql.localdomain sshd[966]: Server listening on :: port 22.
Apr 16 07:14:14 posgresql.localdomain systemd[1]: Started OpenSSH server daemon.
Apr 16 07:17:25 posgresql.localdomain sshd[9933]: Accepted password for root from 192.168.2.180 port 50904 ssh2

 

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

[root@posgresql ~] setsebool -P httpd_can_network_connect_db 1

You may not log in to PostegreSQL if you didn’t run the above command.

Then I run yum update

[root@posgresql ~]# yum update

Installing PosgreSQL 9.4s

Go to the PostgreSQL vet site and click download  https://www.postgresql.org
From downloads page click on Red Hat family Linux (including CentOS/Fedora/Scientific/Oracle variants), then scroll down and click on  repository RPM listing link
34

Scroll down and find version which we want and copy link address35

Then go to terminal and install package

[root@posgresql ~]# yum install https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm

[root@posgresql ~]# yum repolist
<<<OUTPUT TRIMMED>>>
pgdg94/7/x86_64 PostgreSQL 9.4 7 - x86_64 395
updates/7/x86_64 CentOS-7 - Updates 1,491
repolist: 11,560

[root@posgresql ~]# yum install postgresql94 postgresql94-contrib.x86_64
<<<OUTPUT TRIMMED>>>
Complete!
[root@posgresql ~]# rpm -qa | grep postgres
postgresql94-libs-9.4.11-2PGDG.rhel7.x86_64
postgresql94-contrib-9.4.11-2PGDG.rhel7.x86_64
postgresql94-server-9.4.11-2PGDG.rhel7.x86_64
postgresql94-9.4.11-2PGDG.rhel7.x86_64

Then we initialize cluster and start it

[root@posgresql ~]# /usr/pgsql-9.4/bin/postgresql94-setup initdb
Initializing database ... OK
[root@posgresql ~]# systemctl start postgresql-9.4.service

To enable PostgreSQL to start on boot:

[root@posgresql ~]# systemctl enable postgresql-9.4.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.4.service to /usr/lib/systemd/system/postgresql-9.4.service.

36

Congratulations PostgreSQL installed and ready to use 🙂

How to start and stop Postgres:

[root@posgresql ~]# systemctl stop postgresql-9.4.service
[root@posgresql ~]# ps -ef | grep postgres
root 11235 9938 0 08:25 pts/0 00:00:00 grep --color=auto postgres

 
[root@posgresql ~]# systemctl start postgresql-9.4.service
[root@posgresql ~]# ps -ef | grep postgres
postgres 11253 1 0 08:26 ? 00:00:00 /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data
postgres 11254 11253 0 08:26 ? 00:00:00 postgres: logger process 
postgres 11256 11253 0 08:26 ? 00:00:00 postgres: checkpointer process 
postgres 11257 11253 0 08:26 ? 00:00:00 postgres: writer process 
postgres 11258 11253 0 08:26 ? 00:00:00 postgres: wal writer process 
postgres 11259 11253 0 08:26 ? 00:00:00 postgres: autovacuum launcher process 
postgres 11260 11253 0 08:26 ? 00:00:00 postgres: stats collector process 
root 11265 9938 0 08:26 pts/0 00:00:00 grep --color=auto postgres

To be able to connect you must open port 5432 in firewall:

 [root@posgresql ~]# systemctl status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
 Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
 Active: active (running) since Sun 2017-04-16 07:14:08 +04; 1h 25min ago
 Docs: man:firewalld(1)
 Main PID: 576 (firewalld)
 CGroup: /system.slice/firewalld.service
 └─576 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid

Apr 16 07:14:07 posgresql.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
Apr 16 07:14:08 posgresql.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.


[root@posgresql ~]# firewall-cmd --state
running

[root@posgresql ~]# firewall-cmd --list-all
public (active)
 target: default
 icmp-block-inversion: no
 interfaces: enp0s3
 sources: 
 services: dhcpv6-client ssh
 ports: 
 protocols: 
 masquerade: no
 forward-ports: 
 sourceports: 
 icmp-blocks: 
 rich rules: 

[root@posgresql ~]# firewall-cmd --permanent --add-port=5432/tcp
success
[root@posgresql ~]# systemctl restart firewalld.service

[root@posgresql ~]# firewall-cmd --list-all
public (active)
 target: default
 icmp-block-inversion: no
 interfaces: enp0s3
 sources: 
 services: dhcpv6-client ssh
 ports: 5432/tcp
 protocols: 
 masquerade: no
 forward-ports: 
 sourceports: 
 icmp-blocks: 
 rich rules:


Now let’s do some tests on our new installed PostgreSQL

[root@posgresql ~]# su - postgres
-bash-4.2$ which psql
/bin/psql
-bash-4.2$ psql
psql (9.4.11)
Type "help" for help.

postgres=# 
postgres=# create user valeh with password 'test';
CREATE ROLE
postgres=# create database testdb owner valeh;
CREATE DATABASE

postgres-# \quit
-bash-4.2$ exit
logout


By default, PostgreSQL is operating through a socket on the localhost. In that configuration, the installation is secured against remote threats. If you do not need to access the database from a remote host, you can leave the default configuration.

To configure access to database from a remote host we must edit some files.
First, we will tell PostgreSQL to start listening on our network interfaces:

 vi /var/lib/pgsql/9.4/data/postgresql.conf

41

uncomment and change it like bellow:

42

By default, PostgreSQL does not allow password authentication. We will change that by editing its host-based authentication (HBA) configuration:

[root@posgresql ~]# vi /var/lib/pgsql/9.4/data/pg_hba.conf

39

Then replace “ident” with “md5” and line with our clients IP address :
43

Restart PostgreSQL and verify that we are now listening on port 5432:

[root@posgresql ~]# systemctl restart postgresql-9.4.service

[root@posgresql ~]# ss -l -n |grep 5432
u_str LISTEN 0 128 /var/run/postgresql/.s.PGSQL.5432 35417 * 0 
u_str LISTEN 0 128 /tmp/.s.PGSQL.5432 35419 * 0 
tcp LISTEN 0 128 *:5432 *:* 
tcp LISTEN 0 128 :::5432 :::*

To connect to database from your host download PgAdmin, install and connect:44

45

Now we connected to our remote database:

46

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s