r.joseph has asked for the wisdom of the Perl Monks concerning the following question:

First off, thanks a ton for all the great replies to my previous post on databases and such. They really helped, and I think that I am on my way!

Now, here is a good question that I am really hoping one of my fellow monks can answer for me. Here is my dillema: I installed PostgreSQL (I know, I'll get flamed by some MySQL pyscho, but let me tell you I tried MySQL first, but it wouldn't install! Equal opprotunity to all, I swear!), but I installed it on a different server than the one my page is hosted on. The reason I opted to do this is two fold: first, I heard that it was possible (even recommended) to have your database and web servers seperate; and second, the server my page is hosted on is an old Cobalt RaQ2 which is pretty slow and running out of memory. The machine I installed PGSQL on is much faster - my reason.

My Dillema: How in the golly-gosh-darn-heck do I connect to server_1 (the PGSQL) server from a CGI script running on server_2 (my sites server) with DBI? Oh please, please, honorable ones, assist me in my quest to find the holy grail of perl knowledge! Ok, maybe not the grail, but atleast this little paper cup painted gold :-).

Thanks a ton to all!
R.Joseph

P.S. If it helps any, I don't believe that the two servers are directly networked, and even though housed in the same complex should be viewed as two entirely seperate servers.

Replies are listed 'Best First'.
Re: Ha! Another database question!
by mdillon (Priest) on Dec 14, 2000 at 07:53 UTC
    this answer comes to you based upon information found in 'perldoc DBD::Pg': 
    #!/usr/bin/perl -w

use DBI;

use strict;

use vars qw($dbh);

BEGIN { $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=server_1",
    $username, $password, { RaiseError => 1 }); }

END { $dbh->disconnect; }

# do DBI stuff

    [download]
[reply]
[d/l]
Re: Ha! Another database question!
by Hrunting (Pilgrim) on Dec 14, 2000 at 09:05 UTC
    mdillon's response is the perl side of it. It's as easy as that. On the postgre side, don't forget to configure a user (mySQL uses a GRANT statement, and I think that's pretty much standard SQL; check your documentation) and secure it by host and password (ie. only allow that user to connect from that host with that password).

    And if you're considering using this database a lot from different scripts, for the love of God, don't put the user and password and database location in each script. Write a module (My::DB or something) that connects to the database for you and returns the handle. There's no sense in having the user/pass in 15 different scripts that you then have to scrounge through when you have to change the information.

    The other benefit is that you can implement connection caching and things like that, although other modules may do this more efficiently for you.

    Probably a bit more information than you may have asked for, but it's good to plan these things out ahead of time before you get too far down the road and have to smack yourself in the forehead.

[reply]
      Thx for the tip about the module. That's something I need to do as well. Haven't written a module before - always nice to do something new :)
[reply]
Re: Ha! Another database question!
by chorg (Monk) on Dec 14, 2000 at 10:40 UTC
    A quick note:

    This applies for sure to MySql, but I'm pretty sure that it's an across the board thing as well. When a database is on the same machine as the process querying it, it uses ultra fast Unix domain sockets to return data. If you put it on another machine then you are reduced to using the slower TCP sockets. If you are using CGI, and not mod_perl, then I'd advise putting the database on the same box as the script, and then using "localhost" to call the database. If you have the DB on a separate box, then you'll have to allow the DB to recieve queries from the machine that you are on, and you'll have to specify the hostname somehow... and that will be slower.
    _______________________________________________
    "Intelligence is a tool used achieve goals, however goals are not always chosen wisely..."

[reply]
Re: Ha! Another database question!
by hotyopa (Scribe) on Dec 14, 2000 at 09:41 UTC

    This might be completely obvious, but if they are on different networks, you may just need a hole in the firewall on port 5432 too.

    Glad to see you've gone with Postgres. I started with it a couple of weeks ago. Seems to be on the up and up, esp. when v7.1 hits the streets.

[reply]

Back to Seekers of Perl Wisdom