Wednesday, 2 November 2016

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 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:


Currently configured user roles can be seen using the command:

# \du

Version of PostgreSQL installed:

# SELECT 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".

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    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
Welcome to psql 8.1.11 (server 8.4.18), the PostgreSQL interactive terminal.

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.

No comments:

Post a Comment