Deploying a High-Availability PostgreSQL Database on UpCloud

Introduction

PostgreSQL is an open-source relational database management system that is compliant with SQL standards and ACID properties. In addition to its relational data model, PostgreSQL also supports the object-oriented database model, which enables working with complex and user-defined data types and structures for storing and querying data. This hybrid object-relational model makes it suitable for a wide range of use cases and aligns it with a variety of application requirements without sacrificing performance or reliability.

In a typical production environment, PostgreSQL is configured in a highly-available setup that comprises multiple instances or database servers that work together as a cluster. This setup improves system availability by removing the single point of failure associated with a single-instance setup, and can also boost performance by distributing the traffic of database queries among the cluster instances.

However, setting up a high-availability PostgreSQL cluster can be a complex task that involves a lot of manual work and requires deep expertise with PostgreSQL and clustering solutions.

To overcome these complexities, UpCloud offers a managed PostgreSQL database solution that offloads the underlying details of provisioning the database instance and allows for seamless scaling and cluster setup. By using UpCloud’s managed PostgreSQL service, you don’t have to worry about setting up the database cluster yourself, you can just specify the number of instances you want for your cluster and let UpCloud handle the heavy lifting for you!

In this guide, we’ll cover the steps for creating a highly-available PostgreSQL cluster using UpCloud managed service. We’ll start by provisioning a PostgreSQL instance, connecting to it, and creating some test data. Then we’ll scale our setup for high-availability with multiple instances. Finally, we’ll test a failover scenario when a database instance in the cluster goes down.

Prerequisites

To follow along with this guide, you’ll need to:

• Create an UpCloud account: This gives you access to different UpCloud services using the GUI control panel, command-line interface, or the API.
• Deploy a Cloud server on UpCloud: We’ll use this server to connect to the database using the postgresql client.
• Familiarity with using the CLI: We’ll be using some basic commands throughout this guide to install our tools, navigate project directories, and work with SSH.

Creating a PostgreSQL Instance

Let’s begin by creating the first PostgreSQL instance. As this is a managed service from UpCloud, we don’t have to install the database ourselves, we can simply provision it through UpCloud control panel GUI with the following steps:

1. From the UpCloud dashboard, select Databases from the left and then select Create new Database.

2. Select a location where you want to deploy the database, then choose the database type as PostgreSQL. You can choose a specific version by expanding the Change version option, here I’ll use the latest provided version of 17.5.

3. Under the Plan, you can choose the number of instances you want and the resources for each instance. Here we’ll just start with a single instance, which is suitable for testing purposes as it’s not a highly available setup.

4. Specify whether you want to allow public access through the internet or only private access. Private access can be through the Utility network or an SDN network if you already have one available in the same location.

Databases are usually allowed private access only, while the applications that use the database are the ones exposed publicly

5. In the Allowed IP access, you can specify which source IP addresses can access this database. For our scenario, we’re going to use a cloud server within the Utility network, so I’m going to leave the default Utility network access only.

6. Specify a maintenance schedule for the database. Since this is a managed service, patches and updates are automatically applied when required, so you don’t have to download or install them on your own. But because these automated updates can cause disruption to the service, you control the time window when you want these updates to take place using this maintenance schedule.

7. The Termination protection option is a preventative measure that protects the database from accidental deletion or shutdown.

8. Provide a Hostname prefix and click on Create Database.

9. Wait until the status changes to Started; this means that the database is ready to be used.

Connecting to The Database

Now that our database is running, the next step is to test connectivity to it. We’ll be using an Ubuntu cloud server for the connection, and it must use the Utility network because we only allow private database access from the Utility network. 

From inside the cloud server, use the following steps to test the database connection:

1. Check if the psql client is already installed.

psql --version
Command 'psql' not found

The above output means that psql is not installed, however, if the output shows the version correctly it means that psql is installed and you can skip to step 5 here.

2. Update the package index.

sudo apt update

3. Install the postgres client, this is a command-line utility that we’ll use to connect to the database.

sudo apt install -y postgresql-client

4. Verify the installation.

psql --version
psql (PostgreSQL) 16.8 (Ubuntu 16.8-0ubuntu0.24.04.1)

We can notice here that the psql client version is slightly older than our PostgreSQL database version, this should be fine and satisfies the compatibility requirements for most use cases as mentioned in the psql documentation.

5. Select the Connect option on the top right of the database main page. This will provide the instructions for connecting to the database using different tools including our postgres client (PSQL).

Note that it’s strongly not recommended to provide these credentials in the psql command directly as plain text. In a production setup, you can store these credentials in a separate configuration file for the client that is properly secured with access permissions.

6. Create a .pgpass file under the user home directory. The .pgpass file is a secure way to store and use PostgreSQL credentials.

touch ~/.pgpass

7. Open the file and add the database information in the following format:

{HOST}:{PORT}:{DB_NAME}:{DB_USER}:{DB_PASSWORD}

8. Adjust the permissions for the .pgpass file access.

chmod 0600 ~/.pgpass

9. Use the provided psql command to test connectivity to the database without providing the password, it will automatically retrieve it from the .pgpass file.

The defaultdb is a default database created automatically when you provision the database instance. You can create a new database using the psql client or from the Database page in the UpCloud control panel.

