Solved

Urgent -- Need help installing PostgreSQL on Win XP Home

Posted on 2004-09-26
24
776 Views
Last Modified: 2012-06-21
If you're going to be online for a good part of the day, I could really use some help walking me through installing PostgeSQL on my Windows XP Home box and configuring Apache, PHP & Perl to work with it. Looking at the PG site, it looks like even though ver 8 is only a Beta it is probably the way to go. I don't need a high performance installation, just one that will let me use and learn PG at my own pace on my home network.

Yesterday I installed Apache 2.o, PHP 5.0 and ActivePerl 5.8 and verified (with the help of some awesome EE people) they were all working and playing together nicely. I'm hoping someone here can take me the next step today.

Thanks in advance!
shadow66
0
Comment
Question by:shadow66
  • 14
  • 7
  • 3
24 Comments
 
LVL 9

Expert Comment

by:rjkimble
ID: 12155703
I should be here for a while. I have PostgreSQL 8.0 installed on my Windows XP Home laptop. The first thing to do is to run the installation program and then test the installation using the version of PgAdmin III that is installed with it.
0
 

Author Comment

by:shadow66
ID: 12156425
Thanks for the quick reply. This was my top priority at the time but somehow we never get to do things in the order we'd like to....

In the install routine for 8.0 I'm asked to select procedural languages to install in the default database.  What does this step determine and does it affect my ability later to access the db via Perl and PHP scripts?

Thanks!
0
 

Author Comment

by:shadow66
ID: 12156545
Wow. PgAdmin III is slick. Looks like the install went well.

I decided to just take the default at the procedural language screen, assuming if it isn't what I need that I'll be able to change it later.

I'm going to start hunting for module info for Perl and PHP to try to get web access to pg working now. Hopefully you'll get this in time to steer me clear of _really bad mistakes_ or better yet to point me to _really good sources_  :)
0
 

Author Comment

by:shadow66
ID: 12156597
Just enabled the Postgres module in PHP and tried this script:

<html>
<body>
<?php

$db = pg_connect("localhost","postgres","6KrsNJHL");

?>
</body>
</html>

Got error:

Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL server: could not connect to server: Can't assign requested address (0x00002741/10049) Is the server running on host "localhost" and accepting TCP/IP connections on port postgres? in C:\Program Files\Apache Group\Apache2\htdocs\PgTest.php on line 5

PHP found the function pg_connect so the module loaded. I verified via Windows that the service is running. Now I need to figure out how to check the TCP/IP configuration.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12156719
notepad postgresql.conf

listen_addresses = '127.0.0.1'

or

listen_addresses = '*'  # not secure !!
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12156738
your call arguments are wrong.

<?php
$dbconn = pg_connect("dbname=mary");
//connect to a database named "mary"

$dbconn2 = pg_connect("host=localhost port=5432 dbname=mary");
// connect to a database named "mary" on "localhost" at port "5432"

$dbconn3 = pg_connect("host=sheep port=5432 dbname=mary user=lamb password=foo");
//connect to a database named "mary" on the host "sheep" with a username and password

$conn_string = "host=sheep port=5432 dbname=test user=lamb password=bar";
$dbconn4 = pg_connect($conn_string);
//connect to a database named "test" on the host "sheep" with a username and password
?>
0
 

Author Comment

by:shadow66
ID: 12156908
Earthman2 -- you're awesome.

> listen_addresses = '*'  # not secure !!

Should be ok -- I'm just doing this on my home network behind a router so my machines aren't exposed to the outside world.

>  your call arguments are wrong.

Ya, they sure were. I was using MySql syntax (which I am trying to learn simultaneously).

How do I get this to work with Perl now?
0
 

Author Comment

by:shadow66
ID: 12157127
I just used ppm to install the DBI module and the DBD-mysql driver, but I can't find a DBD driver for postgresql other than DBD-PgPP (Pure Perl PostgreSQL driver for the DBI). What is "Pure Perl" and should I install this one?

