Personal blog of Yzmir Ramirez

Encryption is the process of turning data into an unrecognizable format unless the necessary password (also known as passphrase) or decryption key is provided.

This blog describes how to encrypt the pgBackRest repository. pgBackRest is the backup tool used to perform Postgres database backup, restoration, and point-in-time recovery (PITR). The repository is where pgBackRest stores backups and archives WAL segments.

pgBackRest will encrypt the repository based on a user-provided password, thereby preventing unauthorized access to data stored within the repository.

In this demonstration, it is assumed that the pgBackRest is already installed and configured on the dedicated backup node and configured to take backups from the remote database node. The repository will be configured with a cipher type and key to demonstrate encryption. 

Follow the below steps to encrypt the pgBackRest repository:

Backup node 172.20.20.20 (Dummy IP) 

Remote DB node  172.15.15.15 (Dummy IP)

1) First, generate the cipher key. pgBackRest will use this cipher key to encrypt the pgBackRest repository.

It is important to use a long, random passphrase for the cipher key. A good way to generate one is to run: openssl rand -base64 48. (on the backup node):

postgres@ip-172.20.20.20:~$ openssl rand -base64 48
PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co
postgres@ip-172.20.20.20:~$

2) On the backup node, add the cipher type and key parameters in the pgBackRest configuration file. /etc/pgbackrest.conf:

vi /etc/pgbackrest.conf
repo1-cipher-pass=PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co
repo1-cipher-type=aes-256-cbc

If you have an existing pgbackrest setup, then the existing stanza cannot be used after configuring the encryption for the repository. A new stanza needs to be created for taking the backup in the encryption-enabled repository. Using the existing stanza will result in the following error:

In the below example, the existing stanza dbtest has been used.

postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest --log-level-console=info backup --type=full
2023-09-08 14:22:06.178 P00   INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=5971-2fe78c47 
--log-level-console=info --log-level-file=debug--pg1-host=172.15.15.15 --pg1-host-user=postgres --pg1-path=/var/lib/postgresql/15/main
 --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest
 --repo1-retention-full=2 --stanza=dbtest --start-fast --stop-auto --type=full
ERROR: [095]: unable to load info file '/var/lib/pgbackrest/backup/dbtest/backup.info' or '/var/lib/pgbackrest/backup/dbtest/
backup.info.copy':
       CryptoError: cipher header invalid
       HINT: is or was the repo encrypted?
       CryptoError: cipher header invalid
       HINT: is or was the repo encrypted?
       HINT: backup.info cannot be opened and is required to perform a backup.
       HINT: has a stanza-create been performed?
2023-09-08 14:22:06.180 P00   INFO: backup command end: aborted with exception [095]
postgres@ip-172.20.20.20:~$

The pgBackRest configuration files will look like this after adding the cypher pass (key) and type.

Backup node

cat /etc/pgbackrest.conf:
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
stop-auto=y
repo1-cipher-pass=PNaf798o9Sz1RRRRRRRRhH62R1BSQal+lAxpb3ZTAblNPTxC72E1nAcQGVwn40co
repo1-cipher-type=aes-256-cbc
[dbtest_new]
pg1-path=/var/lib/postgresql/15/main
pg1-host=172.15.15.15
pg1-host-user=postgres

DB node

cat /etc/pgbackrest.conf:
[global]
repo1-path=/var/lib/pgbackrest
repo1-host=172.20.20.20
repo1-host-user=postgres
process-max=2
log-level-console=info
log-level-file=debug
[dbtest_new]
pg1-path=/var/lib/postgresql/15/main

3) Create a new stanza (on the backup node):

postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new  stanza-create
2023-09-08 14:24:55.779 P00   INFO: stanza-create command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=5980-f29c6484
 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres
 --pg1-path=/var/lib/postgresql/15/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc
 --repo1-path=/var/lib/pgbackrest --stanza=dbtest_new
2023-09-08 14:24:56.927 P00   INFO: stanza-create for stanza 'dbtest_new' on repo1
2023-09-08 14:24:57.045 P00   INFO: stanza-create command end: completed successfully (1269ms)
postgres@ip-172.20.20.20:~$

4) Update the archive_command with the new stanza details on the DB node:

postgres=# ALTER SYSTEM SET archive_command = '/bin/pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new archive-push %p';
ALTER SYSTEM

5) Reload the Postgres cluster (on the DB node):

postgres=# select pg_reload_conf();
pg_reload_conf
----------------
 t
(1 row)

6) Execute the check command. The check command validates that pgBackRest and the archive_command setting are configured correctly for archiving and backups for the specified stanza:

postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info check
2023-09-08 15:26:34.349 P00   INFO: check command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=7993-5acde7b9
 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres
 --pg1-path=/var/lib/postgresql/15/main --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc
 --repo1-path=/var/lib/pgbackrest --stanza=dbtest_new
2023-09-08 15:26:35.585 P00   INFO: check repo1 configuration (primary)
2023-09-08 15:26:35.788 P00   INFO: check repo1 archive for WAL (primary)
2023-09-08 15:26:36.990 P00   INFO: WAL segment 000000010000000000000018 successfully archived to 
'/var/lib/pgbackrest/archive/dbtest_new/15-1/0000000100000000/000000010000000000000018-7cef04977b8b50f102a3d74ace8ab1cc4a035c8d.gz' 
on repo1
2023-09-08 15:26:37.092 P00   INFO: check command end: completed successfully (2745ms)
postgres@ip-172.20.20.20:~$

7) Perform a FULL backup:

postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info backup --type=full
2023-09-08 15:26:49.028 P00   INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8060-e6fa0627
 --log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres
 --pg1-path=/var/lib/postgresql/15/main --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc
 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new --start-fast --stop-auto --type=full
