Background: I am busy with an MSc in GIS. It involves using postgresql 9.2 with the postgis extension as the spatial database and QGIS to view, edit and analyse the data. I know postgis is also capable of editing and analyzing spatial data. However, the end users do not have experience with SQL, therefore the point and click QGIS GUI. Part of my thesis involves a guide on installing Postgres/postgis on a server. The problem is, I have very little experience with databases and servers. Although, I have been able to succesfully install and use postgresql/postgis on my win 7 pc, I'm not sure if it is the same for servers.

*Question:*Is postgresql/postgis installed on a server in the same manner as on a pc? If not, please explain (in as much detail as possile) or point me in the right direction. Also, what configurations has to be done for client pc's to connect to postgresql/postgis on the server?

Thanks in advance.

closed as too broad by Mark Storey-Smith, Paul White, Max Vernon, Kin Shah, Philᵀᴹ Apr 30 '14 at 2:09

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • What server? What OS? – Fabrizio Mazzoni Feb 14 '14 at 9:00
  • Windows server 2008. Windows 7 os. – PyMapr Feb 14 '14 at 9:25

Except in Windows-land there's no difference between a "Server" and a "PC" other than typical hardware choices and installed software.

PostgreSQL follows that, and doesn't care in the slightest what you're installing it on. Install it on Win2k8 the same way you would on any Windows desktop OS.

The only possible wrinkle is Group Policy and Active Directory domain membership, but those are also factors in domain-member PCs. So long as your group policies don't restrict the NETWORKSERVICE, and so long as you install PostgreSQL to use the NETWORKSERVICE (default in 9.2 and later) you'll have no issues, it'll be just like any other local machine install.

If you want to run PostgreSQL under an Active Directory user account, or an account with more restrictive group policies, then you might have to set up the service yourself, but you'll have a capable sysadmin to do that if you're in the kind of environment where it might be necessary.

Client configuration depends on what you're connecting with. You may want the ODBC driver psqlODBC, you may want PgJDBC, you might want the Python driver psycopg2, etc etc. The general idea is: install desired driver, use application. The only one that takes more setup is ODBC if used with a predefined DSN, and that's the same for PostgreSQL as any other ODBC app.

To configure the server to accept connections from clients you must:

  • Ensure the server's firewall allows inbound connections to postgres.exe and/or port 5432 (default)
  • Set up pg_hba.conf to allow PostgreSQL to accept remote connections (see the documentation)
  • Create PostgreSQL user accounts for the users
  • Craig, Thanks for the in depth answer. Ive read in the postgres manual that if the data cluster is located on the server, postgres does not have to be installed on the server itself. If I understand correctly, is postgres installed on the client computers and "referred/link" to the data cluster on the server via the config file? – PyMapr Mar 17 '14 at 13:38
  • @Thunderbolt PostgreSQL is a client/server architecture. Client applications talk to a PostgreSQL server over TCP/IP. The PostgreSQL server is the only thing that accesses the data directory, and it must be local. So I don't know where you got that. What part of the manual do you mean exactly? Link + quote? – Craig Ringer Mar 17 '14 at 23:04
  • Thanks for clearing that up. I've read the documentation again, and to be honest, I must have been half asleep the first time I read it. Here is the link anyway (it's the first paragraph). postgresql.org/docs/9.2/static/creating-cluster.html – PyMapr Mar 18 '14 at 13:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.