Sunday, June 16, 2019

Installation of PostgreSQL

Install through yum:

[root@localhost ~]# yum -y install postgresql-server postgresql postgresql-libs
or
[root@localhost ~]# yum -y update postgresql-server postgresql postgresql-libs

Install through apt-get:

[root@localhost ~]# apt-get install postgresql

For manual installation:

Download the .rpm or .deb file(s) and save it to disk.
Binary Distributions: http://www.postgresql.org/download/linux

Install:

[root@localhost ~]# rpm -ivh postgresql-<version>.rpm postgresql-server-<version>.rpm postgresql-libs-<version>.i386.rpm

or

[root@localhost ~]# dpkg -i postgresql-<version>.deb
Initialization of PostgreSQL server:
[root@localhost ~]# service postgresql initdb
[root@localhost ~]# chkconfig postgresql on
or
[root@localhost ~]# postgresql-setup initdb
[root@localhost ~]# systemctl enable postgresql
Configuration:

edit the file /var/lib/pgsql/data/pg_hba.conf replase the line

host    all         all         127.0.0.1/32          ident
with this one
host    all         all         127.0.0.1/32          md5
Starting of PostgreSQL server:
[root@localhost ~]# service postgresql start
or
[root@localhost ~]# systemctl start postgresql
Logon into the PostgreSQL server:
[root@localhost ~]# su postgres
bash-4.2$ psql postgres
could not change directory to "/root"
psql (9.2.18)
Type "help" for help.

postgres=# 
Create PostgreSQL user:
postgres=# CREATE USER vpnttg SUPERUSER;
CREATE ROLE
postgres=# ALTER USER vpnttg UNENCRYPTED PASSWORD 'vpnttgpass';
ALTER ROLE
Check of PostgreSQL users:
postgres=# SELECT * FROM pg_shadow;
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |   passwd   | valuntil | useconfig 
----------+----------+-------------+----------+-----------+---------+------------+----------+-----------
 postgres |       10 | t           | t        | t         | t       |            |          | 
 vpnttg   |    16384 | f           | t        | t         | f       | vpnttgpass |          | 
(2 rows)

postgres=# 
Logout from the PostgreSQL server:
postgres=# \q
bash-4.2$ exit
[root@localhost ~]#
Create of VPNTTG DB:
[root@localhost ~]# export PGPASSWORD=vpnttgpass
[root@localhost ~]# createdb vpnttgdb -h 127.0.0.1 -U vpnttg
List of PostgreSQL databases:
[root@localhost ~]# psql -l -h 127.0.0.1 -U vpnttg
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 vpnttgdb  | vpnttg   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

[root@localhost ~]#