Postgres Plus xDB Replication Server with Multi-Master User's Guide : 5.2 Creating a Publication

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server xDB Replication Server User's Guide

 

5.2 Creating a Publication

Creating your first publication requires the following steps:

    ● Registering the publication server

    ● Adding the publication database

    ● Creating a publication by choosing the tables and views for the publication along with creating any optional filter clauses

Once the publication database has been added, as many publications can be created as there are available tables and views that are readable by the publication database user and that meet the criteria outlined in sections 2.4.2 and 2.4.3.

5.2.1 Registering a Publication Server

When you register a publication server, you are identifying the network location, user name, and password of a specific, running, publication server instance that you want to use to manage all aspects of the publications you will be creating subordinate to it.

It is important that you record the login information for the publication server as you must always use this same publication server instance to manage all publications created subordinate to it as represented in the xDB Replication Console replication tree.

Step 1: Start the publication server if it is not already running.

Note: If you are using Oracle publication or subscription databases, and the publication server has not been restarted since copying the Oracle JDBC driver, ojdbc14.jar, to the lib/jdbc subdirectory of your xDB Replication Server installation, you must restart the publication server.

For Linux only: You can verify the publication server is running by using the following command:

ps aux | grep pubserver

This is shown by the following:

$ ps aux | grep pubserver
502       5221  0.8  2.9 395196 32192 ?        Sl   17:38   0:01 /opt/PostgresPlus/9.1AS/jre/bin/java -Djava.awt.headless=true -jar edb-repserver.jar pubserver 9051
user      5847  0.0  0.0   3996   688 pts/1    R+   17:41   0:00 grep pubserver

If the publication server is running and you wish to restart it, run script /etc/init.d/edb-xdbpubserver with the restart option.

Run the script as the operating system user that you chose during installation in the Publication/Subscription Service Account screen in Step 18 of Section 3.1. (For an xDB Replication Server installation done at the time of Advanced Server installation, use operating system user enterprisedb for Oracle compatible configuration mode and use postgres for PostgreSQL compatible configuration mode.)

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

Enter the password for the operating system user name when prompted.

If the publication server is not running, run the edb-xdbpubserver script with the start option as shown by the following:

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

For Windows only: Open Control Panel, Administrative Tools, and then Services. The publication server runs as a service named Publication Service.

Figure 62 - Windows publication service

Use the Start or Restart link for the service.

Step 2: Register the publication server. Open the xDB Replication Console from the system’s application menu.

Figure 63 - xDB Replication Console menu option

Figure 64 - xDB Replication Console

Step 3: Select the top level Replication Servers node. From the File menu, choose Publication Server, and then choose Register Server. Alternatively, click the secondary mouse button on the Replication Servers node and choose Register Publication Server. The Register Publication Server dialog box appears.

Enter the values you supplied during the installation of xDB Replication Server unless otherwise specified.

    Host. Network IP address of the host running the publication server. This is the network IP address used for pub_ipaddr in the pg_hba.conf file in Section 5.1.4.3. (Do not use localhost for this field.)

    Port. Port number the publication server is using. This is the port number you specified on the Publication Server Details screen in Step 16 of Section 3.1.

    User Name. Database superuser name that the publication server uses to connect to the xDB Control database. This is the user name you specified on the Postgres Installation Details screen in Step 15 of Section 3.1.

    Password. Password of the database superuser given in the User Name field.

    Save login information. Check this box if you do not want to re-register the publication server each time you open the xDB Replication Console. See Section 4.2 for additional information on the advantages and disadvantages of saving server login information.

Note: The user name and password combination you enter is authenticated against the user name and password in the xDB Replication Configuration file residing on the host with the IP address you enter in the Host field.

Figure 65 - Register Publication Server dialog box

Click the Register button after you have filled in the fields. A Publication Server node appears in the replication tree of the xDB Replication Console. Expand the Publication Server node to expose the SMR and MMR type nodes.

Figure 66 – Replication tree after registering a publication server

Continue to build the single-master replication system under the SMR type node.

5.2.2 Adding a Publication Database

The database in which publications are to reside must be identified to xDB Replication Server. This is done by creating a publication database definition.

After the publication database definition is created, a Publication Database node representing that publication database definition appears in the replication tree of the xDB Replication Console. Publications that are to contain tables and views residing within this database can then be created under the Publication Database node.

You must enter database connection information such as the database server network address, database identifier, and database login user name and password when you create the publication database definition. The connection information is used by the publication server to access the publication tables and views when it performs replication.

Step 1: Make sure the database server in which the publication database resides is running and accepting client connections.

Step 2: Select the SMR type node under the Publication Server node. From the Publication menu, choose Publication Database, and then choose Add Database. Alternatively, click the secondary mouse button on the SMR type node and choose Add Database. The Publication Service – Add Database dialog box appears.

