pg2mysql

package module
v0.0.0-...-291973b Latest Latest
Warning

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

Go to latest
Published: May 18, 2021 License: Apache-2.0 Imports: 9 Imported by: 0

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.

Run the verifier after migration to confirm the data has been migrated as expected:

$ pg2mysql -c config.yml verify
Verifying table spaces_developers...OK
Verifying table security_groups_spaces...OK
Verifying table service_bindings...OK
Verifying table droplets...
  FAILED: 1 row missing
  Missing IDs: 1,3,5
Verifying table organizations...OK
Verifying table lockings...OK
Verifying table service_dashboard_clients...OK
Verifying table route_bindings...OK

Verify does an exact comparison (except for timestamps; see Note) of the contents of each row of each table in PostgreSQL to see that a matching row exists in MySQL.

Note: The verify command assumes that the precise PostgreSQL timestamps are truncated when doing the migration over to MySQL. However, it has been found that this behavior is not consistent with all forms of MySQL. Official MySQL rounds the timestamps whereas MariaDB truncates. A PR to intelligently support both would be happily received.

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

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

Directories

Path Synopsis
cmd
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