Backing Up and Restoring a SQL/Protect Database v11
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 is applicable 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 Advanced Server database server by simply using the operating system’s copy utility to create a binary image of the 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 Advanced Server to uniquely identify each database object.
When a database object is created, 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.
The following example shows a plain-text backup file named /tmp/edb.dmp created from database edb using the pg_dump utility program:
$ cd /usr/edb/as11/bin $ ./pg_dump -U enterprisedb -Fp -f /tmp/edb.dmp edb Password: $
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 Advanced Server in PostgreSQL compatibility mode) must have read and write access to the directory specified in sqlprotect_file.
edb=# SELECT sqlprotect.export_sqlprotect('/tmp/sqlprotect.dmp');
export_sqlprotect
-------------------
(1 row)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:
$ /usr/edb/as11/bin/psql -d newdb -U enterprisedb -f /tmp/edb.dmp
Password for user enterprisedb:
SET
SET
SET
SET
SET
COMMENT
CREATE SCHEMA
.
.
.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 do not contain the correct OIDs relative to the database where the backup file has been restored:
$ /usr/edb/as11/bin/psql -d newdb -U enterprisedb Password for user enterprisedb: psql.bin (11.0.2, server 11.0.2) Type "help" for help. newdb=# DELETE FROM sqlprotect.edb_sql_protect_rel; DELETE 2
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 do not contain the correct OIDs relative to the database where the backup file has been restored:
newdb=# DELETE FROM sqlprotect.edb_sql_protect; DELETE 1
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:
newdb=# SELECT * FROM sqlprotect.edb_sql_protect_stats; username | superusers | relations | commands | tautology | dml -----------+------------+-----------+----------+-----------+----- (0 rows)
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:
newdb=# SELECT sqlprotect.drop_stats('appuser');
drop_stats
------------
(1 row)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:
edb=# SELECT * FROM sqlprotect.edb_sql_protect_queries; username | ip_address | port | machine_name | date_time | query -----------+------------+------+--------------+-----------+------- (0 rows)
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:
edb=# SELECT sqlprotect.drop_queries('appuser');
drop_queries
--------------
(1 row)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.
newdb=# SELECT sqlprotect.import_sqlprotect('/tmp/sqlprotect.dmp');
import_sqlprotect
-------------------
(1 row)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 Advanced Server system catalogs:
newdb=# SELECT datname, oid FROM pg_database;
datname | oid
-----------+-------
template1 | 1
template0 | 13909
edb | 13917
newdb | 16679
(4 rows)
newdb=# SELECT rolname, oid FROM pg_roles;
rolname | oid
--------------+-------
enterprisedb | 10
appuser | 16671
newuser | 16678
(3 rows)
newdb=# SELECT relname, oid FROM pg_class WHERE relname IN
('dept','emp','appuser_tab');
relname | oid
-------------+-------
appuser_tab | 16803
dept | 16809
emp | 16812
(3 rows)
newdb=# SELECT * FROM sqlprotect.edb_sql_protect;
dbid | roleid | protect_relations | allow_utility_cmds | allow_tautology |
allow_empty_dml
-------+--------+-------------------+--------------------+-----------------+--
---------------
16679 | 16671 | t | t | f | f
(1 row)
newdb=# SELECT * FROM sqlprotect.edb_sql_protect_rel;
dbid | roleid | relid
-------+--------+-------
16679 | 16671 | 16809
16679 | 16671 | 16803
(2 rows)
newdb=# SELECT * FROM sqlprotect.edb_sql_protect_stats;
username | superusers | relations | commands | tautology | dml
----------+------------+-----------+----------+-----------+-----
appuser | 0 | 5 | 2 | 1 | 0
(1 row)
newedb=# \x
Expanded display is on.
nwedb=# SELECT * FROM sqlprotect.edb_sql_protect_queries;
-[ RECORD 1 ]+---------------------------------------------
username | appuser
ip_address |
port |
machine_name |
date_time | 20-JUN-14 13:21:00 -04:00
query | CREATE TABLE appuser_tab_2 (f1 INTEGER);
-[ RECORD 2 ]+---------------------------------------------
username | appuser
ip_address |
port |
machine_name |
date_time | 20-JUN-14 13:22:00 -04:00
query | INSERT INTO appuser_tab_2 VALUES (2);
-[ RECORD 3 ]+---------------------------------------------
username | appuser
ip_address | 192.168.2.6
port | 50098
machine_name |
date_time | 20-JUN-14 13:39:00 -04:00
query | CREATE TABLE appuser_tab_3 (f1 INTEGER);
-[ RECORD 4 ]+---------------------------------------------
username | appuser
ip_address | 192.168.2.6
port | 50098
machine_name |
date_time | 20-JUN-14 13:39:00 -04:00
query | INSERT INTO appuser_tab_2 VALUES (1);
-[ RECORD 5 ]+---------------------------------------------
username | appuser
ip_address | 192.168.2.6
port | 50098
machine_name |
date_time | 20-JUN-14 13:39:00 -04:00
query | SELECT * FROM appuser_tab_2 WHERE 'x' = 'x';Note the following about the columns in tables edb_sql_protect and edb_sql_protect_rel:
- dbid. Matches the value in the
oidcolumn frompg_databasefornewdb - roleid. Matches the value in the
oidcolumn frompg_rolesforappuser
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.