Переключаемся с mysql на postgresql

Зачем?

После долгого тестирования MariaDB, после долгих сравнений всех преимуществ MariaDB перед MySQL. В один прекрасный день, я понял, что мне надоело перезагружать сервер с MySQL, каким бы он не был. Наверно все знают, что MySQL закрывает соединения, которые не используются в течении wait_timeout . Мне надоело, открывать сайт и видеть:

Я честно боролся с этим ибо не по-джедайски, но mysql все равно уходил. И было принято решение в пользу слоника, потому что он хороший и он никогда не уходит и расходует по-умолчанию всего лишь порядка 24Mb памяти.

Установка и настройка PostgreSQL

Существует множество руководств и т.п. мануалов по установке PostgreSQL. Напишу и я:

Теперь надо отредактировать специальный файл, работающий по принципу файервола. Верхние строки выполняются первыми и переопределяют более нижние. Сделаем так чтобы для входа с локального хоста не требовался пароль ( пропишем trust ) и создадим пользователя:

Зайдем в базу данных под пользователем postgres:

Теперь мы находимся в консоли postgres, создадим пользователя (представлены разные варианты) и базу данных:

Создадим базу данных:

Выход из консоли posgres:

Настройки производительности и т.п. как всегда в файле:

В Ubuntu не пришлось создавать кластер. Для тех кому придется создавать кластер:

Миграция данных

Вначале я опробовал все стандартные способы, пытался сдампить данные вот так:

При попытке залить в PostgreSQL базу данных он ругается, говорит неверные ключи. И вот отчаявшись нашелся способ «соскочить» с mysql.

Мигрировать данные можно с помощью утилиты py-mysql2pgsql , её репозиторий Утилита оказалась многофункциональной и очень интересной, поэтому я решил написать про нее эту статью.

Устанавливаем py-mysql2pgsql

Утилита требует установленных python-mysql и psycopg2

Один раз запускаем чтобы создался конфигурационный файл:

Далее нужно подставить необходимые параметры для копирования одной базы данных в другую. Необходимо чтобы существовала приемная база данных в PostgreSQL. Связь с mysql базой осуществляется через unix socket, его можно посмотреть в nano /etc/mysql/my.cnf , нужно найти socket у меня это /var/run/mysqld/mysqld.sock , можно просто закомментировать, тогда соединение произойдет через TCP socket. Я выбрал соединяться через unix socket.

Отредактируем конфигурационный файл:

Если указываем destination file, то вывод пойдет в файл.

Все данные скопированы!

Также можно снять дамп с помощью этой утилиты, укажем файл:

Запустим, чтобы создать дамп:

Загрузим дамп в PostgreSQL:

Все данные скопированы!

Использование в Django приложении:

В виртуальное окружение нужно установить psycopg2:

В settings.py настройках базы данных надо изменить:

Еще у PostgreSQL есть одна особенность. Он не любит пускать процессы работающие под например www-data с логином username . Создайте пользователя www-data или отключите проверку паролей.

Сложности и переписывание запросов после миграции

Как выяснилось не существует полной совместимости можду MySQL и PostgreSQL. Кто-то скажет «спасибо, кэп!». Признаюсь я знал, что это так, но не предполагал всей полноты проблемы, потому и хочу предупредить.

Например нельзя сделать так:

Вначале слон не поддреживает апострофы (обратные одинарные кавычки).

А теперь он будет ругаться, что все поля которые не входят в группировку должны быть задействованы в функциях аггрегирования. А mysql просто строки сливает вместе и все. Решение этой проблемы специальная функция аггрегирования:

Кроме этого есть много проблем, но они все решаются. Так что смотрите. Кстати django orm нормально работает с PostgreSQL. Мной ошибок не выявлено, только в raw запросах. Правда при этом невелируются все преимущества PostgreSQL перед MySQL. Например функции авторизации.

Удалить mariadb:

Удалить mysql:

Писсимистическая (читать: реалистическая) нота

Пока не знаю правильно ли я сделал. Пока все работает. Есть сложности с запросами. Но это временно. У меня нет 100% уверености, что PostgreSQL будет стабильнее и лучше. Только время покажет. Для моих задач должен был подходить и mysql, но он сука постоянно уходил.

Полезности

Так делается дамп (или сжатый дамп):

Так дамп заливается в базу, база должна быть пустая.

Скрипт для создания резервной копии по крону

Если статья вам понравилась щелкните по рекламе. Спасибо за внимание и за щелчки $)

Mysql to postgresql windows

py-mysql2pgsql — A tool for migrating/converting/exporting data from MySQL to PostgreSQL

This tool allows you to take data from an MySQL server (only tested on 5.x) and write a PostgresSQL compatable (8.2 or higher) dump file or pipe it directly into your running PostgreSQL server (8.2 or higher).

Currently there is no support for importing spatial data from MySQL.

If you’re like me you don’t like random stuff polluting your python install. Might I suggest installing this in an virtualenv?

• Python 2.7
• MySQL-python
• psycopg2
• PyYAML
• termcolor (unless you’re installing on windows)
• pytz

I have only done limited testing on this platform using Python 2.7. Here are the driver dependencies for windows, install these before attempting to install py-mysql2pgsql or it will fail.

All dependencies should be automatically installed when installing the app the following ways

Looking for help?

Don’t worry if this is your first time, it’ll be gentle.

As the output suggests, a file was created at mysql2pgsql.yml for you to edit. For the impatient, here is what the file contains.

Pretty self explainitory right? A couple things to note, first if destination -> file is populated all output will be dumped to the specified location regardless of what is contained in destination -> postgres. So if you want to dump directly to your server make sure the file value is blank.

Say you have a MySQL db with many, many tables, but you’re only interested in exporting a subset of those table, no problem. Add only the tables you want to include in only_tables or tables that you don’t want exported to exclude_tables.

Other items of interest may be to skip moving the data and just create the schema or vice versa. To skip the data and only create the schema set supress_data to true. To migrate only data and not recreate the tables set supress_ddl to true; if there’s existing data that you want to drop before importing set force_truncate to true. force_truncate is not necessary when supress_ddl is set to false.

Note that when migrating, it’s sometimes possible to knock your sequences out of whack. When this happens, you may get IntegrityErrors about your primary keys saying things like, «duplicate key value violates unique constraint.» See this page for a fix

Due to different naming conventions in mysql an postgrsql, there is a chance that the tool generates index names that collide with table names. This can be circumvented by setting index_prefix.

One last thing, the —verbose flag. Without it the tool will just go on it’s merry way without bothering you with any output until it’s done. With it you’ll get a play-by-play summary of what’s going on. Here’s an example.

Data Type Conversion Legend

Since there is not a one-to-one mapping between MySQL and PostgreSQL data types, listed below are the conversions that are applied. I’ve taken some liberties with some, others should come as no surprise.

MySQL	PostgreSQL
char	character
varchar	character varying
tinytext	text
mediumtext	text
text	text
longtext	text
tinyblob	bytea
mediumblob	bytea
blob	bytea
longblob	bytea
binary	bytea
varbinary	bytea
bit	bit varying
tinyint	smallint
tinyint unsigned	smallint
smallint	smallint
smallint unsigned	integer
mediumint	integer
mediumint unsigned	integer
int	integer
int unsigned	bigint
bigint	bigint
bigint unsigned	numeric
float	real
float unsigned	real
double	double precision
double unsigned	double precision
decimal	numeric
decimal unsigned	numeric
numeric	numeric
numeric unsigned	numeric
date	date
datetime	timestamp without time zone
time	time without time zone
timestamp	timestamp without time zone
year	smallint
enum	character varying (with check constraint)
set	ARRAY[]::text[]