2023-09-08 15:26:50.016 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-09-08 15:26:50.622 P00   INFO: backup start archive = 00000001000000000000001A, lsn = 0/1A000028
2023-09-08 15:26:50.622 P00   INFO: check archive for prior segment 000000010000000000000019
2023-09-08 15:26:54.242 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-09-08 15:26:54.447 P00   INFO: backup stop archive = 00000001000000000000001A, lsn = 0/1A000100
2023-09-08 15:26:54.454 P00   INFO: check archive for segment(s) 00000001000000000000001A:00000001000000000000001A
2023-09-08 15:26:54.970 P00   INFO: new backup label = 20230908-152649F
2023-09-08 15:26:55.024 P00   INFO: full backup size = 22.0MB, file total = 961
2023-09-08 15:26:55.024 P00   INFO: backup command end: completed successfully (5999ms)
2023-09-08 15:26:55.025 P00   INFO: expire command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8060-e6fa0627
 --log-level-console=info --log-level-file=debug --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc
 --repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new
2023-09-08 15:26:55.026 P00   INFO: repo1: expire full backup 20230908-145538F
2023-09-08 15:26:55.035 P00   INFO: repo1: remove expired backup 20230908-145538F
2023-09-08 15:26:55.068 P00   INFO: repo1: 15-1 remove archive, start = 000000010000000000000015, stop = 000000010000000000000016
2023-09-08 15:26:55.068 P00   INFO: expire command end: completed successfully (43ms)
postgres@ip-172.20.20.20:~$

8) Perform a DIFFERENTIAL backup (optional step):

postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new --log-level-console=info backup --type=diff
2023-09-08 15:27:01.723 P00   INFO: backup command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8216-4d363fc8 
--log-level-console=info --log-level-file=debug --pg1-host=172.15.15.15 --pg1-host-user=postgres 
--pg1-path=/var/lib/postgresql/15/main --process-max=2 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc 
--repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new --start-fast --stop-auto --type=diff
2023-09-08 15:27:02.644 P00   INFO: last backup label = 20230908-152649F, version = 2.47
2023-09-08 15:27:02.645 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2023-09-08 15:27:03.250 P00   INFO: backup start archive = 00000001000000000000001C, lsn = 0/1C000028
2023-09-08 15:27:03.251 P00   INFO: check archive for prior segment 00000001000000000000001B
2023-09-08 15:27:04.810 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2023-09-08 15:27:05.012 P00   INFO: backup stop archive = 00000001000000000000001C, lsn = 0/1C000100
2023-09-08 15:27:05.017 P00   INFO: check archive for segment(s) 00000001000000000000001C:00000001000000000000001C
2023-09-08 15:27:05.536 P00   INFO: new backup label = 20230908-152649F_20230908-152702D
2023-09-08 15:27:05.591 P00   INFO: diff backup size = 8.3KB, file total = 961
2023-09-08 15:27:05.592 P00   INFO: backup command end: completed successfully (3872ms)
2023-09-08 15:27:05.592 P00   INFO: expire command begin 2.47: --config=/etc/pgbackrest.conf --exec-id=8216-4d363fc8 
--log-level-console=info --log-level-file=debug --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc 
--repo1-path=/var/lib/pgbackrest --repo1-retention-full=2 --stanza=dbtest_new
2023-09-08 15:27:05.602 P00   INFO: repo1: 15-1 no archive to remove
2023-09-08 15:27:05.603 P00   INFO: expire command end: completed successfully (11ms)
postgres@ip-172.20.20.20:~$

9) To check out the backup status and its details, use the info command:

postgres@ip-172.20.20.20:~$ pgbackrest --config=/etc/pgbackrest.conf --stanza=dbtest_new info
stanza: dbtest_new
    status: ok
    cipher: aes-256-cbc
    db (current)
        wal archive min/max (15): 000000010000000000000017/00000001000000000000001C
        full backup: 20230908-151854F
            timestamp start/stop: 2023-09-08 15:18:54+00 / 2023-09-08 15:18:59+00
            wal start/stop: 000000010000000000000017 / 000000010000000000000017
            database size: 22.0MB, database backup size: 22.0MB
            repo1: backup set size: 2.9MB, backup size: 2.9MB
        full backup: 20230908-152649F
            timestamp start/stop: 2023-09-08 15:26:49+00 / 2023-09-08 15:26:54+00
            wal start/stop: 00000001000000000000001A / 00000001000000000000001A
            database size: 22.0MB, database backup size: 22.0MB
            repo1: backup set size: 2.9MB, backup size: 2.9MB
        diff backup: 20230908-152649F_20230908-152702D
            timestamp start/stop: 2023-09-08 15:27:02+00 / 2023-09-08 15:27:04+00
            wal start/stop: 00000001000000000000001C / 00000001000000000000001C
            database size: 22.0MB, database backup size: 8.3KB
            repo1: backup set size: 2.9MB, backup size: 512B
            backup reference list: 20230908-152649F
postgres@ip-172.20.20.20:~$

Conclusion

pgBackRest is a completely free and open source backup tool available for PostgreSQL, and here we have seen the steps to encrypt the pgBackRest repository. You may use it for personal or commercial purposes without any restrictions whatsoever. Its advanced backup repository encryption feature is very easy to implement. 

To learn more about the pgBackRest backup tool and encryption, click the links below: 

• https://pgbackrest.org/user-guide.html
• https://pgbackrest.org/user-guide.html#quickstart/configure-encryption

Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.

Download Percona Distribution for PostgreSQL Today!

Today we are talking about The Needs Review Queue Initiative, What it is, and How it’s helping to improve Drupal with guest Stephen Mustgrave. We’ll also cover Translation Management Tool as our module of the week.

For show notes visit: www.talkingDrupal.com/426

Topics

