Aiven PostgreSQL services are managed from Aiven web console. First login to the console with your email address and password and you will be automatically taken to the "Services" view that shows all the services of the currently selected project.

Projects allow organizing groups of services under different topics and each project can for example have different billing settings. An empty project is created for you automatically when you sign-up and the free credits are attached to this project. You can create new projects by clicking the project name in the left side-bar and selecting "Create a new project". The same menu can also be used to switch between projects.

To get started with Aiven PostgreSQL, first click the "Create a new service" button.

The dialog that opens allows you to specify the main service properties:

  • Service name: A short name for the service used to distinguish it from other services. A random name is provided, but you can type in a more friendly name.
  • Service type: Select "PostgreSQL" and please note that there are more than one PostgreSQL version available (e.g. 9.5 and 9.6). Select the most appropriate version for your needs. If unsure, just go with the latest version.
  • Plan: How many servers and what kind of memory/CPU/disk resources will be allocated to run your service. Note that the single-node "Hobbyist" and "Startup" plans are not recommended for applications where durability of the data is essential. Select one of the "high availability" plans for speedy recovery and best protection against data loss against critical node failures.
  • Cloud: Which cloud and region to run the service on. Note that the pricing of the same service may differ between cloud providers and their regions.

After making the selections, click the "Create" button and you will be taken back to the service list view and the newly created service is shown with an indicator that it is being created.

Click the service name in the list and the "Overview" information page for the service opens. This view shows the connection parameters for your service, its current status and allows making changes to the service.

The "Status" indicator will say "REBUILDING" while the service is being created for you. Once the service is up and running, the light will change to green and it will say "RUNNING". Note that while typically services start in a couple of minutes, the performance between clouds varies and it can take longer under some circumstances.



There are multiple ways you can try out your new PostgreSQL service, but if you have the standard psql PostgreSQL command-line client available, you can simply click on the "Service URL" and copy it to the clipboard and then just launch the psql client in a command shell by pasting the entire service URL as a command-line parameter for it:

$ psql postgres://avnadmin:r03mho73s5oo55g1@pg-3e51160e.demoprj.aivencloud.com:10399/defaultdb?sslmode=require 
 psql (9.5.2, server 9.5.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
 defaultdb=> SELECT user;
 current_user
--------------
 avnadmin
(1 row)

defaultdb=>


You can also use a graphical client like PGAdmin3 to connect. When connecting copy-paste the connection parameters from the overview page on to PGAdmin's "New Server Registration" page. Remember to also change the name of the "maintenance database" to defaultdb  in order to connect to the Aiven PostgreSQL default database.

NOTE: Aiven enforces usage of SSL for PostgreSQL connections, i.e. any client connecting to an Aiven PostgreSQL is required to use SSL. If you have trouble connecting to the database, please make sure your client software is not trying to establish an insecure non-SSL connection.

In order to create more databases, you can go to to the service's "Databases" tab in the Aiven web console and click "Add database". Database users can be added in a similar way under the "Users" tab.

Python example

This Python example uses the psycopg2 library for connecting to Aiven PostgreSQL.

from psycopg2.extras import RealDictCursor
import psycopg2

uri = "postgres://avnadmin:r03mho73s5oo55g1@pg-3e51160e.demoprj.aivencloud.com:10399/defaultdb?sslmode=require"

db_conn = psycopg2.connect(uri)
c = db_conn.cursor(cursor_factory=RealDictCursor)

c.execute("SELECT 1 = 1")
result = c.fetchone()

Node.js example

This example uses the node-postgres library.

var pg = require('pg');

var config = {
    database: "defaultdb",
    host: "pg-3e51160e.demoprj.aivencloud.com",
    password: "r03mho73s5oo55g1",
    port: 10399,
    ssl: "require",
    user: "avnadmin",
};

var client = new pg.Client(config);

client.connect(function (err) {
    if (err)
        throw err;
    client.query('SELECT 1 AS value', [], function (err, result) {
        if (err)
            throw err;

        console.log(result.rows[0]);
        client.end(function (err) {
            if (err)
                throw err;
        });
    });
});

C# example

This C# example uses the Npgsql driver for connecting to Aiven PostgreSQL. 

using Npgsql;
using System;

namespace aiven
{
    class Program
    {
        static void Main(string[] args)
        {
            NpgsqlConnection conn = new NpgsqlConnection("Server=dotnetssl.demoprj.aivencloud.com;User Id=avnadmin;Password=sto68msuj1hwsctt;Database=defaultdb;Port=26663;SSLMode=Require;Trust Server Certificate=true");
            conn.Open();

            // This will return 1333332, can be any query you wish
            NpgsqlCommand command = new NpgsqlCommand("SELECT 666666*2::BIGINT", conn);
            Int64 count = (Int64)command.ExecuteScalar();

            Console.Write("{0}\n", count);
            conn.Close();
        }
    }
}

Java example

This Java example uses the JDBC driver for connecting to Aiven PostgreSQL. You'll need to download the service CA certificate from the Aiven console's service page by clicking the "Show CA certificate" button and pointing the aiven-ca.crt  bit in the connection url to path of the certificate file on disk. Note the sslmode  and sslrootcert  parameters in the URL.

String url = "jdbc:postgresql://pg-3e51160e.demoprj.aivencloud.com:10399/defaultdb?user=avnadmin&password=r03mho73s5oo55g1&sslmode=verify-ca&sslrootcert=aiven-ca.crt";

connection = DriverManager.getConnection(url);
Did this answer your question?