Forum OpenACS Q&A: Re: Remote Postgresql DB Server

Collapse
Posted by Joel Aufrecht on
(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