Document 200175
How to Set Up Postgres Plus xDB Replication Server How to Set Up Postgres Plus® xDB Replication Server A Postgres Evaluation Quick Tutorial From EnterpriseDB July 15, 2010 EnterpriseDB Corporation, 235 Littleton Road, Westford, MA 01866, USA T +1 978 589 5700 F +1 978 589 5701 E info@enterprisedb.com www.enterprisedb.com Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 1 How to Set Up Postgres Plus xDB Replication Server Introduction This EnterpriseDB Quick Tutorial helps you get started with the Postgres Plus Standard Server and Postgres Plus Advanced Server database products. It is assumed that you have already downloaded and installed Postgres Plus Standard Server or Postgres Plus Advanced Server on your desktop or laptop computer. This Quick Tutorial is designed to help you expedite your Technical Evaluation of Postgres Plus Standard Server or Postgres Plus Advanced Server. In this Quick Tutorial you will learn how to set up the Postgres Plus xDB Replication Server. For more informational assets on conducting your evaluation of Postgres Plus, visit the self-service web site, Postgres Plus Open Source Adoption. In this Quick Tutorial you will learn the following: • • • • • • Basic features of the xDB Replication Server Fundamental replication concepts and vocabulary The physical and logical components of a replication system How to install and configure the xDB Replication Server How to register publication and subscription servers How to start replication Note: Complete documentation for Postgres Plus xDB Replication Server will be released shortly after general availability of Postgres Plus Advanced Server 8.4. Feature Description Postgres Plus xDB Replication Server is an asynchronous, master-to-slave replication system enabling replication of tables from an Oracle® database to a Postgres Plus Standard Server or Postgres Plus Advanced Server database. The following are some of the features of xDB Replication Server: • • • Different replication methods are available for different needs. Two types of replication methods are supported. Snapshot replication takes a copy of the entire table. Synchronization replication takes only the modifications made to the table data since the last replication. Replication data can be filtered. A filter can be defined specifying a subset of rows in a source table or view to include during replication. All other rows are excluded from replication. Replication occurs asynchronously. Once xDB Replication Server is started, it can run unattended. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 2 How to Set Up Postgres Plus xDB Replication Server • • • • Replication can be scheduled. Replication can be scheduled to run repeatedly at a specific time interval, daily at a specific time of day, weekly on specific days of the week, or monthly on specific months. Replication history can be viewed. Two levels of replication history are available. The date and time when a replication occurred is recorded. In addition, the specific inserts, updates, and deletions made to synchronize a replicated table can be viewed. Replication can be distributed. The source and target databases can reside on different hosts, as well as the individual components of xDB Replication Server to optimize performance. Replication can run from the command line. In addition to a graphical user interface, a command line interface allows scripts to embed commands to configure and run replication. The following sections introduce you to the concepts and components of xDB Replication Server. Replication Concepts A publish and subscribe architecture is used by xDB Replication Server. The data to be made available for copying is made public as a publication. To get a copy of that data, you must subscribe to that publication. A publication is defined as a named set of tables and views within a database. The database that contains the publication is called the publication database of that publication. To get a copy of a publication, you must create a subscription. A subscription is a named association with a publication. Along with its association with a publication, a subscription is associated with a database to which the publication is to be copied. This database is called the subscription database. Replication is said to occur when xDB Replication Server initiates and completes either of the following processes: 1) applies changes that have been made to rows in the publication since the last replication occurred, to rows in tables of the subscription database (called synchronization); or 2) copies rows of the publication to empty tables of the subscription database (called a snapshot). The subscription tables are the tables in the subscription database where each subscription table is created from a corresponding table or view in the publication. Note: xDB Replication Server creates tables in the subscription database for views in the publication. The following illustrates a basic replication system: Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 3 How to Set Up Postgres Plus xDB Replication Server xDB Replication Server Physical Components xDB Replication Server is not a single executable program, but rather a set of programs that work together to form a replication system. The following diagram shows the programs and database components of xDB Replication Server and how they are used to form a complete replication system. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 4 How to Set Up Postgres Plus xDB Replication Server xDB Replication Server is the engine that enables replication system configuration, scheduling, monitoring, maintenance, replication, and recovery. xDB Replication Server consists of the following software components: • • • • • xDB Replication Console. Graphical user interface that allows you to configure and run a replication system. (Commands written using the xDB Replication Server CLI can be used in place of the xDB Replication Console.) Publication server. The program that configures the publication database for replication and initiates replication. Subscription server. The program that configures the subscription database for replication. xDB Control database. The Postgres Plus database containing the metadata of the replication system. xDB Replication Configuration file. Text file containing connection and authentication information used by the publication server and subscription server upon startup to connect to the xDB Control database. Also used to authenticate Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 5 How to Set Up Postgres Plus xDB Replication Server registration of the publication server and subscription server from the user interface when creating a replication system. The entire replication system is completed with the addition of the following components: • • One or more publication databases One or more subscription databases xDB Replication Server Logical Components The logical components are created when you build a replication system using the xDB Replication Console or the xDB Replication Server CLI. The logical components are stored as part of the replication system metadata in the xDB Control database. Creating a replication system requires the following steps: • • • • • • Register the publication server Create a publication database definition Create a publication Register the subscription server Create a subscription database definition Create a subscription Each of these steps creates a logical component that is represented by a node in the replication tree window of the xDB Replication Console. A detailed description of each of these steps is presented later in this tutorial. Tutorial Steps This section provides directions for installing and configuring xDB Replication Server, and then performing your first replication. Installing xDB Replication Server Depending upon whether you are using Postgres Plus Standard Server or Postgres Plus Advanced Server, installing xDB Replication Server can be done a number of different ways. • • For Postgres Plus Standard Server. Install xDB Replication Server using StackBuilder Plus after you have installed Postgres Plus Standard Server For Postgres Plus Advanced Server. Install xDB Replication Server as part of the Postgres Plus Advanced Server installation process, or install xDB Replication Server using StackBuilder Plus after you have installed Postgres Plus Advanced Server Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 6 How to Set Up Postgres Plus xDB Replication Server Regardless of how you install xDB Replication Server, the information you are requested to supply is the same. The following sections describe the two methods of installing xDB Replication Server. Installing With StackBuilder Plus Start up StackBuilder Plus. When you are presented with the list of applications to install, select Postgres Plus xDB Replication Server under the Replication Solutions category. Follow the directions on the StackBuilder Plus installation screens at which point the xDB Replication Server package is downloaded to your computer. When the following screen appears click the Next button to install xDB Replication Server. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 7 How to Set Up Postgres Plus xDB Replication Server The first setup screen appears as follows: Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 8 How to Set Up Postgres Plus xDB Replication Server Follow the directions for the licensing agreement that appears next. When the Installation Directory screen appears, allow it to install the components in the default location shown. Click the Next button. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 9 How to Set Up Postgres Plus xDB Replication Server When you are presented with the Select Components screen, leave all components selected. This will install the xDB Replication Console, the publication server, and the subscription server. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 10 How to Set Up Postgres Plus xDB Replication Server The next several screens concern your EnterpriseDB User Account and are omitted from this tutorial. Follow the directions on these screens. Note: When you reach the Postgres Plus Installation Details screen, it is suggested that you record the values you enter on this screen and on the following screens as this information is needed during the publication and subscription server registration process. In the following screen, enter the name of the Postgres Plus database that you want xDB Replication Server to use to store the replication system metadata. If the database does not already exist, it will be created. This is the xDB Control database of the replication system. The user name and password entered on this screen must be that of an existing database superuser. This user name will be used by the publication server and subscription server to connect to the xDB Control database. You must also use this user name and password when you register a publication server or a subscription server running on this host. Note: The database name xdb is used in this example instead of the default database postgres (edb on Advanced Server). Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 11 How to Set Up Postgres Plus xDB Replication Server Enter an available port on which you want the publication server to run, or allow it to default to 9011 as shown in this example. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 12 How to Set Up Postgres Plus xDB Replication Server Enter an available port on which you want the subscription server to run, or allow it to default to 9012 as shown in this example. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 13 How to Set Up Postgres Plus xDB Replication Server Enter the operating system account under which the publication server and subscription server are to run, or allow it to default to postgres (enterprisedb for Advanced Server installed in Oracle compatible configuration mode). Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 14 How to Set Up Postgres Plus xDB Replication Server Start the installation by clicking the Next button on the Ready to Install screen. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 15 How to Set Up Postgres Plus xDB Replication Server When installation has completed, click the Finish button on the completion screen. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 16 How to Set Up Postgres Plus xDB Replication Server Installing With Advanced Server When you install Postgres Plus Advanced Server, you can also install xDB Replication Server at the same time. Note: Only the installation screens relevant to xDB Replication Server are illustrated in this tutorial. During Advanced Server installation you will be presented with a components selection screen. Be sure at a minimum, that the Database Server and xDB Replication Server components are selected. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 17 How to Set Up Postgres Plus xDB Replication Server Continue through the Advanced Server installation screens. When you encounter the xDB Replication Server Components screen, leave all items selected. This will install the xDB Replication Console, the publication server, and the subscription server. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 18 How to Set Up Postgres Plus xDB Replication Server In the xDB Replication Server Configuration screen enter the following: • • • Publication Server Port. An available port on which the publication server will run or allow it to default. Default port number is 9011. Subscription Server Port. An available port on which the subscription server will run or allow it to default. Default port number is 9012. Replication Server Control Database. The database name of the xDB Control database. If the database does not already exist, it will be created. Default database name is postgres (edb for Advanced Server installed in Oracle compatible configuration mode). Note: The Replication Server Control Database field is set to xdb in this example instead of using the default. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 19 How to Set Up Postgres Plus xDB Replication Server Continue with the installation process. When the Pre-Installation Summary screen appears make note of the following information as you will need it during xDB Replication Server configuration: • • • • • Database Port. Port number on which the database server listens for connections. Used by the publication server and the subscription server to connect to the xDB Control database. Database Superuser. Database superuser name. Used by the publication server and subscription server to connect to the xDB Control database. Operating System Account. Operating system account user name. Used to start and run the publication server and subscription server. xDB Publication Server Port. Port number on which the publication server runs. Needed when configuring the publication server. xDB Subscription Server Port. Port number on which the subscription server runs. Needed when configuring the subscription server. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 20 How to Set Up Postgres Plus xDB Replication Server Complete the remaining installation steps for Advanced Server. Post-Installation Host Environment When the installation process is finished, you will have a publication server daemon and a subscription server daemon running on your host computer. (On Microsoft Windows® systems, the publication server and subscription server run as services named Publication Service and Subscription Service.) The application menu contains a new item for the xDB Replication Console. Note: On some Linux systems, you may have to restart the server before you can see the xDB Replication Console choice in the application menu. If you did not choose an existing database as your xDB Control database, a new database will have been created. The following files are created that you will use during the configuration process. File Name edb-repl.conf (Linux) Location /etc Description xDB Control database connection Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 21 How to Set Up Postgres Plus xDB Replication Server File Name Location Description information xDB Control database connection edb-repl.conf (Windows) POSTGRES_PLUS_HOME\etc information /etc/init.d edb-xdbpubserver (Linux) Start, stop, or restart the publication server edb-xdbsubserver (Linux) /etc/init.d Start, stop, or restart the subscription server pubserver.log subserver.log POSTGRES_PLUS_HOME/bin/ Publication server log file logs POSTGRES_PLUS_HOME/bin/ Subscription server log file logs Note: POSTGRES_PLUS_HOME is the directory where Postgres Plus is installed. Prerequisite Steps For the example in this tutorial, a replication system is created based upon a publication consisting of two tables named dept and emp belonging to schema edb in an Oracle database. The Oracle system identifier (SID) is xe. Step 1: The JDBC driver for Oracle, ojdbc14.jar, must be accessible to the Java virtual machine (JVM) running xDB Replication Server. Download the Oracle JDBC driver, ojdbc14.jar, from the Oracle download site and copy it to the lib/jdbc subdirectory of the Postgres Plus home directory. $ su root Password: $ cd /opt/PostgresPlus/8.4SS/lib/jdbc $ cp /home/user/Downloads/ojdbc14.jar . $ ls -l total 2516 -rw-r--r-- 1 root root 528518 2010-06-01 00:25 edb-jdbc14.jar -rw-r--r-- 1 root root 1555682 2010-07-05 17:20 ojdbc14.jar -rw-r--r-- 1 root root 472831 2010-06-01 00:25 postgresql-8.4-701.jdbc3.jar Step 2: Create a publication database user name in the Oracle database. The publication database user owns the triggers and other database objects that the publication server creates in the Oracle database to manage replication. The following SQL statements create the user name pubuser and grant the required privileges. CREATE USER pubuser IDENTIFIED BY password; GRANT CONNECT TO pubuser; GRANT RESOURCE TO pubuser; GRANT CREATE ANY TRIGGER TO pubuser; GRANT SELECT ON edb.dept TO pubuser; GRANT SELECT ON edb.emp TO pubuser; Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 22 How to Set Up Postgres Plus xDB Replication Server Step 3: Create a subscription database user name in the Postgres Plus database server. The subscription database user owns the database in which the replication tables reside. The user name subuser is created for this purpose in this example. In pgAdmin (Postgres Studio in Advanced Server), click the secondary mouse button on the Login Roles node and choose New Login Role. Fill in the Properties tab of the New Login Role dialog box. Click the Role Privileges tab and select the check boxes Superuser and Can Modify Catalog Directly. Click the OK button. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 23 How to Set Up Postgres Plus xDB Replication Server Step 4: Create a Postgres Plus subscription database owned by the subscription database user. The subscription database contains the replicated tables. The database subdb is created for this purpose in this example. In pgAdmin (Postgres Studio in Advanced Server), click the secondary mouse button on the Databases node and choose New Database. Fill in the Properties tab of the New Database dialog box. Be sure to select subuser in the Owner field then click the OK button. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 24 How to Set Up Postgres Plus xDB Replication Server Step 5 (For Linux only): Modifications may be necessary to the /etc/hosts file. The hostname -i command returns the IP address associated with the host name: $ hostname -i 127.0.0.2 If the loopback address 127.x.x.x is returned such as in the preceding example, edit the /etc/hosts file so that the network IP address is associated with the host name instead. The following example shows the modified /etc/hosts file so that the host name opensuse-vm is now associated with the network IP address 192.168.10.103 instead of the loopback address 127.0.0.2: 192.168.10.103 #127.0.0.2 opensuse-vm.vmplanet.net opensuse-vm opensuse-vm.vmplanet.net opensuse-vm Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 25 How to Set Up Postgres Plus xDB Replication Server On some Linux systems, you may need to restart the network service after you have modified the /etc/hosts file. This may be done a number of different ways depending upon the Linux system you are using as shown by the following variations: service network restart /etc/init.d/networking restart sudo /etc/init.d/networking restart The following example illustrates the service network command: $ su root Password: $ service network restart The hostname -i command now returns the network IP address of the host: $ hostname -i 192.168.10.103 Step 6: Add entries to the host-based authentication file, pg_hba.conf, to allow access to the xDB Control database and the subscription database from the publication server and subscription server. In a default Postgres Plus installation, this file is located in the data subdirectory of the Postgres Plus home directory. In general, the following entries are needed to allow the publication server and the subscription server access to the xDB Control database: host control_dbname control_dbuser host control_dbname control_dbuser pub_ipaddr/32 sub_ipaddr/32 md5 md5 If you installed xDB Replication Server using StackBuilder Plus, the values you substitute for control_dbname and control_dbuser are the entries made in the Control Database and User Name fields, respectively, on the Postgres Plus Installation Details screen during xDB Replication Server installation. If you installed xDB Replication Server during Advanced Server installation, the value you substitute for control_dbname is the entry made in the Replication Server Control Database field on the xdb Replication Server Configuration screen. Substitute enterprisedb for control_dbuser if Advanced Server installation was done in Oracle compatible configuration mode. Substitute postgres for control_dbuser if Advanced Server installation was done in PostgreSQL compatible configuration mode. The values you substitute for pub_ipaddr and sub_ipaddr are the network IP addresses where the publication server and the subscription server are running. On Linux systems, use the network IP address returned by the hostname -i command you used Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 26 How to Set Up Postgres Plus xDB Replication Server in Step 5. On Windows systems, open a Command Prompt window and use the ipconfig command to obtain the host’s network IP address. In general, the following entries are needed to allow the publication server and the subscription server access to the subscription database: host sub_dbname host sub_dbname sub_dbuser sub_dbuser pub_ipaddr/32 sub_ipaddr/32 md5 md5 The value you substitute for sub_dbname, was created in Step 4 (subdb in this example). The value you substitute for sub_dbuser, was created in Step 3 (subuser in this example). For this example, the resulting pg_hba.conf file appears as follows: # TYPE DATABASE USER CIDR-ADDRESS # "local" is for Unix domain socket connections only local all all # IPv4 local connections: host xdb postgres 192.168.10.103/32 host subdb subuser 192.168.10.103/32 host all all 127.0.0.1/32 # IPv6 local connections: host all all ::1/128 METHOD md5 md5 md5 md5 md5 Note: Since the publication server and the subscription server are running on the same host, only one entry is needed per database to allow access by both the publication server and the subscription server. Reload the configuration file after making the modifications. Choose Reload Configuration (Expert Configuration, then Reload Configuration on Advanced Server) from the Postgres Plus application menu. This will put the modified pg_hba.conf file into effect. Registering the Publication Server and Creating a Publication Step 1: Start the publication server if it is not already running. Note: If the publication server has not been restarted since copying the Oracle JDBC driver, ojdbc14.jar, to the lib/jdbc subdirectory of your Postgres Plus installation, you must restart the publication server. On Linux hosts you can verify the publication server is running by using the following command: ps aux | grep pubserver Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 27 How to Set Up Postgres Plus xDB Replication Server This is shown by the following: $ ps aux | grep pubserver postgres 4820 0.7 2.2 192444 23188 pts/3 Sl 15:50 0:01 /usr/lib/jvm/java-1.6.0-sun-1.6.0.u6/jre/bin/java -jar edb-repserver.jar pubserver 9011 postgres 4895 0.0 0.0 3068 736 pts/3 S+ 15:53 0:00 grep pubserver For Windows hosts open Control Panel, Administrative Tools, and then Services. The publication server runs as a service named Publication Service. If the publication server is running and you wish to restart it, run the edbxdbpubserver script located in the /etc/init.d directory with the restart option. Run the script as the publication/subscription service account that you chose during installation, which is operating system user postgres in this example (enterprisedb for Advanced Server installed in Oracle compatible configuration mode). $ su postgres Password: $ /etc/init.d/edb-xdbpubserver restart Password: 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: $ /etc/init.d/edb-xdbpubserver start Password: For Windows hosts open Control Panel, Administrative Tools, and then Services. 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. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 28 How to Set Up Postgres Plus xDB Replication Server Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 29 How to Set Up Postgres Plus xDB Replication Server Step 3: 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 of Step 6 of Prerequisite Steps. (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 (xDB Replication Server Configuration screen if this is an Advanced Server installation). 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 Plus Installation Details screen (use enterprisedb if this is an Advanced Server installation in Oracle compatible configuration mode). Password. Password you specified on the Postgres Plus Installation Details screen for the database superuser (password you chose for enterprisedb if this is an Advanced Server installation in Oracle compatible configuration mode). Save login information. Check this box so you do not have to re-enter the User Name and Password each time you restart the xDB Replication Console. Click the Register button after you have filled in the fields. A Publication Server node appears in the replication tree in the xDB Replication Console window. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 30 How to Set Up Postgres Plus xDB Replication Server Step 4: Create a publication database definition. Note: The Oracle database server must be running and accepting client connections for this and subsequent steps. Click the secondary mouse button on the Publication Server node and choose Add Database. The Add Database dialog box appears. Fill in the following fields: • • • • • • Database Type. Be sure Oracle is selected. Host. IP address of the host on which the Oracle database server is running. Port. Port on which the Oracle database server is listening for connections. User. The publication database user name created in Step 2 of Prerequisite Steps. Password. Password of the database user. Service ID. The Oracle System Identifier (SID) of the Oracle instance running the publication database if the SID radio button is selected, or the net service name of a connect descriptor as defined in the TNSNAMES.ORA file if the Service Name radio button is selected. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 31 How to Set Up Postgres Plus xDB Replication Server Step 5: Click the Test button. If Test Result: Success appears, click the OK button, then click the Save button. A Publication Database node is added to the replication tree under the Publication Server node. Step 6: Click the secondary mouse button on the Publication Database node and choose Create Publication. The Create Publication dialog box appears. Fill in the following fields: • • 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. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 32 How to Set Up Postgres Plus xDB Replication Server • • 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 was checked, then views would appear in the Publish list as well. Select All. Check this box if you want to include all tables and views in the Publish list in the publication. Click the Create button. A Publication node is added to the replication tree. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 33 How to Set Up Postgres Plus xDB Replication Server Registering the Subscription Server and Creating a Subscription Step 1: Start the subscription server if it is not already running. Repeat the same process as in Step 1 of Registering the Publication Server and Creating a Publication. Note: If the subscription server has not been restarted since copying the Oracle JDBC driver, ojdbc14.jar, to the lib/jdbc subdirectory of your Postgres Plus installation, you must restart the subscription server. Use script /etc/init.d/edb-xdbsubserver located in the /etc/init.d directory to restart or start the subscription server. This is illustrated by the following: $ ps aux | grep subserver postgres 22614 0.1 2.2 192236 23300 ? Sl 15:02 0:02 /usr/lib/jvm/java-1.6.0-sun-1.6.0.u6/jre/bin/java -jar edb-repserver.jar subserver 9012 vmplanet 23507 0.0 0.0 3068 740 pts/3 S+ 15:25 0:00 grep subserver $ su postgres Password: $ /etc/init.d/edb-xdbsubserver restart Password: Enter the password for the operating system user name when prompted. For Windows hosts open Control Panel, Administrative Tools, and then Services. Use the Start or Restart link for the service named Subscription Service. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 34 How to Set Up Postgres Plus xDB Replication Server Step 2: Open the xDB Replication Console. Click the secondary mouse button on the Replication Servers node and choose Register Subscription Server. The Register Subscription 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 subscription server. This is the network IP address used for sub_ipaddr in the pg_hba.conf file of Step 6 of Prerequisite Steps. (Do not use localhost for this field.) Port. Port number the subscription server is using. This is the port number you specified on the Subscription Server Details screen (xDB Replication Server Configuration screen if this is an Advanced Server installation). User Name. Database superuser name that the subscription server uses to connect to the xDB Control database. This is the user name you specified on the Postgres Plus Installation Details screen (use enterprisedb if this is an Advanced Server installation in Oracle compatible configuration mode). Password. Password you specified on the Postgres Plus Installation Details screen for the database superuser (password you chose for enterprisedb if this is an Advanced Server installation in Oracle compatible configuration mode). Save login information. Check this box so you do not have to re-enter the User Name and Password each time you restart the xDB Replication Console. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 35 How to Set Up Postgres Plus xDB Replication Server Click the Register button after you have filled in the fields. A Subscription Server node appears in the replication tree. Step 3: Create a subscription database definition. Click the secondary mouse button on the Subscription Server node and choose Add Database. The Add Database dialog box appears. Fill in the following fields: • • • • • • Database Type. Be sure Postgres Plus is selected. (Select EnterpriseDB for an Advanced Server installation in Oracle compatible configuration mode.) Host. IP address of the host on which the subscription database server is running. Port. Port on which the database server is listening for connections. User. The subscription database user name created in Step 3 of Prerequisite Steps. Password. Password of the database user. Database. The name of the subscription database created in Step 4 of Prerequisite Steps. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 36 How to Set Up Postgres Plus xDB Replication Server Step 4: Click the Test button. If Test Result: Success appears, click the OK button, then click the Save button. A Subscription Database node is added to the replication tree under the Subscription Server node. Step 5: Click the secondary mouse button on the Subscription Database node and choose Create Subscription. The Create Subscription dialog box appears. Unless otherwise noted, enter the same values you gave in Step 3 of Registering the Publication Server and Creating a Publication. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 37 How to Set Up Postgres Plus xDB Replication Server • • • • • • Subscription Name. Enter a name for the subscription that is unique amongst all subscription names. Host. Network IP address of the publication server that appears as the parent node of the publication to be subscribed to when viewed in the replication tree. Port. Port used by the publication server. User Name. User name of the registered publication server. Password. Password of the user. Publication Name. Click the Load button to get a list of available publications. Choose the publication to which to subscribe. Click the Create button. A Subscription node is added to the replication tree. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 38 How to Set Up Postgres Plus xDB Replication Server Snapshot Replication The very first replication from a publication to a subscription must be done using snapshot replication. Once the first snapshot is successfully taken, subsequent replications can be done using either the synchronization method (if the publication was not initially defined as a snapshot-only publication) or the snapshot method. Step 1: Click the secondary mouse button on the Subscription node and choose Snapshot. The Snapshot dialog box appears. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 39 How to Set Up Postgres Plus xDB Replication Server Step 2: Click the Snapshot button. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 40 How to Set Up Postgres Plus xDB Replication Server Step 3: Snapshot Taken Successfully will appear if the snapshot was successful. Click the OK button. If the snapshot was not successful, scroll through the messages in the Snapshot dialog box window. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 41 How to Set Up Postgres Plus xDB Replication Server The publication has now been replicated to the subscription database. The subscription tables appear under a schema with the same name as the Oracle schema, which is edb in this example. Synchronization Replication Step 1: Click the secondary mouse button on the Subscription node and choose Synchronize. The Synchronize dialog box appears. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 42 How to Set Up Postgres Plus xDB Replication Server Step 2: Click the Synchronize button. Step 3: Subscription Synchronized Successfully will appear if the synchronization was successful. Click the OK button. If the synchronization was not successful, scroll through the messages in the Synchronize dialog box window. Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 43 How to Set Up Postgres Plus xDB Replication Server Modifications have now been applied to the rows in the subscription tables reflecting changes made to the publication since the last replication. Conclusion In this Quick Tutorial you learned how to set up Postgres Plus xDB Replication Server. You should now be able to proceed confidently with a Technical Evaluation of Postgres Plus knowing that your data can be easily and efficiently replicated from an Oracle database to a Postgres Plus database. The following resources should help you move on with this step: • • • • • • • • • • PostgreSQL Replication Solutions Replication Training Classes from EnterpriseDB High Availability Database Solutions for PostgreSQL and Postgres Plus Replication Packaged Services from EnterpriseDB Postgres Plus Technical Evaluation Guide Postgres Plus Getting Started resources Postgres Plus Quick Tutorials Postgres Plus User Forums Postgres Plus Documentation Postgres Plus Webinars Copyright © 2010 EnterpriseDB Corporation. All rights reserved. 44
© Copyright 2024