Postgres Plus xDB Replication Server with Multi-Master User's Guide : 9.4 Miscellaneous xDB Replication Server Processing Topics

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server xDB Replication Server User's Guide

 

9.4 Miscellaneous xDB Replication Server Processing Topics

This section contains various topics covering the following:

    ● Handling special characters in replication data

    ● Replicating Oracle partitioned tables

    ● Performing an offline snapshot and subsequent synchronization

    ● Generating an encrypted password

    ● Writing cron expressions

9.4.1 Publication and Subscription Server Configuration Options

The publication server and the subscription server support various configuration options for purposes such as the following:

    ● Optimize synchronization performance based on the types of transactions affecting the publication. (See Section 5.8.2 for details on these particular options.)

    ● Utilize alternate loading methods in snapshot replications. (See Section 5.8.1 for details on these particular options.)

    ● Special configuration options for multi-master replication. (See Section 6.11 for details on these particular options.)

    ● Adjust memory usage and transaction size for replications.

    ● Replicate certain Oracle partitioned table types.

    ● Replicate special characters found in publication data.

Most options apply to the publication server only, although a few are used by the subscription server.

The configuration options for the publication server are set and passed in a text file called the publication server configuration file with file name xdb_pubserver.conf.

The configuration options for the subscription server are set and passed in a text file called the subscription server configuration file with file name xdb_subserver.conf.

See Section 3.2 for the directory locations of these files.

Modified publication server configuration options take effect after the publication server is restarted. Similarly, modified subscription server configuration options take effect after the subscription server is restarted.

The configuration options that have been explicitly put into effect by overriding their defaults in the configuration files are logged in the publication server log file and the subscription server log file. Section 3.2 contains the directory locations of these log files.

The following is a description of how to set the configuration options. This is followed by sections describing the purpose of each option.

Step 1: The publication and subscription server configuration files are created during xDB Replication Server installation and already contain all of the configuration options as comments with their default settings.

