如何从MySQL迁移到PostgreSQL

方石剑
558 阅读3分钟

提示：从MySQL到PostgreSQL

最近的项目涉及到将所有的数据库从MySQL迁移到PostgreSQL，包括那些在Rails和Reporting中使用的数据库。

我个人非常喜欢这种变化，但在MySQL和PostgreSQL之间转换时，有几件事我必须记住，我将列举这些。

MySQL升级错误

这与MySQL->PostgreSQL的迁移没有什么关系，而是我在升级我的本地MySQL版本时不时遇到的事情，特别是在做了通常的brew upgrade 。

这是一个连接到MySQL时的错误，如果你使用GUI，例如MySQL Workbench或Sequel Pro，就会发生这种情况。该错误说。

Table 'performance_schema.session_variables' doesn't exist

这很容易解决，没有什么真正的疯狂，只是执行。

mysql_upgrade -u root -p --force

PostgreSQL升级错误

与上面的MySQL升级错误类似，PostgreSQL在升级到一个主要版本后也会抱怨。例如，当我从9.4升级到9.5。我得到的错误是。

FATAL: 数据库文件与服务器不兼容 DETAIL:数据目录是由PostgreSQL 9.4版本初始化的，它与这个9.5.0版本不兼容。

这个需要多一点，但也很容易解决。显然，我在这里使用的路径取决于你的实际安装情况，但如果你也是一个Homebrew用户，那么弄清楚这些应该不难。

mv /usr/local/var/postgres /usr/local/var/postgres945
initdb /usr/local/var/postgres -E utf8
pg_upgrade -b /usr/local/Cellar/postgresql/9.4.5/bin -B /usr/local/Cellar/postgresql/9.5.0/bin -d /usr/local/var/postgres945 -D /usr/local/var/postgres

MySQL到PostgreSQL的Rails数据库迁移

最后，在做这个迁移的时候，我不得不处理的另一个常见的事情是实际的数据库迁移，然后在这里和这里执行一些ALTERS，以使用真正的类型和导入的数据。

有很多方法可以做到这一点，比如你可以使用AWS的DMS之类的东西，或者你可以按照我的穷人的方式来做。

基本上我使用FromMySqlToPostgreSql，这是一个用PHP编写的命令行脚本，它不仅可以转换类型，还可以导入数据，说实话，使用起来非常简单。唯一的要求是有一个最新的PHP Cli版本。只要按照官方软件库中关于在Homebrew中安装PHP的说明，然后就可以了。

brew install php54-pdo-pgsql

确保你的.bashrc 包括。

export PATH="$(brew --prefix homebrew/php/php54)/bin:$PATH"

关于模式和数据库内容，你都已经准备好了，但是这里面缺少一个东西，就是把一些列转换为真正的PostgreSQL类型。特别是ActiveRecord用来表示布尔值的tinyint 。

这很容易通过下面的Rake任务来完成，当然是使用MySQL数据库。

namespace :pg do

  desc 'Creates ALTER statements to convert from mysql:tinyint to pg:boolean'
  task create_alters: :environment do
    filename = Rails.root.join("pg_alter_#{Time.now.to_i}.sql")

    File.open(filename, 'w') do |file|
      ActiveRecord::Base.connection.tables.each do |table|
        ActiveRecord::Base.connection.columns(table).each do |column|
          next unless column.type == :boolean

          file.write "-- Table: #{table}\n"
          file.write build_alter_statements(table, column)
          file.write "\n"
        end
      end
    end

    puts "Output file: #{filename}"
  end

 def build_alter_statements(table, column)
    default_value = (column.default.to_i == 1 ? 'TRUE' : 'FALSE')

    <<-SQL
ALTER TABLE #{table} ALTER COLUMN #{column.name} DROP DEFAULT;
ALTER TABLE #{table} ALTER #{column.name} TYPE bool USING CASE WHEN #{column.name}=1 THEN TRUE ELSE FALSE END;
ALTER TABLE #{table} ALTER COLUMN #{column.name} SET DEFAULT #{default_value};

    SQL
  end
end

通过执行

rake pg: create_alters

你应该能够在你的Rails根目录下得到一个文件，其中包含所有正确的ALTER 语句，以正确地将tinyints转换为booleans。

avatar
文章
阅读
粉丝