• Can you give an overview of Needs Review Issue Queue Initiative
• Is the bug smash initiative related to the needs review issue queue
• Is this the same as the needs review bot
• How many issues were in the Needs Review status when you started
• How many issues today
• How long did it take until it was manageable
• How long do items stay on average
• Who else is helping
• Let’s talk through the pagination heading level issue
• What help can the community provide
• How does someone get involved
• Do you think this helps with burnout for core committers
• What’s the future of the initiative

Resources

• Needs Review Queue Bot
• Needs Review Issue Queue
• Pagination heading level issue

Guests

Stephen Mustgrave – smustgrave

Hosts

Nic Laflin – nLighteneddevelopment.com nicxvan John Picozzi – epam.com johnpicozzi Melissa Bent – linkedin.com/in/melissabent merauluka

MOTW Correspondent

Martin Anderson-Clutz – @mandclu Translation Management Tool (TMGMT)

• Brief description:
  • Have you ever wanted to automate the process of creating content translations on your Drupal site? There’s a module for that.
• Brief history
  • How old: created in Jan 2012
  • Versions available: 7.x-1.0-rc3 and 8.x-1.15, the latter of which works with Drupal 9 and 10
• Maintainership
  • Actively maintained
  • Test coverage
  • Documentation
  • Number of open issues: 595, 139 of which are bugs against the 8.x branch
• Usage stats:
  • 8,766 sites
• Maintainer(s):
  • Berdir, a very prolific maintainer in his own right, who also supports well known projects like Search API, Token, Paragraphs, and many more
• Module features and usage
  • Provides a tool set for automating the process of creating translations for your site content, as well as strings used within the site like menus, interface text, and so on
  • Works with more than 30 translation service providers, including many that leverage human translators, but also AI-based services like DeepL and OpenAI
  • Also has a plugin system to determine what text needs to be translated, so it can be easily adapted to very custom needs
  • With the module installed that Translate tab on your nodes changes to have buttons to request a translation in each language
  • Once a translation has been requested, it will run through states like unprocessed, active, and finished
  • Also provides an option for Continuous Translation, where new and updated content is automatically submitted for translation
  • Allows for professional translation at scale, using whatever kind of service works best for your site
  • The need for robust translation capabilities is what originally got me started using Drupal, so it’s great to see that there are enterprise-grade options for sites that need to manage translations at scale

Today we are talking about the a new Drupal Book Modernizing Drupal 10 Theme Development, What’s new in Drupal 10 theming, and tools that can help speed up theming with guest Luca Lusso. We’ll also cover Admin Dialogs as our module of the week.

For show notes visit: www.talkingDrupal.com/425

Topics

• Why write a book about Drupal theming
• How does the book modernize theming
• Who is the book for
• Do you have to have a certain level of knowledge to start
• What are some new aspects of Drupal 10 that are covered in the book
• Does the book talk about:
  • Javascript frameworks
  • Native Web Components
• What tools outside of Drupal do you talk about
• How did you conduct your research
• Do you have plans to keep the github updated
• How long did it take to write the book
• Tech moves quickly, what is the shelf-life of the book
• Future editions
• Purchase from Amazon or Packt
• Translation
• Plans for another book

Resources

• Admin Dialog Blog
• Book github
• Buy the book

Guests Hosts

Nic Laflin – nLighteneddevelopment.com nicxvan John Picozzi – epam.com johnpicozzi Melissa Bent – linkedin.com/in/melissabent merauluka

MOTW Correspondent

Jacob Rockowitz – @jrockowitz Admin Dialogs

• Brief description: (from the maintainer)
  • The Admin Dialogs module improves the UI by reducing the number of page loads. For example, instead of opening a delete confirmation page, the module will show the form in a dialog (modal) form.
  • https://www.chapterthree.com/blog/improve-drupal-admin-ui-new-admin-dialogs-module
• Brief history
  • How old: Created on May 2023
  • Versions available: 1.0.x stable release
  • Last release: 1.0.17 – July 12, 2023
• Maintainership
  • Actively maintained? Yes
  • Number of open issues: 6
  • Test coverage
    • No test coverage
    • Module is fairly simple and easy to manually test
    • Code quality is very good
• Usage stats:
  • sites 150+
• Maintainer(s):
  • Minnur Yunusov (minnur)
  • https://www.drupal.org/u/minnur
  • https://www.minnur.com/
• Sponsor
  • Chapter Three
• Module features and usage
  • Comes with the ability to add modal or off-canvas dialogs to different links in Drupal.
  • Easy to use. Most features available after installing the module.
  • Adds controls dialog type for operation links like Edit, Delete etc.
  • Adds and controls dialog type for local tasks.
  • Adds and controls dialog types for local actions.
  • Ability to add dialogs via specified A tag paths.
  • Ability to add dialogs via specifying CSS selectors (classes and IDs).
  • Adds option to control delete button dialog.
  • You can add support for your modules by adding configs created in the module.
  • Experimental: Add loading spinner to form submit elements on form submit.
• Discussion
  • The module does one thing and does it really well
  • Require no initial configuration.
  • Worth reviewing common administration tasks for contributed modules and deciding if a modal dialogs or sidebar can improve the admin UX.

Although the introduction of physical backups in Percona Backup for MongoDB (PBM) made it possible to significantly cut the restore time for big datasets, it’s hard to beat snapshots in speed and efficiency. That’s why we introduced external backups (aka snapshot-based backup API) in PBM 2.2.0

The idea came from the requests to bring EBS snapshots into PBM. But we decided that, instead of focusing on a particular type, we would give users the ability to leverage their own backup strategies to meet their needs and environment.

How Percona’s snapshot-based backup API works

What PBM does during physical backup can be genuinely split into three phases. 

