pgverify

README

pgverify

pgverify is a tool for verifying consistency of data between different database engines.

Why?

Migrating database engines can be a huge headache; newer relational databases often try to mitigate this by asserting some level of PostgreSQL syntax compatability, but there can be small differences in data types, output formats, etc that make it difficult to affirmatively verify that the actual data between instances is in-sync.

pgverify attempts to solve this with a suite of various tests executed against the specified targets and compared for consistency, many of which generate per-table hashes from the data.

Getting started

First, clone this repository and run make build to generate the pgverify binary. Then, run with specified targets as a list of PostrgeSQL syntax connection URIs:

$ pgverify postgresql://user1:passwd1@host1:port/database postgresql://user2:passwd2@host2:port/database [...]

The resuling table output will outline which tables are in sync between the targets, and which are not:

$ ./pgverify \
		--tests bookend,full,rowcount,sparse \
		--include-tables testtable1,testtable2,testtable3 \
		--aliases cockroachdb/cockroach:latest,cockroachdb/cockroach:v21.2.0,postgres:10,postgres:11,postgres:12.6 \
		postgres://root@crdb-latest:26257/testdb \
		postgres://root@crdb-21-2-0:26258/testdb \
		postgres://postgres@psql-10:5432/testdb \
		postgres://postgres@psql-11:5433/testdb \
		postgres://postgres@psql-12-6:5434/testdb
+--------+------------+----------------------------------+----------------------------------+----------+----------------------------------+-------------------------------+
| schema |   table    |             bookend              |               full               | rowcount |              sparse              |            target             |
+--------+------------+----------------------------------+----------------------------------+----------+----------------------------------+-------------------------------+
| public | testtable1 | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | cockroachdb/cockroach:latest  |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | cockroachdb/cockroach:v21.2.0 |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:10                   |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:11                   |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:12.6                 |
|        | testtable2 | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | cockroachdb/cockroach:latest  |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | cockroachdb/cockroach:v21.2.0 |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:10                   |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:11                   |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:12.6                 |
|        | testtable3 | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | cockroachdb/cockroach:latest  |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | cockroachdb/cockroach:v21.2.0 |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:10                   |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:11                   |
|        |            | 354c0eedeecf8907a90dd98ed3d826d9 | bffe76957644e5755db2dd2f608bfdb3 |       50 | 454ce26f516555a103be83011b043dfd | postgres:12.6                 |
+--------+------------+----------------------------------+----------------------------------+----------+----------------------------------+-------------------------------+

See pgverify --help for flag configuration options.

Supported databases

Database Engine	Supported Versions
PostgreSQL	>=10
CockroachDB	>=21.2

Test modes

Test mode	Description
full	Generates an MD5 hash from all of the rows in a table. Memory intensive, but the highest confidence test.
bookend	Generates an MD5 hash from the first and last X rows in a table, configured by --bookend-limit X.
sparse	Generates an MD5 hash from approximately 1/X rows in a table, configured by --sparse-mod X.
rowcount	Simply queries and compares total row count for a table.

Gotchas

• Due to PostgreSQL and CockroachDB having slightly differing ways of sorting keys in a jsonb value, this tool uses length(jsonb::text) as a low-fidelity proxy fingerprint.

Documentation

Index

• Constants
• func ExcludeColumns(columns ...string) optionFunc
• func ExcludeSchemas(schemas ...string) optionFunc
• func ExcludeTables(tables ...string) optionFunc
• func IncludeColumns(columns ...string) optionFunc
• func IncludeSchemas(schemas ...string) optionFunc
• func IncludeTables(tables ...string) optionFunc
• func WithAliases(aliases []string) optionFunc
• func WithBookendLimit(limit int) optionFunc
• func WithLogger(logger log.FieldLogger) optionFunc
• func WithSparseMod(mod int) optionFunc
• func WithTests(testModes ...string) optionFunc
• func WithTimestampPrecision(precision string) optionFunc
• type Config
  • func NewConfig(opts ...Option) Config
  • func (c Config) Validate() error
  • func (c Config) Verify(ctx context.Context, targets []*pgx.ConnConfig) (*Results, error)
• type Option
• type Results
  • func NewResults(targetNames []string, testModes []string) *Results
  • func Verify(ctx context.Context, targets []*pgx.ConnConfig, opts ...Option) (*Results, error)
  • func (r *Results) AddResult(targetName string, schemaTableHashes SingleResult)
  • func (r Results) CheckForErrors() []error
  • func (r Results) WriteAsTable(writer io.Writer)
• type SingleResult

Constants