Not just any valid MySQL database schema can be simply converted to the PostgreSQL. So when you end with a different database schema please note that:

• Most MySQL versions don’t enforce NOT NULL constraint on date and enum feilds. Because of that NOT NULL is skipped for this types. Here’s an excuse for the dates: http://bugs.mysql.com/bug.php?id=59526.

I ported much of this from an existing project written in Ruby by Max Lapshin over at https://github.com/maxlapshin/mysql2postgres. I found that it worked fine for most things, but for migrating large tables with millions of rows it started to break down. This motivated me to write py-mysql2pgsql which uses a server side cursor, so there is no «paging» which means there is no slow down while working it’s way through a large dataset.

About

Tool for migrating/converting from mysql to postgresql.

Миграция данных с MySQL на PostgreSQL

По мере работы с базами данных, ознакомления с их плюсами и минусами, возникает момент, когда принимается решение миграции с одной СУБД в другую. В данном случае возникла задача переноса сервисов с MySQL на PostgreSQL. Вот небольшой перечень вкусностей, которые ждут от перехода на PostgreSQL, версии 9.2 (с более подробным списком возможностей можно ознакомится тут):

• наследование таблиц (есть ограничения, которые обещают в будущем исправить)
• диапазоны: int4range, numrange, daterange
• поддержка из коробки несколько языков для хранимых функций (PL/pgSQL, PL/Tcl, PL/Perl, PL/Python и голый C)
• оператор WITH, позволяющий делать рекурсивные запросы
• (планируется) материализованные представления (частично они доступны и сейчас — как IUD правила к представлению)
• (планируется) триггера на DDL операции

Как правило, существующие решения опираются на работу с уже готовым SQL дампом, который конвертируется в соответствии с синтаксисом целевой БД. Но в некоторых случаях (активно использующееся веб-приложение с большим объемом информации) такой вариант несет определенные временные затраты на создание SQL дампа из СУБД, его конвертации и загрузку получившегося дампа снова в СУБД. Поэтому оптимальней будет online-вариант (прямиком из СУБД в СУБД) конвертера, что может существенно уменьшить простой сервисов.

Языком для реализации выбран C++ (с некоторыми возможностями из C++11x), библиотеки для соединения с MySQL и PostgreSQL использовались нативные, в качестве IDE был задействован Qt Creator.

Алгоритм миграции состоит в следующем. Подразумевается что в БД-получателе уже создана структура таблиц, соответствующая структуре в БД-источнике. Формируется список таблиц для переноса данных, который затем распределяется в пуле потоков. Каждый поток имеет подключение к БД-источнику и к БД-получателю. Т.е. параллельно переносится несколько таблиц. Profit!

Традиционно любое приложение имеет некоторый каркас — набор системных компонент, на которые опираются другие компоненты — работа с конфигурационным файлом, логом, обработчик ошибок, менеджер памяти и прочее. В нашем случае, используется только самое необходимое для решения задачи. Во-первых, были переопределены (исключительно для удобства) некоторые фундаментальные и составные типы (да, знаю, можно было аlias templates использовать, но получилось так):

Теперь, что касательно добавления данных в PostgreSQL. Есть два варианта — использовать запросы INSERT, которые на большом массиве данных не очень себя показали в плане производительности (особенности транзакционного механизма), или через команду COPY, которая позволяет непрерывно пересылать порции данных, отправляя в конце передачи специальный маркер (символ-терминатор). Еще один нюанс связан с определением типа (поля в таблице) в PostgreSQL. В документации не указано (возможно не было чтения между строк документации), как можно вернуть человекопонятный идентификатор типа, поэтому было составлено соответствие oid (почти уникальный идентификатор каждого объекта в БД) и типа:

Подготовка и выполнение задач состоит в следующем:

• создается список таблиц
• создаются подключения (по количеству задач) к БД-источнику и БД-приемнику
• распределяются диапазоны из списка таблиц задачам
• задачи запускаются на выполнение (с переданным диапазоном таблиц и подключениями к БД)
• ожидается выполнение задач (главный поток + созданные потоки)

В каждой задаче выделяется три статических буфера по 50 МБ, в которых происходит подготовка данных для команды COPY (экранирование специальных символов и конкатенация значений полей):

Результаты

Для переноса 2 Гб данных в PostgreSQL, c включенным WAL-архивированием, потребовалось порядка 10 минут (создано 16 потоков).

Converting from other Databases to PostgreSQL

Contents

Non-specific

• Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) performs automatic migration of any popular RDBMS to PostgreSQL.
• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back with up to 16 parallel workers and over a million records per second throughput for PostgreSQL target.
• pgloader knows how to load data from MySQL, SQLite, MS SQL Server, dBase files, CSV files and fixed-width data files, and more. Released under The PostgreSQL Licence.
• DBConvert PostgreSQL database migration and sync software Database conversion and synchronization between PostgreSQL/ Amazon RDS, MySQL, MS SQL Server, SQL Azure, Oracle, MS Access.
• dbForge Studio for PostgreSQL helps to migrate data from/to various relational DB servers such as Oracle, MySQL, SQL Server, MariaDB.
• Converting data between PostgreSQL and others database formats ESF Database Migration Toolkit enables you to transfer data across various databases, supporting PostgreSQL, MySQL, Oracle, SQL Server, IBM DB2, Informix, Microsoft Access, Microsoft Excel, dBase, Foxpro, Firbird, SQLite etc. — by Martin Williams
• Comparison of different SQL implementations by Troels Arvin (covers PG 8.4 and MySQL 5.0)
• Transactional DDL in PostgreSQL: A Competitive Analysis by Greg Smith
• Migrating from one database to another with Pentaho ETL by Nicola Benaglia
• dataPro Conversion tool for PostgreSQL, SQLite, MySQL, Oracle, SQL Server and Microsoft Access. Transfer database objects between different databases and servers, convert tables schema and migrate data from one database type to another.
• DataDiff CrossDB is a Windows GUI utility to compare and synchronize/transfer data from PostgreSQL to/from Oracle, MSSQL, MS Access or MySQL databases.
• PostgreSQL Data Wizard is a Windows GUI utility to transfer both schema and data from any ADO-compatible source (like MS Access, MySQL, SQL Server, Oracle, etc) to PostgreSQL.
• SQL::Translator is a Perl module for translating table definitions between different software.
• Foreign data wrappers may be useful for exporting data from other databases
• Postgres Migration Toolkit Software pack to convert Oracle, MySQL, SQL Server and FoxPro to PostgreSQL, and vice versa.
• sqlacrossover SQLAlchemy-based cross-database migration tool
• Skyvia Web service for cloud data integration for PostgreSQL with Salesforce, Dynamics CRM, SugarCRM, Zoho CRM, QuickBooks, FreshBooks, ExactTarget, MailChimp, Bigcommerce, MySQL, SQL Server, SQL Azure, Amazon RDS.
• OmniDB Open source full-featured web tool for database management. Currently supports PostgreSQL only. More RDBMS support coming soon, including the ability of converting databases from any supported RDBMS to PostgreSQL and back.
• Exportizer Enterprise Universal data conversion tool for PostgreSQL, Oracle, SQL Server, SQLite, Interbase, Firebird, MySQL, DB2, Informix, SQL Anywhere, Microsoft Access, Microsoft Excel, dBase, CSV. Can export data either from GUI or from command line.
• jOOQ SQL Translator Web service for SQL syntax translation between > 25 RDBMS dialects

Apache Derby

• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including Derby).
• derby2pg Converter program that will convert the tables, data and indexes in a given schema to a script that can be used to populate a PostgreSQL database.

DBase II, III, IV+ / DBF Format

