postgres

YugabyteDB

YugabyteDB, What Is It Good For?

YugabyteDB, What Is It Good For? 800 507 Exist Software Labs

The database evolved from the network & hierarchical model of the 60s to what eventually became the dominant force in databases in the 70s up to the present time—the relational model. From this model arose the big names in Relational Database Management Systems like DB2, Oracle, and Sybase. In the 80s, a player emerged that would forever change the RDBMS landscape: Postgres.

Postgres is the only RDBMS to have won DB of the year 3 times: 2017, 2018, & 2020. This speaks of the universal trust that developers have placed on the system and this has only increased with the rise of open-source software and cloud-native computing.

Which begs the question: What is cloud-native computing? To put it simply, the rise in the services-based model championed by the major cloud providers like AWS, GCP, & Azure has given way to a new way of developing applications, with key emphases on scalability, resilience, high-availability, and agile deployments.

Combining the relational supremacy of Postgres and cloud-native computing, we are then faced with the next step in the evolution of the RDBMS—Distributed SQL— and YugabyteDB is the No. 1 Distributed SQL platform today.

Use Cases

What is YugabyteDB good for? I will mention 4: cloud-native applications, applications requiring massive scale, geo-distributed workloads, and traditional RDBMS modernization.

1. Cloud-native applications – Build stateful microservices by leveraging modern cloud-native frameworks such as GraphQL, or in any language of your choice like Django, Spring, Go, etc.

2. Massive-scale applications – Seamlessly deploy IoT and streaming services that demand high throughput, support for large data sets, and/or many concurrent connections.

3. Geo-distribution – Move data closer to users for resilience, performance, and compliance with the industry’s largest choice of deployment and replication models.

4. RDBMS modernization – Evolve Oracle, SQL Server, and DB2 workloads to a modern distributed SQL database with minimal disruption. If you can migrate to PostgreSQL, you can migrate to YugabyteDB.


Real-World Projects

We have deployed YugabyteDB in several government and educational institutions that employed cloud-native applications development—requiring user connectivity from various geo-locations—along with the migration of MySQL databases.

YugabyteDB has delivered in all of these projects, requiring very minimal tweaking in existing SQL code and even speeding up queries in many instances.

YugabyteDB will very soon be the de facto RDBMS of choice in a cloud-native world.

Exist is your data solutions partner of choice!

Explore the next level of your digital transformation journey with our Data Solutions Services. Let’s look at opportunities to better maximize your ROI by turning your data into actionable intelligence. Connect with us today, and we’ll proudly collaborate with you!

The Elephant Behind the Excellence

The Elephant Behind the Excellence 650 486 Exist Software Labs

The preceding year, 2021, was an eventful year for EXIST Data Solutions: new team members were added, new technologies were learned, and new projects were implemented.

In the enterprise database front, PostgrEX was implemented in a prestigious 5-star hotel and casino, a state university, and a major security agency handling the biggest mall in the country.

YugabyteDB, the No. 1 cloud-native, distributed SQL database in the world, was implemented in 3 government projects, and premium EnterpriseDB support was rendered to the country’s primary energy market corporation.

On the data analytics front, Greenplum was also successfully implemented in 3 government projects, thereby enabling these entities to turn their data into actionable insights.

But what do all these business-transforming technologies have in common? In a word: Postgres.

Postgres is the database engine upon which PostgrEX, YugabyteDB, EDB, and Greenplum are based. With most of them, modifications in varying degrees were done to core Postgres to deliver a product that is still Postgres, but better!

As indicated in the article, Databases in 2021: A Year in Review, the dominance of Postgres in the year 2021 was undeniable:

The conventional wisdom among developers has shifted: PostgreSQL has become the first choice in new applications. It is reliable. It has many features and keeps adding more. In 2010, the PostgreSQL development team switched to a more aggressive release schedule to put out a new major version once per year (H/T Tomas Vondra). And of course, PostgreSQL is open-source.

PostgreSQL compatibility is a distinguishing feature for a lot of systems now. Such compatibility is achieved by supporting PostgreSQL’s SQL dialect (DuckDB), wire protocol (QuestDB, HyPer), or the entire front-end (Amazon Aurora, YugaByte, Yellowbrick). The big players have jumped on board. Google announced in October that they added PostgreSQL compatibility in Cloud Spanner. Also in October, Amazon announced the Babelfish feature for converting SQL Server queries into Aurora PostgreSQL.

