How To: Network PostgreSQL Database

Frequently Asked Questions/Answers, Tutorials, and Common How To's

Moderator: Moderators

How To: Network PostgreSQL Database

Postby prajna » Sun May 11, 2008 3:35 pm

Please note: if you are not behind a router, this will not work for you.

There may come a time where you want one database that two computers share within the same local network. This is fairly simple, but to make sure I have written this tutorial to assist those who need help in this matter.

First we need to know about how your network is setup. Go to a command prompt and type ipconfig (on Mac or linux type ifconfig in a terminal).
You will see all your physical connections to the internet here. We are interested in the IP addresses of these connections. They should start with 192.168, but most importantly we are worried about the 3rd number in the ip address. Is it a 1 or a 0? If it is a zero follow step A below. If its a one, follow step B below.

The general overview is to update your configuration files to let the server know that computers in your same subnet are allowed access to the server. To do this, goto Start -> Programs -> PostgreSQL-> Configuration Files -> pg_hba.conf. Scroll to the bottom of the file. You will see something similar to this at the bottom:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
#host all all ::1/128 md5


Step A) simply add the line below to the bottom of your document:
host all all 192.168.0.0/24 md5

Save and exit.

Step B) simply add the line below to the bottom of your document:
host all all 192.168.1.0/24 md5

Save and exit.

To make these changes on Mac you need to change permissions on the /Library/PostgreSQL/8.4/data folder (note: not ~/Library).
Right click the data folder and choose "Get Info". At the bottom of the info window change permissions to Read & Write for "everyone".
You can then make the changes to the config files, which are in the "data" folder.
After you edit the config files you MUST change the permissions back to No Access for "everyone".


Thanks for fooz for this extra info:
Now, go back to Start -> Programs -> PostgreSQL -> Configuration Files -> postgresql.conf and add the following line to the end of the file:

listen_addresses = '*'

This tells your server to listen to ALL addresses your computer has, not just localhost (which is only accessible from the local computer).

Once saved, your computer will now send SQL results to computers in your local network. To connect to that server remotely, you need to know that server's address. Goto Start -> Run -> command and click OK. Now type ipconfig and look for the ip address of your computer. If you have more than one reply, choose the one that says "local area connection". Mine looks like this:

Windows IP Configuration


Ethernet adapter VMware Network Adapter VMnet8:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.63.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

Ethernet adapter VMware Network Adapter VMnet1:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.226.1
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . :

Ethernet adapter Local Area Connection 2:

Connection-specific DNS Suffix . :
IP Address. . . . . . . . . . . . : 192.168.1.16
Subnet Mask . . . . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . . . . : 192.168.1.254


My IP address here is 192.168.1.16, so my server's ip address is the same. Therefore, to log into this database from my other computers in the house the server:port:pass is - 192.168.1.16:5432:dbpass. Substitute your information to access your database from another computer in your local network. Also, if you have removed password authentication from your server, please substitute "trust" for "md5" in the line you are adding to your pg_hba.conf file.

Good luck!
prajna
 
Posts: 734
Joined: Thu Apr 03, 2008 1:58 pm

Return to FAQs, Tutorials, and How To's [Read Only]

Who is online

Users browsing this forum: No registered users and 5 guests

cron
highfalutin