First, it prepares the database and ensures the data files can be safely copied. Then, it copies files to the storage. And lastly, it returns the database to the non-backup state — closes backup cursors, etc. Something similar happens with the restore: prepare the cluster, copy files, and prepare data so the cluster can start in a consistent state.  For more details, you can see the Tech Peek section in our blog post on physical backups, but for the purposes of this blog, those details don’t matter.

The new API literally breaks up the backup and restore process into these three stages. Giving the user full control over the data copy. So it can be either EBS or any other snapshot, or ‘cp -Rp’ … or whatever fits your needs.

To start, just run pbm backup -t external. PBM will notify when the data will be ready for copying with the prompt saying from exactly which node on each shard it should be done (backup needs to be done only from one node on each replica set). Then, when the snapshot(s) (data copy) is done, you have to tell PBM to finish the backup with pbm backup-finish <backup_name>. 

And that’s it. Restore follows the pattern. Start the restore with pbm restore [backup_name] -external, copy data files to every node of the corresponding replica set in the cluster when PBM prepares everything, and finish the restore with pbm restore-finish.

Backup:

Restore:

Restore your existing snapshots with PBM

The great thing is that you can restore snapshots taken without PBM. PBM creates backup metadata during an external backup, and if [backup_name] is provided for the restore, it will use it to check backup compatibility with the target cluster in terms of topology and PSMDB version and to define “restore-to-time.” But restore can be run with no [backup_name] perfectly fine. Just the checks will be omitted (that’s on you), and the “restore-to-time” will be picked from the provided data. PBM will look into the actual data during the restore and define the most recent common cluster time across all shards. Just be mindful that there is not much we can check and ensure for non-PBM snapshots. Another thing that PBM might need regarding the backup data is MongoDB storage options. These are preserved in the backup metadata as well, but in case of an existing snapshot, you can pass it via –config flag.

We are looking into leveraging snapshot-based backups in our tools like Percona Operator for MongoDB and Percona Monitoring and Management.

This feature was released as Technical Preview so that we can adjust it in further iterations following your feedback. So, give it a try and either leave your thoughts on the forum or file a Jira ticket.

Give Percona Backup for MongoDB a try

Today we are talking about the Web Sustainability Guidelines, How sustainability applies to the web, and how your website can be more sustainable with guests Mike Gifford and Andy Blum. We’ll also cover LB Plus as our module of the week.

For show notes visit: www.talkingDrupal.com/424

Topics

• What are the Web sustainability guidelines
• Do they only apply to environmental impact
• When we think about sustainability we think of funding, does WSG speak to that
• Why are the WSG important
• What is the best way to implement WSG
• How do the WSG’s apply to Drupal
• Have the WSG’s been finalized
• Are they open source
• How can someone get involved

Resources

• Web Sustainability Guidelines
• Carbon footprint of your website
• Eggcorns
• Andy’s blog post
• w3c/sustyweb
• Sustainable web design working group
• Green Web Foundation

Guests

Mike Gifford – mgifford.medium.com @mgifford

Hosts

Nic Laflin – nLighteneddevelopment.com nicxvan John Picozzi – epam.com johnpicozzi Melissa Bent – linkedin.com/in/melissabent merauluka

MOTW Correspondent

Martin Anderson-Clutz – @mandclu Layout Builder Plus

• Brief description:
  • Have you ever wanted to make Layout Builder easier and more intuitive for content creators? There are a few modules that can help with that, but today we’re going to talk about one called Layout Builder Plus
• Brief history
  • How old: Originally created in Apr 2022
  • Versions available: 2.0.1 release, compatible with Drupal 10 and 11
• Maintainership
  • Actively maintained, latest release just a week ago
  • Number of open issues: 2, both bugs, but both marked as fixed
• Usage stats:
  • 9 sites
• Maintainer(s):
  • Tim Bozeman of Tag1
• Module features and usage
  • Provides an overhaul of the Layout Builder UI, to make it easier for content creators:
  • Show a curated list of promoted blocks with icons, with lesser-used blocks available in a separate tab
  • Once a block is placed it shows automatically generated content, instead of asking the user to fill out a form before they can see what it will look like
  • Editing the content of a block happens in an overlay instead of the settings tray, so it can use more of the screen
  • Moves the Save Layout and other action buttons to the bottom of the page
  • Also adds some nice capabilities to Layout Builder, including:
  • Drag and drop entire sections
  • Change the layout of an existing section, even if it has blocks in it
  • Clone and update existing blocks
  • Finally, it includes a submodule to integrate with the Section Library module, which allows for a section within a layout to be saved so it can be reused again and again
  • I’ll also note that this is a module nominated by one of our listeners in the #talkingdrupal channel of the Drupal slack workspace, so if there’s a module you’d like to hear about in this segment, drop us a note in there

To maintain competitiveness and operational efficiency — not to mention ensure security and compliance when your database version reaches End of Life (EOL)  – it’s crucial to upgrade your database systems from time to time. When it comes to undertaking this considerable (and potentially time-consuming) project, you generally have one of two options: DIY upgrades or a database upgrade service. Each approach has its own set of advantages and challenges that can significantly impact the success of your upgrade.

Do-it-yourself (DIY) database upgrades vs a database upgrade consulting services

DIY upgrades

A DIY approach to database upgrades is often tempting for organizations looking to minimize costs and maintain control over their systems. The DIY route provides a hands-on experience and a deeper understanding of the internal workings of your database. It can be a good fit for those with in-house expertise capable of managing the complexities involved in an upgrade. The potential for cost savings is also an advantage — DIY means not having to hire (or pay) anyone else. 

However, the challenges of DIY upgrades are significant. They require a substantial time investment and a steep learning curve, especially for complex databases or those without an in-house IT team with the necessary skills. There’s also a higher risk of errors, which can lead to downtime, data loss, and security vulnerabilities. Plus, there’s the risk that if things go truly sideways, you can end up spending a lot more money than you saved by not hiring a database upgrade consulting service. 

