pg2mysql

package module
v0.0.0-...-a9d9d9b Latest Latest
Warning

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

 Go to latest Published: Jun 21, 2023 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)
}

Source Files

View all Source files

Directories

Path Synopsis
cmd
pg2mysql
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
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.