PostgreSQL is an open-source object-relational database system with a lot of capabilities. It is known for its dependability, feature robustness, and performance. An embedded PostgreSQL database is included with the DashboardFox software.
PostgreSQL must, as with any database, be backed up regularly. Customers must back up PostgreSQL regularly, and many don't know how because they are unfamiliar with the database technology.
DashboardFox does not keep any of our clients' source data; instead, the software accesses their databases directly, whether PostgreSQL, MySQL, Oracle, Microsoft SQL, Dremio, MS Access, or any other ODBC-compatible source.
The only exception is when you’re importing MS Excel or using our new API connections. Data from those sources is stored directly in the PostgreSQL database (which makes having a backup even more important).
DashboardFox saves all of its database connection information, user and security information, report definitions, audit logs, license information, and many other items that keep DashboardFox up and running.
This guide will go through some best practices for backing up PostgreSQL with DashboardFox.
Best Practices for Backing Up the Embedded PostgreSQL Database
In general, we would recommend that you back up DashboardFox’s PostgreSQL database under the following conditions:
- DashboardFox will be upgraded to a new version.
- You want to change any reports or semantic layers in a major way.
- You are actively involved in the report creation process, whether long-term or project-by-project.
- Any significant security modifications have been made or are being made by you.
- You're importing new data from MS Excel or CSV into your apps based on MS Excel or CSV.
So why are these best practices so important? The answer is simple– they are risky scenarios. If you make a mistake during any of these processes, you can always restore your database to a point in time before those changes. It’s basic damage control.
DashboardFox does not keep track of modifications to reports at this time. So, if someone saves a report by accident, or if they remove an important report or folder of reports in the Documents library, the only method to recover them is to restore the PostgreSQL database.
When working on new reports or making significant modifications, it's vital to back up frequently. A nightly backup is recommended at a minimum once you've reached a steady-state production environment.
This may sound time-consuming, but ensuring that your database is error-free and secure requires very little effort.
Recommended Tools for DashboardFox Backups in PostgreSQL Databases
Take a look at some tools and methods we recommend for performing backups.
SQLBackupAndFTP
SQLBackupAndFTP is our preferred tool for performing DashboardFox backups. This tool is low-cost, and the backup and restore process is seamless. Backups can be scheduled multiple times a day, saved locally, and pushed to too many cloud storage backup sites and notifications for errors and outages. This tool has to restore functionalities built-in, so you can seamlessly perform backups and restores and never need to stop the database.
Make A Copy Of The PGSQL Folder Inside “C:\program files(x86)\dashboardfox”
Or, at the very least, the pgsql\data directory. You can do a manual backup each time before you complete any of the other actions listed in this post. C:\program files(x86)\dashboardfox\pgsql can also be a folder that your usual backup program targets, and you can use Windows to construct a script and schedule it to conduct frequent backups. The backup can then be compressed and stored on the cloud using open-source software such as rclone.org.
To duplicate the folder for backup, you don't have to stop and start the PostgreSQL database. To do a restore, we recommend stopping the DashboardFox Database service (and DashboardFox Master Service), renaming the existing folder (to have it for troubleshooting an issue if needed), and replacing it with the backup version. It's fairly straightforward. However, it requires a little more effort than the previous suggestion since it’s manual.
Perform An Image Backup At The File System’s Level
Many backup tools, especially in this age of virtual machines, provide an image-level backup of the entire file system. This works fine because, by default, you’re getting the entire directory we mentioned above as part of the backup.
For restore, however, you’ll need to restore the entire server to copy the pgsql data directory to the original location when restoring the database. In practice, this strategy is rarely used.
Perform an SQL Dump with pg_dump and pg_dumpall
While the database is in use, this strategy enables thorough and consistent backups without preventing other users from accessing the database. It will build a script file with SQL commands for various database objects and data in each table created before the backup begins.
You can back up your database using the pg dumpall and pg dump utilities provided by the database. Because the entire database cluster shares information about roles and tablespaces and does not belong to a separate database, it is not exported. Only the database data in the database cluster is backed up by pg dump.
To finish the job, run pg dump on each database in the cluster and dump global items shared by all databases. This currently covers properties like database users and groups, tablespaces, and access rights for all databases.
Perform Continuous Archiving
A full backup at the file system level is combined with an incremental backup at the Write-ahead Log level. Restore the overall file system level backup file first, then replay the previous backup WAL file to bring the system to a previous state when recovery is required.
You don't require a perfect and consistent file system backup as a starting point. Log replay will rectify any internal irregularities in the backup. Users may use it to play an infinitely long sequence of WAL files, and they can use it to provide a continuous backup by simply archiving WAL files.
What Not to Do When Backing Up a PostgreSQL Database
We don't advocate depending only on backups of the entire server image. You'd have to restore the entire server only to get to a report or a database if you needed to restore it.
We also recommend using a method that copies or stores your backup to a remote storage spot. While having a copy of the backup locally can speed recovery, complete server failures happen, so having a shallow copy can save you a big headache. SQLBackupAndFTP makes that easy.
We also don't advocate simply not taking a backup of your PostgreSQL database. When in a trial, it’s easy to ignore this step, but many of our customers find they take their trial efforts and move them directly into production. When something horrible happens, telling a customer that you can't help them because they don't have a backup is excruciatingly difficult. If you stick to nightly backups, you'll be fine.
How DashboardFox Can Further Help
We think that the article above is enough for you to be convinced that you will use DashboardFox, and it’s going to turn out spectacularly. We have already given you the best ways you can maximize DashboardFox for your PostgreSQL needs, so let’s talk about some of the other things we could not mention.
There are a lot of benefits to DashboardFox, but we will highlight these three major ones and show you why we can offer a lot of things on the table.
For one, we are self-hosted. The PostgreSQL embedded database we mentioned, by default, can only be accessed by DashboardFox locally. It won’t accept a remote connection, so your data is secure. There’s no need to copy your data into a 3rd party network as many Cloud BI providers require.
In short, your security is our priority in DashboardFox.
You can also experience swift responses from our team of experts whenever you encounter issues while using DashboardFox for your data visualization, business intelligence, or other uses. We are very willing to get to the top of things, even offering you screen-sharing sessions so that we can personally fix the problems ourselves.
Feel like a VIP without spending every penny with DashboardFox.
Of course, you might think that these come at a high cost. Nope, you’re mistaken. For one, DashboardFox has no subscription policies. You will only get charged once, and that’s it!
Without worrying about recurring subscriptions, you can use DashboardFox to your heart’s content. Again, you can get this at an affordable price because small and medium-sized businesses also deserve the best quality of service.
Still not convinced? Reach out to us! You are our priority, so don’t waste time and set that meeting with us or that live demo we offer for free. We might even throw in a free trial to push the envelope even further.