P4toDB mirrors Perforce metadata in an SQL Database. It can mirror all Perforce data or just a subset.
P4toDB's database replication is very similar to Perforce Server metadata replication except that it converts retrieved database records into SQL statements for updates to the target database. You can read about Perforce Replication in the Replication Chapter in the System Administrator's Guide. This article provides an overview of the process. Please consult the P4toDB README.txt file included in the distribution for additional detail on the steps below, or the P4toDB README Knowledge Base article.
There are three parts to P4toDB replication: the Perforce Server, the P4toDB process, and the SQL Database. They may all run on the same machine or each may be on its own machine. For performance, it is recommended that at least the P4toDB process and your database be on a separate machine from your Perforce Server.
Perforce Server Machine Setup
No additional setup is required.
Important note: you must not compress (-z flag) your journal files when checkpointing your server or rotating its journal. P4toDB does not work with compressed checkpoint or journal files. Once you've loaded your checkpoint, you can do future checkpoints with the -Z flag as this compresses just the checkpoint, not the journal.
P4toDB Machine Setup
A Java 1.6 or later JRE is required. You will need a jdbc driver suitable for your database. Install the P4toDB software according to the release notes and copy your jdbc driver into the <install_dir>/lib directory.
Create a user and schema in your database to hold the P4toDB tables.
Ensure sufficient disk space for your data. For a ballpark amount, allocate 2 to 4 times the space consumed by the Perforce Server's db.* files that you intend to replicate.
The customer is responsible for tuning the database. Perforce Support can assist.
Characters that cannot be translated, either from Perforce to P4toDB, or from the P4toDB to Database, will appear as "?" in your database data.
Two pieces of information control handling of bytes and characters.
p4todb.bat/sh CHARSET variable
This setting defines how the bytes received from the Perforce server will be converted to characters.
Unicode enable Perforce Server: use "UTF-8", always. This is the default.
Non-unicode Perforce Server: always change the CHARSET environment variable in the p4todb.bat/sh script to a valid java character encoding that best represents your meta data. For example, "cp1252 "(windows 1252) if most your Perforce clients are on windows, or "iso-8859-15" if you have Linux clients.
Your P4toDB properties file defines the UNICODE property. This controls the datatype of the database character columns.
Unicode enable Perforce Server: use "UNICODE = true". This is the default.
Non-unicode Perforce Server: In general, you can still use UNICODE = true. If set to false, your table columns will be created as varchar rather than unicode capable nvarchar. Consult your database's admin guide for the performance implications of using varchar vs nvarchar.
One specific example for using false is MS SQL Server as it stores nvarchar in UNICODE UCS-2. UCS-2 always uses 2 bytes per character, even for ASCII characters. This impacts performance and adversely allow you to run into key length restrictions (900 bytes).
Create your P4toDB Configuration File
Generate a sample configuration file:
Edit your P4toDB.bat/sh Script
If your Perforce server is not unicode enabled, edit the CHARSET variable as described above.
Edit your P4toDB Configuration File
Edit your Perforce server settings:
Edit your JDBC settings:
Edit other properties (recommended but optional):
- LOG_ROTATE_SIZE (consider reducing the size to 10MB)
Alternate Journal (Journal Prefix) location. If you rotate your journal to a non-default location, set to the 'journalPrefix' used for acheckpoint or journal rotation:
Other Optional Properties
Table and Column naming properties: some default column and table names are SQL reserved names. If you use the default names, this means you will have to delimit those names in all of your SQL statements. Consult the properties files for the meaning of these table and column naming properties:
It is recommended that you give some thought to what Perforce tables you need to replicate. Perforce index tables might not provide additional value. Please review Perforce's database schema. You can control which Perforce metadata tables are replicated by specifying either of these properties:
- DB_TO_REPL (only replicate these tables)
- DB_NO_REPL (all tables except these tables)
Later Helix Server releases require hacking of p4todb table P4TODB_CFG's column 'TABLES' to support replicating all tables in servers >= 2015.1.
For performance reasons, avoid replicating tables that won't be needed. To workaround this issue, use option --print-ddl, extract the CREATE TABLE statement for creating table p4todb_cfg and alter column 'tables' to be VARCHAR(1000). Create this table and only this table. You can now proceed to loading your checkpoint or running --init-and-quit as mentioned below.
Configuration File Connection Test
Generate the DDL to confirm you can connect to both the Perforce Server and your database based on your config file 'my.properties'.
p4todb.sh --config-file my --print-ddl
p4todb.bat --config-file my --print-ddl
If the DDL is generated and displayed, then you have successfully connected to both the Perforce Server and your SQL database.
If you specified table/column alternate naming properties, review the generated DDL to assure table and column names are as desired.
Load Checkpoint into the Database
Find your latest checkpoint, make sure it is not compressed, and that it is accessible by your Perforce Server process.
The command to run, with the appropriate checkpoint number, is:
p4todb --config-file my --checkpoint NUMBER --one-shot
The --one-shot parameter loads the checkpoint and then the process ends. Inspect the P4toDB log for errors and do a sanity check of your database tables to ensure proper load. For example, select the last changelist number from the change table.
The P4toDB process runs continually to transfer Perforce transactions to the SQL database.
Monitoring the P4toDB Process
A quick check of the p4todb_config will show the last time the P4toDB process requested metadata:
select MODIFIED from p4todb_config ;
If the datetime of the MODIFIED column is not changing, check that your P4toDB process exists.
A Severe log entry will stop the P4toDB process and log the error to the
This option loads the current checkpoint or journal and then stops. This allows you to inspect meta data before starting continual replication.
You typically use this option with the "--checkpoint <ckp.number>" option. This option:
- creates the tables (and sequences for oracle)
- populates table P4TODB_CFG, specifically the columns NUMBER and TABLES
- it does not create indexes nor load any data
This option allows you to ALTER any table definitions, such as column lengths, before loading the checkpoint.
Adjusting the Schema
You can use the --print-ddl to view you schema before it's created. Do not change any column or table names.
One method to alter your schema is to perform these steps:
- create tables and populate P4TODB_CFG:
p4todb --config-file <file> --init-and-quit --checkpoint <ckp.number>
- schema changes: ALTER any tables.
- Load the checkpoint:
p4todb --config-file <file> --one-shot
This also creates indexes.
- schema changes: add any additional indexes
- Start replication:
p4todb --config-file <file>
Upgrading Your Perforce Server
The currently supported method is to drop all tables are reload from a checkpoint.
Unsupported: When Perforce is upgraded, some schema changes may be necessary. The quick way is to generate a new empty schema and compare the new to old.
Make a copy of your configuration file and change the TABLE_PREFIX within. Now run "p4todb --init-and-quit --config-file <new-config-file>". You'll now have two schemas of tables and two rows in table P4TODB_CFG.
Compare the values in the column P4TODB_CFG.TABLES which is a "|" separated list of "<table> <version>" This will guide you to the tables you need to ALTER.
For each table version changes:
- Update the old P4TODB.TABLES column to have the new version number
- Compare the old table and new table definitions: ALTER the old table definition as required
- New tables may also be created: rename the new table (with the new TABLE_PREFIX) to use the old TABLE_PREFIX
- Cleanup: drop the new db objects and remove the new row from P4TODB_CFG
Restart replication with your original configuration file.