0
 
LVL 9

Expert Comment

by:rjkimble
ID: 12158132
I'm glad earthman2 was around to pick up the slack. I threw out my back, and I haven't been able to sit for any extended period.

Pure Perl probably means that the driver is written completely in Perl (i.e., no dependence on "foreign" libraries written in C or assembly language). That should be a good one to try. I don't know anything about Perl or PHP, so I'm of no particular use on either of those technologies.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12158341
can you run

perl -v

Question is probably how to make apache and Perl work together see apache docs and mod_perl docs
http://perl.apache.org
0
 

Author Comment

by:shadow66
ID: 12159847
Thanks to everyone for sticking with me...

> perl -v
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

I've now installed DBD::PgPP. For starters I'd just like to get this script to work from a shell, independent of Apache:
------------------------
#!c:/Perl/bin/Perl.exe
use DBI;
my ($driver, $database,  $user, $password);
my ($dsn, $dbh);
$driver = "PgPP";
$db = "Test";
$dsn = "DBI:$driver:$db";
# also tried this format: "DBI:$driver:dbname=$db";
$hostname = "localhost";
$user = "postgres";
$password = "abcd";
$dbh = DBI->connect ($dsn, $user, $password) or
    die "Cannot connect to $dsn: $DBI::errstr";
$dbh->disconnect();
----------------------
I currently get this error when I execute this script from a shell:

dbih_getcom given an undefined handle (perhaps returned from a previous call whi
ch failed) at C:/Perl/site/lib/DBI.pm line 601.
DBI:PgPP:dbname=Test

In case it would be helpful to know, the following 2 scripts both work on my box now.
----------------------------
#Access PostgreSQL through PHP
<html>
<body>
<?php
$dbh = pg_connect("dbname=Test user=postgres password=abcd") or die ("Could not connect to database");
pg_close($dbh);
?>
</body>
</html>
----------------------------------
#Access MySql through Perl
#!c:/Perl/bin/Perl.exe

use DBI;
my ($driver, $database,  $user, $password);
my ($dsn, $dbh);
$driver = "mysql";
$db = "Test";
$dsn = "DBI:$driver:$database";
$hostname = "localhost";
$user = "MyUser";
$password = "abcd";
$dbh = DBI->connect ($dsn, $user, $password) or
    die "Cannot connect to $dsn: $DBI::errstr";
$dbh->disconnect();
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12160557
#! c:/Perl/bin/Perl.exe
# see http://search.cpan.org/~arc/DBD-PgPP/PgPP.pm
use DBI;
my ($user, $password);
$user = "postgres";
$password = "abcd";
  my $dbh = DBI->connect("dbi:PgPP:dbname=Test;host=localhost;port=5432",  $user, $password )
                  or die "Cannot connect to Test: $DBI::errstr";
$dbh->disconnect();


0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 22

Expert Comment

by:earth man2
ID: 12161464
DBD::PgPP is a Pure Perl client interface for the PostgreSQL database. This module implements network protocol between server and client of PostgreSQL, thus you don't need external PostgreSQL client library like libpq for this module to work. It means this module enables you to connect to PostgreSQL server from some operating systems which PostgreSQL is not ported. How nifty!
0
 

Author Comment

by:shadow66
ID: 12161865
Once again, earhtman2, you rock! I guess I need to learn to experiment more with the various forms of connect. I don't understand why sometimes you have to use the form that specifies the port, etc.

The last part of this request is the Apache part. As a total guess, I tried to navigate to:
  http://localhost/cgi-bin/perltest2.pl

which is the file I just successfully executed via a shell to used to verify Perl->PostgreSQL access. However, I got the good old error:
  Internal Server Error
  The server encountered an internal error or
  misconfiguration and was unable to complete your request.

So, assuming I can't just execute a perl script by navigating to it via a web browser, could you give me a bare bones web-access script using Perl that I can try to open via my browser? I presume it would be something similar to the php script I have (above, that is currently working), maybe like:

#Access PostgreSQL through Perl
<html>
<body>
Invoke pgtest2.pl
</body>
</html>

Thanks!
0
 
LVL 9

Accepted Solution

by:
rjkimble earned 50 total points
ID: 12162060
One way to access a Perl script is via CGI. Just place a script in cgi-bin directory. The script gets all its input from stdin and writes all its output to stdout. Obviously, it should write the entire HTML page. All the request parameters and other useful CGI variables are placed in the environment. I'm certain that Perl has libraries that make working with CGI fairly painless.

There's also mod_perl. I know nothing about that, so you'll have to look to somebody else to help you there.
0
 

Author Comment

by:shadow66
ID: 12162258
Well, the script I tried above was in the cgi-bin directory. It doesn't take any input or generate output, it just connects to a database. It's the script earthman2 gave me to validate Perl->PostgreSQL access:

#! c:/Perl/bin/Perl.exe
# Works when executed from a shell
use DBI;
my ($user, $password);
$user = "postgres";
$password = "abcd";
  my $dbh = DBI->connect("dbi:PgPP:dbname=Test;host=localhost;port=5432",  $user, $password )
                  or die "Cannot connect to Test: $DBI::errstr";
$dbh->disconnect();

So I was hoping that if I navigated to this file via my browser, Perl would execute the script and I'd just end up with a blank page, and this would validate my configuration. But I got an Apache error instead.
0
 
LVL 22

Assisted Solution

by:earth man2
earth man2 earned 450 total points
ID: 12162676
you have to be able to execute the following file ApacheDBI.pl using C:\PERL\bin\perl.exe

Apache httpd.conf must define ScriptAlias like the following
    #
    # ScriptAlias: This controls which directories contain server scripts.
    # ScriptAliases are essentially the same as Aliases, except that
    # documents in the realname directory are treated as applications and
    # run by the server when requested rather than as documents sent to the client.
    # The same rules about trailing "/" apply to ScriptAlias directives as to
    # Alias.
    #
    ScriptAlias /cgi-bin/ "/var/www/cgi-bin/"

in your case replace the unix path "/var/www/cgi-bin/"  with the correct windows directory where you stuff your cgi executables.

open http://localhost/ApacheDBI.pl in browser

#! C:\PERL\bin\perl.exe

# $Id: ApacheDBI.pl,v 1.1.1.1 2002/10/15 19:20:19 bmomjian Exp $

# don't forget to create in postgres the user who is running
# the httpd, eg 'createuser nobody' !
#
# demo script, tested with:
#  - PostgreSQL-7.1.1
#  - apache_1.3.12
#  - mod_perl-1.23
#  - perl5.6.0
#  - DBI-1.14

use CGI;
use DBI;
use strict;

my $query = new CGI;

print  $query->header,
       $query->start_html(-title=>'A Simple Example'),
       $query->startform,
       "<CENTER><H3>Testing Module DBI</H3></CENTER>",
       "<P><CENTER><TABLE CELLPADDING=4 CELLSPACING=2 BORDER=1>",
       "<TR><TD>Enter the data source: </TD>",
           "<TD>", $query->textfield(-name=>'data_source', -size=>40, -default=>'dbi:Pg:dbname=template1'), "</TD>",
       "</TR>",
       "<TR><TD>Enter the user name: </TD>",
           "<TD>", $query->textfield(-name=>'username'), "</TD>",
       "</TR>",
       "<TR><TD>Enter the password: </TD>",
           "<TD>", $query->textfield(-name=>'auth'), "</TD>",
       "</TR>",
       "<TR><TD>Enter the select command: </TD>",
           "<TD>", $query->textfield(-name=>'cmd', -size=>40), "</TD>",
       "</TR>",
       "</TABLE></CENTER><P>",
       "<CENTER>", $query->submit(-value=>'Submit'), "</CENTER>",
       $query->endform;