To change the setting of a configuration option, edit the publication server or subscription server configuration file by removing the comment symbol (#) from the option and substituting the desired value in place of the currently coded value.

The following example shows a portion of the publication server configuration file where replacement of null characters in the publication data has been activated and the replacement character has been set to the question mark character.

replaceNullChar = true

#Null Replacement Character
nullReplacementChar = ?

Step 2: Restart the publication or subscription server.

$ su enterprisedb
Password: 
$ /etc/init.d/edb-xdbpubserver restart
Publication Service stopped
Password: 
Publication Service started

The following sections provide additional detail on the server configuration options.

9.4.1.1 Controlling Logging Level

Note: The option described in this section applies to the publication server and the subscription server.

Set the logging.level option to control the severity of messages written to the publication server log file and the subscription server log file (see Section 9.3.2.4).

logging.level={OFF | SEVERE | WARNING | INFO | CONFIG | FINE | FINER | FINEST | ALL}

The default value is SEVERE.

9.4.1.2 Replacing Null Characters

Note: The options described in this section apply to the publication server only.

A character consisting of binary zeros (also called the null character string) and represented as 000 in octal or 0x00 in hexadecimal can result in errors when attempting to load such data into a Postgres character column.

You may get the following error in the Migration Toolkit log file when performing a snapshot replication of an Oracle table that contains the null character string:

Loading Table Data in 8 MB batches...
Disabling FK constraints & triggers on edb.null_test before truncate...
Truncating table NULL_TEST before data load...
Disabling indexes on edb.null_test before data load...
Loading Table: NULL_TEST ...
Error Loading Data into Table: NULL_TEST: ERROR: invalid byte sequence for encoding "UTF8": 0x00
  Where: COPY null_test, line 2

The same circumstance may also produce the following error in the Migration Toolkit log file:

Loading Table Data in 8 MB batches...
Disabling FK constraints & triggers on edb.null_clob before truncate...
Disabling indexes on edb.null_clob before data load...
Loading Large Objects into table: NULL_CLOB ...
[NULL_CLOB] Migrated 1 rows.
com.edb.util.PSQLException: Zero bytes may not occur in string parameters., Skipping Batch

If any of these errors occur, you can set an option that will convert each null character encountered in character columns of the source tables to a space character, or to any other character of your choice, before loading the target tables.

Note: This option does not alter null characters encountered in columns with binary data types such as Oracle RAW and BLOB data types.

Set the following option:

replaceNullChar=true

This option results in the substitution of a space character for each null character encountered in the source character data.

If you want to use a character other than a space character to replace each null character, use the following option in addition to replaceNullChar=true.

nullReplacementChar=char

char is a single character you want to substitute for the null character. For example, the following combination will replace each null character with the hash symbol #.

replaceNullChar=true
nullReplacementChar=#

9.4.1.3 Schema Migration Options

Note: The option described in this section applies to the subscription server only.

The option in this section controls how certain aspects of the publication database schema are migrated to the subscription database.

skipCheckConst

By default, column CHECK constraints from publication tables are migrated to the subscription table definitions when the subscription is created. Set this option to true if you do not want CHECK constraints as part of the subscription table definitions.

Setting this option to true is useful if the CHECK constraint is based on a built-in function supported by the publication database server, and this built-in function does not exist in the subscription database server.

skipCheckConst={true | false}

The default value is false.

9.4.1.4 Replicating Oracle Partitioned Tables

Note: The option described in this section must be set to the same value for both the publication server and the subscription server.

In Oracle, table partitioning provides the capability to store table rows in different physical locations (tablespaces) according to a rule defined on the table.

The most common types of Oracle table partitioning are the following:

    Range Partitioning. Ranges of values defined on a column determine which tablespace a row is stored.

    List Partitioning. A list of values defined on a column determines which tablespace a row is stored.

    Hash Partitioning. An algorithm on a column generates a hash key, which determines which tablespace a row is stored.

Postgres does not support table partitioning in the same manner as Oracle. However, Oracle table partitioning can be implemented in Postgres using the two Postgres features called table inheritance and rules.

Postgres table inheritance allows you to create a parent table from which you can create one or more child tables that inherit the columns of the parent. Each child is an independent table in its own right except that it includes the column definitions of its parent.

Rules can be defined on the parent table to direct which child table an inserted row is to be stored. Since each child table can be stored in a different physical location, the behavior associated with range or list partitioning can be reproduced.

The importPartitionAsTable option controls what happens when an Oracle partitioned table is part of the publication.

importPartitionAsTable={true | false}

The default value is false.

Depending upon the Oracle partitioned table type and the setting of the importPartitionAsTable option one of the following may occur:

    ● A set of inherited tables is created in Postgres to which the Oracle partitioned table is replicated. The rows can be stored in different physical locations.

    ● A plain, single table with no inheritance is created in Postgres to which the Oracle partitioned table is replicated. All rows are stored in one physical location.

    ● No table is created in Postgres for the Oracle partitioned table, and an error message is written to the Migration Toolkit log file.

When importPartitionAsTable=false (the default setting), the following occurs:

    ● A list partitioned table is replicated as a set of inherited Postgres tables.

    ● A range partitioned table is replicated as a set of inherited Postgres tables.

    ● A hash partitioned table is not replicated to Postgres, and an error message is written to the Migration Toolkit log file.

Note: If there are subscription tables created as sets of Postgres inherited tables, then you must also set the enableConstBeforeDataLoad option in the publication server configuration file to true. See Section 9.4.1.5 for information on the enableConstBeforeDataLoad option.

When importPartitionAsTable=true, the following occurs:

    ● A list partitioned table is replicated as a single Postgres table with no inheritance.

    ● A range partitioned table is replicated as a single Postgres table with no inheritance.

    ● A hash partitioned table is replicated as a single Postgres table with no inheritance.

Setting the importPartitionAsTable option to true allows you to replicate a broader range of Oracle partitioned table types, but as normal Postgres tables without simulating partitions by using inheritance.

9.4.1.5 Snapshot Replication Options

Note: The options described in this section apply to the publication server only unless otherwise specified.

The server configuration options discussed in this section apply to snapshot replications.

escapeTabDelimiter

When JDBC COPY is used in snapshot replication, the data delimiter between column values is an escaped tab character (\t). Set this option to false if you do not want to escape the tab delimiter character.

escapeTabDelimiter={true | false}

The default value is true.

mtkCopyDelimiter

When JDBC COPY is used in snapshot replication, the data delimiter between column values is an escaped tab character (\t). Set this option to change the data delimiter character.

mtkCopyDelimiter=c

c denotes the single replacement character for the data delimiter.

The default value is \t.

enableConstBeforeDataLoad

The enableConstBeforeDataLoad option controls whether or not table constraints, including triggers, are re-enabled before loading data into target tables. The default process is that the tables are loaded first, and then the constraints are enabled afterwards.

Activate this option if there are triggers that affect how data is loaded into the target tables.

If there are target tables created as sets of Postgres inherited tables resulting from partitioned Oracle source tables, then this option must be enabled.

enableConstBeforeDataLoad={true | false}

The default value is false.

9.4.1.6 Assigning an IP Address for Remote Method Invocation

Note: The option described in this section applies to the publication server and the subscription server.

For Linux only:

An alternative method to modifying the /etc/hosts file so that the host name is associated with a non-loopback IP address as discussed in Section 5.1.4.2 is to specify the network IP address using the java.rmi.server.hostname option.

In the publication server configuration file, set this option to the network IP address of the host running the publication server.

In the subscription server configuration file, set this option to the network IP address of the host running the subscription server.

java.rmi.server.hostname=xxx.xxx.xx.xxx

For example, instead of modifying the /etc/hosts file to look like the following for a publication or subscription server running on host 192.168.10.102:

192.168.10.102   opensuse-vm.vmplanet.net opensuse-vm
#127.0.0.2       opensuse-vm.vmplanet.net opensuse-vm

You can set the IP address in the server configuration file as shown by the following:

#On Linux machines, the localhost to real IP may not give correct results. Hence
#users are advised to override the following property with server IP address
java.rmi.server.hostname=192.168.10.102

9.4.1.7 Using pgAgent Job Scheduling

Note: The option described in this section applies to the publication server only.

Note: Using pgAgent job scheduling has significance only if Postgres is the publication database.

Note: You must have pgAgent installed and running on the host where the publication database resides.

When the pgdbschedule option is set to true, xDB Replication Server uses the pgAgent job scheduler instead of the default Quartz job scheduler.

When activated, pgAgent takes over the following scheduling tasks from Quartz:

    ● Scheduling shadow table history cleanup in the publication database. See Section 7.4.1 for information on scheduling shadow table history cleanup.

    ● Scheduling transaction set creation. A transaction set creation job is scheduled to run every hour to create transaction sets from the updates on the source tables. Transaction sets are applied to the target tables.

Unlike the Quartz scheduler, pgAgent can still run and perform its tasks even if the publication server is not running.

pgdbschedule={true | false}

The default value is false.

9.4.1.8 Forcing Immediate Shadow Table Cleanup

Note: The option described in this section applies to the publication server only.

A cleanup job is provided that can be run on demand or on a schedule to remove dead (processed) tuples from the shadow tables (see Section 7.4).

However, to perform even quicker cleanup scheduling, turn on this option to force the cleanup of shadow tables after every synchronization replication.

postSyncShadowTableCleanup={true | false}

The default value is false.

9.4.1.9 DDL Change Replication Table Locking

Note: The option described in this section applies to the publication server only.

When the DDL change replication process is invoked, each affected table in the replication system is acquired in turn with an exclusive lock before the DDL change is applied to the table.

Set ddlChangeTableLock to false if you do not want an exclusive lock placed on the table before applying the DDL change. This option should be set to false only if there are no write transactions expected on the target table. If write transactions do occur, they may not be recorded by the replication system.

See Section 7.6 for information on DDL change replication.

ddlChangeTableLock={true | false}

The default value is true.

9.4.2 Encrypting the Password in the xDB Replication Configuration File

If you need to change the password in the xDB Replication Configuration file, you must first encrypt the password. Use the encrypt command of the xDB Replication Server CLI to generate the encrypted form of the password from its plain text form given in an input file.

Step 1: Create a text file with the password you wish to encrypt. Do not leave any white space before or after the password.

The following example shows the text newpassword in the input file passfile:

$ cat passfile
newpassword
$

Step 2: Use the edb-repcli.jar file to execute the xDB Replication Server CLI with the encrypt command as shown by the following:

$ export PATH=/opt/PostgresPlus/9.1AS/jre/bin:$PATH
$ cd /opt/PostgresPlus/9.1AS/bin
$ java -jar edb-repcli.jar -encrypt -input ~/passfile -output ~/encrypted

The following shows the encrypted form of the password in the output file encrypted:

$ cat ~/encrypted
4mKq/4jQQoV2IypCSmPpTQ==
$

Step 3: Copy and paste the encrypted password into the xDB Replication Configuration file.

user=enterprisedb
port=5444
password=4mKq/4jQQoV2IypCSmPpTQ==
type=enterprisedb
host=localhost
database=xdb

9.4.3 Writing a Cron Expression

A cron expression is a text string used to express a schedule of dates and times. The Linux cron tool uses a cron expression to schedule the execution of a job. xDB Replication Server uses the Quartz job scheduling system for scheduling replications.

When creating a schedule for an xDB Replication Server replication system, a cron expression can be specified. There are a number of formats for cron expressions. You must use the cron expression format supported by Quartz.

The remainder of this section provides an overview of most of the types of cron expressions that can be used for an xDB Replication Server schedule. For a more comprehensive treatment of cron expressions, refer to the Quartz documentation.

A Quartz cron expression consists of six mandatory fields, followed by one optional field. Each field is separated from its neighbors by one or more consecutive space characters. The fields are order dependent and are listed as they must appear below:

ss mi hr dd mm dow [ yyyy ]

Table 10 - Cron Expression Fields

Field

Values

Description

ss

0 - 59

Second of the minute

mi

0 - 59

Minute of the hour

hr

0 - 23

Hour of the day

dd

1 - 31 or ?

Day of the month – if dow is given, then dd must be specified as ?

mm

1 - 12 or JAN - DEC

Month of the year (3-letter month abbreviations are not case sensitive)

dow

1 – 7 or SUN – SAT or ?

Day of the week – if dd is given, then dow must be specified as ? (3-letter day of the week abbreviations are not case sensitive)

yyyy

1970 - 2099

Year – if omitted, then any year applies

There are a number of characters that have special meaning that can be utilized in all fields unless noted.

Table 11 - Cron Expression Special Characters

Character

Meaning

Example

,

Separates a list of values

MON,WED,FRI – Every Monday, Wednesday, and Friday

-

Separates the low and high end of a range of values

MON-FRI – Every Monday through Friday

*

Allows all legal values for the field

0 10 14 * * ? – Every day of every month at 2:10 PM

x/i

Specifies an increment, i, starting with x

0 0/10 * * * ? – Every 10 minutes starting on the hour for every day of every month (e.g., 8:00:00, 8:10:00, 8:20:00)

L

When used in the day of the month (dd) field, means the last day of the month

0 30 15 L 8 ? – Every August 31st at 3:30 PM

L

When used by itself in the day of the week field (dow), means Saturday

30 0 12 ? AUG L – The next Saturday in August at 30 seconds past 12:00 noon

xxxL

When used in the day of the week field (dow) following a day of the week, means the last xxx day of the month

30 0 12 ? AUG 6L – The last Friday in August at 30 seconds past 12:00 noon

xW

Used in the day of the month field (dd) following a day of the month, x, to specify the weekday closest to x without going over into the next or previous month.

1W – The weekday closest to the 1st of the month. If the 1st is a Wednesday, the result is Wednesday the 1st. If the 1st is a Sunday, the result is Monday the 2nd. If the 1st is a Saturday, the result is Monday the 3rd because the result does not go into the previous or following month.

xxx#n

Used in the day of the week field (dow) to specify the nth xxx day of the month

2#3 – The third Monday of the month (2 = Monday, 3 = third occurrence)

The following illustrates some examples of cron expressions.

Table 12 - Cron Expression Examples

Cron Expression

Meaning

0 0 12 20 AUG ? 2009

12:00:00 noon on August 20, 2009

0 15 13 ? AUG WED

1:15:00 PM every Wednesday in August

30 30 8 ? * MON,WED,FRI

8:30:30 AM every Monday, Wednesday, and Friday of every month

0 0 8 ? * 2-6

8:00:00 AM Monday thru Friday of every month

0 0/30 8,9,10 15,L * ?

8:00:00 AM, 8:30:00 AM, 9:00:00 AM, 9:30:00 AM, 10:00:00 AM, 10:30:00 AM on the 15th and the last day of the month of every month

0 0 9 ? 9 L

9:00:00 AM each Saturday in September

0 0 1 ? * MonL

1:00:00 AM on the last Monday of the month of every month

0 30 16 15W sep ?

4:30:00 PM on the weekday of September closest to the 15th

0 30 16 ? * WED#2

4:30:00 PM on the second Wednesday of every month

9.4.4 Disabling Foreign Key Constraints for Snapshot Replications

In a snapshot replication, the publication server calls EnterpriseDB’s Migration Toolkit, which disables foreign key constraints on tables so it can truncate the target tables before loading rows. In Postgres, foreign key constraints are implemented using triggers, so in actuality, the Migration Toolkit disables triggers on the target tables by setting column reltriggers of pg_catalog.pg_class to zero for each target table.

No user (not even a superuser) is allowed to directly modify the data in a Postgres system catalog table unless the following conditions are satisfied:

    ● The database user attempting to modify the rows of a system catalog table is a superuser.

    ● In the Postgres system catalog table pg_catalog.pg_authid, the column rolcatupdate is set to true for the row identifying the superuser attempting to update a system catalog table.

To verify that a user has the privilege to update the system catalog tables, select the user name under the Login Roles node in pgAdmin (Postgres Enterprise Manager Client in Advanced Server). The Update Catalogs property should be set to Yes.

Figure 256 - User with privilege to update system catalogs

If the Update Catalogs property is set to No, click the secondary mouse button on the user name in the Object Browser and choose Properties from the menu. Select the Role Privileges tab, check the Can Modify Catalog Directly box, and click the OK button.

Figure 257 - Granting system catalog update privilege

9.4.5 Quoted Identifiers and Default Case Translation

A quoted identifier is an identifier created with its name enclosed within double quote characters ("). The text enclosed within double quotes is stored as the object identifier name exactly as given with no default case translation of alphabetic characters. Quoted identifiers occur in both Oracle and Postgres.

For example, CREATE TABLE "MyTable" … produces a table name that is stored in the database system’s data dictionary as MyTable. References to this table must be made using an uppercase M, an uppercase T, and lowercase letters for the rest of the name.

If a database object is created without the double quotes surrounding its identifier name, default case translation of alphabetic characters occurs.

In Oracle, the default case translation is to uppercase. For example, CREATE TABLE MyTable … would result in an object identifier name of MYTABLE.

In Postgres, the default case translation is to lowercase. For example, CREATE TABLE MyTable … would result in an object identifier name of mytable.

9.4.6 Replicating the SQL Server SQL_VARIANT Data Type

This section discusses how to replicate a table containing the SQL Server SQL_VARIANT data type.

The SQL_VARIANT data type defines a column so that the individual values in that column may be of different data types. For example, the same SQL_VARIANT column can store values that have been explicitly cast as character, integer, numeric, and date/time.

However, if a table containing a SQL_VARIANT column is to be replicated to a Postgres database, the usage of the column in Postgres is restricted to a single data type to which all the values in the SQL_VARIANT column are implicitly convertible (that is, without the use of explicit casting). For example, an integer value is implicitly convertible to a FLOAT data type, but a floating point value is not implicitly convertible to an INTEGER data type.

The following restrictions apply in order to use replication on tables with the SQL_VARIANT data type:

    ● The values stored within the SQL_VARIANT columns of the table to be replicated must be implicitly convertible to the same data type in Postgres.

    ● If there is more than one table with SQL_VARIANT columns to be replicated to the same Postgres database, then all such SQL_VARIANT columns must contain values that are implicitly convertible to the same data type in Postgres.

In the Postgres subscription database, you define a domain named sql_variant that maps to an underlying data type to which all values in the SQL_VARIANT columns are implicitly convertible.

The following example shows how to set up replication for a table containing a SQL_VARIANT data type used to store numeric values, but of different data types.

The SQL Server table definition is the following:

CREATE TABLE variant_tbl (
    f1              INTEGER PRIMARY KEY,
    f2              SQL_VARIANT
);

INSERT INTO variant_tbl VALUES (1, CAST(1423.23 AS NUMERIC(6,2)));
INSERT INTO variant_tbl VALUES (2, CAST(8001 AS INTEGER));
INSERT INTO variant_tbl VALUES (3, CAST('4321' AS CHAR(4)));
GO

The following query uses a function named SQL_VARIANT_PROPERTY to show the values stored in column f2 and their data types.

1> SELECT *,
2>     SQL_VARIANT_PROPERTY(f2,'BaseType') AS basetype,
3>     SQL_VARIANT_PROPERTY(f2,'Precision') AS precision,
4>     SQL_VARIANT_PROPERTY(f2,'Scale') AS scale
5> FROM variant_tbl;
6> GO
f1          f2         basetype   precision  scale
----------- ---------- ---------- ---------- ----------
          1 1423.23    numeric    6          2
          2 8001       int        10         0
          3 4321       char       0          0

(3 rows affected)

In the Postgres subscription database, create a domain named sql_variant with an underlying data type that is compatible with the values that are stored in the SQL Server SQL_VARIANT column:

CREATE DOMAIN sql_variant AS NUMERIC(6, 2);

After replication occurs, the subscription table is created using the sql_variant domain in place of the SQL_VARIANT data type of the publication table.

At the bottom of the following Object Browser window, note the presence of the sql_variant domain under the Domains node of the public schema.

Figure 258 - Subscription table with sql_variant column

9.4.7 Restricted Mode Operation

Restricted mode refers to limitations on certain xDB Replication Server operations after your trial period expires. Restricted mode can be lifted by registering your xDB Replication Server product as described in Section 3.3.

Restricted mode currently affects multi-master replication systems. Creation and usage of single-master replication systems are not affected.

The following multi-master replication operations are restricted:

    ● New master definition nodes or master nodes cannot be created (see sections 6.2.2 and 6.3). However, existing master nodes and master definition nodes remain operational.

    ● New publications cannot be created (see Section 6.2.3).

    ● Tables cannot be added nor removed from existing publications (see sections 7.5.3.1 and 7.5.3.2).

    ● A publication table definition cannot be modified using the DDL change replication feature (see Section 7.6).

    ● A new replication schedule cannot be created, nor can an existing replication schedule be modified (see sections 7.1 and 7.2.1).

    ● A snapshot replication cannot be performed (see Section 6.5.1).

    ● The role of the master definition node cannot be switched to another master node (see Section 6.9).

    ● The conflict resolution options for a publication table cannot be changed (see Section 6.8).

A dialog box similar to the following appears if you attempt a restricted operation:

Figure 259 – Disallowed restricted mode operation

You must register your xDB Replication Server product by entering a valid license key to re-enable restricted operations. See Section 3.3 for instructions on performing this operation.

Previous PageTable Of ContentsNext Page