Initial Database Synchronization for PostgreSQL to PostgreSQL Replication
This section provides information about how initial synchronization of the database is performed when replicating data from PostgreSQL to PostgreSQL (cloud target databases).
You can set up an active replication between the following source and target platform combinations:
-
On-Prem Logical Source to Aurora Target
-
On-Prem Physical Source to Aurora Target
-
Azure Flexi Server Source to Aurora Target
-
Aurora Source to Azure Flexi Target
-
Aurora Source to On-Prem Target
To set up an active replication:
-
Run the pg_setup utility on both the source and target servers to prepare the environment.
-
Start SharePlex and execute the stop post command on the target server:
sp_ctrl (pslrhel7linux01:4073)> stop post
It ensures that even if a configuration file is not activated on the source, the Poster queue will be created but remain stopped.
-
Close access to the source database for users.
-
Activate the configuration file on the source.
sp_ctrl> activate config <config_file_name>
This creates the necessary replication slot as defined in pg_setup.
-
In the source database, create a snapshot while keeping the session open:
begin transaction isolation level REPEATABLE READ;
select pg_export_snapshot();
-
Use the following snapshot to run pg_dump on the source:
pg_dump --snapshot=00000003-000001BC-1 -d <database_name> -Fc -b -v -f dump04172024.sql -n <schemas>
For example:
pg_dump --snapshot=00000003-000001BC-1 -d testdb -Fc -b -v -f dump04172024.sql -n splex --username=splex
-
Resume user access to the source database while pg_dump is in progress.
-
Use the dump file to execute pg_restore on the target database after pg_dump is completed:
pg_restore -v -h pslaurorapgdb01.cihp157rpcvu.us-west-1.rds.amazonaws.com -U splex -d auroratest -j2 dump04172024.sql -n splex
-
Once pg_restore is executed, log in to the target database and check the tables.
-
After successful restoration, start the post queue on the target server using start post in sp_ctrl:
sp_ctrl> start post
-
Validate replication to ensure everything is working correctly.
Apply an Oracle application patch or upgrade
Apply an Oracle Application Patch or Upgrade
This chapter contains procedures to follow when you need to apply an application patch or upgrade and there is an active replication configuration. These procedures apply to Oracle databases.
Contents
Before you patch or upgrade an application
Review the following topics before you patch or upgrade an application on a system where SharePlex replication is active.
Which procedure to use?
There are different procedures for applying an application patch or upgrade to an Oracle database while replication is in process. Which one to choose depends on what changes the patch or upgrade makes.
|
If the patch/upgrade applies DDL that is not supported by SharePlex. For details on the DDL that SharePlex supports, see the SharePlexSharePlex Release Notes. |
Manually apply the patch/upgrade to the source and target by following either of these procedures:
Apply Patch/Upgrade to Source then Copy it to Target
Apply Patch/Upgrade to Source and Target |
|
If the patch/upgrade does any of the following:
- Performs DML changes.
- Performs supported DDL on the source system. For details on the DDL that SharePlex supports, see the SharePlexSharePlex Release Notes.
- Changes users and security on source system (other than SharePlex)
|
Manually apply the patch/upgrade to the source, then allow SharePlex to replicate the changes to the target. Follow this procedure:
Apply Patch to Source and Replicate it to the Target
Note: Because this procedure assumes that SharePlex can replicate all of the changes that the patch or upgrade applies, the patch/upgrade is not applied to the target. |
The effect of patches and upgrades on partitioned replication
A patch or upgrade can make changes that affect the column partitions of vertically partitioned replication in your configuration file. Take the following into account when you perform this procedure.
| Adds columns that do not satisfy the column partition of the table |
(Optional) Drop the columns from the target table after the patch or upgrade is applied. |
| Adds columns that need to be in the column partition of the table |
Add those columns to the source and target column partition lists in the configuration file. |
| Drops columns that are part of the column partition of the table |
Remove those columns from the source and target column partition lists in the configuration file. |
| Changes the name of a column that is in the column partition of a table |
Change the column name in the source and target column partition lists in the configuration file. |
For more information, see Configure Vertically Partitioned Replication.
Naming conventions used
In these procedures, the "source" system is one of the following:
- The source system of a single-direction replication configuration, including cascading replication.
- All source systems of a consolidated replication configuration.
- The trusted source system in a peer-to-peer replication configuration.
In these procedures, the "target" system is one of the following:
- The target system of a single-direction replication configuration, including cascading and consolidated replication.
- The secondary systems in a peer-to-peer replication configuration.
In this procedure, the SharePlex commands in the procedure apply to all sp_cop instances that apply to the replication strategy you are using (for example, all sp_cop processes on a target in consolidated replication).
Apply patch/upgrade to source then copy it to target
Supported databases
Oracle on all supported platforms
When to use this procedure
Use this procedure if the patch or upgrade makes DDL changes of a type not replicated by SharePlex. For a list of objects for which DDL is supported, see the SharePlex Release Notes.
Overview of the procedure
Use this procedure to run an Oracle hot backup to copy patches or upgrades from the source system to the target system, instead of applying the patch or upgrade directly on the target system. This is useful if the patch or upgrade makes extensive changes that are of the type(s) not supported by SharePlex replication, or if you are unsure of what it does.
With this procedure, you can keep the configuration file active on the source system. You use the reconcile command to identify and eliminate the following:
- Duplicate DML and supported DDL from the patch or upgrade operations that were replicated but also applied by the backup.
- Production transactions that were replicated but also applied by the backup.
Apply the patch/upgrade
To apply the patch or upgrade:
- Stop user access to the instances involved in replication on the source and target systems, but do not shut down SharePlex.
- On the source system, run sp_ctrl.
-
On the source system, flush the data to the target system. This command stops Post and places a marker in the data stream that establishes a synchronization point between source and target data.
sp_ctrl> flush datasource
where: datasource is the datasource of the source instance, for example o.oraA.
- On the source system, apply the patch or upgrade.
- On the source system, restore user access to the source instance.
-
[If the patch/upgrade adds objects that must be replicated] Edit the configuration file as follows (do not deactivate it). The patch or upgrade may have affected column partitions or column conditions in partitioned replication. For more information, see Change an Active Configuration File.
-
Copy the configuration file.
sp_ctrl> copy config filename to newname
-
Edit the copy.
sp_ctrl> edit config newname
Save the file.
-
Do one of the following:
-
If you added objects in the previous step, activate the new configuration file.
sp_ctrl> activate config newname
-
If you did not make any changes to the original configuration file, activate that one.
sp_ctrl> activate config filename
- On the source, run the Oracle hot backup.
-
On the source, switch log files and make a note of the highest archive-log sequence number.
On-premises database:
svrmgr1> alter system switch logfile;
Amazon RDS database:
Use Amazon RDS procedure rdsadmin.rdsadmin_util.switch_logfile.
- On the target, recover the target database from the hot backup using the UNTIL CANCEL option in the RECOVER clause, and cancel the recovery after Oracle fully applies the log that you recorded in the previous step.
- On the target system, open the database with the RESETLOGS option.
- Run the Database Setup utility on the target instance, but do not create a new user. Choose the existing SharePlex user and password (copied in the backup). For more information, see Database Setup Utilities in the SharePlex Reference Guide.
-
On the target system, issue the reconcile command using the sequence number of the log that you noted previously. If you are using named post queues, issue the command for each one. If you do not know the queue names, issue the qstatus command first.
sp_ctrl> qstatus
sp_ctrl> reconcile queue queuename for datasource-datadest seq sequence_number
Example: reconcile queue SysA for o.oraA-o.oraA seq 1234
Note: The reconcile process retains control of sp_ctrl until it is finished.
- On the target system, if the patch or upgrade installed triggers on the tables in replication, disable them or run the sp_add_trigger.sql utility script so that the triggers ignore the SharePlex user.
- On the target system, if the patch or upgrade added check constraints or scheduled jobs that perform DML, disable them.
-
On the target system, perform any cleanup required by partitioned replication.
-
On the target system, start Post.
sp_ctrl> start post
The two instances are now in synchronization, and SharePlex resumes replication.
