Get started with Aiven for PostgreSQL®

Start using Aiven for PostgreSQL® by creating a service, connecting to it, and loading sample data.

Prerequisites

• Access to the Aiven Console
• psql command line tool installed
• Terraform installed if you prefer to get started using code

Create a service

Console

Create an Aiven for PostgreSQL® service in the Aiven Console.

Terraform

1. Create an authentication token.

2. Create the following Terraform files:

   • provider.tf, where you specify the version in the required_providers block

       terraform {
         required_providers {
           aiven = {
             source  = "aiven/aiven"
             version = ">=4.0.0, < 5.0.0"
           }
         }
       }
     
       provider "aiven" {
         api_token = var.aiven_api_token
       }

   • postgresql.tf, where you include the aiven_pg resource

       resource "aiven_pg" "pg" {
         project                = data.aiven_project.my_project.project
         service_name           = "postgresql"
         cloud_name             = "google-europe-west3"
         plan                   = "startup-4"
       }
     
       output "postgresql_service_uri" {
         value     = aiven_pg.postgresql.service_uri
         sensitive = true
       }

   • variables.tf, where you declare the API token and project name variables

     variable "aiven_api_token" {
       description = "Aiven console API token"
       type        = string
     }
     
     variable "project_name" {
       description = "Aiven console project name"
       type        = string
     }

   • terraform.tfvars, where you add the Aiven access token and project name

       aiven_api_token = "AIVEN_AUTHENTICATION_TOKEN"
       project_name    = "AIVEN_PROJECT_NAME"
       admin_username  = "YOUR_SERVICE_USERNAME"
       admin_password  = "YOUR_SERVICE_PASSWORD"

3. Run terraform init > terraform plan > terraform apply --auto-approve.

Configure a service

Edit your service settings if the default service configuration doesn't meet your needs.

Console

1. Select the new service from the list of services on the Services page.
2. On the Overview page, select Service settings from the sidebar.
3. In the Advanced configuration section, make changes to the service configuration.

Terraform

Configure service parameters by updating the aiven_pg resource, for example:

resource "aiven_pg" "pg" {
  project                = data.aiven_project.my_project.project
  service_name           = "postgresql"
  cloud_name             = "google-europe-west3"
  plan                   = "startup-4"
  maintenance_window_dow  = "monday"
  maintenance_window_time = "10:00:00"
  termination_protection = true

  static_ips = toset([
    aiven_static_ip.ips[0].static_ip_address_id,
    aiven_static_ip.ips[1].static_ip_address_id,
    aiven_static_ip.ips[2].static_ip_address_id,
    aiven_static_ip.ips[3].static_ip_address_id,
  ])

  pg_user_config {
    pg_version = 14
    backup_hour               = 01
    backup_minute             = 30
    shared_buffers_percentage = 40
    static_ips = true
    ip_filter_string = ["0.0.0.0/0"]
    admin_username = var.admin_username
    admin_password = var.admin_password

    pgbouncer {
      autodb_max_db_connections = 200
    }

    public_access {
      pg         = true
      prometheus = false
    }

    ## project_to_fork_from  = "source-project-name"
    ## service_to_fork_from  = "source-pg-service"
    ## pg_read_replica       = true

    pg {
      idle_in_transaction_session_timeout = 900
      log_min_duration_statement          = -1
      deadlock_timeout                    = 2000
    }
  }

  timeouts {
    create = "20m"
    update = "15m"
  }
}

See the available configuration options in Advanced parameters for Aiven for PostgreSQL.

Connect to the service

Console

1. Log in to the Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.

2. On the Overview page of your service, click Quick connect.

3. In the Connect window, select a tool or language to connect to your service, follow the connection instructions, and click Done.

   psql 'postgres://ADMIN_PASSWORD@vine-pg-test.a.aivencloud.com:12691/defaultdb?sslmode=require'

Terraform

Access your new service with psql using the postgresql_service_uri output you received after running terraform apply --auto-approve.

