Tips for Managing PostgreSQL Remotely

A wide range of resources are available for you when managing your PostgreSQL database clusters remotely. With the right tools managing it remotely is not a difficult task. 

Using fully-managed services for PostgreSQL offers an observability that can deliver most of what you need to manage your database. They provide you with an alerting system, metrics, automation of time-consuming system administration tasks, managing your backups, etc.

When running on-prem it’s a different challenge. That’s what we’ll cover in this blog. We’ll share tips on managing your PostgreSQL database cluster remotely.

Database Observability

The term observability might not be a familiar thing to some folks. Observability is not a thing of the past, it’s the trend when managing your databases (or even PaaS or SaaS applications). Observability deals with monitoring, but to some extent it covers the ability to determine the state of your database health and performance and has a proactive and reactive capability which decides based on a certain status of your database nodes. 

A good example of this is in ClusterControl. When ClusterControl detects warnings based on the checks on a given configuration it will send alerts to the provided channels. These can be setup and customized by the system or Database Administrator. 

If your primary database has been degraded and unable to process transactions (either read or writes) ClusterControl will react accordingly and start to trigger a failover so that a new node can process the unwarranted cause of overflowing traffic. While this occurs, ClusterControl can notify the engineers what happened by triggering alarms and sending alerts. Logs are also centralized and which investigation and diagnostic tasks can be done in one place, allowing you to provide a quick result.

Although this might not mean that ClusterControl is a complete package for Observability, it is one of the powerful tools. There are tools that are more architectured also to manage especially in containerized environments such as Rancher mixed with Datadog. 

How Does This Help You In Managing Remotely?

One basic principle of management is to have peace of mind. If a problem occurs, the tools you are using for observability must be able to notify you via email, sends you SMS, or through a pager applications (like PagerDuty) to alert you to the status of your databases cluster,

or you can receive alerts such like below…

It is very important that it notifies you when changes occur. You can then improve and analyze the state of your infrastructure and avoid any impacts that can affect the business.

Database Automation

It is very important that most of the time-consuming tasks are automated. Automation allows you to downsize the manpower. What does it mean to automate your PostgreSQL database clusters? 

Failover

Failover is an automatic approach that occurs when an unprecedented incident occurs (such as a failure on hardware, a system crash, power loss in your main primary node, or a network loss within the entire data center). Your failover capacity must be regularly tested and follow industry standard practices. The service discovery of an internal failure must go to the point that it has been determined as true and it’s actually happening.

In ClusterControl, when an incident occurs it triggers the failover mechanism and promotes the most updated standby node and then triggers alarms as seen below…

Then, it works in the background for a failover as you have seen below, the progress is on the move.

leaving the result as it finishes below…

Backup Scheduling

Backups are a very important part of Disaster and Recovery Planning (DRP). Backups serve as your backbone when your cluster data goes adrift after a split brain or network partition encounters. There are certain occasions where pg_rewind can be beneficial also but automation of your backups are always very important to avoid any such huge loss of data and lesser RPO and RTO.

In ClusterControl you can take or create a backup without any special tools or add utility work to script an automated backup. All are there and it will be up to your organization when the backup will take place and what are the policies of your backup including its retention. In fact, the most important thing here is, backup shall not interfere with your production environment and shall not lockup your nodes when backup takes place.

Backup verification plays also a very important role here. Rest assured, your backup must be a valid type of backup and is a reliable copy when crisis takes place. Adding the mechanism to store your backup not only in your premises or data center, but also store it elsewhere securely like in the cloud or to AWS S3 or Google Cloud Storage for example.

With ClusterControl, this has been taken easily and single handedly all in the platform by just following the GUI as shown below,

This allows you to pick up the backup method you choose, store it in the cloud to add more backup retention and assurance by spreading your backup copy not only in one source but also in the cloud. Then, you have an option to verify the backup once it’s finishing creating the backup to verify if it’s a valid one or not. Part of it is also you can choose to encrypt your backup which is a very important practice when storing your data at rest and complying security regulatory guidelines.

Database Security

Security is usually the majority’s primary concern when it comes to managing your PostgreSQL database cluster remotely. Who will be able to access the database remotely or should it be only local? How to add security restrictions and how to manage the users and review the user’s permission by a Security Analyst. It is very important to have a more set in place and provide a clear picture of your architecture so it can be dissected where are the loopholes and what are the necessary things to improve or tighten the security.

ClusterControl provides you an overview of and management of your PostgreSQL users and provides you a visualization and an editor for your pg_hba.conf, which manages how the users can be authenticated. 

For User Management it provides an overview of the list of users and it’s privileges in the database cluster. It also allows you here to modify or change the user’s privileges if it’s not in accordance to your security and company guidelines. Managing remotely requires that all of your users must have specific permissions and roles and when it can only be used or accessed and limits the role to avoid damage in your database.

It’s also very important in your PostgreSQL to review and verify that there’s no lapses with the authentication of the user. When it can be allowed and its scope to be able to connect to the servers. It is best that this is visualized like we have below,

This allows you to easily verify and avoid the authentication overlooked for such possible loopholes that an attacker might be able to log in due to weak rules in authentication.

Using SSL and encryption adds more security and robustness when your database is accessed remotely. But if you are accessing your database remotely outside your organization premise, it is best to encapsulate your data such as logging in through a VPN. You can check out our blog on Multi-DC PostgreSQL: Setting Up a Standby Node at a Different Geo-Location Over a VPN.

Centralized Database Logs

Centralization of aggregated logs provides you a very convenient way to investigate and implement a security analysis tool to understand your database clusters and how they behave. This is very beneficial when managing remote databases. Some common approaches are using Logstash using the ELK stack or the powerful open-source management for logs, Graylog. 

Why is it Important to Centralize Your Database Logs? 

In case you need to investigate a cluster-wide problem and see what has been going through your database clusters, proxies, or load balancers. It is very convenient to just look upon one place. Some very rich and powerful tools like I mentioned above let you search dynamically and in real time. They also provide metrics and graphs which is a very convenient way for analysis.

With ClusterControl, there’s a convenience provided when accessing the logs. Although the logs are not collected and stored centrally, it offers you an overview and ability to read the logs. See below…

You may even review the jobs of what did ClusterControl detected and had acted either based on the Alarms or going through the Jobs just like below,

Conclusion

Managing your PostgreSQL database clusters remotely can be daunting, especially when it comes to security, monitoring, and failover. If you have the right tools, industry standards, and best practices for implementation, security, and observability then you can have peace of mind when you manage your database; regardless of your location.