shortcutaub.ie/pgsql-instructions

These instructions explain how to connect to a PostgreSQL database after requesting one through aub.ie/dbcreate for your coursework. For production databases, please contact dbadmin@auburn.edu or submit a request via aub.ie/prod-db. 

Content

- Connect via a GUI Client

- Connect via SSH

- Connect via a PHP script

Connect via a GUI Client

There are many GUI Clients available for PostgreSQL such as DBeaver, pgAdmin, etc. 

DBeaver is widely considered as one of the best database GUI clients. It supports multi database types including PostgreSQL and is a fully cross-platform application that runs on windows, macOS, and Linux. 

Follow below instruction to install DBeaver and setup the connection to your PostgreSQL database. 

1. Download DBeaver from DBeaver Community. Download | DBeaver Community 

2. Install it on your computer. 

3. Connect to AU VPN on your computer. 

3. Create a new connection and enter the information as below. 

4. Click "Test connection" button. 

4.1 Click "Download" button for pgJDBC driver. 

4.2 Confirm the test connection successful. 

5. Click "Ok" button to finish the connection setup. 

6. Right-click the connection and select "connect" menu. 

7. Confirm your database shows under "Databases" folder. 

8. Right-click your database and select "SQL Editor" > "New SQL script". 

Connect via SSH

This section shows how to connect to your PostgreSQL database through mallard.auburn.edu.  

1. Connect to AU VPN. 

2. Open an SSH session or application (SecureCRT is recommended; installation link found here)

3. Connect to Mallard with the following settings:

3.1 Connection type: SSH2

3.2 Hostname: mallard.auburn.edu

3.3 Port: 22

3.4 Username: your Auburn Univ. username

3.5 Authentication: Password

3.6 Key exchange: diffie-hellman, diffie-hellman-group

3.7 When you attempt to connect, you will be prompted to enter your Auburn Univ. password

4. Once connected, at the prompt, enter:

psql -h pgsqllab.auburn.edu -U yourdbusername -d yourdbname

5. Enter your password to connect your database. 

6. Confirm your Database name as prompted.  

Connect via a PHP Script

This section shows how to connect your database through a PHP connection. 

If you need more information about getting to the files in the web server, please refer to Instructions on how to connect to the mallard webserver using SFTP with WinSCP, Filezilla, and CyberDuck

1. pgsql extension

<?php

$host = 'pgsqllab.auburn.edu';
$user = 'yourDBusername';
$password = 'yourPassword';
$dbname= 'yourDBname';
$port = 5432;

// create connection string
$connection_string = "host=$host port=$port dbname=$dbname user=$user password=$password";

// establish connection
$dbconn = pg_connect($connection_string);

if($dbconn) {
     echo "Connected successfully!";
} else {
         echo "Error in connection: " . pg_last_error();
}

// close the connection when done
pg_close($dbconn);

?>

2. PDO extension

<?php

$host = 'pgsqllab.auburn.edu';
$user = 'yourDBusername';
$password = 'yourPassword';
$dbname = 'yourDBname';
$port = 5432;

// Create Data Source Name (DSN) string
$dsn = "pgsql:host=$host;port=$port;dbname=$dbname";

// Set options for strict error reporting and associative arrays
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    // Establish connection
    $pdo = new PDO($dsn, $user, $password, $options);
    
    echo "Connected successfully!";
    
} catch (\PDOException $e) {
    // Catch connection errors safely
    echo "Error in connection: " . $e->getMessage();
}

// Close the connection when done by destroying the object
$pdo = null;

?>