const (
	// A full test is the default test mode. It is the only test mode that checks all
	// of the rows of a given table, guaranteeing equivalent values between targets.
	TestModeFull = "full"

	// The bookend test is similar to the full test mode, but it only checks a certain
	// number of rows from the beginning and end of the table, sorted by primary key.
	TestModeBookend = "bookend"
	// The number of rows checked in the bookend test mode is configurable.
	TestModeBookendDefaultLimit = 1000

	// A sparse test checks a deterministic subset of the rows in a table.
	TestModeSparse = "sparse"
	// The number of rows checked in the sparse test mode is configurable,
	// equalling approximately 1/mod of the total.
	TestModeSparseDefaultMod = 10

	// A rowcount test simply compares table row counts between targets.
	TestModeRowCount = "rowcount"

	TimestampPrecisionMilliseconds = "milliseconds"
)

Functions

func ExcludeColumns

func ExcludeColumns(columns ...string) optionFunc

ExcludeColumns sets the exclude columns configuration.

func ExcludeSchemas

func ExcludeSchemas(schemas ...string) optionFunc

ExcludeSchemas sets the exclude schemas configuration.

func ExcludeTables

func ExcludeTables(tables ...string) optionFunc

ExcludeTables sets the exclude tables configuration.

func IncludeColumns

func IncludeColumns(columns ...string) optionFunc

IncludeColumns sets the include columns configuration.

func IncludeSchemas

func IncludeSchemas(schemas ...string) optionFunc

IncludeSchemas sets the include schemas configuration.

func IncludeTables

func IncludeTables(tables ...string) optionFunc

IncludeTables sets the include tables configuration.

func WithAliases

func WithAliases(aliases []string) optionFunc

WithAliases sets the aliases for the target databases. Is ignored if not equal to the number of targets.

func WithBookendLimit

func WithBookendLimit(limit int) optionFunc

WithBookendLimit sets the bookend limit configuration used in the bookend test mode.

func WithLogger

func WithLogger(logger log.FieldLogger) optionFunc

WithLogger sets the logger configuration.

func WithSparseMod

func WithSparseMod(mod int) optionFunc

WithSparseMod sets the sparse mod configuration used in the sparse test mode.

func WithTests

func WithTests(testModes ...string) optionFunc

WithTests defines the tests to run.

func WithTimestampPrecision

func WithTimestampPrecision(precision string) optionFunc

WithTimestampPrecision sets the precision level to use when comparing timestamp values. This can be useful for addressing precision differences between engines, i.e. millisecond vs. microsecond.

Types

type Config

type Config struct {
	// Filters for which schemas and tables to run verification tests on.
	// Exclude overrides Include.
	IncludeTables  []string
	ExcludeTables  []string
	IncludeSchemas []string
	ExcludeSchemas []string
	IncludeColumns []string
	ExcludeColumns []string

	// TestModes is a list of test modes to run, executed in order.
	TestModes []string
	// BookendLimit is the number of rows to include when running a bookend test.
	BookendLimit int
	// SparseMod is used in the sparse test mode to deterministically select a
	// subset of rows, approximately 1/mod of the total.
	SparseMod int

	// Aliases is a list of aliases to use for the target databases in reporting
	// output. Is ignored if the number of aliases is not equal to the number of
	// supplied targets.
	Aliases []string

	// TimestampPrecision is the precision level to use when comparing timestamp values.
	TimestampPrecision string

	Logger log.FieldLogger
}

Config represents the configuration for running a verification.

func NewConfig

func NewConfig(opts ...Option) Config

NewConfig returns a new Config with default values overridden by the supplied Options.

func (Config) Validate

func (c Config) Validate() error

Validate checks that the configuration contains valid values.

func (Config) Verify

func (c Config) Verify(ctx context.Context, targets []*pgx.ConnConfig) (*Results, error)

Verify runs all verification tests for the given table.

type Option

type Option interface {
	// contains filtered or unexported methods
}

Option interface used for setting optional config properties.

type Results

type Results struct {
	// contains filtered or unexported fields
}

Results stores the results from tests run in a verification. It is accessed from the per-target goroutines and is designed to be thread-safe.

func NewResults

func NewResults(targetNames []string, testModes []string) *Results

NewResults creates a new Results object, configured with the output-formatted names of the targets and list of test modes ran.

func Verify

func Verify(ctx context.Context, targets []*pgx.ConnConfig, opts ...Option) (*Results, error)

Verify runs all verification tests for the given table, configured by the supplied Options.

func (*Results) AddResult

func (r *Results) AddResult(targetName string, schemaTableHashes SingleResult)

AddResult adds a SingleResult from a test on a specific target to the Results object.

func (Results) CheckForErrors

func (r Results) CheckForErrors() []error

CheckForErrors checks for and returns a list of any errors found by comparing test outputs.

func (Results) WriteAsTable

func (r Results) WriteAsTable(writer io.Writer)

WriteAsTable writes the results as a table to the given io.Writer.

type SingleResult

type SingleResult map[string]map[string]map[string]string

SingleResult represents the verification result from a single target, with the schema: SingleResult[schema][table][mode] = test output.