Когда запустился мой сайт Design Harbour (тогда он назывался Abscissa Tech), был один разумный выбор для веб-сайта, поддерживаемого базой данных на основе PHP: MySQL. Затем, летом 2001 года, я портировал сайт на PostgreSQL (также известный как Postgres) и не оглядывался назад.
Это первое из серии из двух частей описывает мою мотивацию для перехода на Postgres и объясняет пошаговое руководство по преобразованию существующих данных MySQL в Postgres. Во второй части объясняется, как изменить бэкэнд PHP, чтобы он работал с новой системой баз данных.
Мотивация для переключения
Я впервые прочитал о Postgres в статье на PHPBuilder . Он сравнил Postgres и MySQL , которые я использовал исключительно в то время. Но после того, как я прочитал статью, Postgres потерялся в информационных хранилищах моего мозга — это был интересный факт, но не имеющий отношения к моему веб-дизайну.
Я продолжал использовать MySQL и связывал сбои и сбои MySQL с некомпетентностью моего хостинга, которую я не мог изменить в то время. Как только я смог, я сменил веб-хостов. Сервис и философия этого нового хоста сильно отличались от моего предыдущего, так как они были более привержены безопасности и стабильности, чем мой старый хост. Новая компания пыталась убедить меня использовать Postgres, предполагая, что он более стабилен, но я отказался от этой идеи и указал, что весь мой сайт уже написан для MySQL. Они уступили и установили MySQL специально для моего сайта. Вот тогда и начались проблемы.
Моей первой работой было копирование моих старых данных MySQL со старого сервера на сервер MySQL моего нового веб-хоста. Сначала я сбросил существующие данные в файл SQL, который я перенес на новый веб-сервер. Затем я приступил к импорту файла SQL. При виде файла из нескольких тысяч строк MySQL быстро рухнул. Когда компания перезапустила MySQL, примерно половина моих данных была там, но MySQL работал только с перебоями. Наконец, им пришлось удалить импортированную информацию, чтобы я мог попробовать еще раз. MySQL снова упал. Этот цикл продолжался еще несколько раз, пока я наконец не решил разделить свой файл SQL на несколько частей. Мне пришлось попробовать несколько раз, но в итоге я смог импортировать большую часть своих данных на новый сервер MySQL. Все было хорошо, и я вздохнул с облегчением.
В течение следующих нескольких месяцев MySQL падал почти два раза в неделю, что привело к очень серьезной аварии в конце июня 2001 года. На этот раз данные, хранящиеся в MySQL, были безнадежно повреждены и не подлежали восстановлению. У меня был файл резервной копии SQL, но я никоим образом не наслаждался задачей восстановления данных из этой резервной копии, основываясь на моем предыдущем опыте импорта больших объемов данных в MySQL. Примерно в то же время, хостинговая компания призвала меня еще раз портировать сайт, написанный на PHP, на Postgres. Разочарованный MySQL, я наконец решил сделать это.
Передача данных из MySQL в Postgres
Портировать данные из MySQL в Postgres немного сложно, поскольку Postgres поддерживает более стандартную версию SQL, чем MySQL, и невозможно сделать прямой дамп SQL для Postgres. Тем не менее, синтаксис SQL очень похож, поэтому в моем случае это не заняло много времени.
Преобразование дамп MySQL
Сначала попросите веб-хостинга создать базу данных для вашей учетной записи. Базы данных в Postgres, как и базы данных MySQL, состоят из набора таблиц, которые содержат фактические данные. Затем создайте дамп файла SQL вашей базы данных MySQL с помощью команды mysqldump .
mysqldump -u username -p databasename > sqldump.txt
Загрузите весь файл дампа SQL на свой компьютер, используя FTP. Теперь, когда у вас есть файл SQL на вашем компьютере, вы можете превратить его в файл, который будет импортировать Postgres.
Сначала вырежьте все запросы MySQL CREATE TABLE из файла дампа и вставьте их в отдельный текстовый файл. Следующим шагом является переопределение таблиц на языке, понятном Postgres.
SQL для создания таблиц в Postgres похож, но не идентичен MySQL. Вот пример:
CREATE TABLE practicetable { someID SERIAL, time TIMESTAMP DEFAULT now(), name VARCHAR(50), address VARCHAR(50), city VARCHAR(50), state VARCHAR(2), country VARCHAR(3) DEFAULT 'USA', postlcode VARCHAR(15), age smallint, lattitude real, longitude real, somebool boolean, message textitem };
В определении таблицы Postgres за именем поля должен следовать тип поля. Несколько общих типов полей приведены в предыдущем примере, но вы можете найти обширный список в документации Postgres по типам данных . Postgres имеет огромный выбор типов данных для различных задач и может хранить всевозможные данные, от интернет-адресов и денежной информации до определения геометрических объектов. Вот краткий обзор наиболее часто используемых типов данных.
Поле типа SERIAL является эквивалентом автоматического увеличения уникального идентификатора MySQL. Базы данных Postgres могут содержать функции и отчеты SQL, а также таблицы и записи; Автоинкрементная функция SERIAL автоматически добавляется в базу данных при определении поля типа SERIAL в вашей таблице. Поскольку система автоинкремента находится за пределами реальной таблицы, мы можем создать собственную логику для значения уникального идентификатора и выполнить другие операции. При портировании с MySQL на Postgres достаточно действия по умолчанию.
Тип VARCHAR — это именно то, чем он является, текстовое поле переменной длины. Длина поля определяется значением в скобках. Например, VARCHAR (5) определяет поле, которое может содержать до 5 символов текста.
SMALLINT , INT и BIGINT используются для определения целочисленных элементов. Поля SMALLINT могут хранить числа в диапазоне от -32768 до +32767 (этот фактический размер может незначительно отличаться в зависимости от типа вашего компьютера; предыдущий является наиболее распространенным целочисленным размером системы). Поля INT могут хранить большие числа от -2147483648 до +2147483647. Типы полей BIGINT предназначены для чего-либо большего и не имеют ограничения по размеру.
РЕАЛЬНЫЕ типы полей предназначены для действительных чисел, которые содержат десятичные дроби. Они могут хранить до 6 знаков после запятой. Поля DOUBLE PRECISION похожи, но могут содержать до 15 знаков после запятой.
BOOLEAN поле имеет значение true или false, один или ноль. Он идентичен своему аналогу MySQL.
Поля TIMESTAMP похожи на своих двоюродных братьев в MySQL. Отметка времени обновляется до текущей даты и времени каждый раз, когда обновляется запись. Поля времени Postgres также могут содержать информацию о часовом поясе. Подробнее о более сложных способах использования данных времени Postgres читайте на странице даты и времени в документации PostgreSQL .
Создание таблиц
После создания отдельного файла SQL определения таблицы и переопределения таблиц в Postgres-Spely убедитесь, что каждый запрос CREATE TABLE заканчивается точкой с запятой — требование Postgres. Затем подключитесь к своему веб-хосту с помощью такого инструмента, как telnet, и создайте таблицы, используя следующий метод.
Сначала откройте файл определения таблицы в текстовом редакторе. Затем войдите на компьютер своего хоста и запустите интерактивный терминал Postgres, psql, введя psql . Схема аутентификации по умолчанию использует ваше имя пользователя telnet / FTP для учетной записи Postgres. Это позволяет Postgres автоматически аутентифицировать вашу личность, не заставляя вас вводить комбинацию имени пользователя и пароля. Ваш веб-хост может аутентифицироваться по-разному, и в этом случае вы можете дать программе psql дополнительные аргументы, такие как: psql -d databasename -U username -W . -D позволяет вам указать базу данных, -U указывает имя пользователя и -W запрашивает у psql пароль.
После запуска psql вставьте каждый запрос CREATE TABLE по отдельности в psql и нажмите клавишу ввода. Если вы допустите ошибку в синтаксисе SQL, psql сообщит вам, что пошло не так. Вводя каждую таблицу отдельно, вы получаете отладочную информацию для каждой таблицы, что значительно упрощает работу.
Если после ввода определений таблицы вы поймете, что пропустили одно или два поля, у вас есть два варианта. Вы можете использовать команду ALTER TABLE или просто удалить таблицу с помощью DROP TABLE, а затем ввести ее снова, как только вы исправите ее. Если вы используете второй метод, вы натолкнетесь на предостережение Postgres, которое оставляет определенные артефакты из пропущенных таблиц, которые мешают создавать новые.
Чтобы использовать команду DROP TABLE, введите DROP TABLE practicetable; , Это приведет к удалению таблицы, но когда вы перейдете к переопределению таблицы, вы получите ошибку. Удаление таблицы не исключает последовательность, которая соответствует полям типа SERIAL, которые находятся в таблице. Эти оставшиеся последовательности мешают при попытке воссоздать таблицу. Чтобы решить эту проблему, удалите последовательность, используя имя последовательности DROP SEQUENCE sequencename; прежде чем бросить стол. К сожалению, имя последовательности не совпадает с именем SERIAL. Когда вы определяете поле типа SERIAL, Postgres автоматически генерирует последовательность с таким именем: tablename_colname_seq . В случае практического использования оператор DROP SEQUENCE будет выглядеть следующим образом: DROP SEQUENCE practicetable_someID_seq; , Тогда вы можете оставить стол и начать все сначала.
После завершения вставки таблиц введите z чтобы дважды проверить список таблиц. После завершения ввода q завершит работу psql. Наконец-то пришло время подготовить ваши данные для импорта в Postgres.
Очистка свалки
Поскольку MySQL в основном соответствует стандарту языка SQL, импортировать фактические данные из файла дампа SQL не так уж сложно. Однако есть предостережение о том, что мы должны отредактировать дамп SQL, прежде чем передать его Postgres.
Основное отличие ввода данных между MySQL и Postgres заключается в кавычках. В Postgres строковые значения (значения, содержащие текст) должны быть заключены в одинарные кавычки. MySQL допускает и то и другое, но программа mysqldump, к счастью, использует одинарные кавычки, которые удобны для Postgres. Тем не менее, MySQL и Postgres отличаются тем, что делать с кавычками, которые появляются внутри строки. Где MySQL представляет кавычки с "" , Postgres требует " . Используйте функции поиска / замены вашего текстового редактора, чтобы заменить все "" на " . Интересно, что Postgres использует '' для представления одинарных кавычек; MySQL также использует этот метод, поэтому вам не придется менять экранирование одинарной кавычки.
Импорт в Postgres
После того, как вы исправите кавычки в файле дампа SQL, загрузите файл на компьютер своего веб-хоста, войдите в систему, как вы делали это раньше, чтобы создать таблицы. Перейдите в каталог, где находится файл дампа SQL. Запустите psql, но с несколькими другими параметрами командной строки: psql -f sqldump.txt и замените sqldump.txt новым именем, которое вы дадите файлу. Эта команда берет весь файл SQL и импортирует его прямо в соответствующие таблицы Postgres. Как и раньше, вам, возможно, придется добавить дополнительные параметры, чтобы psql мог правильно вас аутентифицировать. Если вы получите какие-либо ошибки, psql сообщит вам, где они произошли. Найдите эту часть файла, попробуйте выяснить и устранить проблему и вручную ввести проблемный запрос в инструмент командной строки psql. В моем случае все импортировалось без сучка и я был готов закончить работу. Только немного позже я заметил другую проблему.
После того, как я начал использовать новый сайт, управляемый Postgres, я столкнулся с другой проблемой несовместимости между MySQL и Postgres. Последовательности Postgres, используемые полями с автоинкрементом типа SERIAL, начинаются с 1 и автоматически увеличиваются на единицу каждый раз, когда вставляется запись с полем типа SERIAL. Однако когда я импортировал дамп MySQL, SQL в этом дампе определил значения моего основного целочисленного ключа. В моем случае у меня были уникальные идентификаторы, считавшие до 60, но последовательность все еще была на одном. Каждый оператор INSERT, который я сделал, не удался, потому что последовательность давала результат, который не был уникальным ID. Разочарованный, я выполнил 60 инструкций INSERT, чтобы привести последовательность в нужное положение, но позже я узнал, что более быстрое решение от друга, который регулярно использует Postgres. Вот что он сказал мне сделать:
Подключитесь к вашему хосту, используя терминальную программу, такую как telnet. Затем запустите программу psql, как вы делали, когда определяли таблицы. Сначала определите наибольшее значение идентификатора, которое существует в таблице. Это можно сделать, используя SELECT fieldname FROM tablename WHERE fieldname=MAX(fieldname); , Затем удалите соответствующую последовательность из базы данных, используя DROP SEQUENCE table_colname_seq; где стол это имя таблицы, а colname это имя поля SERIAL. Затем заново CREATE SEQUENCE table_colname_seq START 61; последовательность, используя CREATE SEQUENCE table_colname_seq START 61; и замените 61 номером наибольшего значения идентификатора в базе данных плюс один.
Установка инструмента с графическим интерфейсом
После того, как я успешно поместил свои данные в Postgres, мне понадобился способ, чтобы мой не разбирающийся в Unix партнер мог успешно работать с данными в базе данных. Моим выбором для этого с MySQL был phpMyAdmin, отличный инструмент для просмотра и редактирования баз данных в Интернете. К счастью для меня, phpMyAdmin был портирован на Postgres в виде phpPgAdmin .
Установка phpPgAdmin проста. Сначала загрузите последний стабильный дистрибутив с веб-сайта phpPgAdmin и поместите его в свою учетную запись веб-хостинга, где его можно будет просмотреть в Интернете. Затем войдите на компьютер вашего хоста, используя такой инструмент, как telnet. Перейдите в каталог, в котором находится файл phpPgAdmin.tar.gz, и введите tar -xzvf phpPgAdmin.tar.gz чтобы распаковать программу (может потребоваться заменить другое имя файла; это просто используется в качестве примера). Затем перейдите в новый подкаталог, созданный в процессе распаковки, и прочитайте файл README.
Наконец, откройте файл config.inc.php в текстовом редакторе на компьютере вашего веб-хоста (vi, pico и emacs — популярные текстовые редакторы; сверьтесь с вашим хостом, чтобы узнать, что они имеют в наличии). Внутри файла я нашел инструкции по настройке phpPgAdmin. Заполните несколько вопросов в файле и загрузите страницу в браузере. PhpPgAdmin запросит у вас имя пользователя и логин, после чего вы сможете начать управлять базой данных через ее интерфейс.
Вывод
Postgres является более стабильным и надежным сервером баз данных, который может обрабатывать гораздо большую нагрузку, чем MySQL. Он также обладает более продвинутыми функциями и ближе к стандартам SQL, чем его аналог. Преобразование данных из MySQL в Postgres может быть выполнено с использованием файлов дампа SQL по умолчанию, если вы будете следовать рекомендациям, описанным выше. Это может стать непонятной задачей, но хорошая помощь доступна. Если вы оказались в тупике, вы можете найти документацию на очень полезном сайте Postgres .
В следующем учебном пособии из этой серии будут рассмотрены различия между доступом к MySQL и Postgres в PHP, а также представлены пояснения, советы и хитрости, позволяющие быстро сделать существующий код дружественным к Postgres.
Сделайте переход: как я, вы будете рады, что сделали.