How to connect to Postgres Database Remotely

By default, PostgreSQL database server remote access disabled for security reasons. You can by default only access it from the same machine you are on.

Step # 1: Allow remote IP address to access PostgreSQL

You need to open file called /var/lib/pgsql/data/pg_hba.conf. Login as postgres user using su command:
$ su - postgres
$ vi /var/lib/pgsql/data/pg_hba.conf  (directory might be different depending on where you installed postgres).
Now append following line. Let us say you would like to give access to 192.168.0.xxx on your network:

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

host    all    all   192.168.0.0  255.255.255.0  trust ----------------------------------------------------

Please replace 192.168.1.0 and 255.255.255.0 to reflect the actual network IP address range of the clients system in your own network.

Save close the file.

Step # 2: Allow communication over TCP/IP

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf

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

Change the line listen_addresses, which by default is usually commented out with value 'localhost' to a value to accept remote machines connecting to it.

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

listen_addresses = '*' -------------------------------------

Make sure your address is not commented out  (the "#" is a comment from that symbol to end of line).

Save and close the file.

Step # 3: Restart PostgreSQL server

Restart the PostgreSQL server. It's best to shutdown, then restart it and check the 'Logfile' for any errors. If you botched something, it will most likely show up in the logfile right away.This will open default port 5432. 

Step # 4: Test your setup

Use psql command from client system as follows:

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

Postgres Installation on mac

Download  postgresql-8.4.7.tar.gz from http://www.postgresql.org/ftp/source/v8.4.7/

Follow the below instructions to extract, install and configure "Postgres"

#tar -xvfz postgresql-8.4.7.tar.gz

#cd  postgresql-8.4.7

$./configure

$make 

$make install

Create postgres account(System Preferences->Accounts->new User->postgres)

$su -

imac:~ root#mkdir /usr/local/pgsql/data
imac:~ root#chown postgres /usr/local/pgsql/data
imac:~ root#su -l postgres
imac:~ postgres$/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

The above command will take the default encoding SQL_ASCII

If you want to select different encoding use:

/usr/local/pgsql/bin/initdb -E UTF8 /usr/local/pgsql/data

set the path using:

imac:~ postgres$ export PATH=$PATH:/usr/local/pgsql/bin

starting the postgres using:

imac:~ postgres$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/logfile start

stopping the postgres using:

imac:~ postgres$ /usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data 

Using Postgres command line shell

Starting Postgres Interpreter Shell

• SU to user 'postgres' (see above)
• $ psql test              (test is name of database you want to connect to)

// Quit shell

test=> \q

// import sql file
test=> \i basics.sql

// list databases
test=> \l

// 'use' database test
test=> \c test

// List tables in current dbms
test=> \d

// Show table structure
test=> \d tablename