pg2mysql

README

pg2mysql

pg2mysql was created to facilitate migrating data from PostgreSQL to MySQL given mostly equivalent schemas.

In PostgreSQL it is common to use the text datatype for character data, which the PostgreSQL documentation describes as having effectively no limit. In MySQL this is not the case, as the datatype with the same name (text) is limited to 65535, and the more common datatype, varchar, is defined with an explicit limit (e.g. varchar(255)).

This means that, given a column with text datatype in PostgreSQL, there must be enough room in the equivalent MySQL column for the data in PostgreSQL to be safely migrated over. This tool can be used to validate the target MySQL schema against a populated PostgreSQL database and, provided the data in the PostgreSQL database is compatible, the migration to move the data from PostgreSQL to MySQL.

Install from source

go get github.com/pivotal-cf/pg2mysql/cmd/pg2mysql

Usage

Create a config:

$ cat > config.yml <<EOF
mysql:
  database: some-dbname
  username: some-user
  password: some-password
  host: 192.168.10.1
  port: 3306

postgresql:
  database: some-dbname
  username: some-user
  password: some-password
  host: 192.168.10.2
  port: 5432
  ssl_mode: disable
EOF

Note: See PostgreSQL documentation for valid SSL mode values.

Run the validator:

$ pg2mysql -c config.yml validate
found incompatible rows in apps with IDs [2]
found incompatible rows in app_usage_events with IDs [9 10 11 12]
found incompatible rows in events with IDs [16 17 18]

If there are any incompatible rows, as in above, they will need to be modified before proceeding with a migration.

Run the migrator:

$ pg2mysql -c config.yml migrate --truncate
inserted 1 records into spaces_developers
inserted 0 records into security_groups_spaces
inserted 0 records into service_bindings
inserted 2 records into droplets
inserted 2 records into organizations
inserted 3 records into lockings
inserted 0 records into service_dashboard_clients
inserted 0 records into route_bindings
...

Note: The --truncate flag will truncate each table prior to copying data over.

Documentation

Index

• func EachMissingRow(src, dst DB, table *Table, f func([]interface{})) error
• func GetIncompatibleRowCount(db DB, src, dst *Table) (int64, error)
• func GetIncompatibleRowIDs(db DB, src, dst *Table) ([]int, error)
• type Column
  • func GetIncompatibleColumns(src, dst *Table) ([]*Column, error)
  • func (c *Column) Compatible(other *Column) bool
  • func (c *Column) Incompatible(other *Column) bool
• type Config
• type DB
  • func NewMySQLDB(database string, username string, password string, host string, port int) DB
  • func NewPostgreSQLDB(database string, username string, password string, host string, port int, ...) DB
• type Migrator
  • func NewMigrator(src, dst DB, truncateFirst bool, watcher MigratorWatcher) Migrator
• type MigratorWatcher
• type Schema
  • func BuildSchema(db DB) (*Schema, error)
  • func (s *Schema) GetTable(name string) (*Table, error)
• type StdoutPrinter
  • func NewStdoutPrinter() *StdoutPrinter
  • func (s *StdoutPrinter) DidBuildSchema()
  • func (s *StdoutPrinter) DidDisableConstraints()
  • func (s *StdoutPrinter) DidFailToDisableConstraints(err error)
  • func (s *StdoutPrinter) DidFailToMigrateRowWithError(tableName string, err error)
  • func (s *StdoutPrinter) DidMigrateRow(tableName string)
  • func (s *StdoutPrinter) EnableConstraintsDidFailWithError(err error)
  • func (s *StdoutPrinter) EnableConstraintsDidFinish()
  • func (s *StdoutPrinter) TableMigrationDidFinish(tableName string, recordsInserted int64)
  • func (s *StdoutPrinter) TableMigrationDidStart(tableName string)
  • func (s *StdoutPrinter) TableVerificationDidFinish(tableName string, missingRows int64, missingIDs []string)
  • func (s *StdoutPrinter) TableVerificationDidFinishWithError(tableName string, err error)
  • func (s *StdoutPrinter) TableVerificationDidStart(tableName string)
  • func (s *StdoutPrinter) TruncateTableDidFinish(tableName string)
  • func (s *StdoutPrinter) WillBuildSchema()
  • func (s *StdoutPrinter) WillDisableConstraints()
  • func (s *StdoutPrinter) WillEnableConstraints()
  • func (s *StdoutPrinter) WillTruncateTable(tableName string)
• type Table
  • func (t *Table) GetColumn(name string) (int, *Column, error)
  • func (t *Table) HasColumn(name string) bool
• type ValidationResult
• type Validator
  • func NewValidator(src, dst DB) Validator
• type Verifier
  • func NewVerifier(src, dst DB, watcher VerifierWatcher) Verifier
• type VerifierWatcher

Functions

func EachMissingRow

func EachMissingRow(src, dst DB, table *Table, f func([]interface{})) error

func GetIncompatibleRowCount

func GetIncompatibleRowCount(db DB, src, dst *Table) (int64, error)

func GetIncompatibleRowIDs

func GetIncompatibleRowIDs(db DB, src, dst *Table) ([]int, error)

Types

type Column

type Column struct {
	Name     string
	Type     string
	MaxChars int64
}

func GetIncompatibleColumns

func GetIncompatibleColumns(src, dst *Table) ([]*Column, error)

func (*Column) Compatible

func (c *Column) Compatible(other *Column) bool

func (*Column) Incompatible

func (c *Column) Incompatible(other *Column) bool

type Config

