In this article, we will learn how to create and manage read replicas in Azure Database for PostgreSQL.
Introduction
Data hosted in data repositories is accessed by a variety of applications. When data repositories are migrated from one hosting platform to another or when the load on a data repository increases, typically replication is adopted as an interim or a long-term solution approach. These are just two of the typical examples where replication is used. Replication has been around in database practice almost since the advent of databases. For those who are new to replication, it is the practice of synchronizing the database objects, data as well as configuration from parent (also known as the master) database to child databases (also known as replicas). This synchronization can be real-time, near real-time or asynchronous depending on the criticality of availability of data in the replicas. Almost every standard database supports replication. Cloud databases like Azure SQL, Azure Database for Postgres, Cosmos DB, etc. on the Azure cloud platform also support replication.
Azure Database for PostgreSQL is an Azure offering of the open-source Postgres database. As there are many databases and data warehouses that are derived from Postgres, during migration from Postgres to a different flavor of another database or data warehouse that is compatible with Postgres, often read replicas are employed. The replicas are read-only since it’s a one-way replication from the master database to replicas. And replicas serve the purpose of decreasing the load on the primary transactional database in production environments. Replicas are typically used as migration sources, reporting and ad-hoc analytics sources and for other purposes. Let’s go ahead and learn to create and manage read replicas in Azure Database for PostgreSQL.
Pre-requisites
It is assumed that one has an existing Azure account with required access to Azure Database for PostgreSQL. Navigate to the Azure Database for PostgreSQL service, and click on the button to create a new instance. You would be provided four options to select the edition of PostgreSQL that you intend to create. We can use the Single Server option with the most basic configuration for this exercise. Once the database is created, it completes the first part of the pre-requisite.
To work with this database, we need to have an editor installed on the local machine which can connect to this database instance. One of the most popular IDE to work with PostgreSQL is pgAdmin, which we will use to work with Azure Database for PostgreSQL as well. It is assumed that one has this IDE installed on the local machine, the server is already registered in the IDE and successful connectivity with it has been tested. Once this part is done, our pre-requisites are met for this exercise and we can start focusing on creating the replica.
Replication in Azure Database for PostgreSQL
Navigate to the Azure Database for PostgreSQL instance that we created as a part of the pre-requisite and land on the dashboard page of the instance. It would look as shown below.
We need to have some sample data already created in this instance which we can use to verify whether the data and database objects are getting replicated in the new instance. Consider creating at least one table with some records in it in this database instance. In our case, we have two tables created as seen under the tables category as shown below.
On the left pane of the dashboard page, click on the replication tab as we intend to create a replica of this instance. Once we click on it, it would open a page as shown below. The first setting that we can see is the Azure replication support. This setting is related to the level of logging to be done. The default level is replica when the instance is created with default settings. We have the option to change it to a logical level which means we intend to have a more verbose level of logging. Under the master category, we can see the name of this instance listed which means that this instance will act as the master instance based on which replicas can be created.
Click on the Add Replica button from the toolbar, and it would open a new page as shown below. It should be kept in view that an existing instance of Azure Database for PostgreSQL cannot be used as a replica candidate. A new instance of the database server needs to be created, for which we need to provide a name as shown below. The configuration capacity of this new instance would be identical to the existing instance by default during instantiation. Click on the OK button to start creating the new instance.
Once created, this instance would appear under the replica category as shown below. This confirms that a master replica relation exists between these two instances. We can have as many replicas of this master instance as required.
Connect to the new replica instance of Azure Database for PostgreSQL and you would find that both the tables that we had in the existing database are replicated in the replica instance as well. Also, consider validating different schemas and configurations of this database instance and you would find that everything should be identical to the master instance.
The master instance acts as a model server based on which other instances can be created so that they have all the necessary base database objects to bootstrap database development. It can be the case that once the replica is created, one may need to add some new objects to the master instance and the question arises that whether the new objects created in the master instance will be replicated in the replica. If the replication is live, it is expected that these objects should get replicated. An easy way to validate this is by creating a new object in the master instance as shown below.
Navigate to the replica instance and this newly created object should be reflected in the replica almost instantaneously. Another scenario is creating new objects in the replica. As we learned earlier that the newly created database server is a replica, and it’s configured with a one-way replication. So, it should not be possible to create any new database objects in the replica instance. An easy way to validate it by attempting to create a new instance in the replica instance. You would encounter an error shown below as the replica database instance is read-only.
At a certain stage in the database management life cycle, the replica database may be ready to promote to a totally independent database. In that case, we need to stop the replication from master to replica and bring the replica instance out of the master – replica relationship. To perform the same, navigate back to the replication tab of the database instance of the Azure portal and click on the Stop Replication button.
It should be kept in view that this is a permanent operation. We are not temporarily pausing the replication. Once this operation is performed, this replica database instance would be promoted as a fully independent instance and can never be configured again as a replica of the primary or master database server instance as shown below.
Once the operation is complete, we won’t see the replica instance now under the replica list of the master database server instance. If you have an open connection to the replica instance in your IDE, it would be lost in the interim till the replica is promoted to a fully independent instance. Once done, we may want to test the capability of this newly promoted instance to verify that now it’s a read-write instance and not just a replica. To validate it, attempt to create a new database object in this instance of Azure Database for PostgreSQL and you should be able to create it as shown below.
In this way, we can create replicas and manage replication of Azure Database for PostgreSQL instance on Azure platform.
Conclusion
In this article, we understood the basics of replication. We created an instance of Azure Database for PostgreSQL, created a replica, validated replication, validated replica restrictions, promoted replica to an independent instance and validated the same as well.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023