Concepts of Database Maintenance
 
Concepts of Database Maintenance
An Introduction to Database Maintenance
PSQL is a comprehensive database management system built around the MicroKernel Database Engine (MKDE). PSQL offers easy installation, uncomplicated maintenance, and high levels of performance and reliability. While PSQL can run for months or years with practically no maintenance, you can get the most out of it by understanding some of its unique features and learning how to perform useful tasks. This manual describes how to tune, configure, and manage your PSQL engine and associated databases.
Configurations
Database Security
Data Archival and Restoration
Troubleshooting
Helpful Utilities
Configurations
You can configure separate settings for the server and client aspects of the database engine. The settings allow you to optimize the performance of the engine based on your business requirements.
The following figure illustrates the flow from an application program to the database files at the operating system. The database engine resides between the application program and the data files.
Figure 1 Conceptual View of Database Engine Configuration
The types of settings that you can configure for server include the following:
Access
Communication protocols
Compatibility with previous versions of the MicroKernel Engine (MKDE)
Data integrity
Debugging
Directories
Memory usage
Performance
The types of settings that you can configure for client include the following:
Access
Communication protocols
Performance
Security
Application characteristics
You configure these settings within the PCC. See Configuration Reference for the tasks and references pertaining to configuration.
Database Security
The access to a PSQL database can be protected in several ways. Administrative-level security is set through the operating system. You can control who can administer a PSQL database with the security mechanisms native to the operating system.
PSQL also provides relational security at the user and group levels. You can control who can access the data and at what capability. For example, for each table within a PSQL database, you can specify whether a user or group can create, select, update, insert into, delete, or alter the table.
You establish security by setting a password for the entire database. At that point, the only user authorized to access the database is a default user named Master. You can then add additional users and groups.
Security can be set within the PCC. Also supported are two SQL statements pertaining to security: GRANT and REVOKE. These two SQL statements also allow you to set security at both the table and the column level.
The GRANT syntax integrates with transactional owner names, allowing owner names to be enforced when using relational access.
See PSQL Security for information pertaining to security, owner names, users, and groups.
Data Archival and Restoration
Backing up data is a routine part of protecting your databases and ensuring disaster recovery. You have several ways in which you can back up and restore your PSQL databases.
If your business allows you to stop all applications that access a PSQL database, you may use any of the operating system utilities, or third-party software, to backup or restore the database files.
Archival logging is another backup method that you can use to supplement operating system utilities. Archival logging allows you to keep a log of all database operations since your last backup. In case of a system failure, you can restore the data files from backup then roll forward the changes from the log file to return the database to the state it was in prior to the system failure.
Continuous operations allows you to backup database files while the database engine is running and users are connected. After starting Continuous Operations, the database engine closes the active data files and stores all changes in temporary data files (called delta files). When the backup is complete, you turn off Continuous Operations. The database engine then reads the delta file and applies all the changes to the original data files.
See Logging, Backup, and Restore for additional information about backing up and restoring databases.
Troubleshooting
The PSQL User's Guide and Getting Started With PSQL manuals both contain troubleshooting information. Getting Started With PSQL contains troubleshooting information pertaining to installing the PSQL products. The User’s Guide contains general troubleshooting information as well as an extensive list of frequently asked questions (FAQs).
Other resources at your disposal for troubleshooting include the Actian Corporation Knowledge Base, which contains information based on actual customer solutions and common problems, and the Support area of the PSQL Web site.
Helpful Utilities
PSQL comes with a variety of utilities designed to help you control and manage your databases. For a list of the primary PSQL utilities, see PSQL User's Guide. Note that some utilities can be excluded if you perform a custom installation. See Getting Started With PSQL.