One measurement of the popularity of a database is the DB-Engine rankings. This ranking is not perfect and the score is somewhat subjective, but it’s a reasonable approximation for the top 10 systems. As of December 2021, the ranking shows that while PostgreSQL remains the fourth most popular database (after Oracle, MySQL, and MSSQL), it reduced the gap with MSSQL in the past year.

Another trend to consider is how often PostgreSQL is mentioned in online communities. This gives another signal for what people are talking about in databases.

What does all this mean for you and your business? It means you can entrust your most mission-critical applications to Postgres and its derivatives; it means you can break free of vendor lock-in and redirect cost savings to core business initiatives; it means your company can be a better version of itself–a more profitable version–in the year 2022!

Contact us and find out how EXIST Data Solutions can meet all your database-related requirements.

Exist is your data solutions partner of choice!

Explore the next level of your digital transformation journey with big data and analytics. Let’s look at opportunities to better maximize your ROI by turning your data into actionable intelligence. Connect with us today, and we’ll proudly collaborate with you!

A Fully Dockerized MySQL to YugabyteDB Migration Strategy Using pgloader

A Fully Dockerized MySQL to YugabyteDB Migration Strategy Using pgloader 768 487 Exist Software Labs

While there have been many who began their journey to relational databases with the simple and popular MySQL, the evolution of business use cases involving more than read optimization and the need for more performant, full-fledged, read/write-optimized OLTP systems have given rise to a widespread migration from MySQL to Postgres.

Along with this, the transition from monolithic to cloud-native has also paved the way for distributed SQL systems that allow for read/write functionality in every node of the database cluster (while maintaining ACID-compliance across all nodes) and cloud-agnostic deployments of these nodes across geographic zones and regions. This is the future of the database, a future where reliability, accessibility, and scalability are built into the product. The future of the database is YugabyteDB.
 

From MySQL to YugabyteDBfast!

The method that we will be using to migrate a MySQL database to YugabyteDB is through the use of pgloader, a very reliable tool for migrating from MySQL (even SQL Server) to Postgres. We will first migrate the MySQL database to a Dockerized Postgres instance using Dockerized pgloader.

Once the MySQL database has been migrated to Postgres, we will then use the ysql_dump utility that comes with every installation of YugabyteDB to dump the Postgres database into a YugabyteDB-friendly format. This is one of the very useful traits of ysql_dump: it ensures that your Postgres dump can be fully restored in a YugabyteDB instance.

After getting the dump, we will restore this dump in the blank YugabyteDB database that we’ve created beforehand, thereby completing the migration from MySQL to YugabyteDB!

 

Steps

1. Get the Postgres Docker container

docker run -e POSTGRES_HOST_AUTH_METHOD=trust -p 5432:5432 -d postgres:11

2. Create the MySQL database counterpart in Dockerized Postgres

CREATE DATABASE <db name>;

3. Run Dockerized pgloader to load from MySQL to Dockerized Postgres

docker run --rm --name pgloader dimitri/pgloader:latest pgloader --debug mysql://<user name>:<password>@<ip address of MySQL DB server>:3306/<source database name> postgresql://postgres@<ip address of Dockerized Postgres>:5432/<destination database name>

*If a user error is encountered, make sure the user and IP address combination indicated in the error is created in the MySQL source and has access to the databases to be migrated.”

4. Since pgloader creates a Postgres schema using the database name and puts the tables there, we can change the schema name to “public”

DO LANGUAGE plpgsql
     $body$
     DECLARE
     l_old_schema NAME = '<schema name>';
     l_new_schema NAME = 'public';
     l_sql TEXT;
     BEGIN
     FOR l_sql IN
     SELECT
          format('ALTER TABLE %I.%I SET SCHEMA %I', n.nspname, c.relname, l_new_schema)
     FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE
     n.nspname = l_old_schema AND
     c.relkind = 'r'
     LOOP
     RAISE NOTICE 'applying %', l_sql;
     EXECUTE l_sql;
     END LOOP;
     END;
     $body$;

5. In this example, we will be using Dockerized Yugabyte as the destination (also applies to other form factors)

a. 1-node cluster with no persistence: 

docker run -d --name yugabyte  -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false

b. With persistence:

docker run -d --name yugabyte  -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 -v ~/yb_data:/home/yugabyte/var yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false

6. Go inside the Yugabyte container

a. To access the interactive terminal of the container:

docker exec -it <yugabyte container id> /bin/bash

b. Go to the bin directory:

cd /home/yugabyte/postgres/bin

c. Make sure destination database exists in YugabyteDB:

CREATE DATABASE <destination yugabytedb name>;

d. Dump the database in the Postgres container:

./ysql_dump -h <ip address of Postgres container> -U postgres -d <database name of postgres db> -p 5432 -f <dump name>.sql

e. Restore the Postgres dump in the blank database in the YugabyteDB instance:

./ysqlsh -p 5433 -d <database name of destination yugabyte db> -f <dump name>.sql

 

And there you have it! You have successfully migrated your MySQL database to the future of the database. You have migrated to YugabyteDB!

yugabytedb migration

Exist is your data solutions partner of choice!

Explore the next level of your digital transformation journey with big data and analytics. Let’s look at opportunities to better maximize your ROI by turning your data into actionable intelligence. Connect with us today, and we’ll proudly collaborate with you!

Enterprise Database

The Future of the Database: YugabyteDB

The Future of the Database: YugabyteDB 768 487 Exist Software Labs

The journey to application modernization brought about by the cloud-native renaissance continues, and the benefits to be had are truly being enjoyed by the enterprises that embrace the path. Speed, scalability, resiliency, and agility may seem to just be industry buzzwords, but in reality, they translate to better application deployment, performance, and availability, which further translate to what really matters: happy customers.

This has given way to the concomitant need for databases to adapt to this need for speed, scalability, resiliency, and agility. The way traditional databases have implemented a single-node access to the database cluster via the master node has proven untenable in a commercial environment wherein the need to scale users, not just locally, but across the regional and geographical divide, has become dire and ubiquitous.

This is where the gap is filled by YugabyteDB.

 

What is YugabyteDB?

What is YugabyteDB?

YugabyteDB is a transactional, distributed SQL database that was designed primarily to possess the virtues of the cloud-native philosophy. Its creators wanted a chiefly OLTP database that was fast, easy to add more nodes to, able to tolerate node failures, upgradable without incurring any downtime, and deployable in all form factors (public/private cloud, VMs, and on-prem).

Being a distributed SQL database, it has automatic distribution of data across nodes in a cluster, automatic replication of data in a strongly consistent manner, support for distributed query execution so clients do not need to know about the underlying distribution of data, and support for distributed ACID transactions.

It is a multi-API database that exposes the following APIs (more will be added in the future): 

  • YSQL – an ANSI SQL, fully-relational API that is completely compatible with PostgreSQL 11.2
  • YCQL – a semi-relational SQL API that is based on the Cassandra Query Language

It is a Consistent and Partition Tolerant (CP) database in that in the event of a network partition within the database cluster wherein one of the nodes cannot communicate with the other nodes and determine majority membership, data consistency over availability is prioritized by the system and this node will not be able to accept writes, whereas the nodes that are still part of the majority will remain unaffected.

It is completely open source, released under the Apache 2.0 license.

 

What are the key benefits of YugabyteDB?

The following are some of the benefits that are immediately enjoyed “out-of-the-box”:

  • No single point of failure given all nodes are equal
  • Distributed transactions across any number of nodes
  • Scale write throughput linearly across multiple nodes and/or geographic regions.
  • Low-latency reads and high-throughput writes.
  • Strongly consistent, zero data loss writes.
  • Cloud-neutral deployments with a Kubernetes-native database.
  • Automatic failover and native repair.
  • 100% Apache 2.0 open source even for enterprise features.

In other words, you get a cloud-native, transactional, distributed SQL database system that allows you to read and write on every node in the cluster (with ACID assurance), distribute your application load across many nodes in many regions and geographies, read and write data fast, deploy anywhere, and be highly available—all in open source!

 

Use Cases

YugabyteDB is perfect for:Use Cases of YugabyteDB

Just this morning, social media personality, James Deakin, posted on his FB wall about a particular bank whose “app feels like it’s running on windows 95” (his own words). He ended up closing his account due to the overall poor customer experience brought on by the subpar performance of this bank’s client-facing, internet applications, along with other concerns.

YugabyteDB is perfect for the client-facing, Internet, transactional application.

Want to know more about the Yuggernaut of Distributed SQL? Contact us.

Exist is your data solutions partner of choice!

Explore the next level of your digital transformation journey with big data and analytics. Let’s look at opportunities to better maximize your ROI by turning your data into actionable intelligence. Connect with us today, and we’ll proudly collaborate with you!

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!

