Saturday, May 28, 2022
Home Integration/Interfaces Vacuum Your PostgreSQL mirthdb and Prevent Disk Storage Issues Today

Vacuum Your PostgreSQL mirthdb and Prevent Disk Storage Issues Today

Prerequisites

  • Basic knowledge of PostgreSQL
  • PostgreSQL installed and configured as Mirth’s database

Things to Know Beforehand

  • Vacuuming a PostgreSQL database will first increase your drive size before making it smaller. Make sure you have sufficient room to complete the vacuum before beginning.
  • This article is not intended to be a comprehensive overview of PostgreSQL’s vacuuming feature, but rather, to demonstrate a basic vacuum to clean up the database’s dead rows for Mirth.

Preforming a Vacuum

If you are using PostgreSQL as your Mirth database (or any DBMS for that matter) then you will want to monitor disk storage on your Mirth server. Depending on the environment’s message volume, available disk space, the message storage level, and data pruner settings, disk space can fill up rather quickly.

Mirth Connect Administrator typically won’t even start if your disk space fills up completely. Moreover, Mirth would not be able to store any messages on the drive. This will likely not be an issue with a Test system because of the lack of volume coming through. However, if this happens to your Production system you will obviously want to get it resolved as soon as possible.

The way PostgreSQL works is that when data is deleted from the database, the data is removed but disk space is not freed up. Check out the following from PostgreSQL’s website:

“In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. This approach is necessary to gain the benefits of multiversion concurrency control… the row version must not be deleted while it is still potentially visible to other transactions. But eventually, an outdated or deleted row version is no longer of interest to any transaction. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. This is done by running VACUUM.” 1

Click here for more information on how this process works. The documentation also goes over potential solutions to the need to regularly vacuum a PostgreSQL database.

If you need to manually vacuum a PostgreSQL database you can easily do it via pgAdmin, PostgreSQL’s in-house graphical user interface (GUI) for interacting with the database.

1. Open pgAdmin

Note that your installation location could differ especially if you are running Mirth on a Windows server.

2. Enter the master password for your PostgreSQL database and click ‘OK’. This is the same password you set when you installed and created your database.

3. Expand Servers on the left-hand side.

4. Expand Databases on the left-hand side.

4. Right-click on mirthdb.

5. Select Maintenance.

6. Make sure the Maintenance operation is set to ‘VACUUM’, Vacuum’s ‘FULL’ option is set to Yes (if you need to do a full vacuum), and Verbose Messages is set to ‘Yes’.

7. Click ‘OK’. Depending on what version of PostgreSQL you are running, the process will either start immediately or PostgreSQL will create the maintenance job. pgAdmin should notify you once the maintenance is complete.

Note that you can watch your drive space grow/shrink as the vacuuming process completes.

8. Once the vacuum is done the ‘Running…’ message will say ‘Successfully completed’.

9. Check your drive space and confirm that space was freed up.

More Information

There are many more options when it comes to configuring vacuuming for your PostgreSQL database. One of these is the autovacuum feature. As its name implies, the feature automatically performs a vacuum periodically.

PostgreSQL’s autovacuum feature should be turned on by default. However, you can verify that with the following query.

SELECT NAME,SETTING FROM PG_SETTINGS WHERE NAME='autovacuum';

PostgreSQL has some additional documentation that specifically goes over autovacuum amongst other features. Click here now to learn more.

  1. Routine Vacuuming
RELATED ARTICLES

SQL Basics for Healthcare Professionals You Must Learn Now

Prerequisites Basic knowledge of what a database isBasic knowledge of the relational database modelBasic knowledge of the healthcare industry SQL stands for Structured Query Language and...

Why You Must Learn Mirth Connect’s User API Now

Prerequisites Basic knowledge of Mirth ConnectBasic knowledge of JavaScriptBasic knowledge of software documentation and APIsBasic knowledge of Java development NextGen provides several different APIs (Application Programming...

Leverage Mirth’s Channel Scripts Now

Prerequisites Basic knowledge of Mirth ConnectBasic knowledge of JavaScript Each Mirth Connect channel gives you the ability to leverage four different scripts that have different triggers,...

Most Popular

Mirth Connect Extensions: What You Need to Know

Prerequisistes Basic Knowledge of Mirth Connect Overview Users can navigate to Mirth Connect's Extensions screen by selecting Extensions on the left-hand side. You should now see the following...

Take a Tour of System Settings in Mirth Connect Now

Prerequisites Basic Knowledge of Mirth Connect Introduction Mirth Connect has a multitude of system settings that are available for configuration. In larger companies, there will often be...

Why You Must Master Mirth’s Message Template Tool Today

Prerequisuites Basic Knowledge of Mirth ConnectKnowledge of Programming in Mirth Connect Introduction When writing scripts in Mirth Connect, you have a message template tool that allows you...

Make Sense of HL7 v2 Messages Effortlessly Now

Prerequisites Beginner Knowledge of HL7 Introduction If you've been in Healthcare Information Technology for any period of time, you have definitely heard of HL7 (Health Level 7)....

Recent Comments