Create tables

Create the public.products table in the source and destination databases:

CREATE TABLE public.products (
    id    integer PRIMARY KEY,
    name  text NOT NULL,
    price numeric(10,2) NOT NULL,
    stock integer DEFAULT 0
) ON CONFLICT DO NOTHING;

Note that the table in the destination database must have a unique key.

Prepare the configuration file

Generate the progate.yaml configuration file:

./prosync config generate > progate.yaml

Edit the configuration file so that it looks as follows:

version: 1
prosync_options:
	transfer_id: 3026bf7e-abbe-4e2a-b883-b602b964402e
	import_batch_size: 0
source:
	database: progate
	driver_name: postgresql
	host: localhost
	max_conn: 50
	options:
		- sslmode=disable
	password: ""
	port: 5431
	username: progate
destination:
	database: progate
	driver_name: postgresql
	host: localhost
	max_conn: 50
	options:
		- sslmode=disable
	password: ""
	port: 5430
	username: progate
tasks:
	- id: tab1
	  table:
		source_table: public.products
		destination_table: public.products

Perform the initialization

Run the command:

./bin/prosync init -f ./bin/progate.yaml

The publication and logical replication slot are created.

Run the synchronization

Run the command:

./bin/prosync run -f ./bin/progate.yaml

prosync reads changes from the replication slot and applies them to the destination table.

Insert data and check the destination

While prosync is in operation, insert data into the source table:

INSERT INTO public.products VALUES (1, 'Widget',  9.99, 100) ON CONFLICT DO NOTHING;
INSERT INTO public.products VALUES (2, 'Gadget', 19.99,  50) ON CONFLICT DO NOTHING;
INSERT INTO public.products VALUES (3, 'Gizmo',  29.99,  25) ON CONFLICT DO NOTHING;

Check the destination table:

SELECT * FROM public.products ORDER BY id;
 id |  name  | price | stock
----+--------+-------+-------
  1 | Widget |  9.99 |   100
  2 | Gadget | 19.99 |    50
  3 | Gizmo  | 29.99 |    25
(3 rows)

Stop the synchronization

Send the SIGINT signal (Control+C).

Complete and clean up

Run the command:

./bin/prosync complete -f ./bin/progate.yaml

The replication slot and publication get removed. WAL files are no longer accumulated.

Create tables

In the Oracle source:

CREATE TABLE system.products (
    id    NUMBER(10) PRIMARY KEY,
    name  VARCHAR2(255) NOT NULL,
    price NUMBER(10,2)  NOT NULL,
    stock NUMBER(10) DEFAULT 0
) ON CONFLICT DO NOTHING;

In the PostgreSQL destination:

CREATE TABLE public.products (
    id    integer PRIMARY KEY,
    name  text NOT NULL,
    price numeric(10,2) NOT NULL,
    stock integer DEFAULT 0
) ON CONFLICT DO NOTHING;

Prepare the configuration file

Generate the progate_ora.yaml configuration file:

./prosync config generate > progate_ora.yaml

Edit the configuration file so that it looks as follows:

version: 1
prosync_options:
	transfer_id: 550e8400-e29b-41d4-a716-446655440000
	import_batch_size: 0
source:
	database: PROGATE
	driver_name: oracle
	host: localhost
	max_conn: 50
	password: PROGATE
	port: 1521
	username: SYSTEM
destination:
	database: progate
	driver_name: postgresql
	host: localhost
	max_conn: 50
	options:
		- sslmode=disable
	password: ""
	port: 5430
	username: progate
tasks:
	-id: tab1
		table:
		source_table: SYSTEM.PRODUCTS
		destination_table: public.products

Note that for Oracle, the source.database configuration parameter defines the system identifier (SID) or service name. For testing purposes, prosync_options.import_batch_size is set to zero to transfer data immediately. Avoid doing so in the production environment.

Perform the initialization

Run the command:

./bin/prosync init -f ./bin/progate_ora.yaml

The current source SCN is recorded. No objects are created in Oracle.

Run the synchronization

Run the command:

./bin/prosync run -f ./bin/progate_ora.yaml

prosync reads changes from Oracle redo logs through LogMiner and applies them to the destination.

Insert data and check the destination

While prosync is in operation, insert data into the source table. Execute each statement followed by COMMIT separately in sqlplus:

INSERT INTO SYSTEM.PRODUCTS VALUES (1, 'Widget', 9.99, 100) ON CONFLICT DO NOTHING;
COMMIT;

INSERT INTO SYSTEM.PRODUCTS VALUES (2, 'Gadget', 19.99, 50) ON CONFLICT DO NOTHING;
COMMIT;

INSERT INTO SYSTEM.PRODUCTS VALUES (3, 'Gizmo', 29.99, 25) ON CONFLICT DO NOTHING;
COMMIT;

Check the destination table:

SELECT * FROM public.products ORDER BY id;
 id |  name  | price | stock
----+--------+-------+-------
  1 | Widget |  9.99 |   100
  2 | Gadget | 19.99 |    50
  3 | Gizmo  | 29.99 |    25
(3 rows)

If you do not see the changes in the destination table, force switch the redo log:

ALTER SYSTEM ARCHIVE LOG CURRENT;

And query the destination table again.

Stop the synchronization

Send the SIGINT signal (Control+C). The current SCN is saved automatically, so at the next launch of run, data transfer will start with this SCN.

Complete and clean up

Run the command:

./bin/prosync complete -f ./bin/progate_ora.yaml

The transaction cache is cleaned up. No Oracle objects are deleted as they were not created during the initialization.