Perforce Public Knowledge Base - P4toDB Getting Started Guide
Reset Search
 

 

Article

P4toDB Getting Started Guide

« Go Back

Information

 
Problem
I need to setup and run P4toDB.  I've read the README file but I'm not sure exactly what I need to do.
Solution

General Information

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.
 

Database Setup

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.
 

Character Sets

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.

UNICODE property

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).


Configuring P4toDB

Create your P4toDB Configuration File

Generate a sample configuration file:

p4todb.sh --sample-config

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:

  • P4USER
  • P4PORT
  • P4PASSWD

Edit your JDBC settings:

  • DRIVER
  • URL

Edit other properties (recommended but optional):

  • LOG_FILE
  • LOG_ROTATE_SIZE (consider reducing the size to 10MB)

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:

  • TABLE_PREFIX
  • COLUMN_PREFIX
  • UPPERCASE_IDS
  • DB_SCHEMA

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)

Configuration File Connection Test

Generate the DDL to confirm you can connect to both the Perforce Server and your database:


p4todb.sh --print-ddl
or
p4todb.bat --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.properties --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.


Run Replication

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 LOG_FILE.



Other Notes

Option --one-shot

This option loads the current checkpoint or journal and then stops.  This allows you to inspect meta data before starting continual replication.
 

Option --init-and-quit

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:

  1. Update the old P4TODB.TABLES column to have the new version number
     
  2. Compare the old table and new table definitions:  ALTER the old table definition as required
     
  3. New tables may also be created:  rename the new table (with the new TABLE_PREFIX) to use the old TABLE_PREFIX
     
  4. Cleanup:  drop the new db objects and remove the new row from P4TODB_CFG

Restart replication with your original configuration file.

Related Links

Feedback

 

Was this article helpful?


   

Feedback

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

Characters Remaining: 255