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

1: How to install postgresql on another machine?

Posted by Ke Wang on 04/17/02 10:04 PM

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!

2: Response to How to install postgresql on another machine? (response to 1)

Posted by David Walker on 04/17/02 11:03 PM

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:127.0.0.1:5432 nonrootaccount@db_machine

If you haven't already, generate your ssh2 key using ssh-keygen -d
Put the ssh2 (/root/.ssh/id_dsa.pub) 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 127.0.0.1 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 127.0.0.1::security

3: Response to How to install postgresql on another machine? (response to 1)

Posted by Gilbert Wong on 04/18/02 12:15 AM

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.

4: Response to How to install postgresql on another machine? (response to 1)

Posted by Don Baccus on 04/18/02 04:40 PM

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.