type Config struct {
	MySQL struct {
		Database string `yaml:"database"`
		Username string `yaml:"username"`
		Password string `yaml:"password"`
		Host     string `yaml:"host"`
		Port     int    `yaml:"port"`
	} `yaml:"mysql"`

	PostgreSQL struct {
		Database string `yaml:"database"`
		Username string `yaml:"username"`
		Password string `yaml:"password"`
		Host     string `yaml:"host"`
		Port     int    `yaml:"port"`
		SSLMode  string `yaml:"ssl_mode"`
	} `yaml:"postgresql"`
}

type DB

type DB interface {
	Open() error
	Close() error
	GetSchemaRows() (*sql.Rows, error)
	DisableConstraints() error
	EnableConstraints() error
	ColumnNameForSelect(columnName string) string
	DB() *sql.DB
}

func NewMySQLDB

func NewMySQLDB(
	database string,
	username string,
	password string,
	host string,
	port int,
) DB

func NewPostgreSQLDB

func NewPostgreSQLDB(
	database string,
	username string,
	password string,
	host string,
	port int,
	sslMode string,
) DB

type Migrator

type Migrator interface {
	Migrate() error
}

func NewMigrator

func NewMigrator(src, dst DB, truncateFirst bool, watcher MigratorWatcher) Migrator

type MigratorWatcher

type MigratorWatcher interface {
	WillBuildSchema()
	DidBuildSchema()

	WillDisableConstraints()
	DidDisableConstraints()

	WillEnableConstraints()
	EnableConstraintsDidFinish()
	EnableConstraintsDidFailWithError(err error)

	WillTruncateTable(tableName string)
	TruncateTableDidFinish(tableName string)

	TableMigrationDidStart(tableName string)
	TableMigrationDidFinish(tableName string, recordsInserted int64)

	DidMigrateRow(tableName string)
	DidFailToMigrateRowWithError(tableName string, err error)
}

type Schema

type Schema struct {
	Tables map[string]*Table
}

func BuildSchema

func BuildSchema(db DB) (*Schema, error)

func (*Schema) GetTable

func (s *Schema) GetTable(name string) (*Table, error)

type StdoutPrinter

type StdoutPrinter struct{}

func NewStdoutPrinter

func NewStdoutPrinter() *StdoutPrinter

func (*StdoutPrinter) DidBuildSchema

func (s *StdoutPrinter) DidBuildSchema()

func (*StdoutPrinter) DidDisableConstraints

func (s *StdoutPrinter) DidDisableConstraints()

func (*StdoutPrinter) DidFailToDisableConstraints

func (s *StdoutPrinter) DidFailToDisableConstraints(err error)

func (*StdoutPrinter) DidFailToMigrateRowWithError

func (s *StdoutPrinter) DidFailToMigrateRowWithError(tableName string, err error)

func (*StdoutPrinter) DidMigrateRow

func (s *StdoutPrinter) DidMigrateRow(tableName string)

func (*StdoutPrinter) EnableConstraintsDidFailWithError

func (s *StdoutPrinter) EnableConstraintsDidFailWithError(err error)

func (*StdoutPrinter) EnableConstraintsDidFinish

func (s *StdoutPrinter) EnableConstraintsDidFinish()

func (*StdoutPrinter) TableMigrationDidFinish

func (s *StdoutPrinter) TableMigrationDidFinish(tableName string, recordsInserted int64)

func (*StdoutPrinter) TableMigrationDidStart

func (s *StdoutPrinter) TableMigrationDidStart(tableName string)

func (*StdoutPrinter) TableVerificationDidFinish

func (s *StdoutPrinter) TableVerificationDidFinish(tableName string, missingRows int64, missingIDs []string)

func (*StdoutPrinter) TableVerificationDidFinishWithError

func (s *StdoutPrinter) TableVerificationDidFinishWithError(tableName string, err error)

func (*StdoutPrinter) TableVerificationDidStart

func (s *StdoutPrinter) TableVerificationDidStart(tableName string)

func (*StdoutPrinter) TruncateTableDidFinish

func (s *StdoutPrinter) TruncateTableDidFinish(tableName string)

func (*StdoutPrinter) WillBuildSchema

func (s *StdoutPrinter) WillBuildSchema()

func (*StdoutPrinter) WillDisableConstraints

func (s *StdoutPrinter) WillDisableConstraints()

func (*StdoutPrinter) WillEnableConstraints

func (s *StdoutPrinter) WillEnableConstraints()

func (*StdoutPrinter) WillTruncateTable

func (s *StdoutPrinter) WillTruncateTable(tableName string)

type Table

type Table struct {
	Name    string
	Columns []*Column
}

func (*Table) GetColumn

func (t *Table) GetColumn(name string) (int, *Column, error)

func (*Table) HasColumn

func (t *Table) HasColumn(name string) bool

type ValidationResult

type ValidationResult struct {
	TableName            string
	IncompatibleRowIDs   []int
	IncompatibleRowCount int64
}

type Validator

type Validator interface {
	Validate() ([]ValidationResult, error)
}

func NewValidator

func NewValidator(src, dst DB) Validator

type Verifier

type Verifier interface {
	Verify() error
}

func NewVerifier

func NewVerifier(src, dst DB, watcher VerifierWatcher) Verifier

type VerifierWatcher

type VerifierWatcher interface {
	TableVerificationDidStart(tableName string)
	TableVerificationDidFinish(tableName string, missingRows int64, missingIDs []string)
	TableVerificationDidFinishWithError(tableName string, err error)
}