Database upgrade consulting services 

Opting for a professional upgrade service, on the other hand, provides expertise and a structured approach to your upgrade. These services come with teams of experts who are well-versed in the latest technologies and can offer insights into best practices — and anticipate any potential challenges —  for your specific database environment.

One of the most compelling reasons to choose a professional service is the reduced risk. Upgrade services often have proven methodologies, robust testing procedures, and backup strategies to ensure a smooth transition with minimal downtime. This can be particularly important for businesses that rely heavily on their database for daily operations.

Additionally, choosing a professional service can be more cost-effective in the long run. While there is an upfront cost, the expertise provided can prevent costly mistakes and inefficiencies. These services can also often complete upgrades faster than an internal team might, allowing your staff to focus on core business activities rather than the intricacies of a database upgrade.

Choosing the right database upgrade consulting service

The significance of choosing the right database upgrade consulting service cannot be overstated. It’s about finding a partner that understands the nuances of your data environment and has the expertise to enhance its function without interrupting your business operations. The reliability of the service provider should be your north star. This includes their track record, the robustness of their infrastructure, and their ability to deliver on promises without causing disruptions to your business.

Understanding your specific systems 

Compatibility with existing systems is critical. An upgrade isn’t just about the latest features; it’s about ensuring those new features work harmoniously with your existing applications and software. The right service provider will have a deep understanding of different database architectures and will be adept at integrating new technologies with legacy systems.  Better yet, choose a provider with multi-database expertise. 

Minimizing downtime and performance disruptions 

Minimizing downtime and performance disruptions is a top priority. The best database service providers use sophisticated tools and techniques to ensure that upgrades are seamless, often executed with zero downtime strategies.

Service reliability

Service reliability goes beyond mere uptime. It encompasses the provider’s ability to foresee challenges and mitigate them proactively. It’s the assurance that your database is in capable hands, maintained by experts who ensure it operates at peak efficiency.

Customer support and expertise 

Excellent customer support and expertise are non-negotiable. The right database upgrade service offers access to a team of experts who can provide insightful consultations and rapid responses to your concerns. This ensures that any issue is resolved swiftly, keeping your database running smoothly.

Effective security measures 

In today’s digital landscape, security measures in place are as crucial as the database’s performance. The upgrade service must demonstrate a comprehensive approach to security, safeguarding your data against all forms of vulnerabilities and ensuring compliance with global data protection regulations.

Why Percona stands out for database upgrade consulting services

At Percona, our unique blend of technical excellence, open source commitment, technology-agnostic expertise, and an unwavering focus on customer success sets us apart. 

We’ve been successfully upgrading databases across various industries and database types for nearly two decades. Our approach reduces unnecessary expenditures, maximizes resource utilization, and minimizes downtime. You’ll benefit from a comprehensive upgrade service that aligns with your budget and goals 

Learn more about Percona upgrade services

Percona is a leading provider of unbiased, performance-first, open source database solutions that allow organizations to easily, securely, and affordably maintain business agility, minimize risks, and stay competitive, free from vendor lock-in. Percona software is designed for peak performance, uncompromised security, limitless scalability, and disaster-proofed availability.

Our Release Roundups showcase the latest Percona software updates, tools, and features to help you manage and deploy our software. It offers highlights, critical information, links to the full release notes, and direct links to the software or service itself to download.

Today’s post includes those releases and updates that have come out since October 31, 2023. Take a look.

Updated Release of Percona Distribution for PostgreSQL 16.0

The updated release of Percona Distribution for PostgreSQL 16.0 came out on November 2, 2023. It includes the Docker images for x86_64 architectures and aims to simplify the developers’ experience with the Distribution. Refer to the Docker guide for how to run Percona Distribution for PostgreSQL in Docker.

Download the Updated Release of Percona Distribution for PostgreSQL 16.0

Percona Distribution for MySQL (PXC-based variation) 8.0.34

Percona Distribution for MySQL (PXC-based variation) 8.0.34 was released on November 1, 2023. It is the most stable, scalable, and secure open source MySQL distribution, with two download options: one based on Percona Server for MySQL and one based on Percona XtraDB Cluster. This release is focused on the Percona XtraDB Cluster-based deployment variation and is based on Percona XtraDB Cluster 8.0.34

Download Percona Distribution for MySQL (PXC-based variation) 8.0.34

Percona XtraDB Cluster 8.0.34-26

On November 1, 2023, Percona XtraDB Cluster 8.0.34-26 was released. It supports critical business applications in your public, private, or hybrid cloud environment. Our free, open source, enterprise-grade solution includes the high availability and security features your business requires to meet your customer expectations and business goals. Percona XtraDB Cluster implements telemetry that fills in the gaps in our understanding of how you use Percona XtraDB Cluster to improve our products. Participation in the anonymous program is optional. You can opt-out if you prefer not to share this information. Find more information in the Telemetry on Percona XtraDB Cluster document.

Download Percona XtraDB Cluster 8.0.34-26

Percona XtraBackup 8.1.0-1

Percona XtraBackup 8.1.0-1 was released on November 2, 2023. It is a 100% open source backup solution for all versions of Percona Server for MySQL and MySQL® that performs online non-blocking, tightly compressed, highly secure full backups on transactional systems. This is an Innovation release. This type of release is only supported for a short time and is designed to be used in an environment with fast upgrade cycles. Developers and DBAs are exposed to the latest features and improvements.

Download Percona XtraBackup 8.1.0-1

Percona Distribution for MongoDB 5.0.22

On November 9, 2023, we released Percona Distribution for MongoDB 5.0.22, a freely available MongoDB database alternative, giving you a single solution that combines enterprise components from the open source community, designed and tested to work together. It includes Percona Server for MongoDB and Percona Backup for MongoDB.

