Forem: Talha Munir 🇵🇸

Backing up and restoring in EDB BigAnimal

Talha Munir 🇵🇸 — Sat, 11 Nov 2023 11:12:26 +0000

Backups

BigAnimal backs up the data in your PostgreSQL clusters. BigAnimal uses any of the following storage solutions which certainly depends on the cloud your cluster is deployed on.

AWS uses Amazon S3 (standard tier) as an Object storage solution
Azure uses Azure blob storage as an Object storage solution.
Google Cloud uses cloud storage as an Object storage solution.

The organization that has purchased all the subscriptions is responsible for all the charges related to the object storage solution.

PostgreSQL clusters in BigAnimal are continuously backed up through a combination of base backups and transaction log archiving. When a new cluster is created an initial base backup is taken. After that whenever the WAL file is closed, which by default happens after every 5 minutes. It is uploaded to the cloud object storage solution. If your cluster has faraway replicas the application will copy the WAL files from your cloud object storage solution and asynchronously transfer them to the faraway replicas. Your organization is solely responsible for the charges that are associated with the cloud object storage solution.

Replication lags with faraway replicas:

With faraway replicas, the primary server writes to the archive, which is moved to the object store. The replica reads from the object store as files arrive. Prior to the archive replicating across to the replica, the log file must write at least 16MB of data to the WAL to cut a new archive file. If the time interval for closing a WAL file is too large, it can introduce a delay until the replica receives the latest record. The amount of time that it takes to fill up the 16 MB log file and copy it to the archive is the replication lag. The replication lag is the period of time when data can be lost. The time totally depends on the amount of activity in your database. You need to measure the replication lag to determine if it's acceptable from a possible data-loss perspective. If it isn't acceptable, consider using a distributed high-availability cluster.

References:

Deleting a cluster in Big Animal

Talha Munir 🇵🇸 — Sun, 05 Nov 2023 10:20:42 +0000

In our series of Big Animal, we will see how we can delete a cluster in the application. You can easily restore deleted clusters for as long as the backup is available. The backup is available for the retention period set for each cluster. After the retention period is over the application automatically deletes the backup. To stop the management costs on the cluster, you can also contact EDB's BigAnimal support team. Otherwise, the costs accumulate way too much.

Delete your cluster:

Go to the clusters page in the portal.
To delete the cluster perform one of the following actions:
- Select the Delete cluster icon in the row for the cluster.
- Select the cluster you want to delete. From quick actions on the cluster details page, select the delete option.
Confirm that you want to delete the cluster. Once the process finishes, the cluster is listed on the Deleted tab of the Clusters page.
To stop the associated management costs, contact EDB's support team.

Restoring your cluster:

You can also restore your deleted cluster for as long as the backup is available.

Select the Delete tab on the clusters page in the BigAnimal portal.
Select the restore icon for the cluster you want to restore.
On the cluster restore page review the selections in the cluster summary. Select the restored cluster to begin the restore process.

When the process is completed, the restored cluster is available on the Clusters page.

References:

Managing regions in BigAnimal

Talha Munir 🇵🇸 — Sat, 04 Nov 2023 05:54:20 +0000

In this blog, we will be discussing how we can manage regions in BigAnimal. If you are using BigAnimal's cloud account, you don't need to activate regions, you can simply suspend and delete them.

Activate a new region

When you activate a region, BigAnimal prepares the amount of resources required to deploy clusters. These added resources definitely lead to increased cloud costs. You must activate a region before creating or restoring a cluster.

Every activated region shows a status. The status is only available when you try to restore or create new cluster tabs. You can also activate the region before time on the regions page.

To activate a region before creating a cluster, go to the regions page.
You will be prompted to set up your cloud server provider if it has not been set up yet.
Select activate new region and select your cloud provider and the region you want to activate. You can activate multiple regions where you have plans on adding clusters.
Lastly just select activate regions.

Suspend, reactivate, or delete a region

Before you suspend or delete a region you must delete all clusters in that region.

On the left panel, select regions. You will see a list of previously activated regions appear.
Select the icons next to a region to either:
- Suspend or delete an active region
- Reactivate a suspended region

Region status references

These are various region statuses that will be visible in different states:

