Не работает блинк из oracle в postgresql

Обновлено: 07.07.2024

Я использовал sudo netstat -nlp | grep 5432 , чтобы увидеть статус, но ничего не показалось. И я поискал в Интернете, кто-то сказал мне изменить pg_hba.conf , но я не могу locate этот файл. И я тоже пробовал эту команду sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432 . Не может работать.

22 ответа

Шаг 1. Убедитесь, что база данных работает

Команда может отличаться в зависимости от вашей операционной системы. Но в большинстве систем * ix будет работать следующее: он будет искать postgres среди всех запущенных процессов

В моей системе Mac OSX это выплевывает

Последний столбец показывает команду, используемую для запуска сервера, и параметры.

Вы можете просмотреть все варианты, доступные для запуска сервера postgres, используя следующее.

Оттуда вы увидите, что варианты -D и -r - это соответственно datadir и logfilename .

Шаг 2. Если служба postgres запущена

Используйте find для поиска местоположения сокета, который должен быть где-нибудь в /tmp

Если postgres запущен и принимает подключения к сокету, приведенное выше должно сообщить вам местоположение сокета. На моей машине это оказалось:

Затем попробуйте подключиться через psql, явно указав расположение этого файла, например.

Шаг 3. Если служба запущена, но вы не видите сокет

Если вы не можете найти сокет, но видите, что служба запущена, убедитесь, что в файле pg_hba.conf разрешены локальные сокеты.

Перейдите к datadir , и вы должны найти файл pg_hba.conf .

По умолчанию в нижней части файла вы должны увидеть следующие строки:

Если вы его не видите, вы можете изменить файл и перезапустить службу postgres.

В моем случае я увидел эту ошибку, и postgres не запущен.

Проблема заключалась в том, что при установке не удалось создать требуемый кластер.

Решением было создать папку /etc/postgres//main

А затем создайте кластер с помощью:

После этого, просто перезапустив службу postgresql, все должно работать.

У меня возникла эта проблема при работе с PostgreSQL в Ubuntu 18.04.

Я проверил свой статус PostgreSQL и понял, что он работает нормально, используя:

Я также попытался перезапустить сервер PotgreSQL на машине, используя:

Но проблема не исчезла:

После ответа Noushad я сделал следующее :

Перечислите все кластеры Postgres, запущенные на вашем устройстве:

Это дало мне этот вывод красного цвета, показывая, что все они не работают, а статус также показывает не работает :

Перезапустите pg_ctlcluster для одного из кластеров серверов. Для меня я перезапустил PG 10 :

Однако он вызвал ошибку ниже, и та же ошибка возникла, когда я попытался перезапустить другие кластеры PG:

Проверьте журнал на наличие ошибок, в данном случае мой - PG 10 :

Я увидел следующую ошибку:

Это было вызвано тем, что я внес изменения в права доступа к файлам для каталога данных PostgreSQL.

Я исправил это, выполнив команду ниже. Я выполнил команду для 3 кластеров PG на своей машине:

После чего я перезапустил каждый из кластеров PG:

И вот, наконец, я снова проверил работоспособность кластеров:

На этот раз все снова было хорошо, так как статус показал онлайн :

Надеюсь, это поможет

В моем случае служба работала, но кластер не работал, и psql не запускался. Мои файлы конфигурации выглядели идеально, но они продолжали выдавать ошибки конфигурации и, казалось, игнорировали внесенные мной изменения.

Оказывается, всякий раз, когда вы используете синтаксис ALTER SYSTEM SET . , PostgreSQL записывает в файл с именем postgresql.auto.conf . Этот файл читается в дополнение к обычным файлам postgresql.conf и pg_hba.conf . В моем дистрибутиве Ubuntu (18.04) они находятся в разных папках (!):
- pg_hba.conf и postgresql.conf оба находятся в /etc/postgresql/12/main
- Автоматически созданный файл: /var/lib/postgresql/12/main/postgresql.auto.conf

Я пытался изменить конфигурацию с помощью ALTER SYSTEM SET listen_addresses = <my-ip> , но допустил ошибку, в результате чего возникла некорректная конфигурация «призрак», которую я не смог найти. Как только я стер оскорбительную строку в postgresql.auto.conf , он все исправил.

Я мог бы решить эту проблему, установив правильные разрешения для datadir. Так должно быть

Убедитесь, что Postgres работает, используя:

Проверьте каталог данных и postgresql.conf .