Now that we’ve verified the connection to the database, the next step is to try writing some data. Instead of using the psql client, we’ll demonstrate this by connecting to the database using n8n, which is a popular workflow automation tool that supports using external PostgreSQL databases. So let’s first install n8n on our cloud server with the following steps:

1. Set the required environment variables for the database connection. n8n will use these environment variables to connect to the database.

DB_TYPE = postgresdb

n8n can use sqlite or PostgreSQL, this specifies that we need to use PostgreSQL

DB_POSTGRESDB_DATABASE = n8n

This is the database name that n8n will use, we’ll need to create this in our PostgreSQL instance.

DB_POSTGRESDB_HOST = <Host name of the database instance>

DB_POSTGRESDB_PORT = <db port number>

The database host name and port number, we can retrieve this from the database connection information in the UpCloud control panel.

DB_POSTGRESDB_USER = <database username>

DB_POSTGRESDB_PASSWORD = <password>

The database username and password, we can get this information also from the UpCloud control panel under the database Auth section.

DB_POSTGRESDB_SSL_ENABLED = true

Since our database requires SSL connections, we need to set this to true.

2. Create the new database for n8n. Here I’ll simply use the UpCloud control panel.

3. n8n requires Node.js version 18 or higher, the easiest way to install it is through nvm with the following commands

curl -o- https://raw.githubusercontent.com/nvm-sh/nvm/v0.40.3/install.sh | bash

\. "$HOME/.nvm/nvm.sh"

nvm install 22

4. Start n8n directly using npx.

npx n8n

5. You should be able to access n8n using the cloud server IP and port 5678.

6. Create a test workflow. This can be anything, we’ll only use it to test writing data to the database.

Simulating a Database Failure

Now let’s simulate a database failure by manually shutting down our database instance, we’ll then try to access our n8n server again and see what happens:

1. Select the Shutdown option from the database main page.

2. After the database is stopped, try to access the n8n server again.

As we can see, we’re getting an error of “Database is not ready!”, simply because we only had one instance of the database the n8n service became unavailable when the instance is down.

3. We can also see the status of each specific instance under the nodes section. This can be helpful if we have a cluster of instances as we’ll see later.

4. Now let’s restore the service by starting our database instance again. You’ll notice that the database instance will transition first to the standby state and then to the master state. In a cluster setup, if a master is already running and serving the database writes, the other instances should remain in the standby state.

5. You should be able to access the n8n server again and see the previously created workflow.

Configure High-Availability for PostgreSQL

To avoid a single-instance failure from disrupting the whole service, we need to set up a high-availability cluster for our PostgreSQL database. One benefit of using a managed service is that it removes the complexities of such configuration. So we can simply create our cluster with the following steps:

1. Under the settings tab, increase the number of nodes for the database. You might need to select a new plan as part of this operation.

2. Save your changes and wait for the rebalancing process and data sync to finish.

3. You should be able to see two instances with master and standby status.

That’s it, you now have a high-availability PostgreSQL cluster with these simple steps!

It’s important to note that the hostname used for accessing the database doesn’t change, so you don’t have to modify any configuration for the clients connecting to the database. During a connection, this hostname will resolve to one of the database instances IP addresses, and if any failure occurs it will seamlessly switch over to the other IP address without the need for any manual configuration changes.

However, there’s a dedicated hostname created for serving Read requests from the standby/replica instances. You can use this dedicated hostname to reduce the load on the master instance and optimize your database performance.

Another benefit of using a high-availability cluster is during the updates or maintenance. Instead of interrupting the database service for applying the required updates, they can now be rolled out first on the replica instance without impacting the master node, and once the update is finished on the replica it can be applied to the master after that, while promoting the replica temporarily to a master role to serve the traffic. This avoids the downtime caused during the update process.

Testing the Database Cluster

Now let’s test that our database cluster is operational. In a managed database we don’t have direct access to an instance, so we’ll try our scenario by applying an update to our database version, this should take one of the instance down during the process without interrupting the database access:

Once the upgrade is started, we should see our standby instance is not ready while the master instance will keep running:

This means that the upgrade process is now occurring on the standby instance first, while the cluster is still operational and serving requests through the master. We can test this by accessing our n8n server:

Once the upgrade is complete you should see both instances as running:

All this process happens seamlessly in the background without any manual changes required.

Conclusion

PostgreSQL is a popular relational database management system that fits a wide range of use cases. From scalability, to performance and extensibility, PostgreSQL is capable of satisfying enterprise-level application requirements. However, setting up a production-grade high-availability PostgreSQL cluster can be a complex task, that’s where a managed database service comes into play.

Managed databases remove the administrative overhead associated with manually provisioning database instances and setting up database clusters. It offloads the underlying complexities to the cloud service provider while offering users the flexibility to specify their desired cluster configuration. UpCloud offers a managed PostgreSQL service that makes provisioning database clusters a breeze. You just specify the desired number of nodes and the cluster automatically gets created for you.

In this article, we covered the steps for creating a managed PostgreSQL on UpCloud. We tested the connectivity to the database instance by deploying n8n on a cloud server, then we created some test data to verify the database access. Finally, we scaled our database for high-availability by configuring additional nodes in the setup, and we verified the cluster operations during maintenance and updates.

Ready to deploy your high-availability PostgreSQL in the Cloud? Try UpCloud now and create a reliable and performant database cluster setup with minimal effort.