Aiven for PostgreSQL^® tutorial

UPDATE: You'll find instructions for getting started with Aiven for PostgreSQL in the Aiven Developer Portal

Here's a short overview of the Aiven for PostgreSQL's web console. The console is where the magic happens and you can launch your fully managed databases in seconds!

Developers: We also provide a full HTTP REST API and a powerful command-line client.

<a name="more"></a>

Sign-up

Signing up to Aiven is simple and only requires your name, email address and a choice of password. Account is activated by clicking an activation link sent via email immediately after submitting the signup form.

If you already have an account, you can login directly.

Creating your first database

After logging in, you will see an overview of the services you have created. The current billing estimate is shown in the top right corner.

Services can be temporarily powered off (shown with a grey icon above) when not needed and fully restored to the exact same state at a later time. Services that are powered off do not generate charges.

When you first login, the services list will be empty and a new database can be created by clicking the Create a new service button.

You will be prompted with a name for the service, the service plan and the destination cloud where to host the service.

The plan defines how much hardware resources your database instances will have, length of backup history, high-availability enabled or not, among a few other things. The full plan details are available on the aiven.io website.

The destination cloud setting defines the initial location where the service is hosted at. Note that this selection is easily revisited later by migrating the entire service from one destination to another.

Since this is the first service that you are creating, you need to enter billing information. Note that Aiven does not store your credit card information directly. The credit card details are processed by Stripe and stored safely in their system. Stripe is one of the biggest credit card payment service providers. New services created in the same project will automatically use the same payment information from this point on.

Once the payment details have been entered, the service will automatically launch and will be running in a moment. Clicking on the name of the service in the service list will show you further service details:

This view shows you the connection parameters that can be copy-pasted to your application. A general service status is shown below the connection parameters. The above screenshot shows the service as RUNNING, meaning everything is OK. The status will show REBUILDING when the service is being launched, migrated to another location or being upgraded.

High-availability

When a database node malfunctions for any reason, it is replaced automatically with a new one. For example, if a the cloud provider's hardware fails, Aiven immediately detects the failure and starts building a replacement node. The new node is restored from backups to the same state where it was before the failure and gets connected to the other nodes.

When using a service plan with high-availability enabled, in case of a standby-node failure, you will not notice anything special, just the service status will change to REBUILDING for a short while. If the failed node was the active database master, there will be a short moment when the database is not accessible as master is confirmed non-functional and the already running standby-node is promoted as the new master. After the promotion a new standby node is created automatically and the situation will return back to normal.

Online upgrades and migration between clouds

Below the status you'll find a compact collection of actions you can perform for the service.

One of the more exciting features is our automatic online migration between different locations and cloud providers. A running service can be migrated from cloud to cloud simply by clicking the Migrate button and choosing a new home for the service. Our list of supported hosting locations is pretty extensive, as can bee seen below (with a sneak peek of the upcoming DigitalOcean regions!).

Selecting a new home for your service will automatically move the service safely one node at a time to the new location. The service will remain available during the whole transition period. The service status will report REBUILDING during the time the data is being transferred. Please note that the IP address to which the service's DNS address points to will change when switching between cloud providers.

Upgrading the plan is as easy as the migration. Just click Upgrade, select a new plan and the data will automatically be migrated to new beefier nodes.

SQL statistics

The SQL activity in your database can be viewed in a couple of different ways. First is the Query Statistics tab that shows how many times each kind of query has been executed, how long they took, etc. The data can be sorted by clicking the column headings: first click will choose the sorting criteria and the second click will reverse the sorting order.

You can reset the statistics and start collecting from an empty state by clicking the Reset stats button. This can help you for example asses impact of a added or changed index to query performance.

The currently running queries are visible under the Current Queries tab. Note that it is common that SQL queries do not take more than a millisecond or two to execute, so this view can often be empty although there is a lot of activity in the database.

Automatic real-time backups

Finally, there is the Backups tab that shows the list of periodic full backups taken from the database. In addition to full backups, Aiven constantly stores an incremental stream of the latest transactions (PostgreSQL write-ahead-log, WAL) along with the backups. This allows restoring the the database not just to the time of the latest full backup, but also to any point in time between backups. 

The backups are stored in the corresponding cloud provider's "object storage" (e.g. Amazon S3) encrypted. These are highly redundant and reliable services for long-term data storage.

Aiven keeps your data encrypted all the time. The data disk containing the database is encrypted. The full and incremental backups are encrypted before they are stored to the object storage. Also the real-time replication stream between the database master and the standby node(s) is encrypted. Your client connections to the database are always required to be encrypted.

You will not need to worry about the backups. Everything is automated.

Restricting access to the database

Once the service is running, it is a good idea to restrict the network addresses that can be used to connect to the database, to provide an extra layer of security. Click the Change button beside Allowed IP addresses to enter a list of network addresses or sub-network ranges. The changes will take effect immediately after saving the changes.

Billing and project members

A quick word about our billing model: Each service is billed based on the number of hours it has been running. An invoice is generated monthly and automatically billed from the credit card assigned to the project. Staying on track about the billing is simple as there is a billing estimate that is shown in the main service view and is updated hourly.

Each project can have multiple members, just click the Invite user in the project members view and enter the email address of the new member. Please note that every project member has privileges to create and terminate services and those services will be charged from the credit card attached to the project.

To get your database started, just head to aiven.io!

Further reading

• Future PostgreSQL: Improvement to the Replication Protocol

• Use cases for PostgreSQL®

• TypeScript, react-hook-form, zod: blissful forms