В моем случае каталог данных в -D отличался от каталога в postgresql.conf

Итак, я изменил каталог данных в postgresql.conf , и это сработало.

У меня была аналогичная проблема, и проблема была в файле конфигурации pg_hba.conf. Ранее я внес некоторые изменения, из-за которых сервер отказывался от ошибок при попытке его запуска. Комментирование лишних дополнений решило проблему.

У меня такая же проблема. Вроде нет сокета, когда нет кластера.

Во время установки не удалось создать кластер по умолчанию, поскольку не был задан языковой стандарт по умолчанию.

Это может вызвать что угодно, например, моя проблема была вызвана опечаткой в файлах конфигурации. Некоторые люди говорят, что это вызвано файлами сертификатов, другая группа считает, что причиной этого являются несогласованные местные жители.

Если вы не можете найти решение своей проблемы, удалите postgres и переустановите его. Это лучшее решение.

Во время новой установки postgresql. По умолчанию имя пользователя и пароль назначаются как «postgres». Эта СУБД предоставляет возможность добавить роль для нового пользователя и создать базу данных. Если вы получаете такие ошибки:

логин по умолчанию логин:

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

Чтобы выйти из режима быстрого использования

Чтобы создать новую роль пользователя

Теперь вы находитесь в интерактивной оболочке psql. Наслаждаться. Не забудьте войти под своим именем пользователя и ввести psql для оболочки.

Итак, для меня и моих друзей, работающих над приложением Node.js (с Postgres и Sequelize), нам пришлось

brew services start postgresql **** (используйте Homebrew для запуска postgres)

createdb <name of database in config.json file>

Просто хочу сделать небольшое дополнение: если ваш экземпляр жалуется на сокет, вы также можете проверить unix_socket_directories в файле /data/postgresql.conf , который мог быть установлен в /tmp , например, если вы использовали сторонний дистрибутив. Вы можете изменить его на /var/run/postgresql и перезапустить службу. Для этого также может потребоваться создать каталог postgresql в /var/run и subsys/postgresql-9.6 в /var/lock , если они еще не существуют (работало для меня с postgresql 9.6).

Ошибка означает, что сервер Postgres не запущен. Попробуйте запустить его:

Убедитесь, что сервер запускается при загрузке:

Краткое руководство по debian для удаленного доступа к базе данных postgres на сервере из клиента psql: (измененная конфигурация документирована в файлах):

sudo /etc/init.d/postgresql restart изменения вступают в силу

войдите со стороны клиента с помощью psql --host=ipofserver --port=5432 --username=remoteuser --password --dbname=mydb

Я решил эту проблему, проверив свою файловую систему, диск был полностью заполнен, поэтому база данных не запускалась

Подключения к сокету домена Unix "/var/run/postgresql/.s.PGSQL.5432" ?

Я пробовал несколько способов устранения неполадок, пока не проверил использование своего диска и не обнаружил, что он заполнен, используется 100%,

Решено! Хотя я не знаю, что случилось, но я просто удалил все и переустановил. Это команда, которую я использовал, чтобы удалить sudo apt-get --purge remove postgresql\* и dpkg -l | grep postgres . Последний - найти все пакеты, если он не чистый.

Я столкнулся с той же проблемой и

Это сработало для меня.

Пару раз сталкивался с подобной проблемой. Обычно я просто выполняю новую установку PostgreSQL, следуя этому tutorial, и это решает проблему за счет потери данных.

Я был полон решимости получить настоящее исправление сегодня. Перезапуск PostgreSQL разрешил это на ubuntu. sudo /etc/init.d/postgresql restart

Если при запуске службы Postgres ошибок нет, выполните следующие действия.

Шаг 1. Запуск pg_lsclusters отобразит список всех кластеров Postgres, работающих на вашем устройстве.

Скорее всего, в вашем случае статус будет понижен. Если нет, перезапустите службу PostgreSQL

Шаг 2: перезапустите pg_ctlcluster

Шаг 3. Шаг 2 завершился неудачно, и возникла ошибка

Если перезапуск pg_lsclusters не был успешным, он выдаст ошибку. Моя ошибка была (вы можете увидеть ошибки в журналах /var/log/postgresql/postgresql-9.6-main.log )

Шаг 4: проверьте право собственности на postgres

Убедитесь, что postgres является владельцем /var/lib/postgresql/version_no/main например: sudo chown postgres -R /var/lib/postgresql/9.6/main/

Шаг 5. Убедитесь, что пользователь postgres принадлежит к группе пользователей ssl-cert.

