Backing up and restoring a SQL/Protect database v16
Backing up a database that's configured with SQL/Protect and then restoring the backup file to a new database requires considerations in addition to those normally associated with backup and restore procedures. These added considerations are mainly due to the use of object identification numbers (OIDs) in the SQL/Protect tables.
Note
This information applies if your backup and restore procedures result in re-creating database objects in the new database with new OIDs, such as when using the pg_dump
backup program.
If you're backing up your EDB Postgres Advanced Server database server by using the operating system’s copy utility to create a binary image of the EDB Postgres Advanced Server data files (file system backup method), then this information doesn't apply.
Object identification numbers in SQL/Protect tables
SQL/Protect uses two tables, edb_sql_protect
and edb_sql_protect_rel
, to store information on database objects such as databases, roles, and relations. References to these database objects in these tables are done using the objects’ OIDs, not their text names. The OID is a numeric data type used by EDB Postgres Advanced Server to uniquely identify each database object.
When a database object is created, EDB Postgres Advanced Server assigns an OID to the object, which is then used when a reference to the object is needed in the database catalogs. If you create the same database object in two databases, such as a table with the same CREATE TABLE
statement, each table is assigned a different OID in each database.
In a backup and restore operation that results in re-creating the backed-up database objects, the restored objects end up with different OIDs in the new database from what they were assigned in the original database. As a result, the OIDs referencing databases, roles, and relations stored in the edb_sql_protect
and edb_sql_protect_rel
tables are no longer valid when these tables are dumped to a backup file and then restored to a new database.
Two functions, export_sqlprotect
and import_sqlprotect
, are used specifically for backing up and restoring SQL/Protect tables to ensure the OIDs in the SQL/Protect tables reference the correct database objects after the tables are restored.
Backing up the database
Back up a database that was configured with SQL/Protect.
Create a backup file using
pg_dump
.This example shows a plain-text backup file named
/tmp/edb.dmp
created from databaseedb
using thepg_dump
utility program:
Connect to the database as a superuser, and export the SQL/Protect data using the
export_sqlprotect('sqlprotect_file')
function.sqlprotect_file
is the fully qualified path to a file where the SQL/Protect data is saved.The
enterprisedb
operating system account (postgres
if you installed EDB Postgres Advanced Server in PostgreSQL compatibility mode) must have read and write access to the directory specified insqlprotect_file
.
The files /tmp/edb.dmp
and /tmp/sqlprotect.dmp
comprise your total database backup.
Restoring from the backup files
Restore the backup file to the new database.
This example uses the
psql
utility program to restore the plain-text backup file/tmp/edb.dmp
to a newly created database namednewdb
:
Connect to the new database as a superuser, and delete all rows from the
edb_sql_protect_rel
table.This deletion removes any existing rows in the
edb_sql_protect_rel
table that were backed up from the original database. These rows don't contain the correct OIDs relative to the database where the backup file was restored.
Delete all rows from the
edb_sql_protect
table.This deletion removes any existing rows in the
edb_sql_protect
table that were backed up from the original database. These rows don't contain the correct OIDs relative to the database where the backup file was restored.
Delete any of the database's statistics.
This deletion removes any existing statistics for the database to which you're restoring the backup. The following query displays any existing statistics:
For each row that appears in the preceding query, use the drop_stats
function, specifying the role name of the entry.
For example, if a row appeared with appuser
in the username
column, issue the following command to remove it:
Delete any of the database's offending queries.
This deletion removes any existing queries for the database to which you're restoring the backup. This query displays any existing queries:
For each row that appears in the preceding query, use the drop_queries
function, specifying the role name of the entry. For example, if a row appeared with appuser
in the username
column, issue the following command to remove it:
Make sure the role names that were protected by SQL/Protect in the original database are in the database server where the new database resides.
If the original and new databases reside in the same database server, then you don't need to do anything if you didn't delete any of these roles from the database server.
Run the function
import_sqlprotect('sqlprotect_file')
, wheresqlprotect_file
is the fully qualified path to the file you created in Step 2 of Backing up the database.
Tables edb_sql_protect
and edb_sql_protect_rel
are populated with entries containing the OIDs of the database objects as assigned in the new database. The statistics view edb_sql_protect_stats
also displays the statistics imported from the original database.
The SQL/Protect tables and statistics are properly restored for this database. Use the following queries on the EDB Postgres Advanced Server system catalogs to verify:
Note the following about the columns in tables edb_sql_protect
and edb_sql_protect_rel
:
dbid. Matches the value in the
oid
column frompg_database
fornewdb
.roleid. Matches the value in the
oid
column frompg_roles
forappuser
.Also, in table
edb_sql_protect_rel
, the values in therelid
column match the values in theoid
column ofpg_class
for relationsdept
andappuser_tab
.
- Verify that the SQL/Protect configuration parameters are set as desired in the
postgresql.conf
file for the database server running the new database. Restart the database server or reload the configuration file as appropriate.
You can now monitor the database using SQL/Protect.