Forum OpenACS Q&A: How to install postgresql on another machine?

I need to install aolserver and postgresql on different machines. The
machines are using ssh. How should I change the nsd.tcl?

ns_section "ns/db/pool/main"
    ns_param Driver postgres
    ns_param Connections 15
    ns_param DataSource localhost::security
    ns_param User nsadmin
    ns_param Password ""

Should just replace localhost to the other machine name or ip? Will
it automatically ssh to the database's machine?

thanks a lot!

Posted by David Walker on
If you truely want to do what you described here is the way I do it. Using ssl is probably simpler but I haven't done it and can't tell you how and ssh is nice because it requires one more layer of security.

Add this line to your /etc/inittab
tw01:345:respawn:/usr/bin/ssh -2 -e none -p 22 -c blowfish -L 5432: nonrootaccount@db_machine

If you haven't already, generate your ssh2 key using ssh-keygen -d
Put the ssh2 (/root/.ssh/ public key for your root account of the web server in /home/nonrootaccount/.ssh/authorized_keys2 on the database server
"init q" will cause your machine to reread /etc/inittab and, if everything works, will start forwarding port 5432 of to your database server over the encrypted ssh link

Change the DataSource line to use an IP instead of "localhost". The driver connects on Unix sockets for the name "localhost" and uses tcpip for anything else.
ns_param DataSource

Posted by Gilbert Wong on
I have a DB server running on a private network behind the webserver.  You can change localhost to the remote machine name.  You will also need to edit the pg_hba.conf file (/usr/local/pgsql/data/) on the remote machine to allow for remote connections.  I believe it is turned off by default.  Make sure you start the postmaster with the -i option.

On the OpenACS side, you will need to modify the bootstrap installer to allow for remote connections.  By default, it does not pass the host parameter for the db and will not be able to connect to remote machines.  I don't know if that functionality has been added to the CVS tree.  I can look up the edit I made to if you need.

Posted by Don Baccus on
I have vague memories of adding your change, Gilbert, many months ago.  If you have time to check the OpenACS 4.5 beta to see if I added it, and added it correctly if I did add it, I'd appreciate it.