Activating: The status activating appears when you have activated the region and BigAnimal is setting up the compute and networking resources to support the cluster you just requested. Backups and logs of the cluster are not yet available.
Active: BigAnimal has completed setup up all the prerequisites and has also set up computing and networking resources. The region has at least one cluster but you can create more, the region now has backups and logs available.
Suspended: You have deleted active clusters, Big Animal will then remove the active allocated compute and networking resources. The backups and logs are still available.
Deleted: You have deleted the clusters. BigAnimal has removed the computing and networking resources. The backups and logs are no longer available. One account owner can delete a region.
Error: Any error occurred while trying to activate or suspend a region. The cause can be a quota issue. You can also contact EDB support if you need some additional help in solving the issues you are facing.

references:

Modifying your cluster

Talha Munir 🇵🇸 — Thu, 26 Oct 2023 06:56:40 +0000

Modifying your cluster

Big Animal provides you the opportunity to modify the cluster by editing its configuration settings.
You can also modify the clusters by installing Postgres extensions.

Modifying Cluster's Configuration Settings

Sign in to the BigAnimal portal
On the clusters page, You will see the list of clusters. Select the name of the cluster you want to edit.
From the top right corner of the Cluster Info panel select the edit cluster option.
You can modify some of the following settings on the tab of the edit cluster page. I have separated the settings with respect to different tabs. ### Cluster Info tab:
- Cluster type setting: You cannot shift from a high availability cluster or a single node cluster to a distributed high availability cluster or vice versa.
- Number of Replicas.

Cluster Settings tab:

Instance Type: Changing instance types can lead to higher infrastructure charges.
Volume Type: You cannot switch between the io2 and io2 Block Express volume types in an AWS cluster.
Volume Properties: It can take up to six hours to tune IOPS or resize the disks of your cluster because AWS requires a cooldown period after volume modifications.
Networking type: If you are using Azure and previously set up a private link and want to change to a public network you must remove the private link resources before making the change.
Cluster Name and Password.

Data Groups tab:

Nodes: After the creation of the cluster, you cannot change the number of data nodes.

DB Configuration tab:

Database configuration parameters: If you are using faraway replicas, only a small subset of parameters are editable. These parameters need to be modified in the replica when increased in the replica's source cluster.

Additional Settings tab:

Retention period for backups.
Custom maintenance window: You can set or modify the maintenance window where maintenance upgrade occurs for the cluster.
Read-only workloads: Enabling read-only workloads can lead to higher cloud infrastructure charges.
PgBouncer: Enabling the feature of pg bouncer can also lead to higher infrastructure costs.
IAM(Identity access management): Turning on the ability to log into the Postgres using IAM credentials.
Super User access: Disabling the option removes superuser access for edb_admin but any other superusers existing in the database retain their superuser privileges.

Modify a data group:

You can modify the data groups in your distributed high-availability cluster by editing the configuration settings. You can follow the below steps to modify a data group.

Sign in to the BigAnimal portal.
On the clusters page, you will be shown a list of data groups, select the data group you want to edit.
Select the Edit, option next to the data group.
Edit the cluster settings in the Data Groups tab.
Select save.
Confirm the changes in the pop-up window.

References:

Faraway Replicas-part 3

Talha Munir 🇵🇸 — Wed, 25 Oct 2023 12:06:00 +0000

In this part 3 of the replica series we will be discussing how we can control the replicas using CLI commands.
You can view first 2 parts over here
part1
part2

Faraway Replicas CLI commands

You can use replica-specific CLI commands to create, promote, and get information on faraway replicas.

To update we use faraway-replica update
To delete we use faraway-replica delete
To restore we use faraway-replica restore

Create a faraway replica:

You use the faraway-replica create command to create a replica from CLI. You can either use interactive mode or a config file. The example below uses interactive mode.

biganimal faraway-replica create

You will be prompted to confirm that either you want to create the faraway replica. After the faraway replica creation process is complete, it generates a replica cluster-ID.

Get information on faraway replicas

You can use the faraway-replica show-connected command to get information on faraway clusters for a specified source cluster. You can use either interactive mode or specify the settings with flags on the CLI.
The example below shows the interactive mode.

biganimal faraway-replica show-connected

The output will show the provider region, cluster name, and connected faraway replicas.

Promote a faraway replica

You can use the faraway-replica promote command to promote an existing replica to a standalone single-node standby high-availability cluster. You can use either interactive mode or specify the settings with flags on the CLI.

biganimal faraway-replica promote

You will be prompted to confirm whether you want to promote the faraway replica. It will generate a cluster ID when the promotion process is completed.

IAM authentication CLI commands

To create a cluster that is enabled for IAM authentication, set the --iam-authentication flag on cluster create command to yes or in the configuration file to true.

