PostgreSQL in the cloud

Like many other systems, PostgreSQL is available in the cloud as a Database as a Service (DBaaS). These services create and manage databases for you, with high availability and backup included. So it’s less work, but not zero work, and you still have responsibilities…which you will see later.

Getting ready

We will select EDB’s BigAnimal as an example of a PostgreSQL cloud service, since EDB has the largest number of contributors to open source PostgreSQL, over the longest period.

EDB’s BigAnimal creates clusters within your own cloud account, allowing you to understand and control the costs you incur when running PostgreSQL. So, the first step is to log in to your host cloud account: https://www.biganimal.com/.

How to do it…

Using EDB’s BigAnimal as a specific example, navigate through these steps:

1. If you don’t have an account, you can sign in using the Free Trial at http://biganimal.com/; click Try for free, sign up, and sign in. This will take you to Step 5 of this sequence. If you do already have an account, then you can start at Step 2.
2. Connect to the cloud portal – for example, Azure. If you have multiple accounts, as we do, then make sure you are connected to the right account. BigAnimal is then available as a marketplace subscription.
3. Go to https://portal.biganimal.com/:

Figure 1.8: The portal welcome screen

4. Manage your cloud limits, if necessary.
5. Select Create New Cluster, and then set Cluster Name and Password:

Figure 1.9: The portal main screen

6. In this example, we will create a cluster called Cluster2. Specify Database Type. Select the software type and version – for example, PostgreSQL 16. Select the cloud provider and distribution across region(s) – for example, Azure and Central India:

Figure 1.10: BigAnimal database type

7. Specify the instance type and key details, all of which will then be provisioned for you:
   • Specify the instance type – for instance, D4s v3:
     • How many CPUs? (such as 4 vCPUs)
     • How much RAM? (such as 16GB RAM)
   • Specify storage:
     • Volume type? (Azure Premium Storage)
     • Provisioned IOPS? (4 Gi, 120 IOPS, 25 MB/s)
   • Specify other aspects:
     • Networking? (Public)
     • High availability? (Yes)
     • HA clusters are configured with a single primary and two replica nodes using streaming physical replication. Clusters are configured across availability zones automatically. synchronous_replication is configured by default.
8. Create the cluster. Wait for the cluster to be built, which will usually be very quick, yet varies according to the options selected in the previous step. Assume it will take 1 hour to avoid sitting and watching it:

Figure 1.11: The BigAnimal progress bar

9. Set up Connection Info for our new Cluster2:

Figure 1.12: EDB’s BigAnimal connection details

Test the connection and then set up the connection details, as discussed in earlier recipes. Assign the new instance a shortcut name, since remembering a node name such as p-r5w2xuuuos.pg.biganimal.io will not be easy!

How it works…

The cloud (or DBaaS) means that PostgreSQL is managed for you, so this is all you need to do.

EDB’s BigAnimal provides a GUI to allow you to create PostgreSQL clusters manually on demand. One of the main themes in this cookbook is using repeatable, scriptable mechanisms where possible, so I recommend that you use either a Command-Line Interface (CLI) or an Application Programming Interface (API). The API uses a RESTful interface to define and manage clusters.

Note that when you run a database service, you still have these and other responsibilities:

• You are responsible for contacting the support team if things are not as you think they should be.
• You are responsible for keeping your passwords to the cluster secure.
• You are responsible for creating users with appropriate access rights to your data.
• You are responsible for choosing whether to enable high availability and for noting the availability level offered by the service.
• You are responsible for data modeling, query performance, and scaling the cluster to meet your performance needs.
• You are responsible for choosing the appropriate resources for your workload, including instance type, storage, and connections. You are also responsible for managing your cloud resource limits to ensure the underlying infrastructure can scale.
• You are responsible for periodically restoring and verifying the restores to ensure that archives are completed frequently and successfully to meet your needs.
• You are responsible for paying!

So, the cloud is a good way forward, but not a way to avoid taking full responsibility for your overall application and database.

There’s more…

Cloud services are also available from these and others:

• Aiven
• Amazon Web Services
• Crunchy
• Google
• Microsoft