«Ландшафт» СУБД в проектах нашей компании до недавнего времени выглядел так: большую часть составляла Oracle, существенно меньшие — MS SQL и MySQL.

Но, как известно, нет ничего вечного, и недавно к нам поступил запрос о применимости Postgres в одном из наших проектов. К этой СУБД мы присматривались в последние пару лет очень пристально — посещали конференции, meetup’ы, но вот попробовать ее в «боевых» условиях до недавнего времени не доводилось.

Итак, задача

Дано: сервер Oracle (single instance) 11.2.0.3 и набор не связанных друг с другом схем общим объемом

50GB. Необходимо: перенести данные, индексы, первичные и ссылочные ключи из Oracle в Postgres.

Выбор инструмента миграции

Обзор инструментария для миграции показал наличие как коммерческих инструментов, таких как Enterprise DB Migration Toolkit и Oracle Golden Gate, так и свободного ПО. Перевод был запланирован однократный, поэтому требовалось зрелое средство, вместе с тем понятное и простое. Кроме того, конечно, учитывался и вопрос стоимости. Из свободного ПО наиболее зрелым на сегодняшний день является проект Ora2Pg Жиля Дарольда (Darold Gill), он же во многом превзошел по функционалу и коммерческие варианты. Преимущества, склонившие чашу весов в его сторону:

богатый функционал;
активное развитие проекта (15 лет разработки, 15 мажорных релизов).

Установка и настройка окружения

В компании мы используем подход DevOps для создания виртуальных машин, установки необходимого софта, конфигурирования и развертывания ПО. Наш рабочий инструмент — Ansible. Но для того, чтобы облегчить восприятие и не вводить в статью новые сущности, к делу не относящиеся, далее мы будем показывать ручные действия из командной строки. Для тех, кому интересно, мы выкладываем Ansible playbook для всех шагов здесь.

Итак, на виртуальной машине с OS Centos 6.6 выполним следующие шаги.

Установим репозиторий Postgres.
Установим Postgres 9.4 сервер.
Создадим БД и настроим доступ.
Установим Postgres как сервис и запустим его.
Установим instant клиент Oracle.
Установим утилиту Ora2Pg.

Настройка доступа сводится к тому, что мы специально понижаем безопасность соединения Postgres для удобства тестирования. Конечно, в продакшн-среде мы не рекомендуем так делать.

В файле /var/lib/pgsql/9.4/data/postgresql.conf необходимо раскомментировать строчку listen_addresses = '*' . В файле /var/lib/pgsql/9.4/data/pg_hba.conf для локальных и удаленных соединений необходимо поставить метод trust . Секция после редактирования выглядит так:

Зарегистрируем Postgres как сервис и запустим его:

Для установки Oracle instant client необходимо загрузить с OTN следующие пакеты:

Создадим папку для tnsnames.ora :

Установим следующие переменные окружения (в .bash_profile пользователя):

И проверим работоспособность.

Если все ок — то получим примерно такой вывод:

Остался последний шаг настройки — установка Оra2pg. Скачиваем с сайта последнюю версию Ora2Pg (на момент написания статьи была версия 15.2). Установим необходимые пакеты:

Установим модуль CPan:

Установим дополнительные модули для Perl:

Распакуем Ora2pg в, скажем, /install:

Миграция

СУБД Postgres по «духу» наиболее близка к Oracle. В обеих хорошо соотносятся типы данных, и там, и там есть такое понятие, как схема. Воспользуемся этим и будем переносить данные «посхемно». Процесс миграции будет состоять из следующих шагов.

Создание проекта миграции с помощью Оra2pg.
Правка файла конфигурации ora2pg.conf.
Выгрузка DDL таблиц, индексов, constraints из Oracle.
Создание БД в Postgres.
Импорт DDL таблиц, подготовленный на 3-м шаге.
Копирование данных.
Импорт DDL индексов и constraints.

Создадим проект миграции. Проект состоит из набора папок tables/functions/views/packages, в которых будут находится sql-файлы с DDL соответствующих объектов, конфигурационного файла ora2pg.conf и скрипта запуска — export_schema.sh.

Конфигурирование

Файл конфигурации Ora2pg довольно объемен, и я остановлюсь только на тех параметрах, которые являются корневыми или потребовались во время миграции наших данных. Про остальные я рекомендую узнать из этой статьи.

Секция, описывающая параметры соединения c БД Oracle:

Секция, описывающая, какую схему выгружаем:

И указание, в какую схему загружаем:

Указываем тип экспорта. Параметр COPY говорит о том, что мы будем копировать данные напрямую из Oracle в Postgres, минуя текстовый файл.

Секция, описывающая параметры соединения c БД Postgres:

Секция конвертации типов данных. Для того, чтобы тип number() без указания точности не конвертировался в bigint , укажем:

На этом конфигурационные шаги закончены, и мы готовы приступить к переносу. Выгрузим описания схемы в виде набора sql-файлов c DDL объектов:

Создадим базу данных qqq, пользователя test_owner и выдадим необходимые права.

Выполним импорт sql-файла c DDL таблиц:

Теперь все готово к копированию данных. Запускаем:

Несмотря на тот факт, что в командной строке мы указываем параметр -о с именем файла, в который следует сохранять выгрузку, вставка данных происходит напрямую из Oracle в Postgres. В нашем случае скорость вставки была около 6 тыс. строк в секунду, но это, конечно же, зависит от типов копируемых данных и окружающей инфраструктуры.

Остался последний шаг — создать индексы и constraints.

Если в процессе выполнения предыдущих команд вы не получили ошибок — поздравляю, миграция прошла успешно! Но, как известно из закона Мерфи: «Anything that can go wrong will go wrong».

Наши подводные камни

Следующей сложностью оказалось то, что в Postgres нет типа, аналогичного Oracle anydata. В связи с этим мы были вынуждены, проанализировав и поправив логику приложения, в ущерб гибкости сконвертировать его в «подходящие» типы, например, в varchar2(100) . Кроме того, если у вас есть какие-то кастомные типы, то все придется переделывать, поскольку они не транслируются, но это тема как минимум для отдельной статьи.

Подведем итоги

Утилита Ora2Pg, несмотря на сложность настройки, проста и надежна в использовании. Ее смело можно рекомендовать для миграции небольших и средних БД. Кстати, ее автор на PGConf Russia объявил о том, что начинает проект MS2Pg. Звучит многообещающе.

Так уж случилось, что с Oracle организация наша работает давно и плотно. Сам я познакомился с Oracle Database ещё во времена 6-ой версии и, с тех пор, какого либо дискомфорта не испытывал. Всё испортили рыночные отношения. С недавних пор, мы начали замечать, что Заказчик гораздо благосклоннее смотрит на наши проекты если в них используются бесплатные СУБД. О портации одного из таких проектов и будет мой рассказ.

Выбор бесплатной СУБД был, для меня, делом долгим и непростым, но, в конечном итоге, свёлся к двум всем известным альтернативам. PostgreSQL привлекал богатой (и всё ещё развивающейся) функциональностью, в то время как MySQL заманивал производительностью и «нулевым» администрированием. Поскольку Oracle нас разбаловал и SQL мы все знали и любили, многочисленные и новомодные NoSQL варианты отпали ещё в полуфинале.

Сложно сказать, на какой из СУБД я бы остановился в конечном итоге, если бы не одна шабашка, давшая мне возможность «вживую» пощупать и PostgreSQL и MySQL, не спеша сравнить их и принять, на мой взгляд, вполне обоснованное решение. Помимо функционала, разумеется, сравнивалась и производительность. Я не буду рассказывать деталей, но одним из принципиальных моментов «шабашки» была возможность быстро и надёжно (ACID, да) вставлять в базу данных большое количество записей. На эту тему и был проведён тест:

По оси ординат отложено количество записей фиксированной длины, сохраняемых в БД ежесекундно. Число в «легенде» означает размер транзакции. Здесь следует заметить, что MySQL замерялся «как есть», а PostgreSQL с использованием небольшой нашлёпки, обеспечивавшей возможность работы с привычными мне партиционированными таблицами и материализованными представлениями. Поскольку речь шла о «надёжном» хранении, MyISAM на графике представлен исключительно для полноты картины и понимания того, где находится «теоретический максимум» искомой производительности на используемом «железе».

Поскольку само тестирование проводилось довольно давно и никакими SSD на имеющемся в наличии железе даже не пахло, к абсолютным значениям, показанным на графике, не стоит относиться как к догме. Безусловно, можно сохранять данные ещё быстрее, но меня интересовало соотношение производительности различных СУБД, работавших в (почти) одинаковых условиях. Для меня стало сюрпризом, что PostgreSQL, даже утяжелённый триггерами партиционирования, работает почти также быстро как MySQL, с использованием InnoDB, а на больших транзакциях (1000 записей и больше) начинает догонять MyISAM!