Bug fixes and improvements provided by MongoDB are included in Percona Server for MongoDB, and there are several enhancements to Percona Backup for MongoDB 2.3.0. Please review the release notes.

Download Percona Distribution for MongoDB 5.0.22

Percona Server for MongoDB 5.0.22-19

Percona Server for MongoDB 5.0.22-19 was released on November 9, 2023. It is an enhanced, source-available, and highly-scalable database that is a fully-compatible, drop-in replacement for MongoDB 5.0.22 Community Edition, supporting MongoDB 5.0.22 protocols and drivers. A release highlight is that you can now configure the retry behavior for Percona Server for MongoDB to connect to the KMIP server when using data-at-rest encryption.

Download Percona Server for MongoDB 5.0.22-19 

That’s it for this roundup, and be sure to follow us on Twitter to stay up-to-date on the most recent releases! Percona is a leader in providing best-of-breed enterprise-class support, consulting, managed services, training, and software for MySQL, MongoDB, PostgreSQL, MariaDB, and other open source databases in on-premises and cloud environments and is trusted by global brands to unify, monitor, manage, secure, and optimize their database environments.

If you have any experience working with database software, you have undoubtedly heard the term Kubernetes a lot. You may already be using it daily and find it makes running applications in the cloud much more manageable. But for those who are not so familiar, in this post, we will discuss how Kubernetes has emerged as the unsung hero in an industry where agility and scalability are critical success factors.

At its core, Kubernetes (often abbreviated as K8s) is an open source tool that automates the deployment, scaling, and management of containerized applications. It simplifies infrastructure management and is the driving force behind many cloud-native applications and services.

For some background, Kubernetes was created by Google and is currently maintained by the Cloud Native Computing Foundation (CNCF). It has become the industry standard for cloud-native container orchestration.

To get you started with Kubernetes, this post offers a high-level introduction to its core features, highlighting its built-in advantages and showcasing some practical, real-world uses.  It’s important to note that this overview is not intended to be an exhaustive guide to every facet of Kubernetes. Kubernetes can be complex, which is why we offer comprehensive training that equips you and your team with the expertise and skills to manage database configurations, implement industry best practices, and carry out efficient backup and recovery procedures.

Making container deployment simpler

One of Kubernetes’ primary use cases is deploying containerized apps. Applications are packaged into a single, lightweight container with their dependencies, typically including the application’s code, customizations, libraries, and runtime environment. These containers encapsulate all the components necessary to run the application independently, making them portable and consistent across various environments. Kubernetes manages and orchestrates these containers, handling tasks such as deployment, scaling, load balancing, and networking.

With Kubernetes, you can define and manage your application deployments declaratively, meaning you can tell it how your apps should operate, and Kubernetes takes care of the rest.

Maintaining high availability

Kubernetes also makes it easier for applications to scale in response to changing workloads to maintain high availability. Applications can be horizontally scaled with Kubernetes by adding or deleting containers based on resource allocation and incoming traffic demands. It distributes the load among containers and nodes automatically, ensuring that your application can handle any spike in traffic without the need for manual intervention from an IT staff.

In addition, Kubernetes provides features like continuous monitoring, self-healing capabilities (automatically replacing containers if they happen to fail), and rolling updates (gradual updates of your applications), ensuring that your applications are always available — even in the face of failures or updates. And, if an issue ever does arise from an update, Kubernetes enables quick rollbacks to a previous version, preventing extended outages.

Multi-cloud deployments

Kubernetes is cloud-agnostic, freeing your applications from being tied to a single cloud provider. Your workloads, encapsulated in containers, can be deployed freely across different clouds or your own hardware. This portability ensures that your applications remain consistent and operational, regardless of where they are deployed.

Because of this flexibility, businesses may choose the infrastructure that best meets their needs. Instead of depending on the services of a single cloud vendor, Kubernetes provides the flexibility to distribute workloads over various cloud providers or on-premises infrastructure, reducing the risk of becoming trapped in a single ecosystem. This makes it much easier for organizations to transition to open source solutions, reduce costs, and avoid vendor lock-in.

Controlled access and security

A wide range of security capabilities are available with Kubernetes, which are intended to safeguard your clusters’ integrity and containerized applications. These consist of the following but are not restricted to:

• Network policies: Establishing communication guidelines between pods provides additional protection at the network level. Essential features include Allow and Deny Rules, Ingress and Egress Rules, and Segmentation and Isolation. These are all intended to make it harder for someone to compromise other pods, even if they manage to gain access to one.
• Role-Based Access Control (RBAC): RBAC manages permissions to ensure that only individuals, programs, or processes with the proper authorization can utilize particular resources. In essence, it establishes permissions within a Kubernetes cluster.
• Secrets management: To guarantee that your applications can safely access the credentials they require, Kubernetes additionally offers a secure means of storing sensitive data, including configuration data, passwords, and API keys.
  

Cost and resource efficiency

One of Kubernetes’ main advantages is its efficient use of resources. By putting several containers onto a single node, it optimizes without over-provisioning and provides tools for setting resource limits and requests for containers. When you combine all of this, you can ensure that your apps are allotted the appropriate amount of CPU and memory in the most effective way possible, saving you money and improving performance.

Continuous Integration and Deployment (CI/CD)

Kubernetes is a dependable base for continuous integration and delivery pipelines and not just a platform for orchestrating containers. It can be easily combined with continuous integration/continuous delivery (CI/CD) tools to automate the building, testing, and deployment of applications, facilitating the release of new versions/updates and code across numerous servers — with no downtime — enhancing development workflows and providing for quicker time-to-market for your applications.

Efficient microservices

The technique of creating applications using microservices architecture makes it possible to divide an application into smaller, independently deployable services, which simplifies development and maintenance. These services, known as “microservices,” can interact with one another through APIs and are each intended to carry out a particular task or feature of the application.

Management of stateful applications

Although stateless applications are frequently associated with Kubernetes, this flexible platform can also effectively handle stateful workloads, such as database management. For companies looking to streamline stateful application management while maintaining high availability and data integrity, Kubernetes is an excellent option because of its automation, scalability, and dependability

Real-world Kubernetes use cases

Numerous industries, including finance, healthcare, e-commerce, education, etc., have adopted Kubernetes. It is an invaluable tool for resolving complicated issues and streamlining processes due to its flexibility and scalability. Here are a few examples of actual Kubernetes use cases across various industries:

E-commerce: Traffic to e-commerce platforms varies, particularly during sales, promotions, and holidays. The seamless shopping experience is ensured by Kubernetes, which assists in automatically scaling web applications to handle this increased load.

Entertainment: To guarantee that viewers have continuous access to films and TV series, film studios and television networks rely on Kubernetes for content delivery and streaming. Just consider the sheer number of people who stream Netflix every night!

Finance: Financial institutions utilize Kubernetes for its security and compliance features. It facilitates simple scalability and effective disaster recovery and enables them to deploy and administer applications in a highly regulated environment.

Education: Universities often use Kubernetes for projects that require substantial resources because it scales computing clusters for research, data analysis, and simulations.

Healthcare: Hospitals and healthcare providers use Kubernetes for processing and analyzing vast amounts of health data so that medical records, lab results, and patient information are protected and available when needed.

Telecommunications: By guaranteeing low-latency communication, Kubernetes assists the telecom sector in quickly deploying 5G and edge computing applications.

Conclusion

Kubernetes’ versatility and scalability empower organizations to streamline and simplify their containerized workloads while enhancing scalability, ensuring high availability, and improving resource efficiency. It is the go-to choice for businesses looking to move to the cloud, embrace microservices, and deploy applications across various environments, all while avoiding the high cost of vendor lock-in. 

Are you interested in learning more about Kubernetes or need assistance with your cloud-native strategy? With Percona Kubernetes Operators, you can manage database workloads on any supported Kubernetes cluster running in private, public, hybrid, or multi-cloud environments. They are 100% open source, free from vendor lock-in, usage restrictions, and expensive contracts, and include enterprise-ready features by default.

Learn more about Percona Kubernetes Operators

Discover how Percona Operator for MongoDB transformed one of our valued customers’ experience with Kubernetes. With Percona as their trusted partner, they’ve experienced reliable solutions and outstanding support for their database needs.

FAQ

What are some common use cases for Kubernetes?

Kubernetes is widely used for managing containerized applications, making it suitable for various scenarios, including web services, microservices, and data processing applications.

Is Kubernetes suitable for small businesses, or is it primarily for large enterprises?

All sizes of businesses can benefit from Kubernetes. Small businesses can also benefit from its features, like automation and resource optimization, even though large corporations frequently use it for complex workloads.

Can Kubernetes work with different cloud providers?

Yes, Kubernetes offers the flexibility and portability of your app since it is cloud-agnostic and compatible with multiple cloud providers.

In this blog post, we will discuss how to extend Percona Monitoring and Management (PMM) to get PostgreSQL metrics on checkpointing activity, internal buffers, and WAL usage. With this data, we’ll be able to better understand and tune our Postgres servers.

We’ll assume there are working PostgreSQL and PMM environments set up already. You can search the blog for more information on this if needed. For generating load, we used pgbench with the following commands:

shell> pgbench -i -s 100 sbtest
shell> pgbench -c 8 -j 3 -T $((60*60*4)) -N -S -P 1 sbtest

Creating a custom query collector

Note: This step will not be needed with soon-to-come postgresql_exporter PMM versions!

The first step is to include a new query that will gather data on WAL usage because this is not yet collected by default on the latest version of PMM. However, do note that only three graphs will use these custom metrics, so if you want to try out the dashboard without doing this customization, it’s also possible for you to do it (at the expense of having a bit less data).

For this, it’s as easy as executing the following (in the PMM client node that is monitoring the Postgres servers):

cd /usr/local/percona/pmm2/collectors/custom-queries/postgresql/high-resolution/
cat<<EOF >queries-postgres-wal.yml
# ######################################################
# This query gets information related to WAL LSN numbers in bytes, 
# which can be used to monitor the write workload.
# Returns a COUNTER: https://prometheus.io/docs/concepts/metric_types/
# ######################################################
pg_wal_lsn:
  query: "SELECT pg_current_wal_lsn() - '0/0' AS bytes, pg_walfile_name(pg_current_wal_lsn()) as file, pg_current_wal_insert_lsn() - '0/0' AS insert_bytes, pg_current_wal_flush_lsn() - '0/0' AS flush_bytes"
  metrics:
    - bytes:
        usage: "COUNTER"
        description: "WAL LSN (log sequence number) in bytes."
    - file:
        usage: "COUNTER"
        description: "WAL file name being written to."
    - insert_bytes:
        usage: "COUNTER"
        description: "WAL insert LSN (log sequence number) in bytes."
    - flush_bytes:
        usage: "COUNTER"
        description: "WAL flush LSN (log sequence number) in bytes."
EOF
chown pmm-agent:pmm-agent queries-postgres-wal.yml
chmod 660 queries-postgres-wal.yml

This will result in the following new metrics on PMM:

pg_wal_lsn_bytes
pg_wal_lsn_file
pg_wal_lsn_insert_bytes
pg_wal_lsn_flush_bytes

Importing the custom PMM dashboard

For viewing these new metrics, we will import a new custom PMM dashboard that will provide this information in a structured and easy-to-understand way. This can be done in two easy steps (since I’ve uploaded it to Grafana Labs).

1. Click on the Dashboards -> Import menu: 

2. Import via grafana.com with ID number (19600):

3. Select the PostgreSQL folder and Metrics, and import it:

After this, you’ll be taken to the dashboard, which I generally “star” for easy access later on:

Using the new dashboard

The dashboard is divided into three main areas, all of which have relevant configurations at the top and graphs following them. The main idea is to be able to easily detect what the server is doing at any point in time so we can better understand how it reacts to the workload and tune accordingly. These graphs and panels not only show changes in usage and workload patterns but also changes in configuration, which can help during root cause analysis and performance reviews.

Checkpointing section

Here, we will find everything related to checkpointing: when are checkpoints started (and due to what) and when they finish. Checkpointing has a lot of impact on the write throughput/utilization by Postgres, so it’s important to make sure that it’s not being triggered before needed.

In the following example, we can see how at the beginning, there were many instances of forced checkpointing, not only because the checkpointer was running more often than checkpoint_timeout seconds but because of the kind of metric we can see in the Checkpoints graph (requested vs. scheduled). We can’t see it yet because the WAL graphs are at the bottom, but this was fixed after we increased the value for max_wal_size.

Additionally, after the metrics refresh, we can see that the Checkpoint Timeout stat panel shows another change closer to the end of our currently selected time range:

This means that we have also changed the checkpoint_timeout value. This was done only after our workload was more stable and there were no more forced checkpoints being issued.

Buffers section

This section holds configurations and metrics related to reads to and writes from the shared buffers. Again, demonstrating it by example, let’s see how much of an impact correctly sizing the shared_buffers has on our test workload. It’s set at 128Mb by default, which is hardly enough for any serious workload and will mean that we should see a lot of churn in both reads and writes until we increase it. The only downside is that modifying shared_buffers needs a restart, so before doing that, we can increase bgwriter_lru_maxpages (which doesn’t need a restart) to avoid the writer stopping each round and get a bit more performance out of it at the expense of I/O.

We can clearly see how, at first, there were constant reads into the shared buffers, and the background writer was doing most of the writes. Additionally, we can see the positive impact on increasing bgwriter_lru_maxpages because that process is not being throttled anymore. After we increased shared_buffers and restarted Postgres, the reads decreased to almost having to read nothing from the page cache or disks, and the writes are no longer done by the background writer but by the checkpointer.

WAL usage section

The last section pertains to write-ahead logs, which are another source of potential performance bottlenecks. Note that (for now) this is the only section that needs data coming from the custom query collector we added at the beginning, so you can use the rest of the dashboard even if you decide not to include it. PMM already collects the configurations, so we will only miss data from the graphs.

The WAL Writes Per Checkpoint Timeout graph will group the number of bytes that were written in chunks of checkpoint_timeout seconds so we can have a clear view of the expected max_wal_size value. If the chunks (shown as orange bars) exceed the max_wal_size (shown as a red line), it means that there will be forced checkpointing. We can easily dimension max_wal_size knowing that it should be larger than any orange bar, and we can tell when exactly it was changed, following changes in the red line. The WAL Writes graph uses the same metric but is shown as a rate, which can help us pinpoint heavy write times more granularly. Lastly, the WAL Locations graph is included for completeness and shows the different locations for WAL pointers (such as insertion and flush locations), which, according to the documentation, are mainly used for debugging purposes.

Hints and tips

Most graphs contain additional information and links on either the configuration variables or the metrics they show, so they are useful in case we need a bit of help interpreting them. Just hover the mouse over the “i” icon on each of them:

Getting even more data

Starting from PostgreSQL 15, we have a new view on WAL metrics: pg_stat_wal. 

The metrics used in this custom collector and dashboard will work for older versions, but in the future, it will be nice to see what data we can extract from this new view and how we can use it to tune our servers.

Conclusion

This is another example of how tunable and powerful PMM is. Not only because we can add our custom metrics easily but because it already collects many metrics we can use out of the box. Additionally, it’s easy to share new PMM dashboards via the Grafana Labs page by simply publishing it and sharing the ID number. With all this new information at hand, we can now better tune our PostgreSQL instances!

Lastly, we are working closely with the PMM Dev team to include this new dashboard in the PMM server itself, so all these custom steps won’t be needed in future releases. Let us know if you have any comments on it.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

Download Percona Monitoring and Management Today

With the release of Percona XtraBackup 8.0.34-39, Percona XtraBackup (PXB) allows a backup on server version 8.0.35 and higher. We have removed the server version check because the MySQL 8.0 series has transitioned to bug fixes.

A feature change in the MySQL 8.0 series, such as the redo log record structure, could cause PXB to fail when taking a backup. To ensure a valid backup, use a PXB version equal to or higher than your source server version. Complete the process and prepare and restore your backups regularly. Do not assume that, because you have no error messages, that the backup ran successfully. 

Before the backup starts, PXB checks the source server version to the PXB version to prevent a failed or corrupted backup due to source server changes. Percona XtraBackup 8.0.22 added the no-server-version-check option. This option ignored the check so that the backup could continue and could have one of the following consequences:

• Backup failed
• Backup corrupted
• Backup successful

The check had the following scenarios:

• The source server version and the Percona XtraBackup version are the same: the backup continues.
• The source server version is less than the Percona XtraBackup version: the backup continues.
• The source server version is greater than the Percona XtraBackup version: the backup stops and generates an error message.
• The source server version is greater than the Percona XtraBackup version; with the --no-server-version-check option, the backup continues.

With the release of MySQL 8.0.34, the MySQL 8.0 series transitioned to bug fixes. We do not expect any breaking changes that would break backward compatibility. Therefore, Percona XtraBackup 8.0.34 will backup and restore a server version higher than 8.0.34.