Understanding Backup Methodologies for PSQL Data 


Continuous Operations

Let me start with the basics.  Continuous Operations provides the ability to backup data files while database applications are running and users are connected.  This means that if you have one or more data files open during the backup, you are able to backup said data file(s), without concern for possible corruption.  Although I will not cover all the possible causes for corruption, I will say that backing up your data file while the application also has the file open is one possible way to cause corruption.

Continuous Operations can be invoked using the command line utility, Butil.

Here is an example, using the Demodata database file, BILLING.MKD:

BUTIL –STARTBU C:\Demodata\BILLING.MKD

Once I have run this command, if I look in the data directory where BILLING.MKD resides, I will find both the original data file, BILLING.MKD, and a new file, BILLING.^^^.  This new file, with the ^^^ extension, is what we call a Delta file.  When a PSQL data file has been put into Continuous Operations mode, the PSQL MicroKernel engine writes data to the Delta file, instead of writing to the original data file.  This means that, in the above example, that the PSQL engine will write any changes to the data file to BILLING.^^^, not to BILLING.MKD.  This allows us to backup BILLING.MKD because the PSQL engine is not writing to it.

If you have multiple files you need to put into Continuous Operations mode, you can create a list file, which is simply an ASCII file, containing a list of the files (with the proper paths to the files).  This list file can be called as an option for the –STARTBU command.  Here is an example using a list file, “startlist.fil”:
BUTIL –STARTBU @startlist.fil

Here is the resulting output:

Btrieve Maintenance Utility 10.10.125.000
Copyright (C) Pervasive Software Inc. 2008
All Rights Reserved.

BUTIL-139: Depending on the number of files being processed, this request may take several minutes.

The command completed successfully.



In order to take a file out of Continuous Operations mode, you will need to use the –ENDBU option.  Here is an example:

BUTIL – ENDBU C:\Demodata\BILLING.MKD

To take all files out of Continuous Operations mode, use the following command:

BUTIL –ENDBU /A

We could also use the same list file we used to put multiple files into Continuous Operations mode, in order to take those same files out of Continuous Operations mode:

BUTIL –ENDBU @startlist.fil

When files are taken out of Continuous Operations mode, the changes that were written to those files will be rolled into the original data file.  The PSQL engine manages this process and it requires no user involvement.

Backup Agent

If managing the commands, paths, and list files associated with Continuous Operations sounds a bit daunting, then we have a solution for you.

Pervasive Backup Agent provides a simple to use interface for Continuous Operations.  It provides both GUI and command line interfaces, allowing customers to easily enable or disable Continuous Operations.

One of the benefits of the Pervasive Backup Agent is that once enabled, it will place all currently open PSQL data files into the Continuous Operations mode.  It will also place any newly created files, or files that are opened after it has been enabled into the Continuous Operations mode.  Backup Agent is the safest and easiest way to ensure your data files are ready to be backed up.

Pervasive Backup Agent runs on the same system as the PSQL engine (Workgroup or Server) that is hosting the data files.  Typically, this is going to be your primary server, where the database files reside.

Here is what the Backup Agent’s GUI interface looks like:


Backup-Agent-Tech-Tip-August-2009

 

As you can see, the interface is very simple.  You only need to click “Start” to enable the Backup Agent.


Backup-Agent-2-Tech-Tip-August-2009
 

When Backup Agent is enabled, the GUI interface will present the user with the option to “Stop” it, or view the log file.  Notice that the GUI interface provides the status at the bottom of the screen, in this case indicating that we have stopped the utility, and that the data stored in the delta files is being merged into the original data files.

For those that prefer to use the command line interface, Backup Agent provides some very simple commands:

To enable Backup Agent:
pvbackup -on (32-bit)
pvbackup64 -on (64-bit)

To disable Backup Agent:
pvbackup -off (32-bit)
pvbackup64 -off (64-bit)

To get the current status of Backup Agent:
pvbackup -status (32-bit)
pvbackup64 -status (64-bit)

NOTE: When running Pervasive Backup Agent on Linux, you must use the command line interface to manage the utility.

Backup Agent works with PSQL Server for Windows and Linux (32-bit and 64-bit) and with PSQL Workgroup.


If you’d like to see how easy Backup Agent is to use, download a trial.

DataExchange

If your data backup needs are a bit more demanding, and you want to maintain a real time backup copy of your data, then DataExchange will be a good fit for you.

DataExchange is Pervasive’s data replication solution, providing the means to reliably move data between two or more Pervasive PSQL databases to maintain a current backup system, drive data into reporting servers, or synchronize multiple remote databases.

DataExchange uses a ‘replication network’ concept to manage the systems that will be included in the data replication process.

We can replicate from a production server to a backup server, which would look something like this:

 PSQL2-Tech-Tip-August-2009


DataExchange can also meet more demanding needs, such as bi-directional, one-to-many, or many-to-one replication configurations:


PSQL-Tech-Tip-August-2009
 

DataExchange provides a wizard to help expedite the process of setting up a replication deployment.

Users who want more control over the replication deployment can use the DXDEPLOY method, which uses .xml files for configuration.  This method offers additional flexibility, such as which files to include/exclude from the replication deployment.

Once the initial deployment has been performed, including the synchronization of the databases in the replication network, only delta changes to the data files are replicated.

Download the Pervasive DataExchange v3.0 trial to see if it is the right solution for you.

Now that you know the various options Pervasive Software provides for maintaining a safe backup of your PSQL data, you can determine the best solution to fit

August 2009 Tech Tip

Welcome to Insights PSQL Edition sponsored by Pervasive Software. PSQL Edition is your monthly newsletter providing news on recent trends, helpful tech tips, product info, and industry news on database technology.
Pervasive

Embeddable Data Management and Integration