bkiahg has asked for the wisdom of the Perl Monks concerning the following question:

Hello Wise Monks,

Linux newbie here. I am trying to query a postgres database on a redhat linux box (fedora core 2) from a windows box (with latest activeperl load - 5.8.7.813). I'm kind of at a loss as to where to go. I've done several cpan searches and some googling and I haven't been able to find some good reading on how to go about this.

I apologize for the lack of versions on the linux and postgres, but I'm not even sure how to look that up.

Any direction would help. And thank you in advance.
  • Comment on Querying Postgres DB on Linux from Windows

Replies are listed 'Best First'.
Re: Querying Postgres DB on Linux from Windows
by davidrw (Prior) on Oct 03, 2005 at 19:39 UTC
    You can use DBI to connect to a remote db -- just specify a dsn of something like dbi:Pg:host=192.168.123.123;port=5432;dbname=foo ... Note that the default post for postgres is 5432, but you must have it in your /usr/local/etc/pg_hba.conf file to allow outside connections. If you don't (or can't) have outside connections, you can get around it easily with a ssh tunnel (though it can be inconvenient).

    You can also install the postgres odbc drivers so that you can connect from your windows box (e.g. using MS Access).

    As for determining versions, uname -a and rpm -qa | grep redhat and rpm -qa | grep postgres and psql --version are good quick-and-dirty places to start...
      You're a wealth of knowledge my friend. These are the results of the above commands.
      [root@localhost root]# uname -a Linux localhost.localdomain 2.6.5-1.358smp #1 SMP Sat May 8 09:25:36 E +DT 2004 i686 i686 i386 GNU/Linux [root@localhost root]# rpm -qa | grep redhat redhat-artwork-0.96-1 redhat-rpm-config-8.0.28-1.1.1 redhat-menus-1.4.1-1 redhat-java-rpm-scripts-1.0.3-2 [root@localhost root]# rpm -qa | grep postgres postgresql-libs-7.4.2-1 postgresql-7.4.2-1 postgresql-server-7.4.2-1 [root@localhost root]# psql --version psql (PostgreSQL) 7.4.2 contains support for command-line editing
      A couple of questions for you, or anyone reading. How do I look up the name of the server? Is it localhost? /usr/local/etc/pg_hba.conf doesn't exist. What do I need to do to make sure I can access it from outside the box?

      If I could vote more than once I would for you. Excellent material. Thank you.
        i forgot this one, too: cat /etc/redhat-release

        name of the server: hostname
        or to get its ip address: /sbin/ifconfig
        note that 'localhost' (and 127.0.0.1) always points to yourself, so you wouldn't use it to access a remote server (unless you have a ssh tunnel)...

        does pg_hba.conf exist anywhere (locate pg_hba.conf)? Refer to the postgres docs on Client Authentication for more information.
Re: Querying Postgres DB on Linux from Windows
by EvanCarroll (Chaplain) on Oct 03, 2005 at 19:42 UTC
    You probably need to learn much more about both then.
    O'reilly also makes books on all of these subjects, the DBI book, and the Postgres book are good starting places. Caveat: The postgres book is out of date, the Postgre doc package can largely replace it. Read up on:
    1. Linux
    2. Postgre SQL
    3. DBI (Perl database framework)
    4. DBD::Pg (Postgrase database driver for DBI)


    Evan Carroll
    www.EvanCarroll.com