Postgres Plus xDB Replication Server with Multi-Master User's Guide : 5.2 Creating a Publication
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.
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 220.127.116.11. (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.
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.
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
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 18.104.22.168 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:
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.