Как легко догадаться, показанный выше график окончательно убедил меня в том, что переходить следует на PostgreSQL. Пересоздание таблиц с переопределением типов столбцов (number в numeric и integer, varchar2 в varchar и text . ) было делом тривиальным. В переносе данных помог XML и XSLT.

Строго говоря, XML помог ещё раньше. Одной из особенностей нашего продукта является хранение в БД описаний бизнес-сущностей в форме обычных табличных данных (не думаю, что он сильно оригинален в этом). Сравнение таких «метаданных» для двух различных схем было настоящей головной болью, до тех пор, пока я не написал небольшой пакет, выгружающий их в XML-описания. Сортировка тегов внутри описаний позволяла сравнивать их как обычные текстовые файлы. XSLT дополнил картину, обеспечив автоматическую генерацию SQL-скриптов из файлов описаний.

Осталось обеспечить работоспособность всего SQL-кода, написанного для Oracle. Большая часть запросов работала, часть — заработала после небольших косметических изменений. Первым делом, я создал таблицу dual:

Не то, чтобы без неё нельзя было обойтись, но в наших запросах она использовалась так часто, что переписывать их было просто нецелесообразно. Чтобы PostgreSQL «был доволен», пришлось добавить в запросы больше строгости:

Все inline view необходимо именовать, а перед псевдонимами столбцов крайне желательно использовать ключевое слово 'as'. Для большинства столбцов его можно опускать, но при использовании таких имён как 'name' или 'value' это ведёт к ошибке. Следующим шагом, стала замена платформозависимого кода на соответствующие конструкции, поддерживаемые как в Oracle, так и в PostgreSQL. Речь идёт об nvl и decode, а также об устаревшем синтаксисе внешнего соединения. Первые две легко заменяются на стандартные (и более гибкие) coalesce и case, в случае же использования внешнего соединения, запрос должен быть переписан:

ANSI Join поддерживается Oracle с 9-ой версии и, на мой взгляд, является более удобным (хотя и менее лаконичным), чем устаревший вариант с использованием (+). Не стоит пытаться сочетать различные формы соединения в одном SQL-запросе. Если мы использовали outer join, то для внутренних соединений вполне логично использовать inner join, а не перечисление таблиц в фразе from через запятую.

Основная часть работы по миграции SQL-кода оказалась связана с переписыванием иерархических запросов. Фраза connect by в PostgreSQL, естественно, не поддерживается. Между тем, в наличии имелось большое количество запросов следующего вида:

Простое переписывание таких запросов, с использованием CTE не позволило бы привести их к платформонезависимому виду. Хотя Oracle (начиная с версии 11.2) поддерживает рекурсивные запросы, синтаксис их отличается от используемого в PostgreSQL. В частности, в PostgreSQL, использование ключевого слова recursive является обязательным, Oracle же его «не понимает». К счастью, в большинстве случаев, иерархическую часть запроса удавалось «спрятать» в представление.

Переписывание операторов merge оказалось более «головоломным» (к счастью, они использовались не так часто, как иерархические запросы). PostgreSQL этот оператор не поддерживает, но зато он поддерживает использование фраз from и returning в операторе update, причём последняя — возвращает полноценный resultset (аналогично оператору select), что позволяет использовать его в фразе with. Я просто оставлю это здесь:

В этом примере можно заметить, что работа с последовательностями в PostgreSQL также отличается от принятой в Oracle. Конечно, в Oracle можно было определить функцию, аналогичную той, что получает значения из последовательностей в PostgreSQL, но переписывания Oracle-кода (также как и Java-кода) хотелось избежать. Кроме того, такой подход мог быть связан с дополнительными накладными расходами.

Много радости доставила работа с датой и временем. Дело в том, что широко используемый в Oracle тип date приучил к некоторой неряшливости при обращении с его значениями. Можно считать, что такое значение представляет собой число, целая часть определяет количество дней, прошедших с некоторой «магической» даты, а дробная — время, с точностью до секунды. После некоторого привыкания (как и к большинству особенностей Oracle), это довольно удобно, но PostgreSQL гораздо строже в том, что касается типов данных.

Добавить, таким образом, к дате константный интервал можно, но что делать, если необходимо добавить переменное значение? Искомое выражение совсем не очевидно:

Пробел в строке перед 'hour' обязателен! Также, можно заметить, что строгость PostgreSQL распространяется и на преобразование числовых значений в строковые (и наоборот, конечно). Маска обязательна, даже если она состоит из одних девяток. Неявные преобразования, столь привычные после работы с Oracle, не работают.

Оставшиеся запросы подверглись менее радикальным изменениям. Пересмотра потребовал весь код работающий со строками, просто потому, что соответствующие функции в Oracle и PostgreSQL выглядят по разному. Столбец rownum, там, где он ещё оставался, пришлось заменить на оконный row_number(). В тех случаях, когда условие на rownum использовалась для ограничения количества выводимых строк, запросы переписывались с использованием фразы limit.

Отдельно стоит рассказать о табличных функциях. И в Oracle и в PostgreSQL они есть. Реализация разумеется различна, но обращение к ним, из SQL-запроса выглядит сходным образом. К сожалению, как и в случае с рекурсивным CTE, всё портит наличие одного ключевого слова:

Осталось разобраться с пакетами. В PostgreSQL такого понятия нет, но, при ближайшем рассмотрении, оказывается, что ему оно не очень то и нужно. Действительно, для чего нужны пакеты в Oracle? Если отбросить в сторону глобальные переменные и инициализационный код (которыми мы не пользуемся), главным достоинством пакетов является то, что они разрывают цепочки зависимостей. При изменении объектов БД, инвалидируются лишь реализации зависимых пакетов, но не их заголовки. Возможность выполнения рекурсивных вызовов внутри пакетов является одним из следствий этого факта.

В PostgreSQL механизм зависимостей не реализован. С рекурсивными вызовами хранимых функций (процедур в PostgreSQL нет) также всё в порядке. Для того, чтобы в клиентский код пришлось вносить минимум изменений, достаточно обеспечить лишь видимость того, что мы продолжаем работать с пакетами. Схемы PostgreSQL подходят для этого как нельзя лучше. Разумеется, в таком «пакете», не удастся реализовать «приватные» функции, но это не очень большая проблема. Вот как будет выглядеть код:

Необходимость удаления всех объектов перед «пересозданием» схемы немного утомляет, но жить можно. Можно заметить в тексте непривычное слово 'strict'. Оно обеспечивает привычное по Oracle поведение, при попытке выборки нуля или более одной записи. Из других запомнившихся моментов, могу упомянуть странную конструкцию, вычисляющую количество строк, изменённых последним запросом:

Реализации всех пакетов пришлось, конечно, переписать, благо их оказалось не так много. Из моего предыдущего повествования можно понять, что весь переписанный SQL-код поделился на три категории:

Запросы, которые путём небольшого переписывания удалось привести к платформонезависимому виду
Запросы, в которых платформозависимые фрагменты удалось скрыть в представлениях
Безусловно платформозависимый код

Было принято решение — хранить все платфомозависимые запросы в БД, загружая их в программный кэш, при первом обращении. Первоначально предполагалась, что каждая из БД будет хранить свои версии запросов, но оказалось удобнее хранить запросы одновременно во для всех используемых СУБД. В Oracle, для хранения текста запросов использовалось CLOB поле, в PostgreSQL — text. Для обеспечения единообразия, было использовано преобразование CLOB в varchar2, что ограничило максимальный размер запроса 4000 символов (один запрос всё-таки вылез за пределы этого размера, но поскольку он предназначался для PostgreSQL версии, «ужимать» его не пришлось). Само преобразование to_char пришлось скрыть с использованием представления:

Резюмируя, могу сказать, что работа оказалась совсем не такой страшной, какой она казалась в начале. Большая её часть была связана с переписыванием иерархических запросов и пакетов Oracle, а большая часть проблем — с более строгим синтаксисом SQL и отсутствием привычных неявных преобразований в PostgreSQL. Объем работ мог бы быть меньше если бы мы изначально использовали более строгий и платфомонезависимый код в Oracle.

Тема переноса (миграции) данных из Oracle в Postgres набирает обороты. В РФ количество запросов по переходу на Postgres на волне импортозамещения, сейчас больше, чем когда либо. Oracle — очень качественный, мощный и отлично документированный инструмент. Но вместе с тем очень дорогой в лицензировании и поддержке. Зачастую охватить все его возможности не получается в силу специфики использования. Ключевыми параметрами эксплуатации в нашем случае являются производительность системы, процент использования (утилизации) железа, простая тех. поддержка, а также дешевое и быстрое масштабирование — легкое развертывание новых баз.

