Backing up and restoring a SQL/Protect database v15
Backing up a database that is configured with SQL/Protect, and then restoring the backup file to a new database requires additional considerations to what is normally associated with backup and restore procedures. This is primarily due to the use of Object Identification numbers (OIDs) in the SQL/Protect tables as explained in this section.
Note
This section applies if your backup and restore procedures result in the re-creation of database objects in the new database with new OIDs such as is the case when using the pg_dump
backup program.
If you are backing up your EDB Postgres Advanced Server database server by simply 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 section does not 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, and not the objects’ 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 whenever a reference is needed to the object 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 the re-creation of the backed up database objects, the restored objects end up with different OIDs in the new database than 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 simply dumped to a backup file and then restored to a new database.
The following sections describe two functions, export_sqlprotect
and import_sqlprotect
, that are used specifically for backing up and restoring SQL/Protect tables in order to ensure the OIDs in the SQL/Protect tables reference the correct database objects after the tables are restored.
Backing up the database
The following steps back up a database that has been configured with SQL/Protect.
Step 1: Create a backup file using pg_dump
.
This example shows a plain-text backup file named /tmp/edb.dmp
created from database edb
using the pg_dump
utility program:
Step 2: Connect to the database as a superuser and export the SQL/Protect data using the export_sqlprotect('sqlprotect_file')
function (where sqlprotect_file
is the fully qualified path to a file where the SQL/Protect
data is to be 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 in sqlprotect_file
.
The files /tmp/edb.dmp
and /tmp/sqlprotect.dmp
comprise your total database backup.
Restoring From the Backup Files
Step 1: Restore the backup file to the new database.
The following example uses the psql
utility program to restore the plain-text backup file /tmp/edb.dmp
to a newly created database named newdb
:
Step 2: Connect to the new database as a superuser and delete all rows from the edb_sql_protect_rel
table.
This step 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 has been restored:
Step 3: Delete all rows from the edb_sql_protect
table.
This step 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 has been restored:
Step 4: Delete any statistics that may exist for the database.
This step removes any existing statistics that may exist for the database to which you are 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:
Step 5: Delete any offending queries that may exist for the database.
This step removes any existing queries that may exist for the database to which you are restoring the backup. The following 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:
Step 6: Make sure the role names that were protected by SQL/Protect in the original database exist in the database server where the new database resides.
If the original and new databases reside in the same database server, then nothing needs to be done assuming you have not deleted any of these roles from the database server.
Step 7: Run the function import_sqlprotect('sqlprotect_file')
where sqlprotect_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 now populated with entries containing the OIDs of the database objects as assigned in the new database. The statistics view edb_sql_protect_stats
also now displays the statistics imported from the original database.
The SQL/Protect tables and statistics are now properly restored for this database. This is verified by the following queries on the EDB Postgres Advanced Server system catalogs:
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 note that in table edb_sql_protect_rel
, the values in the relid
column match the values in the oid
column of pg_class
for relations dept
and appuser_tab
.
Step 8: 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.