psql "$(terraform output -raw postgresql_service_uri)"
psql (13.2)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

defaultdb=>

psql

Connect to your new service with psql CLI tool.

tip

Check more tools for connecting to Aiven for PostgreSQL in Connect to Aiven for PostgreSQL.

Load a test dataset

dellstore2 is a standard store dataset with products, orders, inventory, and customer information.

1. Download the dellstore2-normal-1.0.tar.gz file from the PostgreSQL website and unzip it.

2. From the folder where you unzipped the file, connect to your PostgreSQL instance, create a dellstore database, and connect to it:

   CREATE DATABASE dellstore;
   \c dellstore

3. Populate the database:

   \i dellstore2-normal-1.0.sql

4. Verify what objects have been created:

   \d
   Expected output

    List of relations
    Schema |           Name           |   Type   |  Owner
   --------+--------------------------+----------+----------
    public | categories               | table    | avnadmin
    public | categories_category_seq  | sequence | avnadmin
    public | cust_hist                | table    | avnadmin
    public | customers                | table    | avnadmin
    public | customers_customerid_seq | sequence | avnadmin
    public | inventory                | table    | avnadmin
    public | orderlines               | table    | avnadmin
    public | orders                   | table    | avnadmin
    public | orders_orderid_seq       | sequence | avnadmin
    public | products                 | table    | avnadmin
    public | products_prod_id_seq     | sequence | avnadmin
    public | reorder                  | table    | avnadmin
    (12 rows)

Query data

Read data

Retrieve all the data from a table, for example, from orders:

SELECT * FROM orders;

Expected output

 orderid | orderdate  | customerid | netamount |  tax  | totalamount
---------+------------+------------+-----------+-------+-------------
       1 | 2004-01-27 |       7888 |    313.24 | 25.84 |      339.08
       2 | 2004-01-01 |       4858 |     54.90 |  4.53 |       59.43
       3 | 2004-01-17 |      15399 |    160.10 | 13.21 |      173.31
       4 | 2004-01-28 |      17019 |    106.67 |  8.80 |      115.47
       5 | 2004-01-09 |      14771 |    256.00 | 21.12 |      277.12
       6 | 2004-01-11 |      13734 |    382.59 | 31.56 |      414.15
       7 | 2004-01-05 |      17622 |    256.44 | 21.16 |      277.60
       8 | 2004-01-18 |       8331 |     67.85 |  5.60 |       73.45
       9 | 2004-01-06 |      14902 |     29.82 |  2.46 |       32.28
      10 | 2004-01-18 |      15112 |     20.78 |  1.71 |       22.49
      ...
      (20000 rows)

Write data

Add a row to a table, for example, to customers:

INSERT INTO customers(customerid,firstname,lastname,address1,city,country,region,email,creditcardtype,creditcard,creditcardexpiration,username,password,age,gender)
VALUES(20001,'John','Doe','WEDEBTRTBD','NY','US',11,'john.doe@mailbox.com',3,1879279217775922,2025/11,'user20001','password',44,'M');

Expected output

INSERT 0 1

Check that your new row is there:

SELECT * FROM customers WHERE firstname = 'John';

Expected output

 customerid | firstname | lastname |  address1  | address2 | city | state | zip | country | region |        email         | phone | creditcardtype |    creditcard    | creditcardexpiration | username  |  password  | age | income | gender
------------+-----------+----------+------------+----------+------+-------+-----+---------+--------+----------------------+-------+----------------+------------------+----------------------+-----------+------------+-----+--------+--------
      20001 | John      | Doe      | WEDEBTRTBD |          | NY   |       |     | US      |     11 | john.doe@mailbox.com |       |              3 | 1879279217775922 | 184                  | user20001 | password   |  44 |        | M
(1 row)

Related pages

• Connection pooling and Pgbouncer
• High availability
• Restrict access
• Migrate your PostgreSQL to Aiven
• Aiven Service Level Agreement