To change the IAM authentication setting after creating a cluster use the --iam-authentication flag on the cluster update command.

To change the IAM authentication setting when restoring a cluster, use the --iam-authentication flag on the cluster restore command.

Logging and metrics CLI command

You can get the URLs to access metrics and logs in your cloud providers blob storage solution using cluster show-monitoring-urls CLI command.

For single-node and primary/standby high-availability clusters, the syntax of the command is:

biganimal cluster show-monitoring-urls {--id | --provider --region \
                                       --name} [--metrics] [--logs]

FOr distributed high availability clusters, the syntax of the command is:

biganimal pgd show-group-monitoring-urls {--id --group-id} [--metrics] \
                                         [--logs]

Maintenance Windows CLI command

You can also set and view maintenance windows using the command cluster set-maintenance-window and cluster get-maintenance-window CLI commands.
The syntax of the command is:

biganimal cluster [set|get]-maintenance-window 
                                     {--id | --provider --region --name}
                                     {--enable} [--start-day --start-time]

References:

Faraway Replicas-part 2

Talha Munir 🇵🇸 — Wed, 25 Oct 2023 10:34:28 +0000

In the second part of faraway replicas, we will discuss how you can delete and restore a faraway replica. You can view part 1 here

Delete a faraway Replica:

You can delete the replica by following below steps:

Sign in to the BigAnimal portal.
Go to the cluster page. You will see a list of all previously created clusters.
Select the cluster with the replica you want to delete.
Select the replica you want to delete.
Select the delete icon to delete the replica.
Confirm the checkbox that you want to permanently delete the replica. The replica will be deleted.

Restore a replica backup:

You can also restore a replica backup on a standalone cluster. A new standalone cluster is created and initialized with data from the replica backup. Following are the steps on restoring the backup of a replica.

Select the cluster with the replica that you are willing to restore on the clusters page in the big animal portal.
In the overview tab, You can see the cluster replicas under faraway replicas. Select the Replica.
From quick actions click on Restore Replica.
On the restore replica page.
- Fill in the required fields.
- Select now on the calendar to restore to the last possible recovery point.
- review your selection and select restore to begin the restore process.
The new standalone cluster is now available on the clusters page.

References:

Faraway Replica - part 1

Talha Munir 🇵🇸 — Wed, 25 Oct 2023 10:18:45 +0000

Managing Faraway Replicas in Big Animal:

You can create replicas in different active regions in your cloud. A great feature provided by Big Animal is that there is no limit on the number of faraway replicas for each cluster. However, the addition of resources and data transfers can increase your cloud costs.

Create a faraway Replica:

Below are the steps for creating replicas in your cloud.

Go to the clusters page, You will see a list of previously created clusters.
Select the Create replica icon next to a cluster to create its faraway replica. You will be shown a create faraway replica page.
You cannot edit your cloud provider details because they are automatically inherited.
Move to the cluster setting tab and enter a name for the replica in the cluster name field.
Skip to the region section and select any of the active regions where you will be deploying your replica. The password and database type values are inherited from the source cluster.
Select the instance type in the instance type section.
Select the storage settings in the storage section.
In the networking section you can also specify whether you want to use private or public networking.
On the DB configuration tab keep the inherited values or increase them to avoid any kind of replication issues.
- max_connections
- max_locks_per_transaction
- max_prepared_transactions
- max_wal_senders
- max_worker_processes
On the additional settings tab, in the backups section, change the default replica backup retention period of 30 days using the retention time controls. You can configure the retention period as follows:
- 1-180 days
- 1-25 weeks
- 1-6 months
Enable identity and access management authentication to turn on the ability to log in to Postgres using your AWS IAM credentials.
Select Create Replica and your replica will be created.

Promote a faraway replica:

You can also promote a faraway replica to a full-fledged cluster, which makes it capable of accepting writes. The new cluster created is different from the source cluster and it doesn't replace the source cluster.

Go to the clusters page. You will be able to see previously created clusters.
Select the cluster with the replica you want to promote. In the overview tab, you can see cluter's replica under faraway replicas.
Select Promote Faraway Replica icon next to the replica you want to promote.
The cluster settings are populated with values inherited from the source cluster. You can edit the cluster settings while creating your cluster.
Select promote replica

References:

Managing Postgres extensions

Talha Munir 🇵🇸 — Wed, 25 Oct 2023 09:31:35 +0000

Big Animal supports many Postgres extensions. Some of the extensions can be viewed over here

Extensions in BigAnimal:

Extensions are available when you use your cloud account.
Installing Postgres extensions requires superuser privileges.
If you use your cloud account you can grant superuser privileges to edb_admin so that you can install these extensions on your cluster.

List of extensions:

A list of extensions that can be installed using your cloud account is given below:

auth_delay
bloom
btree_gin (PostgreSQL v12 only)
btree_gist (PostgreSQL v12 only)
citext (PostgreSQL v12 only)
cube (PostgreSQL v12 only)
earthdistance
fuzzystrmatch
hstore (PostgreSQL v12 only)
hstore_plperl
intagg
intarray (PostgreSQL v12 only)
isn (PostgreSQL v12 only)
jsonb_plperl (PostgreSQL v12 only)
ltree (PostgreSQL v12 only)
old_snapshot (PostgreSQL and EDB Postgres Advanced Server v14 and v15 only)
pageinspect
pg_buffercache
pg_freespacemap
pg_prewarm
pg_stat_statements
pg_surgery (PostgreSQL and EDB Postgres Advanced Server v14 and v15 only)
pg_trgm (PostgreSQL v12 and EDB Postgres Advanced Server v15 only)
pg_visibility
pgaudit (PostgreSQL only)
pgcrypto (PostgreSQL v12 only)
pglogical3 (PostgreSQL v12 and v13 only)
pgrowlocks
pgstattupple
postgres_fdw
seg (PostgreSQL v12 only)
sslinfo
tablefunc (PostgreSQL v12 only)
tcn (PostgreSQL v12 only)
tsm_system_rows (PostgreSQL v12 only)
tsm_system_time (PostgreSQL v12 only)
unaccent (PostgreSQL v12 only)
uuid-ossp (PostgreSQL v12 only)
xml2
spi - autoinc
spi - refint
spi - insert_username
spi - moddatetime

Installing extensions:

Use the create_extension command to install extensions. You can enable edb_pg_tuner on the DB configuration tab of the Create or Edit Cluster page of the big animal portal. The following example shows how you can install multiple extensions simultaneously.

Step 1:

Create a text file containing the create extension command for each of the extensions you want to install. In this example, the file is named createExtension.sql

CREATE EXTENSION <extension_name_1> SCHEMA <schema_name>;
CREATE EXTENSION <extension_name_2> SCHEMA <schema_name>;
CREATE EXTENSION <extension_name_3> SCHEMA <schema_name>;
CREATE EXTENSION <extension_name_4> SCHEMA <schema_name>;

Step 2:

Use your Postgres client of choice to load the extensions.

psql <biganimal_connection_string> -f createExtension.sql

Removing Extensions:

You can remove extensions using Drop Extensions command.

Retrieving list of extensions:

You can view the list of all Postgres extensions using pg_available_extension

Pg extension catalog:

The Catalog_pg_extension catalog stores all the information about the installed extensions.

References:

What can a pg_ba_admin do and what they can't

Talha Munir 🇵🇸 — Wed, 25 Oct 2023 08:20:52 +0000

Now we will discuss what a pg_ba_admin role can do, and what actions are out of the reach of the user with this role.

What can a pg_ba admin role do:
A user with a pg_ba_admin role can perform many operations typically reserved for the Postgres superuser. For example, they can:

Create roles
Create Databases
Create, modify, and delete any non-superuser role which also includes the setting of passwords.
SET Another role for any user except the superuser without the need for a password.
Read the data from all the Postgres system catalogs in pg_catalog.
Read and write to all user objects such as tables, views, etc.
Perform all monitoring functions on the Postgres instance.
Install supported extensions for EDB.
Grant pg_checkpoint role to itself and to other users.

What a pg_ba_admin can't do:
There are certain roles and actions that can't be performed by pg_ba_admin. Some of them are mentioned below:

Can't execute programs on the server.
Can't Create tablespaces, casts, operator classes, access methods, and text search templates.
Can't read or write files on the server. pg_ba_admin does not have permission to read or write server files.
Can't Define LeakProof functions.
Can't install unsupported extensions.
Can't execute set session authorization.
Can't execute reindex system catalogs.
Can't create a language with custom handlers.

Creating a distributed high-availability cluster

Talha Munir 🇵🇸 — Mon, 25 Sep 2023 06:49:27 +0000

We will now discuss on how can we create a high availability cluster.
When you create a distributed high-availability cluster, you will need to setup a data group.
The cluster can contain one or two data groups:

After you have specified the type on the cluster information tab and your cluster name and password on cluster settings tab you can select next: Data Groups.
On Nodes settings tab in the nodes section, you can either select two data nodes or three data nodes.
In the database type section you can select the postgres you want to use in the postgres type field.
select the settings for your cluster. You can find the instructions of node settings in the following tabs: cluster settings tab and additional settings tab
In the parameters section on DB configuration tab, you can update the value of the database configuration parameters.
Select Create: Data group. the data group preview will appear.
Finish the process by selecting create cluster. If you want to create a second data group you can select add a data group.

Creating a second data group:

After creating the first data group, you can also create a second data group for your distributed high availability cluster by selecting add a data group before you create the cluster.

By default the second data group also has same settings as your first data group. However, you can still change the settings if you want to. Just know that the settings change can change the settings for the entire cluster. The database type and cloud provider should be same in both data groups.

References:

Cluster Architecture

Talha Munir 🇵🇸 — Mon, 25 Sep 2023 06:33:50 +0000

Previously we discussed the making of clusters in the big animal application.
In this article, we will be discussing the cluster architecture.
The Big Animal clusters can be exposed to client applications in two ways:

Public-The cluster is easily available on the internet
Private-The IP address is private to the VNet or VPC service that is hosting your BigAnimal services. By default, it isn't routable from other networks.

Basic Architecture:

The application initially deploys the VNET in Azure, an amazon virtual private cloud in AWS, or a google VPC in google cloud which are then used to host clusters and their supporting management services.

Load Balancing:

The Big Animal uses the following resources to make routing decisions and distributing requests.

Standard Load Balancer in Azure:

When we create a cluster is created with public network access, a load balancer is created. The load balancer is configured with a public IP address. Once an IP address is assigned it is not changed until we change the network configuration of our cluster. The load balancer always routes to the leader of your cluster.

Amazon network load balancer in AWS:

BigAnimal also creates a new load balancer for each cluster and tags it with the cluster-ID using following format/
service.k8s.aws/stack: default/<cluster_ID>

Since the load balancer IP address in AWS is dynamic, make sure that your application uses the correct DNS name to access the network load balancer of a particular cluster. In your application's AWS account:

Select the load balancers service.
Search for the load balancer with the cluster ID you want to access. 3- Use the DNS name to access the cluster.

Google Load balancer in google cloud:

Big Animal creates a new load balancer using the premium network service. tier for each cluster and tags it using a unique identifier.
The corresponding front end forwarding rule uses the same unique identifier and includes the cluster-ID in the following format.
{"kubernetes.io/service-name":"default/<cluster_ID>-<service_type>"}

Since the load balancer IP address used in google cloud is dynamic, make sure your application uses the correct DNS name to access the network load balancer of a particular cluster.

References:

DB configuration and Additional settings tab in EDB BigAnimal

Talha Munir 🇵🇸 — Tue, 12 Sep 2023 09:59:42 +0000

In the series of creating a cluster this is the last article on the topic.

DB configuration Tab:

You will see the below screen:

In the parameters section, you can update the value of the database configuration parameters as needed.

Additional Settings Tab:

In additional settings tab you will see below options, you can easily set them according to your convenience.

Backups:

You can change the default database backup retention period of 30 days by using the backup retention time control.
You configure the retention period as follows:

1-180 days
1 - 25 weeks
1 - 6 months Big animal deletes backups older than the retention period.

Maintenance:

This settings enable the custom maintenance window option and uses the controls to set a weekly 60-minute maintenance window in which maintenance upgrades occur for the cluster. If you don't set a window, the updates are applied at EDB's discretion with prior notification.

Connections:

- Read-only workloads:
Enable Read-only Workloads. This feature directs read-only operations exclusively toward replicas. If this option is enabled, you might have to raise the IP address resource limits for the cluster:

For Azure, the IP address quota is Standard Public IP Address.
For AWS, the IP address quota is Elastic IP. You might also have to increase the Network Load Balancers per Region value.

Pg Bouncer:

You can enable PG bouncer, that can help you to manage your Postgres connections and will help your workloads run more efficiently.

Access:

Identity and access management:

You can enable IAM authentication to turn on the ability to log in to Postgres using your AWS IAM credentials.

Super User Access:

Enable superuser access to grant super user privileges to the edb_admin role. This option is available for single-node and primary/standby high availability clusters.