if ($query->param) {

    my $data_source = $query->param('data_source');
    my $username    = $query->param('username');
    my $auth        = $query->param('auth');
    my $cmd         = $query->param('cmd');
    my $dbh         = DBI->connect($data_source, $username, $auth);
    if ($dbh) {
        my $sth = $dbh->prepare($cmd);
        my $ret = $sth->execute;
        if ($ret) {
            my($i, $ary_ref);
            print "<P><CENTER><TABLE CELLPADDING=4 CELLSPACING=2 BORDER=1>\n";
            while ($ary_ref = $sth->fetchrow_arrayref) {
                print "<TR><TD>", join("</TD><TD>", @$ary_ref), "</TD></TR>\n";
            }
            print "</TABLE></CENTER><P>\n";
            $sth->finish;
        } else {
            print "<CENTER><H2>", $DBI::errstr, "</H2></CENTER>\n";
        }
        $dbh->disconnect;
    } else {
        print "<CENTER><H2>", $DBI::errstr, "</H2></CENTER>\n";
    }
}

print $query->end_html;


====

there should be some scripts already in the cgi-bin directory ie printenv  and test-cgi.  Make sure you can open those in a browser first.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12162973
windoze needs to know to open .pl files with perl.exe.  So you need to create a file association in da registry.
0
 

Author Comment

by:shadow66
ID: 12163161
Okay, I already had:
  ScriptAlias /cgi-bin/ "C:\Program Files\Apache Group\Apache2\cgi-bin"

in my httpd.conf file and I verified when I navigate to:
  http://localhost/cgi-bin/printenv.pl

that the environment data gets printed properly. So Apache knows how to invoke Perl.

I copied your code into ../cgi-bin/apachedbi.pl and navigated to it. Cool form appeared and I entered:
dbi:PgPP:dbname=Test  #modified :Pg: to :PgPP:
postgres                        # superuser created during install
abcd                              # superuser's password
SELECT * FROM MyData

Using PgAdminIII, I verified this query prints 2 rows. But via the browser, seleting submit seems to generate some kind of activity but doesn't display anthing. What should it do (at least there were no errors!)
0
 

Author Comment

by:shadow66
ID: 12163175
>  windoze needs to know to open .pl files with perl.exe.

Already there -- ActivePerl install must have done this.
0
 

Author Comment

by:shadow66
ID: 12163273
Playing around with the apachedbi.pl form, i find that even if i enter a bogus username or password, i still get the same result: activity but no printed statements.
0
 

Author Comment

by:shadow66
ID: 12163322
Using debug print statement:

...
print "1\n";
if ($query->param) {
print "2\n";

    my $data_source = $query->param('data_source');
    my $username    = $query->param('username');
    my $auth        = $query->param('auth');
    my $cmd         = $query->param('cmd');
    my $dbh         = DBI->connect($data_source, $username, $auth);
    if ($dbh) {            
print "3\n";
...

I've found that the connect statement is not returning a valid handle (output: 1 2 but not 3)
0
 

Author Comment

by:shadow66
ID: 12163418
In fact the script is bailing out at the connect statement:
...
print "1 ";
if ($query->param) {
print "2 ";

    my $data_source = $query->param('data_source');
    my $username    = $query->param('username');
    my $auth        = $query->param('auth');
    my $cmd         = $query->param('cmd');
print "$data_source ";
print "$username ";
print "$auth ";
    my $dbh         = DBI->connect($data_source, $username, $auth);
print "3 ";
    if ($dbh) {            
print "4 ";
...

prints: 1 2 dbi:PgPP:dbname=Test postgres abcd
0
 

Author Comment

by:shadow66
ID: 12163460
DUH! My bad; I didn't modify the data source to include the host and port like told me to earlier. Worked! My rows were printed out just fine. Guess this validates that I can access a postgresql database using Perl embedded in html.

Thanks both of you for sticking with me! 450  to earthman and 50 to rjkimble.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now