pg2mysql

package module
v0.0.6 Latest Latest
Warning

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

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

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
 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.