Step 3: Fill in the following fields:

    Database Type. Select Oracle, SQL Server, PostgreSQL, or Postgres Plus Advanced Server for the type of publication database. For an Advanced Server Oracle compatible installation, select the Postgres Plus Advanced Server option. For PostgreSQL or an Advanced Server PostgreSQL compatible installation, select the PostgreSQL option.

    Host. IP address of the host on which the publication database server is running.

    Port. Port on which the publication database server is listening for connections.

    User. The publication database user name created in Step 1 of Section 5.1.2.

    Password. Password of the database user.

    Service ID (For Oracle). Enter the Oracle System Identifier (SID) of the Oracle instance running the publication database if the SID radio button is selected. Enter the net service name of a connect descriptor as defined in the TNSNAMES.ORA file if the Service Name radio button is selected.

    Database (For Postgres or SQL Server). Enter the Postgres or SQL Server database name.

Figure 67 - Publication Service - Add Database dialog box

Step 4: Click the Test button. If Test Result: Success appears, click the OK button, then click the Save button.

Figure 68 - Successful publication database test

If an error message appears investigate the cause of the error, correct the problem, and repeat steps 1 through 4.

When the publication database definition is successfully saved, a Publication Database node is added to the replication tree under the Publication Server node.

Figure 69 – Replication tree after adding a publication database

For Oracle only: Multiple Oracle databases can be added as publication databases by completing the Add Database dialog box for each database. It is also permissible to add the same Oracle database as two or more distinct publication database definitions if you use different publication database user names for each publication database definition.

For Postgres or SQL Server: Multiple Postgres or SQL Server databases can be added as publication databases by completing the Add Database dialog box for each database. However, unlike Oracle, a given Postgres or SQL Server database can only be added once as a publication database definition.

5.2.3 Adding a Publication

Subordinate to a publication database definition, you create publications that contain tables and views of the database identified in the publication database definition.

Step 1: Select the Publication Database node. From the Publication menu, choose Create Publication. Alternatively, click the secondary mouse button on the Publication Database node and choose Create Publication. The Create Publication dialog box appears.

Step 2: Fill in the following fields under the Create Publication tab:

    Publication Name. Enter a name that is unique amongst all publications.

    Snapshot-only replication. Check the box if replication is to be done by snapshot only. Tables included in a snapshot-only publication do not require a primary key. Tables included in publications on which synchronization replication is to be used must have primary keys.

    Publish. Check the boxes next to the tables that are to be included in the publication. If the Snapshot-Only Replication box is checked, then views appear in the Publish list as well.

    Select All. Check this box if you want to include all tables and views in the Available Tables list in the publication.

Figure 70 - Create Publication dialog box

If you wish to omit certain rows of the publication tables or views from being replicated follow the directions in the next step to create a filter, otherwise go on to Step 4.

Step 3 (Optional): If you want to filter the rows of the publication tables or views, click the Filter Clause tab. Enter an appropriate SQL WHERE clause in the text box next to a table or view to select the rows you want to replicate.

In the following example, only rows where the deptno column contains 30 are included in replications. All other rows are excluded from replication.

Figure 71 - Adding a filter clause

Step 4: Click the Create button. If Publication Created Successfully appears, click the OK button, otherwise investigate the error and make the necessary corrections.

Figure 72 - Publication created successfully

Upon successful publication creation, a Publication node is added to the replication tree.

Figure 73 – Replication tree after adding a publication

5.2.4 Metadata Created for a Publication

After you have added a publication database definition and publications you will find the following database objects have been created:

    ● A schema named _edb_replicator_pub is created in the xDB Control database with metadata database objects that are used to manage the publication.

    ● In the publication database, several metadata database objects are created to control and manage the publication.

    ● If the publication is not a snapshot-only publication, that is synchronization replication is permitted, then three triggers and one shadow table are created for each publication table.

For Oracle only: The metadata database objects are created in the publication database user’s schema as shown in the following output:

SQL> CONNECT pubuser/password
Connected.
SQL> SET PAGESIZE 9999
SQL> SELECT table_name FROM user_tables;

TABLE_NAME
------------------------------
RREP_TABLES
RREP_PUBLICATION_TABLES
RREP_TXSET
RREP_MMR_TXSET
RREP_PUBLICATION_SUBSCRIPTIONS
RREP_TXSET_LOG
RREP_LOCK
RREP_MMR_PUB_GROUP
RREP_PROPERTIES
RREP_TXSET_HEALTH
RRST_EDB_DEPT
RRST_EDB_EMP

12 rows selected.

SQL> SELECT sequence_name FROM user_sequences;

SEQUENCE_NAME
------------------------------
RREP_TX_SEQ
RREP_TXSET_SEQ
RREP_COMMON_SEQ

SQL> SELECT DISTINCT name FROM user_source WHERE type = 'PACKAGE';

NAME
------------------------------
RREP_PKG

SQL> SELECT trigger_name FROM user_triggers;

TRIGGER_NAME
------------------------------
RRPI_EDB_DEPT
RRPU_EDB_DEPT
RRPD_EDB_DEPT
RRPI_EDB_EMP
RRPU_EDB_EMP
RRPD_EDB_EMP

6 rows selected.

For SQL Server only: Most of the metadata database objects are created in the schema you chose in Step 5 of Section 5.1.2.2 as shown by the following:

1> USE edb;
2> GO
Changed database context to 'edb'.
1>
2> SELECT s.name + '.' + o.name "Object Name", o.type_desc "Object Type"
3>   FROM sys.objects o,
4>        sys.schemas s
5>   WHERE s.name = 'pubuser'
6>     AND o.type IN ('U','P')
7>     AND o.schema_id = s.schema_id
8>   ORDER BY 2, 1;
9> GO
Object Name                            Object Type
-------------------------------------- --------------------------------------
pubuser.CleanupShadowTables            SQL_STORED_PROCEDURE
pubuser.ConfigureCleanUpJob            SQL_STORED_PROCEDURE
pubuser.ConfigureCreateTxSetJob        SQL_STORED_PROCEDURE
pubuser.CreateMultiTxSet               SQL_STORED_PROCEDURE
pubuser.CreateTableLogTrigger          SQL_STORED_PROCEDURE
pubuser.CreateTxSet                    SQL_STORED_PROCEDURE
pubuser.CreateUniTxSet                 SQL_STORED_PROCEDURE
pubuser.GetNewTxsCount                 SQL_STORED_PROCEDURE
pubuser.JobCleanup                     SQL_STORED_PROCEDURE
pubuser.JobCreateTxSet                 SQL_STORED_PROCEDURE
pubuser.LoadPubTableList               SQL_STORED_PROCEDURE
pubuser.nextval                        SQL_STORED_PROCEDURE
pubuser.RemoveCleanupJob               SQL_STORED_PROCEDURE
pubuser.RemoveCreateTxSetJob           SQL_STORED_PROCEDURE
pubuser.sp_createsequence              SQL_STORED_PROCEDURE
pubuser.sp_dropsequence                SQL_STORED_PROCEDURE
pubuser.rrep_common_seq                USER_TABLE
pubuser.rrep_lock                      USER_TABLE
pubuser.rrep_mmr_pub_group             USER_TABLE
pubuser.rrep_mmr_txset                 USER_TABLE
pubuser.rrep_properties                USER_TABLE
pubuser.rrep_publication_subscriptions USER_TABLE
pubuser.rrep_publication_tables        USER_TABLE
pubuser.rrep_tables                    USER_TABLE
pubuser.rrep_tx_seq                    USER_TABLE
pubuser.rrep_txset                     USER_TABLE
pubuser.rrep_txset_health              USER_TABLE
pubuser.rrep_txset_log                 USER_TABLE
pubuser.rrep_txset_seq                 USER_TABLE
pubuser.rrst_edb_dept                  USER_TABLE
pubuser.rrst_edb_emp                   USER_TABLE

(31 rows affected)

For non-snapshot only publication tables, triggers are created that reside in the schema containing the publication tables as shown by the following:

1> USE edb;
2> GO
Changed database context to 'edb'.
1>
2> SELECT s.name + '.' + o.name "Trigger Name"
3>   FROM sys.objects o,
4>        sys.schemas s
5>   WHERE s.name = 'edb'
6>     AND o.type = 'TR'
7>     AND o.name LIKE 'rr%'
8>     AND o.schema_id = s.schema_id
9>   ORDER BY 1;
10> GO
Trigger Name
--------------------------------------
edb.rrpd_edb_dept
edb.rrpd_edb_emp
edb.rrpi_edb_dept
edb.rrpi_edb_emp
edb.rrpu_edb_dept
edb.rrpu_edb_emp

(6 rows affected)

Finally, some jobs are created in the msdb database as shown by the following:

1> USE msdb;
2> GO
Changed database context to 'msdb'.
1>
2> SELECT j.name "Job Name"
3>   FROM msdb.dbo.sysjobs j,
4>        master.dbo.syslogins l
5>   WHERE l.name = 'pubuser'
6>     AND j.name LIKE 'rrep%'
7>     AND j.owner_sid = l.sid
8>   ORDER BY 1;
9> GO
Job Name
-----------------------------------
rrep_cleanup_job_edb
rrep_txset_job_edb

(2 rows affected)

For Postgres only: The metadata database objects are created in the schema _edb_replicator_pub as shown in the following:

Figure 74 - Postgres metadata database objects in the publication database

Do not delete any of these database objects as the replication system metadata will become corrupted.

When you remove publications and publication database definitions using the xDB Replication Console or xDB Replication Server CLI, these database objects are deleted during this process.

Previous PageTable Of ContentsNext Page