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
- Terraform
-
Create the following Terraform files:
-
provider.tf
, where you specify the version in therequired_providers
blockterraform {
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 theaiven_pg
resourceresource "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 variablesvariable "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 nameaiven_api_token = "AIVEN_AUTHENTICATION_TOKEN"
project_name = "AIVEN_PROJECT_NAME"
admin_username = "YOUR_SERVICE_USERNAME"
admin_password = "YOUR_SERVICE_PASSWORD"
-
-
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
- Terraform
- Select the new service from the list of services on the Services page.
- On the Overview page, select Service settings from the sidebar.
- In the Advanced configuration section, make changes to the service configuration.
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
- Terraform
- psql
-
Log in to the Aiven Console, and go to your organization > project > Aiven for PostgreSQL service.
-
On the Overview page of your service, click Quick connect.
-
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'
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=>
Connect to your new service with psql CLI tool.
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.
-
Download the
dellstore2-normal-1.0.tar.gz
file from the PostgreSQL website and unzip it. -
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 -
Populate the database:
\i dellstore2-normal-1.0.sql
-
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)