P4toDB mirrors Helix server metadata in an SQL Database. It can mirror all Perforce data or just a subset.
P4toDB's database replication is very similar to Helix Server metadata replication except that it converts retrieved database records into SQL statements for updates to the target database. You can read about Helix Replication in the Replication Chapter in the Helix Versioning Engine Administrator Guide: Multi-Site Deployment. 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.
There are three parts to P4toDB replication: the Helix 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 Helix Server.
Helix Server Setup
Identify a Helix user for P4toDB. This user (type: standard) must have "super" access.
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.7 or later JRE is required for P4toDB 2017.1
The 'java' executable must be in your PATH. Run "java -version" to verify that it is a supported.
Unzip the downloaded p4todb zip file into a directory of your choosing.
You will need a jdbc 4 driver suitable for your database. Copy your jdbc driver jar 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 Helix 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 Helix 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. This is the Java encoding (not the same as Helix character sets). See Java encodings: https://docs.oracle.com/javase/8/docs/technotes/guides/intl/encoding.doc.html
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 based in the US, 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 your database character set stores data in UTF-8. 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 may cause you to run into key length restrictions (900 bytes).
Edit your P4toDB.bat/sh Script
If your Perforce server is not unicode enabled, edit the CHARSET variable as described above.
Create your P4toDB Configuration File
Generate a sample configuration file:
Edit your P4toDB Configuration File
Edit your Perforce server settings:
If you do not wish to put your password into the configuration file, leave P4PASSWD blank and
obtain the ticket before starting p4todb.
If your Helix server is SSL enabled, set this to true:
If SSL is true, you must also run "p4 trust" to trust the connection.
Edit your JDBC settings:
Edit other properties (recommended but optional):
- LOG_ROTATE_SIZE (consider reducing the size to 5-10MB)
Alternate Journal (Journal Prefix) location. If you rotate your journal to a non-default location not defined by the configurable "journalPrefix", set:
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 file for the meaning of these table and column naming properties:
To store metadata from multiple Helix servers in the same database instance, each Helix server's P4toDB configuration file should have a different TABLE_PREFIX.
Tables to Replicate
It is recommended that you give some thought to what Perforce tables you need to replicate. Perforce index tables (like db.revcx) and subset tables (like db.revdx) might not provide additional value. The db.have table, which stores which files all clients have synced, can be huge and hopefully avoided. Please review Perforce's database schema. Again, avoid replicating tables that are not needed for you needs.
You can control which Perforce metadata tables are replicated by specifying either of these properties:
P4toDB 2014.1 and earlier, with later Helix Server releases, require adjusting of p4todb table P4TODB_CFG's column 'TABLES' to support replicating all tables in servers >= 2015.1. See Resolving P4toDB issues with Helix server releases >= 2015.1. These problems are fixed in P4todb 2017.1
- DB_TO_REPL (only replicate these tables)
- DB_NO_REPL (all tables except these tables)
Adding tables later is possible but not easy. If you do not wish to start over from a checkpoint, see Adding Tables to P4toDB Replication.
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.
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 Helix 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 Helix Server
See P4toDB and Upgrading your Helix Server