Skip to main content

Get started with Aiven for PostgreSQL®

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

Prerequisites​

Create a service​

  1. In your project, click Services.

  2. Click Create service.

  3. Select PostgreSQL®.

  4. Select a Service tier.

  5. Select a Cloud.

    note

    You cannot choose a cloud provider or a specific cloud region on the Free tier.

  6. Select a Plan.

    note

    The plans available can vary between cloud providers and regions for the same service.

  7. In the Service details, enter a name for your service.

  8. Optional: Add service tags.

  9. In the Service summary, click Create service.

The status of the service is Rebuilding during its creation. When the status is Running, you can start using the service. This typically takes a couple of minutes and can vary between cloud providers and regions.

Configure a service​

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

  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.

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

Connect to the service​

  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'
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