Introducing PostgrEX: How to Fulfill Your Database SLAs Without Having to Sell a Kidney

Introducing PostgrEX: How to Fulfill Your Database SLAs Without Having to Sell a Kidney 768 487 Exist Software Labs

In a past blog post, I gave the definition of software as being enterprise-grade in the following manner:

A piece of software is enterprise-grade when it caters to the needs of not a single individual, nor a select group of individuals, but the whole organization. When applied to database management systems, an enterprise database is an implementation of database software that serves the organization by managing their humongous collection of data. It must be robust enough to handle queries from hundreds to tens of thousands of users at a time. It must also have a host of features that are geared towards improving the productivity and efficiency of the organization, such as multi-processing, parallel queries, and clustering, to name a few.

To tease it out a little bit further, I would like to propose that a database implementation is “enterprise” when it possesses the following attributes:

1.    A database engine that has proven itself in a multitude of business applications globally in a span of decades

2.     Able to meet strict SLAs (at least 5 nines) through high availability and failover mechanisms

3.    Monitoring

4.    Backup and Recovery Management

5.    Connection Pooling

Traditionally, enterprise database implementations have been costly investments and organizations have been willing to pay the price given the criticality of data to any business endeavor. But given the current economic climate brought on by the COVID-19 pandemic, along with the perennial need for businesses to streamline costs in order to divert savings into the core business, many are asking: Is there a better, more cost-efficient way of implementing a database solution without sacrificing enterprise-ness?

The answer is most certainly! Let me introduce you to PostgrEX.

What is PostgrEX?

PostgrEX is shorthand for Postgres EXIST Enterprise Xpertise.

It is an enterprise-grade database platform built on top of a purely open-source technology stack and is part of EXIST Software Labs Inc.’s Data Solutions.

What are the components of PostgrEX?

1.    Scoping and sizing of DB hardware

We will recommend the hardware specifications (memory, CPU, storage, networking, etc.) that will be optimal for your business requirements based on the current and projected data growth, the total number of users, total concurrent users, largest table size, largest query size, etc.

2.     Installation

We will install the database system, along with the high availability/failover, monitoring, backup/recovery, and connection pooling components.

3.    Optimization

We will optimize the database configuration settings for the best possible performance given the hardware available.

4.    High Availability/Failover/Disaster Recovery

We will set up replication between the Postgres database servers (streaming replication, WAL log-shipping, or a combination of both) in the Main site and we can also set up replication to a DR site.

We will also set up and configure Patroni, etcd, and HAProxy as part of the failover mechanism of the system.

5.    Monitoring

We will install, set up, and configure pgCluu as the default DB cluster monitoring tool.

6.    Backup and Recovery

We will install, set up, and configure Barman as the default DB backup and recovery management tool.

7.    Connection Pooling

We will install, set up, and configure pgBouncer as the default DB connection pooling tool.

8.    Query Optimization

We can also provide query optimization services to your Developers in order to ensure tip-top application performance.

9.    Migration to Postgres

We can migrate your existing SQL Server, MySQL, and Oracle databases to Postgres CE.

What are the technologies used by PostgrEX?

1.    Database

Postgres, or PostgreSQL, is arguably the best open-source object-relational database management system available today. It was DB-Engine’s “DB-of-the-Year” for 2 years straight (2017 and 2018), and has proven itself in mission-critical applications across all industry verticals.

See: Why use PostgreSQL for your Business?

2.    High Availability and Failover

Patroni – an open-source Python application that handles Postgres configuration and is ideal for HA applications. See Patroni documentation.

etcd – a fault-tolerant, distributed key-value store that is used to store the state of the Postgres cluster. See etcd documentation.

HAProxy – provides a single endpoint to which you can connect the application. It forwards the connection to whichever node is currently the master. It does this using a REST endpoint provided by Patroni. Patroni ensures that, at any given time, only the master Postgres node will appear as online, forcing HAProxy to connect to the correct node. See HAProxy documentation.

3.     Monitoring

pgCluu – a lightweight, open-source Postgres monitoring and auditing tool. See pgCluu documentation.

4.    Backup and Recovery

Barman – an open-source backup and recovery management tool. See Barman documentation.

5.    Connection Pooling

pgBouncer – a lightweight, open-source connection pooler for Postgres. See pgBouncer documentation.

Moving Forward with PostgrEX

Is your organization ready to face the challenges of an uncertain future? Having enough money in the bank is certainly a top priority and doing away with unnecessary and exorbitantly-priced database license costs is one way of doing this.

With PostgrEX, your business applications can still enjoy industry-recognized, top-level, enterprise database excellence through the use of expertly-configured, purely open-source technologies. This means you get to keep your kidney to live and fight another day—and many other days!

Contact us for more information.

Download our datasheet now!

Feeling Cold from MySQL Backups that Take Forever? Hot Backups to the Rescue!

Feeling Cold from MySQL Backups that Take Forever? Hot Backups to the Rescue! 768 487 Exist Software Labs

Many business entities have entrusted their data to MySQL, and for certain use cases, this is a sound choice. If you need an RDBMS for basic transaction processing that does not involve complex tasks and does not require full SQL standard compliance, then MySQL may be right for you (for enterprise-scale workloads, Postgres is the only choice). In fact, Facebook and YouTube have used MySQL for a while now, just to name a couple.

However, there is a conundrum that commonly afflicts MySQL users when their data suddenly assumes massive proportions (terabyte range): long backups and the database unavailability that goes along with it.

Simply copying the data files to a backup location will result in internal data inconsistency since data may have been added or changed while the copying was taking place. An alternative to this is stopping the database and performing the backup, which renders the database unavailable! Availability is the rage and rendering the database unusable is very much unacceptable. And even if, by a stroke of luck, management allows for some downtime, the slowness of MySQL’s native backup mechanism would eventually cause anybody to reconsider.

managed IT services

Consider this actual scenario from one of our clients, a large retail outfit in Southeast Asia. Their POS transactions are fed into a MySQL database that is involved in a replication cluster. The database has grown to 2 TB in size and their backups take 15 hours to complete–if at all. The backup process encroaches in upon operating hours and they are forced to just kill the backup. The problem escalates when the Slave server needs to be refreshed (often by lost binary logs preventing it from synchronizing with the Master) since resetting replication involves restoring a backup of the Master on the Slave node. What to do??!!

Enter hot backups and Percona’s Xtrabackup!

Percona XtraBackup

A hot backup is a database backup that can be performed while the database is online.

From Percona’s website:

“Percona XtraBackup is a free, online, open source, complete database backups solution for all versions of Percona Server for MySQL and MySQL®. Percona XtraBackup performs online non-blocking, tightly compressed, highly secure full backups on transactional systems so that applications remain fully available during planned maintenance windows.”

Xtrabackup solved the problem for this retail giant, reducing the backup time from 15 hours to 4 hours! Database availability was no longer an issue and re-establishing replication is now a fast and straightforward endeavor.

The following are the basic steps to beating the cold:

  1. Simply install the tool:
  2. Create a backup directory if one does not yet exist like so, for example: mkdir /var/lib/mysql-backup.
  3. Run the utility: innobackupex –user=root –password=<password> /var/lib/mysql-backup.
  4. Apply the binary logs. For example: innobackupex –apply-log /var/lib/mysql-backup/2018-10-04_15-05-25, “2018-10-04_15-05-25” being the directory where the utility created the backup.

If you need expert database services, contact us and we will be more than happy to solve your DB woes.

Stay warm!