Connecting to PostgreSQL using Perl

PostgreSQL is available on Managed VPS only

Ruachost shared hosting supports MySQL (MariaDB) databases, not PostgreSQL. To use PostgreSQL, choose a Managed VPS.

Perl provides robust database connectivity through the DBI (Database Interface) module and the DBD::Pg driver for PostgreSQL. On ruachost.com, you can use these modules to connect to PostgreSQL databases, run queries, and manage data directly from your Perl scripts.

Why Use Perl for PostgreSQL?

• Flexible scripting language for automation and backend tasks.
• DBI provides a consistent interface across multiple databases.
• Supports prepared statements for secure queries.
• Ideal for legacy applications and quick data processing scripts.

Steps to Connect to PostgreSQL Using Perl

Step 1: Install Required Modules

1. Log in to your hosting account via SSH.

2. Install the DBI and DBD::Pg modules (if not already available):

   cpan DBI
   cpan DBD::Pg

   Or, if using cpanm:

   cpanm DBI DBD::Pg

Step 2: Write a Perl Script to Connect

Example

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

# Database connection parameters
my $dbname   = "yourdbname";
my $host     = "localhost";
my $port     = 5432;
my $username = "yourusername";
my $password = "yourpassword";

# Connect to PostgreSQL
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port",
                       $username, $password,
                       { RaiseError => 1, AutoCommit => 1 })
          or die $DBI::errstr;

print "Connected to PostgreSQL successfully!\n";

# Run a query
my $sth = $dbh->prepare("SELECT fname, lname FROM employee");
$sth->execute();

while (my @row = $sth->fetchrow_array) {
    print "Employee: $row[0] $row[1]\n";
}

# Clean up
$sth->finish();
$dbh->disconnect();

Step 3: Run the Script

• Save the script as connect_pg.pl.

• Make it executable:

  chmod +x connect_pg.pl

• Run the script:

  ./connect_pg.pl

Important Notes

• Replace yourdbname, yourusername, and yourpassword with actual database credentials.
• Ensure PostgreSQL is running and accessible from your hosting environment.
• Always close connections ($dbh->disconnect) to free resources.
• Use prepared statements to protect against SQL injection.