Connect to Aiven for PostgreSQL® with PHP

This example connects to PostgreSQL® service from PHP, making use of the built-in PDO module.

Variables

These are the placeholders you will need to replace in the code sample:

Variable	Description
POSTGRESQL_URI	URL for PostgreSQL connection, from the service overview page

Prerequisites

For this example you will need:

• Download CA certificates from the service overview page, this example assumes it is in a local file called ca.pem.

note

Your PHP installation will need to include the PostgreSQL functions (most installations will have this already).

Code

Add the following to index.php and replace the placeholder with the PostgreSQL URI:

<?php

$uri = "POSTGRESQL_URI";

$fields = parse_url($uri);

// build the DSN including SSL settings
$conn = "pgsql:";
$conn .= "host=" . $fields["host"];
$conn .= ";port=" . $fields["port"];;
$conn .= ";dbname=defaultdb";
$conn .= ";sslmode=verify-ca;sslrootcert=ca.pem";

$db = new PDO($conn, $fields["user"], $fields["pass"]);

foreach ($db->query("SELECT VERSION()") as $row) {
    print($row[0]);
}

This code creates a PostgreSQL client and opens a connection to the database. Then runs a query checking the database version and prints the response

note

This example replaces the query string parameter to specify sslmode=verify-ca to make sure that the SSL certificate is verified, and adds the location of the cert.

To run the code:

php index.php

If the script runs successfully, the outputs should be the PostgreSQL version running in your service like:

PostgreSQL PG_VERSION_NUMBER on x86_64-pc-linux-gnu, compiled by gcc, a 68c5366192 p 6520304dc1, 64-bit