• FoxPro-to-PostgreSQL a program to migrate FoxPro databases to PostgreSQL server. The program does not use ODBC or any other middleware software. Command line support allows to script, automate and schedule the conversion process.
• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including dBase, Clipper, Visual FoxPro and other DBF variants) to PostgreSQL.
• pgloader by Dimitri Fontaine
• Convert .dbf files into PostgreSQL by Tomasz Judycki
• A Visual Basic utility to convert Dbase III, IV, and V by Dennis Bazan
• Porting data from dBASE IV to PostgreSQL by Vijay Deval (2002-09-07)
• PgDBF : Simplified and optimized replacement for XBaseToPg by Kirk Strauser
• Exportizer Pro Converts data from dBase to PostgreSQL and back.

FileMaker Pro

• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including FileMaker Pro, via its ODBC driver).
• Porting from FileMaker Pro to PostgreSQL by Michelle Murrain (24th August 2001)

IBM DB2

• Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from DB2 LUW, DB2 AS/400 (iSeries) and DB2 OS/390 (z/OS) to PostgreSQL.
• Migrating from DB2 to PostgreSQL
• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including DB2 LUW and DB2 iSeries).
• db2topg Migration tool to convert a DB2 UDB Database into a PostgreSQL database.
• Exportizer Enterprise Converts data from DB2 to PostgreSQL and back.

InterBase

• Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from InterBase as well as Firebird to PostgreSQL.
• DBReplicate — Simplify Interbase->PostgreSQL conversions by Kirk Strauser
• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including Interbase).
• Exportizer Enterprise Converts data from Interbase to PostgreSQL and back.

Microsoft Access

• Linking to MS Access tables with ogr_fdw If your PostgreSQL is on windows, you can use ogr_fdw foreign data wrapper, packaged with PostGIS 2.2+ bundle for windows via application stackbuilder. With PostgresQL 9.5, you can use IMPORT FOREIGN SCHEMA to link in all the MS access tables and then cherry pick how you want to restructure.
• exportSQL — a Microsoft Access module which exports Access Database into MySQL, mSQL and PostgreSQL by Dobrica Pavlinusic. Based on the work of Pedro Freire
• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including Access).
• Exportizer Enterprise Converts data from MS Access to PostgreSQL and back.
• MDB Tools by Brian Bruns
  • A quick way to dump all tables as tsv or csv files

If the tablenames have embedded spaces.

A shell script that may be useful for converting entire databases:

If this script is saved to the file access2psql.sh and made executable, then it would be used as follows:

This script won’t work properly if there are tab characters in text columns, though the call to mdb-export could be modified to export INSERT statements to fix this. Also, mdb-schema has trouble representing multi-column foreign keys, so foreignkeys.sql may need some manual editing.

Microsoft SQL Server

• Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from Microsoft SQL Server to PostgreSQL.
• MSSQL-to-PostgreSQL is a migration utility to convert SQL Server or SQL Azure databases to PostgreSQL. Option to filter data using SELECT-queries, synchronization mode, command line support.
• Full Convert Database migration and synchronization from more than 40 database engines to PostgreSQL and back (including SQL Server).
• sqlserver2pgsql Migration tool to convert a Microsoft SQL Server Database into a PostgreSQL database
• pgloader by Dimitri Fontaine
• DBConvert for PostgreSQL and MS SQL Server Conversion and Sync software solutions for cross database migration between PostgreSQL/ Amazon RDS and MS SQL Server/ SQL Azure
• Learning PostgreSQL (Alexander Kuznetsov) — series of blog articles for SQL Server users (2013-10

MySQL

Scripts, programs

pg_chameleon is a python procedure which replicates MySQL into PostgreSQL. The tool can detach the replica for minimal downtime migrations.

pgloader supports full migration of the database schema and data from a single command line and provides a command language for more complex or specific scenarios. It is still fully supported: please report any bugs on its GitHub page. It appears in the 2013 section here because that’s when it has been published first.