package module
v0.0.6 Latest Latest

This package is not in the latest version of its module.

Go to latest
Published: Aug 31, 2017 License: Apache-2.0 Imports: 9 Imported by: 0



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


Create a config:

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

  database: some-dbname
  username: some-user
  password: some-password
  port: 5432
  ssl_mode: disable

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.




This section is empty.


This section is empty.


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)


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 {


	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)


Path Synopsis
This file was generated by counterfeiter
This file was generated by counterfeiter

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL