How to setup PostGIS for use with multiple machines?

Question

I'm a beginner user of postgresql/postgis. I am starting to use Linux more and more but still rely on windows (on different machines). How would I setup a postgis database that I could work with under different systems? Thanks, Eelke

Answer 1

Basically pick a computer as a server, install the OS and database software, open the firewall for port 5432, modify the postgresql.conf and pg_hba.conf files to allow external access, and serve the data to the other computers. These are the same instructions for setting up a server on either Windows or Linux, except one OS is free. Most folks would pick Linux for the server, if you are comfortable setting up the networking and firewall rules.

All other computers (Linux/Mac/Windows/etc) connect to the PostgreSQL server the same way, using:

• hostname, either something like server.company.org or 192.168.0.101
• port, normally 5432 but can be some other number
• username / password, these you set up in, e.g. pgAdmin; default superuser is postgres

Answer 2

A really good guide to setup connecting to a postgres database on another machine (which could be remote) is at http://library.linode.com/databases/postgresql/pgadmin-windows. I won't repeat it here, as that guide covers it well.

The advantage of this is everything transferred between the two machines is encrypted, and you don't need to give any other IP addresses access to the main database other than localhost. If you don't need to worry about these issues, then you can just edit the postgresql.conf and pg_hba.conf as Mike Toews has said.

Answer 3

Not sure if this is the correct SE site to ask on, but you need to be able to access it from both systems, so you need to put it on a common disk partition. Any windows one will do, since linux supports both the old FAT(s) and NTFS types. You'll need to mount it readable and writable for your normal user and tell postgres to look for it there (via -D or $PGDATA).