Postgres Installation in Centos 7 and configuration

Postgres Installation and Commands for system admins::

----------------------------------------------------------------------------------------------

 Postgressql installation is very easy and it completes in a single command

# yum install postgresql-server postgresql-contrib


Once the installtion is completed we can create a new PostgreSQL database cluster

# postgresql-setup initdb


Open the Host based authentication configuration file and edit the lines as follows::

# vi /var/lib/pgsql/data/pg_hba.conf

   
host all all 127.0.0.1/32 md5

host all all ::1/128 md5

# systemctl start postgresql

# systemctl enable postgresql

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, we'll need to log into that account as follows:

# sudo -i -u postgres 

You can get a Postgres prompt immediately by typing:

# psql

Exit out of the PostgreSQL prompt by typing:

    \q

Currently configured user roles can be seen using the command:

# \du

Version of PostgreSQL installed:

# SELECT version();
                                                   version                                                   
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.2.15 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)



Command to see the  list of databases by running:

# psql -l

Some simple POSTGRESQL Commands

createuser --interactive --- to create user interactively
createuser test               --- creates user test
createdb test                  --- creates database test
      

-bash-4.2$ psql -d test  --- connects to database test
psql (9.2.15)
Type "help" for help.

test=# \conninfo           --- checks the database connection information
 
You are connected to database "test" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
test=#

To connect to a Postgresql server remotely follow the steps. If you want to connect from a particular network, add the following entry in the postgresql.conf and pg_bha.conf(Host based authentication configuration file )

# vi  /var/lib/pgsql/data/pg_hba.conf
host    all         all         192.168.101.20/24    trust

# grep listen /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'


Once the entries are added and the service restarted, you should be able to connect to postgresql server remotely as follows::

# psql -U postgres -h 192.168.102.1
Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal.postgres=#

Default port of postgresql is 5432. If you want to change the port for postgresql edit the postgresql.conf file as follows::

# port = 5432  to port = 6969

Firewall entries to allow connections to the database 

# sudo firewall-cmd --permanent --zone=trusted --add-source=<Client IP address>/32

# sudo firewall-cmd --permanent --zone=trusted --add-port=5432/tcp

# sudo firewall-cmd --reload

Creating password for the postgres user

bash-4.2$ psql
psql (9.4.4)
Type "help" for help.

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

Command to login remotely to the database server as follows::

$ psql -h <Server IP Address> -p 5432 -U postgres -W
Password for user postgres:
psql (9.2.15)
Type "help" for help.