Menu

Install PostgreSQL 9.5 on CentOS/RHEL 7/6/5 and Fedora 23/22

PostgreSQL 9.5 Released. PostgreSQL is an open source object-relational, highly scalable, SQL compliant database management system. PostgreSQL is developed at the University of California at Berkeley Computer Science Department. This article will help you for installing PostgreSQL on CentOS, RHEL and Fedora Systems.

Adding PostgreSQL Yum Repository

First step is to install PostgreSQL repository in your system, Use one of below commands as per your system architecture and operating system.
CentOS/RHEL 7
# rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm

CentOS/RHEL 6
# rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-6-x86_64/pgdg-redhat95-9.5-2.noarch.rpm

CentOS/RHEL 5
# rpm -Uvh http://yum.postgresql.org/9.5/redhat/rhel-5-x86_64/pgdg-redhat95-9.5-2.noarch.rpm

Fedora 23:
# rpm -Uvh http://yum.postgresql.org/9.5/fedora/fedora-23-x86_64/pgdg-fedora95-9.5-3.noarch.rpm

Fedora 22
# rpm -Uvh http://yum.postgresql.org/9.5/fedora/fedora-22-x86_64/pgdg-fedora95-9.5-3.noarch.rpm

Fedora 21
# rpm -Uvh http://yum.postgresql.org/9.5/fedora/fedora-21-x86_64/pgdg-fedora95-9.5-2.noarch.rpm
For more details visit PostgreSQL repositories link page where you can get repository package rpm for various operating systems.
Installing PostgreSQL Server
After enabling PostgreSQL yum repository in your system use following command to install PostgreSQL 9.5 on your system with yum package manager.
# yum install postgresql95-server postgresql95
Initializing PGDATA
After installing PostgreSQL server, It’s required to initialize it before using first time. To initialize database use below command.
# /usr/pgsql-9.5/bin/postgresql95-setup initdb
Above command will take some time to initialize PostgreSQL first time. PGDATA environment variable contains the path of data directory.
PostgreSQL data directory Path: /var/lib/pgsql/9.5/data/
Start PostgreSQL Server
To start PostgreSQL service using following command as per your operating systems. Also enable PostgreSQL service to auto start on system boot.
For CentOS/RHEL 7 and Fedora 23
# systemctl start postgresql-9.5
# systemctl enable postgresql-9.5
For CentOS/RHEL 6/5 and Fedora 22/21
# service  postgresql-9.5 start
# chkconfig postgresql-9.5 on
Verify PostgreSQL Installation
After completing above steps, you have installed PostgreSQL 9.5 on your server, Let’s login to postfix to verify that installation completed successfully.
# su - postgres
Use psql command to access PostgreSQL prompt with admin privileges.
$ psql


psql (9.5.0)
Type "help" for help.

postgres=#
You may create password for user postgres for security purpose:
postgres=# \password postgres
You have successfully installed PostgreSQL Server:
Adjust Iptables/Firewall
Next, adjust iptables to access postgresql from remote systems.On CentOS 6.x systems:
vi /etc/sysconfig/iptables
Add the following line:
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT
Save and exit the file. Restart iptables service.
service iptables restart
On CentOS 7 systems:
firewall-cmd --permanent --add-port=5432/tcp
firewall-cmd --permanent --add-port=80/tcp
firewall-cmd --reload

Adjust SELinux

Run the following command to make PostgreSQL work if SELinux enabled on your system.
setsebool -P httpd_can_network_connect_db 1
You may not login to PostegreSQL if you didn’t run the above command.

Create New User and Database
For example, let us create a new user called "payel" with password "centos", and database called "sitedb".Switch to postgres user:

su - postgres

Create user payel.

createuser payel
Create database:
createdb sitedb
Now, login to the psql prompt, and set password and Grant access to the database sitedb for payel:
psql
psql (9.5.0)
Type "help" for help.
postgres=# alter user payel with encrypted password 'centos';
ALTER ROLE
postgres=# grant all privileges on database sitedb to payel;
GRANT
postgres=#

Delete Users and Databases

To delete the database, switch to postgres user:
su - postgres
Enter command:
$ dropdb <database-name>
To delete a user, enter the following command:
$ dropuser <user-name>

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:
vi /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
host    all             all             192.168.1.0/24          md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
[...]
Restart postgresql service to apply the changes:
On CentOS 6.x systems:
service postgresql-9.5 restart
On CentOS 7 systems:
systemctl restart postgresql-9.5

Configure PostgreSQL-Configure TCP/IP

By default, TCP/IP connection is disabled, so that the users from another computers can’t access postgresql. To allow to connect users from another computers, Edit file/var/lib/pgsql/9.5/data/postgresql.conf:
vi /var/lib/pgsql/9.5/data/postgresql.conf
Find the lines:
[...]
#listen_addresses = 'localhost'
[...]
#port = 5432
[...]
Uncomment both lines, and set the IP address of your postgresql server or set ‘*’ to listen from all clients as shown below:
listen_addresses = '*'
port = 5432
Restart postgresql service to apply the changes:
On CentOS 6.x systems:
service postgresql-9.5 restart
On CentOS 7 systems:
systemctl restart postgresql-9.5

About Author:


I am a Linux Administrator and Security Expert with this site i can help lot's of people about linux knowladge and as per security expert i also intersted about hacking related news.TwitterFacebook

Next
Newer Post
Previous
Older Post

0 comments:

Post a Comment

 
Top