[Linux] Install PostgreSQL 9.4 on CentOS 7

[Linux] Install PostgreSQL 9.4 on CentOS 7

postgresql-logo

About
PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows.

Install PostgreSQL

$ rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-1.noarch.rpm

Update the repository using the following command:

$ yum update

Install PostgreSQL with the following command:

$ yum install postgresql94-server postgresql94-contrib

Initialize PostgreSQL database using the following command:

$ /usr/pgsql-9.4/bin/postgresql94-setup initdb

Then, start PostgreSQL service and make it to start automatically on every reboot.

$ systemctl enable postgresql-9.4
$ systemctl start postgresql-9.4

Adjust IP tables and Firewall

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

Access PostgreSQL

$ su - postgres
$ psql

then terminal shows:

psql (9.4.4)
Type "help" for help.

postgres=#

Set “postgres” user password

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

Create New User and Database

$ su - postgres
$ createuser <NEW_USER>
$ createdb <NEW_DATA_BASE>
$ psql

psql (9.4.4)
Type "help" for help.

Then set password and Grant access to the database NEW_DATA_BASE for NEW_USER:

postgres=# alter user <NEW_USER> with encrypted password 'NEW_PASSWORD';
ALTER ROLE

postgres=# grant all privileges on database NEW_DATA_BASE to NEW_USER;
GRANT
postgres=#

Delete Users and Databases

$ su - postgres
$ dropdb <database-name>

To delete a user, enter the following command:
$ dropuser <user-name>

If you want to check tables in one database, you can use the following command:

$ su - postgres
$ psql -d DATABASE_NAME
DATABASE_NAME=# \dt

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.4/data/pg_hba.conf file:

$ vi /var/lib/pgsql/9.4/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.4

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.4/data/postgresql.conf:

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

Find the lines:

[...]
#listen_addresses = 'localhost'
[...]
#port = 5432
[...]

Remove comment 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 save changes:

$ systemctl restart postgresql-9.4
(Visited 108 time, 1 visit today)
Facebooktwittergoogle_plusredditpinterestlinkedinmail
Comments are closed.