Forum OpenACS Q&A: Remote Postgresql DB Server

Collapse
1: Remote Postgresql DB Server
Posted by Carl Robert Blesius on 05/22/03 02:56 PM
Is it possible to run Postgresql on a box other than the one AOLServer is running on (like it is with Oracle)? If so has anyone run multiple AOLServer machines that use one pgDB box?
Collapse
2: Re: Remote Postgresql DB Server (response to 1)
Posted by Jarkko Laine on 05/22/03 03:55 PM
Hi Carl,

Sure it's possible. No problem. PostgreSQL listens some tcp port and if the port is open to other machines, they can connect to it. You just have to define the host and port for the client accordingly.

I have never tested tested that, though, since one box can normally run quite many OpenACS instances.

Collapse
3: Re: Remote Postgresql DB Server (response to 1)
Posted by Patrick Giagnocavo on 05/22/03 04:46 PM
Hi Carl,

Not only is it possible, but in some cases I would strongly recommend it.

I use this strategy (db on one machine, many OpenACS instances on another) for all my shared OpenACS hosting accounts.

The main advantage is that the CPU utilization is spread across 2 machines rather than one. As well, the filesystem's buffer cache on the db machine is thus dedicated to Postgres I/O - there are no other processes competing for use of the disk.

Collapse
4: Re: Remote Postgresql DB Server (response to 1)
Posted by Steve Manning on 05/22/03 11:15 PM
I believe that Postgres ships with the tcp port closed. You have to open the port by firing up Postgres (postmaster) with the -i option.

You should also edit the pg_hba.conf file in the data dir to allow access from the foreign machine - pg_hba.conf allows you to configure the ip of the allowed connections, the type of authentication they should perform and what they acan access.

Also bear in mind that opening any tcp port is a potential security problem so its best done behind a firewall if you have to do it.

- Steve

Collapse
5: Re: Remote Postgresql DB Server (response to 1)
Posted by Joel Aufrecht on 05/23/03 12:40 AM
(This is in the to-doc list - please help me nail it down. Comments and feedback needed.)

How to Connect to a remote PostGres database. This is a skeleton of what I'll put in the docs. It's based on the Reference Platform.

  1. On the database machine
    1. Edit the file /usr/local/pgsql/data/postgresql.conf and change 
      #tcpip_socket = false
      to 
      tcpip_socket = true
      and restart postgresql (service postgres restart)
    2. In the file /usr/local/pgsql/data/pg_hba.conf, grant access to a specific IP address (in this example, 192.168.0.12) by adding this line: 
      host       all         192.168.0.12  255.255.255.255      trust

      This line says that a computer ("host") at 192.168.0.12 (in conjuction with the netmask, which lets you specify a range of ips; in the example we use 255.255.255.255, which limits us to the exact ip) can access any database ("all") on the server. The word "trust" means no further authentication is required above the ip address.

      For other kinds of remote access control, read the comments in the file. Nine different authentication modes are available.

  2. On the client machine

    The client machine is a computer running aolserver but using the remote database

      Edit /web/service0/etc/config.tcl and change each of the three instances of 
      ns_param   datasource    service0
      to 
      ns_param   datasource    192.168.0.11::service0

      In this example, 192.168.0.11 is the ip address of the database server and service0 is the name of the database.

Some other issues when running one OpenACS site across multiple servers (these are rough notes - I'll add details and incorporate everybody's opinions when I add to the actual docs):

  • Synchronizing content. You can designate one as the master, presumably working from cvs, and then use rsync in a cron job from all other machines. I use 
    */5 * * * * rsync -CruL -e ssh  service0@mastermachine.test:/web/service0/ /web/service0/

    (su - to service0 and use crontab -e to edit the cron file. Since this uses ssh, you will have to set up certificates, as detailed in (to be detailed later), which you should do anyway so that the database connection can also be sshed.)

  • Collecting all of the log files into one place
  • Handling outgoing email