Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

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

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.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 at 9:00
    
Windows server 2008. Windows 7 os. –  Thunderbolt Feb 14 at 9:25

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
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? –  Thunderbolt Mar 17 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 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 –  Thunderbolt Mar 18 at 13:37

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