Exist Provides Tech Spark For Major Renewable Energy Project

Exist Provides Tech Spark For Major Renewable Energy Project 768 487 Exist Software Labs

MANILA, Philippines  — Exist, a global consulting and enterprise IT solutions leader, is currently working on the development of the country’s landmark nationwide system to establish the country’s renewable energy market (REM).

Faced with a tight bid schedule which threatened the 2019 project timeline, the Philippine Electricity Market Corporation (PEMC), the governance arm of wholesale electricity spot market (WESM) has finally found its worthy IT partner to help design and build the platform and all accompanying technical systems to run the Philippine Renewable Energy Market System (PREMS) that seeks to promote more RE-based investment locally.

“We are honored for the trust that was given to our team, for believing in our enterprise IT skills and experience to provide solutions for the local energy sector”, reveals Mike Lim, CEO of Exist.  “Working on PREMS and previously on the WESM spot market system afforded us a unique perspective and as a result, we can say we have a better understanding of the local power sector and a bountiful supply of ideas for innovative solutions that could help market participants, as well.”

Under the joint auspices of the United Nations Development Program (UNDP), the Department of Energy (DOE) and the Global Environment Facility (GEF) called Development for Renewable Energy Applications Mainstreaming and Market Sustainability (DREAMS) Project, the development of PREMS is intended to curb local greenhouse gas (GHG) emissions through commercialization of the RE markets and incentivize investments in RE-based power generation projects.

“We’ve assembled our best software minds and utilized knowledge of up and coming technologies to ensure that we deliver a complete system that is perfectly capable to carry out PREMS’ supportive policies and regulatory requirements”,  adds Mr. Lim. “As per practice, we keep close coordination with all partners concerned, working out changes to ensure that we still deliver within schedule.”

In 2008, The Philippine Renewable Energy Act was passed which aimed to elevate the use of renewable energy resources and its capacity development yet in many ways, adoption and preferential use of RE is still very much in a transition phase.

Mr. Lim further adds, “We are aware of the significance of this project, not only for its alternative power-providing component but for the focus this brings towards a cleaner environment and a sustainable future.”

We cannot afford any more delays. We understand this is also about doing our part as a nation to contribute towards the health of our planet”, concludes Mike.

You can view the official UNDP Procurement Notice for the Development of Software, Supply and Delivery of Hardware for the Implementation of the Philippine Renewable Energy Market System (PREMS) awarded to Exist here. (http://procurement-notices.undp.org/view_awards.cfm)

Exist, Ashnik and EnterpriseDB Power Digital Transformation for the Philippine Electricity and Power Sector

Exist, Ashnik and EnterpriseDB Power Digital Transformation for the Philippine Electricity and Power Sector 768 487 Exist Software Labs

At the recently concluded The Future Energy Show Philippines 2019, Exist, Ashnik and EnterpriseDB teamed up to highlight the advantages of using Postgres for a number of IT projects specifically within the electricity sector.

During the 2-day event, booth visitors and fellow sponsors personally listened to the stories about how Exist, Ashnik and EnterpriseDB were able to provide their skills and expertise for the local energy sector which began not too long ago.  

With years of experience working on enterprise systems across a diverse range of industries from banking and finance to retail and healthcare, Exist was able to get the opportunity and win the trust of the main local energy corporations PEMC and WESM for developing mission-critical systems required to bring the electricity spot market closer to achieving its goal of having a transparent, fair, competitive, and reliable market for the trading of electricity throughout the Philippines.

Working closely with Ashnik as a partner, the push for the use of EDB Postgres has always made the most sense as the choice of database with the balanced mix between cost and features that make it the most complete open source-based data platform.

One of the highlights of our participation at the event is our talk entitled “Postgres Powers Digital Transformation in Southeast Asia – Success Stories”, presented by Rekha Ramaswamy, Enterprise Sales Manager at Ashnik and Jenish of Vyas, Solutions Engineer, SE Asia and Hong Kong at EDB Postgres. In this session, they shared compelling business cases and customer success stories highlighting the value of the open source-based EDB Postgres Platform in helping enterprises confidently deliver on the promise of digital business.

“Based on the open source Postgres platform, we’ve enhanced it in such a way that it has additional application security and improved performance. [In using EDB Postgres] we are not compromising on existing features but giving added flexibility with [their] existing legacy database.”
Jenish of Vyas, Solutions Engineer, SE Asia and Hong Kong at EDB Postgres

Check out the slides and videos below: