A.Connecting postgresql locally using port

Please identify port in postgresql.conf under /opt/edb/as9.6/data and connect using psql

There are pre-requisites for allowing local connection.This pg_hba.conf file is under  /opt/edb/as9.6/data.Please make sure last parameter should be md5 not peer.

The below content is for example what should be in pg_hba.conf

# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5

cd /opt/edb/as9.6/bin

-bash-4.1$ ./psql -p 5444 edb enterprisedb
 psql.bin (9.6.2.7)
 Type "help" for help.
port:-5444
Database name:-edb
Password for enterprisedb user:-enterprisedb

B.Allowing remote connections

  1. As a super user, open /etc/postgresql/9.6/main/pg_hba.conf (Ubuntu) or /var/lib/pgsql/9.6/data/pg_hba.conf (Red Hat) in a text editor.

  2. Scroll down to the line that describes local socket connections. It may look like this:

    local   all             all                                      peer
    
  3. Change to:

    host    all             all             0.0.0.0/0               trust
    
    
    1. Save and close the file.

    2. In the same directory, open postgresql.conf.

Under the section on Connection Settings, add or replace the line that starts with listen_addresses to respond to all requests:

listen_addresses = '*'

Please restart postgresql service now.

C.Use psql command from client system as follows(Before that you need to setup pg_hba.conf as above to allow remote connection)

psql -h PostgreSQL-IP-ADDRESS -U USERNAME -d DATABASENAME

Connect to remote server by IP address 192.168.1.5 and login using debasis user to connect to edb database, use:
$ psql -h 192.168.1.5 -U debasis -d edb
Where,

  • -h 192.168.1.5 : Specifies the host name of the machine or IP address (192.168.1.5) on which the server is running.
  • -U debasis : Connect to the database as the debasis username instead of the default. You must have account and permission to connect as debasis user.
  • -d edb : Specifies the name of the database (edb) to connect to.

D.Connect to edb sql

cd /opt/edb/as9.6/bin

-bash-4.1$ ./edb-psql edb
psql.bin (9.6.2.7)
Type “help” for help.

edb=#

Here edb is database name.

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *