Perforce Public Knowledge Base - Adding Tables to P4toDB Replication
Downloads Blog Company Integrations Careers Contact Try Free
Menu Search
Reset Search



Adding Tables to P4toDB Replication

« Go Back



I am currently using P4toDB to replicate a subset of all tables.

How to I replicate additional tables without losing my existing data?


There is no easy way to replicate additional tables;  do not just add the tables to your configuration file.

You have two options:

1)  Start over:  drop all tables/views/sequences you currently have.  (For some SQL vendors, the easiest way may be to drop and recreate the database.)  Then repeat your initial load from checkpoint.

2)  Load the new tables and "merge" the results:  You should only consider this if a "start over" checkpoint load takes a long time and that long down time is unacceptable.

Merging New Tables into Your P4toDB Replication

While your original replication is running, you can load the additional tables into tables with a different prefix.  When the old process and the new additional-table process are at the same replication spot, you can then "merge" the old and new tables into a single replication process.

Load new tables

Create a new configuration file containing:

  • only the new tables to replicate
  • different value for TABLE_PREFIX

Load the tables from checkpoint:

p4todb --config-file newConfig --checkpoint XXX --one-shot

Sync new and old processes

This step is to make sure both process are in the exact same position regarding journal file replication.

Run both processes using option "--one-shot".  This will stop p4todb after a journal finishes.

Your P4TODB_CFG should have two rows:  one for the original replication and another for the new tables.

After each journal --one-shot load, you will have values IS_JOURNAL=1, AT_JNL_END=1, OFFSET=0, and LINES=0.

Check table P4TODB_CFG and ensure that columns IS_JOURNAL, AT_JNL_END, NUMBER, OFFSET, and LINES are all equal for the two processes. If the NUMBER column is unequal, run p4todb (w/--one-shot) for that replication process until the NUMBER values are equal.

You are now ready to merge the two processes.

Final Merge

To your original P4TODB_CFG row, append the values into column TABLES from your new replication row,
separated by a "|".

Using database utilities, rename the new tables to have the same table prefix as your original process.   Oracle users should also rename the sequences.

Add the new tables to your original configuration file.  The tables listed in column P4TODB_CFG.TABLES must match the columns listed in your configuration file.

Restart only the original process


Your original config file replicated some tables and used no value for TABLE_PREFIX.

Your new tables will be for changelists:  change, changex, and desc.  You used TABLE_PREFIX=SMALL.

P4TODB_CFG will have two rows:
  • REPL_ID = 'P4TODB_CFG' for the original tables
  • REPL_ID = 'SMALLP4TODB_CFG' for the new tables

Update the TABLES column (SQL Server syntax):
update old_tbl set old_tbl."TABLES" = old_tbl."TABLES" + '|' + new_tbl."TABLES"
  from P4TODB_CFG old_tbl, P4TODB_CFG new_tbl
  where old_tbl.REPL_ID = 'P4TODB_CFG'
  and new_tbl.REPL_ID = 'SMALLP4TODB_CFG' ;

Rename your tables (SQL Server syntax):
exec sp_rename 'SMALLCHANGE', 'CHANGE' ;
exec sp_rename 'SMALLCHANGEX', 'CHANGEx' ;
exec sp_rename 'SMALLDESC', 'DESC' ;

Rename your tables (Oracle syntax):
rename 'SMALLCHANGE' to 'CHANGE' ;
rename 'SMALLCHANGEX' to 'CHANGEx' ;
rename 'SMALLDESC' to 'DESC' ;

Oracle:  rename the sequences using the rename command.  To find their names:
select sequence_name from ALL_SEQUENCES where sequence_name like 'SMALL%' ;

Related Links



Was this article helpful?



Please tell us how we can make this article more useful.

Characters Remaining: 255