Я решил изучить процесс миграции данных, изучая то, как реализовать пожелания конкретного заказчика, а также из практического любопытства, теперь я пробую перенести небольшую схему около 15 Гбайт с Oracle 11.2.0.4 на Linux Redhat 6.8 в Postgres 9.4 на Windows. Немного погрузившись в тему, я решил написать эту статью и рассмотреть процесс подробно. В отрыве от нашей специфики пост будет полезен интеграторам и DBA при планировании переноса данных/CUT.

Как перенести данные

Как выяснилось, существуют инструменты и под задачи переноса данных. Наиболее рациональным способом, на мой взгляд, является утилита ORA2PG Жиля Дарольда. Ora2PG соединяется с БД Oracle с помощью Perl-модуля и выгружает все объекты схем, перечисленных в конфигурационном файле, в SQL-файлы, параллельно формируя структуру каталогов. Сами файлы представляют собой DDL-команды. Далее выгружаются данные: в виде INSERT команд для таблиц. Кроме того данные можно вставить и непосредственно из базы в базу, т.е. в свежесозданную схему Postgres. Импорт осуществляется модулем PERL:PG.

Начнем с того, что у нас уже есть сервера c развернутыми СУБД Oracle и Postgres в одной сети. Помимо них, для осуществления плана по миграции данных, желательно иметь промежуточную машину с настроенным окружением.

Далее я ставлю виртуальную машину на CentOS 7 в минимальной конфигурации плюс development tools и вывожу ее в Интернет. В нашем случае база Postgres расположена по адресу 10.70.85.11, база Oracle — 10.70.85.14, а виртуальная машина — по адресу 10.70.85.15.
Сама установка ORA2PG потребует подготовки.

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

Выход в Интернет — в моем случае через прокси-сервер: ставим и настраиваем авторизующий прокси CNTLM. Создаем пользователей (пользователь ‘postgres’ должен создаться самостоятельно при установке агента).
Ставим репозиторий Postgres с сайта Postgres для версии 9.4.
Ставим клиент Postgres 9.4 и средства разработки, иначе не соберем Perl-модуль
Ставим instant-клиент Oracle. Он ставится не так просто, как хотелось бы, но готовых инструкций достаточно много.
Настраиваем подключение для обоих клиентов.
Доустанавливаем необходимые пакеты.
Собираем модули Perl.
Ставим сам ORA2 PG.

Установка клиента Oracle

Приведу основные моменты установки:

Если после установки при попытке запуска вас преследует ошибка: “./sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory”, понадобится установить libaio1.

После установки клиента Oracle настраиваем доступ к БД: создаем файл TNSNAMES.ORA и кладем его в подкаталог /network/admin в каталоге клиента.

Предполагаем, что Listener на стороне Oracle настроен по умолчанию.

Известно, что даже опытные администраторы БД не помнят на память синтаксис tnsnames.ora, поэтому привожу пример:

Проверим доступ через tnsping:

На практике я столкнулся с тем, что sqlplus не подключается, ссылаясь на ошибку прокси:

Отключим, указав еще пару строчек в ./bash_profile Oracle:

Ставим клиент PostgreSQL

Укажем репозиторий, найденный на домашней странице:

И ставим клиент:

Опционально можно поставить и серверные компоненты:

Проверяем доступ к базе postgres

Прописываем доступ для нашей машины на стороне сервера Postgres в файле pg_hba.conf, а именно запись следующего вида:

После сохраняем конфигурационный файл и перезапускаем сервис.

Ставим Perl

Perl нужен версии не ниже 5.10. Для установки модулей ставим CPAN:

При первом запуске CPAN попробует сам себя настроить. Из важных вопросов спросит учетную запись для прокси и попросит указать зеркало для пакетов.

Укажем зеркало в РФ и еще одно:

Пробуем собрать модули Perl для подключения

Начнем с DBD::Oracle для подключения к Oracle. Собирать модуль будем из-под Oracle-записи (проверьте, что вы уже поставили ora-instant-client, SDK, SQL plus и библиотеку libaio1), где у нас есть все переменные окружения. На всякий случай подтянем их еще раз:

Первый вариант может не сработать, тогда ставим по документации в Ora2PG.

Далее DBI::PG для postgres. Требуется версия не ниже 1.614, иначе получите интересную ошибку:

Можно обновить все модули из CPAN командой:

Ставим дополнительные библиотеки

Если модуль автоматически не собрался, скопируем исходники, дадим права пользователю postgres и соберем пакет под ним:

После настройки модулей

В БД Postgres открываем pgadmin, создаем пустую базу данных, назначаем права учетной записи — мы заходим под postgres.

Итак, мы все поставили.

Приступаем к сборке самой утилиты

Экспорт данных

Далее работаем под oracle. Запускаем и инициируем наш проект:

Команда создаст дерево каталогов и конфигурационный файл. Переходим к этому файлу. В нем настраиваем подключение к обеим БД. Это секция DBI::oracle — указываем хост инстанс и логин/пароль:

DBI::PG — указываем хост базу данных и логин/пароль:

Указываем схему Oracle для экспорта:

Указываем, что схему нужно пересоздать (по умолчанию):

Указываем схему Postrges, в которую будет осуществляться импорт:

Указываем output-файл для экспорта:

Выбираем данные для экспорта:

В наш экспорт войдут таблицы, индексы, последовательности, триггеры и ограничения целостности данных (далее constraints):

Я также включаю отладочный режим для наглядности:

Начинаем экспорт схемы Oracle. Поскольку это занимает время, запускаем его в фоне для надежности и идем пить чай:

Дампим данные в файлы:

Пробуем импортировать схему

Обратите внимание на следующие опции:

–y — перед загрузкой скрипт предлагает затереть ранее загруженную схему, потом ввести пароль, создать схему и т.д. по кругу.

Опция -y крайне удобна, когда импорт не проходит в десятый раз, и ваши правки просто повторяются автоматически.

Перед загрузкой скрипт предлагает удалить существующую схему с тем же именем, так что вы можете запускать его повторно, если что-то не прошло. Если существует активное подключение, скрипт не сработает.

-I — это значит, что мы не грузим индексы, ограничения на первичные внешние ключи и триггеры. Иначе загрузка данных будет проблематична.

Стоит сказать, что импорт редко проходит гладко. В беспроблемных на первый взгляд таблицах кроются ошибки синтаксиса. В моем случае импорт схемы спотыкался на таблице User и на всех последующих со схожими названиями, далее на индексах и далее на внешних ключах таблиц, где присутствовало слово ‘user’. По умолчанию стоит директива STOP_ON_ERROR.

Обратите внимание на зарезервированные слова – user, offset, from, to. Такие записи нужно заключать в двойные кавычки вручную, иначе импорт «споткнется».

Для подробной справки посмотрим:

Заменим их все! Немного магии редактора sed:

Вполне возможно, что придется закомментировать какой-нибудь триггер или индекс и разобраться с ним позднее.

Зная о проблеме выше, открываем файл /schema/output.sql и правим имена столбцов и таблиц аналогичным образом.

Данные c учетом наших правок следует импортировать вручную:

Ограничения на первичные и внешние ключи:

Посмотрим, что у нас было и что получилось

Для этого используем графические средства администрирования: Oracle SQL Developer для Oracle и PGadmin для postgres:

В новой СУБД создано 130 новых таблиц, что соответствует количеству таблиц в оригинальной БД.

Также видим загруженные последовательности и триггерные функции.

Загрузились и ограничения целостности:

Проверим наличие данных в таблице:

Заключение

В результате преобразований Oracle-Postgres, благодаря утилите Ora2Pg, нам удалось сконвертировать таблицы и преобразовать основные типы, которые используются только в Oracle: varchar2 в varchar, Number в Numeric и т.д. Полагаю, что адекватной альтернативы такому способу нет. Конечно, можно вручную создавать таблицы с другими типами данных в новой БД и переписывать код целиком для функций, но это слишком трудоемко, очень непрактично и скорее мешает, чем помогает решать задачи конкретных заказчиков Вот пример преобразования таблиц.

Автоматически сконвертированы последовательности.

Напоследок, поговорим о других возможных проблемах с экспортом.

Хотя ORA2PG и умеет конвертировать язык PL/SQL в PL/PGSQL, специфический код Oracle в индексах или ограничениях целостности будет сконвертирован с ошибками. Пример от Жиля Дарольда:

Вполне возможны проблемы с кодировками при вставке записей в Postgres. С проблемой зарезервированных слов я уже столкнулся. К счастью, больших объектов типа BLOB в моих таблицах не было. Конвертация типа BLOB в bytea была бы весьма долгой. В документации рекомендуется либо исключить такие таблицы опцией EXCLUDE в конфигурационном файле, либо использовать многопоточность с опцией THREAD_COUNT.

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

Читайте также: