pg_upgrade
Пред. НаверхСерверные приложения Postgres ProНачало След.

pg_upgrade

pg_upgrade — обновить экземпляр сервера Postgres Pro

Синтаксис

pg_upgrade -b старый_каталог_bin [-B новый_каталог_bin] -d старый_каталог_конфигурации -D новый_каталог_конфигурации [параметр...]

Описание

Программа pg_upgrade (ранее называвшаяся pg_migrator) позволяет обновить данные в каталоге базы PostgreSQL или Postgres Pro до последней основной версии Postgres Pro без операции выгрузки/перезагрузки данных, обычно необходимой при обновлениях основной версии, например, при переходе от 9.5.8 к 9.6.4 или от 10.7 к 11.2. Эти действия не требуются при установке корректирующей версии, например, при переходе от 9.6.2 к 9.6.3 или от 10.1 к 10.2.

С выходом новых основных версий в Postgres Pro регулярно добавляются новые возможности, которые часто меняют структуру системных таблиц, но внутренний формат хранения меняется редко. Учитывая этот факт, pg_upgrade позволяет выполнить быстрое обновление, создавая системные таблицы заново, но сохраняя старые файлы данных. Если при обновлении основной версии формат хранения данных изменится так, что данные в старом формате окажутся нечитаемыми, pg_upgrade не сможет произвести такое обновление. (Сообщество разработчиков постарается не допустить подобных ситуаций.)

Программа pg_upgrade делает всё возможное, чтобы убедиться в том, что старый и новый кластеры двоично-совместимы, в частности проверяя параметры времени компиляции и разрядность (32/64 бита) исполняемых файлов. Важно, чтобы и все внешние модули тоже были двоично-совместимыми, хотя это pg_upgrade проверить не может.

pg_upgrade поддерживает обновление с версии 9.0.X и новее до текущей основной версии Postgres Pro, включая бета-выпуски и сборки снимков кода.

Параметры

pg_upgrade принимает следующие аргументы командной строки:

-b каталог_bin
--old-bindir=каталог_bin

каталог с исполняемыми файлами старой версии Postgres Pro; переменная окружения PGBINOLD

-B каталог_bin
--new-bindir=каталог_bin

каталог с исполняемыми файлами новой версии Postgres Pro, по умолчанию это каталог, в котором располагается pg_upgrade; переменная окружения PGBINNEW

-c
--check

только проверить кластеры, не изменять никакие данные

-d каталог_конфигурации
--old-datadir=каталог_конфигурации

каталог конфигурации старого кластера; переменная окружения PGDATAOLD

-D каталог_конфигурации
--new-datadir=каталог_конфигурации

каталог конфигурации нового кластера; переменная окружения PGDATANEW

-j число_заданий
--jobs=число_заданий

число одновременно задействуемых процессов или потоков

-k
--link

использовать жёсткие ссылки вместо копирования файлов в новый кластер

-o параметры
--old-options параметры

параметры, передаваемые непосредственно старой программе postgres; несколько параметров складываются вместе

-O параметры
--new-options параметры

параметры, передаваемые непосредственно новой программе postgres; несколько параметров складываются вместе

-p порт
--old-port=порт

номер порта старого кластера; переменная окружения PGPORTOLD

-P порт
--new-port=порт

номер порта нового кластера; переменная окружения PGPORTNEW

-r
--retain

сохранить SQL и журналы сообщений даже при успешном завершении

-s каталог
--socketdir=каталог

каталог, в котором будет создавать сокеты процесс postmaster во время обновления; по умолчанию выбирается текущий рабочий каталог; переменная окружения PGSOCKETDIR

-U имя_пользователя
--username=имя_пользователя

имя пользователя, установившего кластер; переменная окружения PGUSER

-v
--verbose

включить подробные внутренние сообщения

-V
--version

показать версию, а затем завершиться

--clone

Использовать эффективное клонирование файлов (в ряде систем это называется «reflink») вместо копирования файлов в новый кластер. В результате файлы данных могут копироваться практически мгновенно, как и с использованием -k/--link, но последующие изменения не будут затрагивать старый кластер.

Клонирование файлов поддерживается не во всех операционных системах и только с определёнными файловыми системами. Если этот режим выбран, но клонирование не поддерживается, при выполнении pg_upgrade произойдёт ошибка. В настоящее время оно поддерживается в Linux (с ядром 4.5 или новее) с Btrfs и XFS (если файловая система была создана с поддержкой reflink), а также в macOS с APFS.

-?
--help

показать справку, а затем завершиться

Использование

Далее описан план обновления с использованием pg_upgrade:

  1. Переместить старый кластер (необязательно)

    Если ваш каталог инсталляции привязан к версии, например, /opt/PostgreSQL/14, перемещать его не требуется. Все графические инсталляторы выбирают при установке каталоги, привязанные к версии.

    Если ваш каталог инсталляции не привязан к версии, например /usr/local/pgsql, необходимо переместить каталог текущей инсталляции Postgres Pro, чтобы он не конфликтовал с новой инсталляцией Postgres Pro. Когда текущий сервер Postgres Pro отключён, каталог этой инсталляции Postgres Pro можно безопасно переместить; если старый каталог /usr/local/pgsql, его можно переименовать, выполнив: 

    mv /usr/local/pgsql /usr/local/pgsql.old

  2. Установить новые исполняемые файлы Postgres Pro

    Установите новые исполняемые файлы сервера и вспомогательные файлы. Программа pg_upgrade включена в инсталляцию по умолчанию.

  3. Инициализировать новый кластер Postgres Pro

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

  4. Установить разделяемые объектные файлы расширения

    Многие расширения и пользовательские модули, как из contrib, так и из других источников, используют разделяемые объектные файлы (или библиотеки DLL), например, pgcrypto.so. Если они использовались в старом кластере, разделяемые объектные файлы, соответствующие новому исполняемому файлу сервера, должны быть установлены в новом кластере, обычно средствами операционной системы. Не загружайте определения схемы, например, выполняя CREATE EXTENSION pgcrypto, потому что они будут скопированы из старого кластера. Если доступны обновления расширений, pg_upgrade сообщит об этом и создаст скрипт, который можно будет запустить позже, чтобы обновить эти расширения.

  5. Скопировать пользовательские файлы полнотекстового поиска

    Скопировать пользовательские файлы полнотекстового поиска (словари, тезаурусы, списки синонимов и стоп-слов) из старого кластера в новый.

  6. Настроить аутентификацию

    Программа pg_upgrade будет подключаться к новому и старому серверу несколько раз, так что имеет смысл установить режим аутентификации peer в pg_hba.conf или использовать файл ~/.pgpass (см. Раздел 36.16).

  7. Остановить оба сервера

    Убедитесь в том, что оба сервера баз данных остановлены. Для этого в Unix можно выполнить: 

    pg_ctl -D /opt/PostgreSQL/9.6 stop
pg_ctl -D /opt/PostgreSQL/14 stop

    А в Windows, с соответствующими именами служб: 

    NET STOP postgresql-9.6
NET STOP postgresql-14

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

  8. Подготовиться к обновлению ведомых серверов

    Если вы производите обновление ведомых серверов (как описано в разделе Шаг 10), удостоверьтесь, что эти серверы находятся в актуальном состоянии, запустив pg_controldata в старых ведущем и ведомых кластерах. Убедитесь в том, что «Положение последней контрольной точки» во всех кластерах одинаковое. Также смените wal_level на replica в файле postgresql.conf нового ведущего кластера.

  9. Запустить pg_upgrade

    Всегда запускайте программу pg_upgrade от нового сервера, а не от старого. pg_upgrade требует указания каталогов данных старого и нового кластера, а также каталогов исполняемых файлов (bin). Вы можете также определить имя пользователя и номера портов, и нужно ли копировать файлы данных (по умолчанию), клонировать их или создавать ссылки на них.

    Если выбрать вариант со ссылкой на данные, обновление выполнится гораздо быстрее (так как файлы не копируются) и потребует меньше места на диске, но вы лишитесь возможности обращаться к вашему старому кластеру, запустив новый после обновления. Этот вариант также требует, чтобы каталоги данных старого и нового кластера располагались в одной файловой системе. (Табличные пространства и pg_wal могут находиться в других файловых системах.) Вариант с клонированием работает так же быстро и экономит место на диске, но позволяет сохранить рабочее состояние старого кластера при запуске нового. Для этого варианта тоже требуется, чтобы старый и новый каталоги данных находились в одной файловой системе. Клонирование возможно только в некоторых операционных системах с определёнными файловыми системами.

    Параметр --jobs позволяет задействовать для копирования/связывания файлов и для выгрузки/восстановления схем баз данных несколько процессорных ядер. В качестве начального значения параметра стоит выбрать максимум из числа процессорных ядер и числа табличных пространств. Этот параметр может радикально сократить время обновления сервера со множеством баз данных, работающего в многопроцессорной системе.

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

    RUNAS /USER:postgres "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\PostgreSQL\14\bin;

    Наконец, запустить pg_upgrade с путями каталогов в кавычках, например, так: 

    pg_upgrade.exe
        --old-datadir "C:/Program Files/PostgreSQL/9.6/data"
        --new-datadir "C:/Program Files/PostgreSQL/14/data"
        --old-bindir "C:/Program Files/PostgreSQL/9.6/bin"
        --new-bindir "C:/Program Files/PostgreSQL/14/bin"

    При запуске pg_upgrade проверит два кластера на совместимость и, если всё в порядке, выполнит обновление. Также возможно запустить pg_upgrade --check, чтобы ограничиться только проверками (при этом старый сервер может продолжать работать). Команда pg_upgrade --check также сообщит, какие коррективы вам нужно будет внести вручную после обновления. Если вы планируете использовать режим ссылок на данные или клонирования, укажите вместе с --check или --clone параметр --link, чтобы были проведены специальные проверки для этого режима. Программе pg_upgrade требуются права на запись в текущий каталог.

    Очевидно, никто не должен обращаться к кластерам в процессе обновления. Программа pg_upgrade по умолчанию запускает серверы с портом 50432, чтобы не допустить нежелательных клиентских подключений. В процессе обновления оба кластера могут использовать один номер порта, так как они не будут работать одновременно. Однако для проверки старого работающего сервера новый порт должен отличаться от старого.

    Если при восстановлении схемы базы данных происходит ошибка, pg_upgrade завершает свою работу и вы должны вернуться к старому кластеру, как описывается ниже в Шаг 16. Чтобы попробовать pg_upgrade ещё раз, вы должны внести коррективы в старом кластере, чтобы pg_upgrade могла успешно восстановить схему. Если проблема возникла в модуле contrib, может потребоваться удалить этот модуль contrib в старом кластере, а затем установить его в новом после обновления (предполагается, что этот модуль не хранит пользовательские данные).

  10. Обновить ведомые серверы с потоковой репликацией и трансляцией журнала

    Если вы используете режим ссылок и у вас реализована потоковая репликация (см. Подраздел 26.2.5) или трансляция журнала (см. Раздел 26.2) для ведомых серверов, вы можете быстро обновить эти серверы следующим образом. Вам не нужно будет запускать на них pg_upgrade, вместо этого вы выполните rsync на ведущем. Не запускайте никакие серверы на этом этапе.

    Если вы не используете режим ссылок, либо у вас нет rsync или вы не хотите его использовать, либо если вам нужен более простой вариант, пропустите инструкции в этом разделе и просто пересоздайте ведомые серверы сразу после завершения pg_upgrade и запуска нового ведущего сервера.

    1. Установите новые исполняемые файлы Postgres Pro на ведомых серверах

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

    2. Убедитесь в том, что новые каталоги данных на ведомых серверах не существуют

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

    3. Установить разделяемые объектные файлы расширения

      Установите на новых ведомых серверах те же разделяемые объектные файлы расширения, что вы установили в новом ведущем кластере.

    4. Остановите ведомые серверы

      Если ведомые серверы продолжают работу, остановите их, следуя приведённым выше инструкциям.

    5. Сохраните файлы конфигурации

      Сохраните все нужные вам файлы конфигурации из старых каталогов конфигурации ведомых серверов, в частности postgresql.conf (и все файлы, включённые в него), postgresql.auto.conf и pg_hba.conf, так как они будут перезаписаны или удалены на следующем этапе.

    6. Запустите rsync

      Когда используется режим ссылок, ведомые серверы можно быстро обновить, применив rsync. Для этого в каталоге, внутри которого находятся каталоги старого и нового кластера, для каждого ведомого сервера выполните на ведущем: 

      rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster new_cluster remote_dir

      Здесь каталоги old_cluster и new_cluster задаются относительно текущего каталога на ведущем, а remote_dir находится над каталогами старого и нового кластера на ведомом. Структура подкаталогов в заданных каталогах на ведущем и ведомых серверах должна быть одинаковой. Обратитесь к странице руководства rsync, где подробно описано, как указать удалённый каталог, например так: 

      rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/9.5 \
      /opt/PostgreSQL/9.6 standby.example.com:/opt/PostgreSQL

      Проверить, что будет делать команда, можно, воспользовавшись параметром rsync --dry-run. Выполнить rsync на ведущем необходимо как минимум с одним ведомым, но затем, пока обновлённый ведомый остаётся остановленным, можно запускать rsync на нём для обновления других ведомых.

      В ходе этой операции записываются ссылки, созданные режимом ссылок pg_upgrade, связывающие файлы нового и старого кластера на ведущем сервере. Затем в старом кластере ведомого находятся соответствующие файлы и в новом кластере ведомого создаются ссылки на них. Файлы, не связанные ссылками на ведущем, копируются с него на ведомый. (Обычно их объём невелик.) Это позволяет произвести обновление ведомого быстро. К сожалению, при этом rsync будет напрасно копировать файлы, связанные с временными и нежурналируемыми таблицами, так как они обычно не будут существовать на ведомых серверах.

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

      rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_9.5_201510051 \
      /vol1/pg_tblsp/PG_9.6_201608131 standby.example.com:/vol1/pg_tblsp

      Если вы вынесли pg_wal за пределы каталогов данных, нужно будет запустить rsync и для этих каталогов.

    7. Настройте ведомые серверы с потоковой репликацией и трансляцией журнала

      Настройте серверы для трансляции журнала. (Запускать pg_start_backup() и pg_stop_backup() или делать копию файловой системы не нужно, так как ведомые серверы остаются синхронизированными с ведущим.) Слоты репликации не копируются, их нужно создавать заново.

  11. Восстановить pg_hba.conf

    Если вы изменяли pg_hba.conf, восстановите его исходное состояние. Также может потребоваться скорректировать другие файлы конфигурации в новом кластере, чтобы они соответствовали старому, например, postgresql.conf (и файлы, включённые в него) и postgresql.auto.conf.

  12. Запустить новый сервер

    Теперь можно безопасно запустить новый сервер, а затем ведомые серверы, синхронизированные с ним с помощью rsync.

  13. Действия после обновления

    Если после обновления требуются какие-то дополнительные действия, программа pg_upgrade выдаст предупреждения об этом по завершении работы. Она также сгенерирует файлы скриптов, которые должны запускаться администратором. Эти скрипты будут подключаться к каждой базе данных, требующей дополнительных операций. Каждый такой скрипт следует выполнять командой: 

    psql --username=postgres --file=script.sql postgres

    Эти скрипты могут выполняться в любом порядке, а после выполнения их можно удалить.

    Внимание

    Обычно к таблицам, задействованным в перестраивающих базу скриптах, опасно обращаться, пока эти скрипты не сделают свою работу; при этом можно получить некорректный результат или плохую производительность. К таблицам, не задействованным в таких скриптах, можно обращаться немедленно.

  14. Статистика

    Так как статистика оптимизатора не передаётся в процессе работы pg_upgrade, вы получите указание запустить соответствующую команду для воссоздания этой информации после обновления. Возможно, для этого вам понадобится установить параметры подключения к новому кластеру.

  15. Удалить старый кластер

    Если вы удовлетворены результатами обновления, вы можете удалить каталоги данных старого кластера, запустив скрипт, упомянутый в выводе pg_upgrade после обновления. (Автоматическое удаление невозможно, если в старом каталоге данных находятся дополнительные табличные пространства.) Также вы можете удалить каталоги старой инсталляции (например, bin, share).

  16. Возврат к старому кластеру

    Если выполнив команду pg_upgrade, вы захотите вернуться к старому кластеру, возможны следующие варианты:

    • Если использовался ключ --check, в старом кластере ничего не меняется; его можно просто перезапустить.

    • Если не использовался ключ --link, в старом кластере ничего не меняется; его можно просто перезапустить.

    • Если использовался ключ --link, у старого и нового кластера могут оказаться общие файлы данных:

      • Если работа pg_upgrade была прервана до начала расстановки ссылок, в старом кластере ничего не меняется; его можно просто перезапустить.

      • Если вы не запускали новый кластер, старый кластер не претерпел никаких изменений, за исключением того, что при создании ссылки на данные к имени $PGDATA/global/pg_control было добавлено окончание .old. Чтобы продолжить использование старого кластера, достаточно убрать окончание .old из имени файла $PGDATA/global/pg_control; после этого старый кластер можно будет перезапустить.

      • Если вы запускали новый кластер, он внёс изменения в общие файлы, и использовать старый кластер небезопасно. В этом случае старый кластер нужно будет восстановить из резервной копии.

Примечания

При миграции с PostgreSQL или Postgres Pro Standard с использованием pg_upgrade страницы в куче никогда не преобразуются из 32-битного формата в 64-битный во время или сразу по завершении обновления, поскольку это создаст колоссальную нагрузку на систему. Вместо этого страница преобразуется при первом вызове после обновления. В ходе этого преобразования происходит реорганизация страниц в куче, потому что 64-битная страница в куче должна иметь специальное пространство с определёнными значениями. Эти значения добавляются к значениям xmin и xmax каждого кортежа, позволяя преобразовывать xmin и xmax в 64-битный формат. Однако в некоторых случаях кортежи исходной страницы могут храниться так, что реорганизовать их невозможно, а очистка не выделит достаточно свободного места. Таким образом создаются страницы double xmax. У такой страницы нет специального пространства, фактически это 32-битная страница с полем xmin, которое содержит часть 64-битного поля xmax. Это сделано для соответствия MVCC, поскольку новое 64-битное значение xmax разбивается на две части, которые хранятся в полях xmin и xmax. Всё это возможно благодаря тому, что после обновления через pg_upgrade не остаётся никаких транзакций старого кластера, поэтому поле xmin можно использовать для хранения части 64-битного значения xmax. Страницы double xmax существуют, пока не освободится место для хранения специального пространства, например при удалении записи или обновлении кортежа. Страницы double xmax доступны в режиме чтения, однако при обновлении они преобразуются в 64-битный формат, поскольку при этом удаляются кортежи и освобождается место для преобразования.

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

Программа pg_upgrade запускает на короткое время процессы postmaster со старым и новым каталогом данных. Временные файлы сокетов Unix для взаимодействия с этими процессами по умолчанию создаются в текущем рабочем каталоге. В некоторых ситуациях путь к файлу в текущем каталоге может оказаться слишком длинным для имени сокета. В этом случае вы можете передать параметр -s, чтобы файлы сокетов создавались в другом каталоге с более коротким путём. В целях безопасности этот каталог не должен быть доступен для чтения и записи другим пользователям. (В Windows это не поддерживается.)

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

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

pg_upgrade не поддерживает обновление баз данных, в которых есть таблицы со столбцами, имеющими следующие системные типы данных reg*, ссылающиеся на OID:

regcollation
regconfig
regdictionary
regnamespace
regoper
regoperator
regproc
regprocedure

(Обновление regclass, regrole и regtype поддерживается.)

Если вы производите обновление кластера PostgreSQL версии до 9.2, в которой используется каталог только с файлами конфигурации, вы должны передать расположение собственно каталога с данными программе pg_upgrade, а расположение каталога конфигурации передать серверу, например -d /каталог-данных -o '-D /каталог-конфигурации'.

Если вы используете старый сервер версии до 9.1, работающий с нестандартным каталогом Unix-сокетов, либо его стандартное расположение отличается от принятого в новой версии, задайте в PGHOST расположение сокета старого сервера. (К Windows это не относится.)

При обновлении экземпляра Postgres Pro версии 9.6 или старее для баз данных с многобайтовой кодировкой утилита pg_upgrade может сгенерировать файлы SQL с командами REINDEX/VALIDATE. Вы должны запустить эти файлы, чтобы перестроить индексы или перепроверить ограничения. Эти файлы могут быть созданы в следующих случаях:

  • В старом кластере используются индексы или ограничения, которые зависят от правил сортировки, отличных от основного правила сортировки БД, C, или POSIX.

  • В Windows в старом кластере используются индексы или ограничения, которые зависят от основного правила сортировки с развёрнутым именем, например "Russian_Russia[.кодировка]" или "English_United States[.кодировка]".

Если вы хотите использовать режим ссылок на данные, но при этом исключить изменения в старом кластере при запуске нового, вам может подойти режим клонирования. Если же этот режим недоступен, сделайте копию старого кластера и обновите его в этом режиме. Чтобы получить рабочую копию старого кластера, воспользуйтесь командой rsync и создайте предварительную копию кластера при работающем сервере, а затем отключите старый сервер и ещё раз запустите rsync --checksum, чтобы привести эту копию в согласованное состояние. (Ключ --checksum необходим, потому что rsync различает время с точностью только до секунд.) При этом вы можете исключить некоторые файлы, например postmaster.pid, как описано в Подразделе 25.3.3. Если ваша файловая система поддерживает снимки файловой системы или копирование при записи, вы можете воспользоваться этим для создания копии старого кластера и табличных пространств; при этом важно, чтобы такие снимки и копии файлов создавались одномоментно или когда сервер баз данных отключён.

См. также

initdb, pg_ctl, pg_dump, postgres
Пред. Наверх След.
pg_test_timing Начало pg_waldump
45.13. Porting from Oracle PL/SQL
Prev UpChapter 45. PL/pgSQL — SQL Procedural LanguageHome Next

45.13. Porting from Oracle PL/SQL

45.13.1. Porting Examples
45.13.2. Other Things to Watch For
45.13.3. Appendix

This section explains differences between Postgres Pro's PL/pgSQL language and Oracle's PL/SQL language, to help developers who port applications from Oracle® to Postgres Pro.

PL/pgSQL is similar to PL/SQL in many aspects. It is a block-structured, imperative language, and all variables have to be declared. Assignments, loops, and conditionals are similar. The main differences you should keep in mind when porting from PL/SQL to PL/pgSQL are:

  • If a name used in an SQL command could be either a column name of a table used in the command or a reference to a variable of the function, PL/SQL treats it as a column name. By default, PL/pgSQL will throw an error complaining that the name is ambiguous. You can specify plpgsql.variable_conflict = use_column to change this behavior to match PL/SQL, as explained in Section 45.11.1. It's often best to avoid such ambiguities in the first place, but if you have to port a large amount of code that depends on this behavior, setting variable_conflict may be the best solution.

  • In Postgres Pro the function body must be written as a string literal. Therefore you need to use dollar quoting or escape single quotes in the function body. (See Section 45.12.1.)

  • Data type names often need translation. For example, in Oracle string values are commonly declared as being of type varchar2, which is a non-SQL-standard type. In Postgres Pro, use type varchar or text instead. Similarly, replace type number with numeric, or use some other numeric data type if there's a more appropriate one.

  • Instead of packages, use schemas to organize your functions into groups.

  • Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead.

  • Integer FOR loops with REVERSE work differently: PL/SQL counts down from the second number to the first, while PL/pgSQL counts down from the first number to the second, requiring the loop bounds to be swapped when porting. This incompatibility is unfortunate but is unlikely to be changed. (See Section 45.6.5.5.)

  • FOR loops over queries (other than cursors) also work differently: the target variable(s) must have been declared, whereas PL/SQL always declares them implicitly. An advantage of this is that the variable values are still accessible after the loop exits.

  • There are various notational differences for the use of cursor variables.

45.13.1. Porting Examples

Example 45.9 shows how to port a simple function from PL/SQL to PL/pgSQL.

Example 45.9. Porting a Simple Function from PL/SQL to PL/pgSQL

Here is an Oracle PL/SQL function: 

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
                                                  v_version varchar2)
RETURN varchar2 IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;

Let's go through this function and see the differences compared to PL/pgSQL:

  • The type name varchar2 has to be changed to varchar or text. In the examples in this section, we'll use varchar, but text is often a better choice if you do not need specific string length limits.

  • The RETURN key word in the function prototype (not the function body) becomes RETURNS in Postgres Pro. Also, IS becomes AS, and you need to add a LANGUAGE clause because PL/pgSQL is not the only possible function language.

  • In Postgres Pro, the function body is considered to be a string literal, so you need to use quote marks or dollar quotes around it. This substitutes for the terminating / in the Oracle approach.

  • The show errors command does not exist in Postgres Pro, and is not needed since errors are reported automatically.

This is how this function would look when ported to Postgres Pro: 

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURNS varchar AS $$
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;


Example 45.10 shows how to port a function that creates another function and how to handle the ensuing quoting problems.

Example 45.10. Porting a Function that Creates Another Function from PL/SQL to PL/pgSQL

The following procedure grabs rows from a SELECT statement and builds a large function with the results in IF statements, for the sake of efficiency.

This is the Oracle version: 

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_cmd VARCHAR(4000);
BEGIN
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
                 v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
          || '''; END IF;';
    END LOOP;

    func_cmd := func_cmd || ' RETURN NULL; END;';

    EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;

Here is how this function would end up in Postgres Pro: 

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
DECLARE
    referrer_keys CURSOR IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_body text;
    func_cmd text;
BEGIN
    func_body := 'BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
    END LOOP;

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
                                                        v_url varchar)
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;

Notice how the body of the function is built separately and passed through quote_literal to double any quote marks in it. This technique is needed because we cannot safely use dollar quoting for defining the new function: we do not know for sure what strings will be interpolated from the referrer_key.key_string field. (We are assuming here that referrer_key.kind can be trusted to always be host, domain, or url, but referrer_key.key_string might be anything, in particular it might contain dollar signs.) This function is actually an improvement on the Oracle original, because it will not generate broken code when referrer_key.key_string or referrer_key.referrer_type contain quote marks.


Example 45.11 shows how to port a function with OUT parameters and string manipulation. Postgres Pro does not have a built-in instr function, but you can create one using a combination of other functions. In Section 45.13.3 there is a PL/pgSQL implementation of instr that you can use to make your porting easier.

Example 45.11. Porting a Procedure With String Manipulation and OUT Parameters from PL/SQL to PL/pgSQL

The following Oracle PL/SQL procedure is used to parse a URL and return several elements (host, path, and query).

This is the Oracle version: 

CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR2,
    v_host OUT VARCHAR2,  -- This will be passed back
    v_path OUT VARCHAR2,  -- This one too
    v_query OUT VARCHAR2) -- And this one
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;

Here is a possible translation into PL/pgSQL: 

CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;

This function could be used like this: 

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');


Example 45.12 shows how to port a procedure that uses numerous features that are specific to Oracle.

Example 45.12. Porting a Procedure from PL/SQL to PL/pgSQL

The Oracle version: 

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock
        raise_application_error(-20000,
                 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
    END;
    COMMIT;
END;
/
show errors

This is how we could port this procedure to PL/pgSQL: 

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
DECLARE
    a_running_job_count integer;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1)
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN -- (2)
            -- don't worry if it already exists
    END;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

(1)

The syntax of RAISE is considerably different from Oracle's statement, although the basic case RAISE exception_name works similarly.

(2)

The exception names supported by PL/pgSQL are different from Oracle's. The set of built-in exception names is much larger (see Appendix A). There is not currently a way to declare user-defined exception names, although you can throw user-chosen SQLSTATE values instead.


45.13.2. Other Things to Watch For

This section explains a few other things to watch for when porting Oracle PL/SQL functions to Postgres Pro.

45.13.2.1. Implicit Rollback after Exceptions

In PL/pgSQL, when an exception is caught by an EXCEPTION clause, all database changes since the block's BEGIN are automatically rolled back. That is, the behavior is equivalent to what you'd get in Oracle with: 

BEGIN
    SAVEPOINT s1;
    ... code here ...
EXCEPTION
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
END;

If you are translating an Oracle procedure that uses SAVEPOINT and ROLLBACK TO in this style, your task is easy: just omit the SAVEPOINT and ROLLBACK TO. If you have a procedure that uses SAVEPOINT and ROLLBACK TO in a different way then some actual thought will be required.

45.13.2.2. EXECUTE

The PL/pgSQL version of EXECUTE works similarly to the PL/SQL version, but you have to remember to use quote_literal and quote_ident as described in Section 45.5.4. Constructs of the type EXECUTE 'SELECT * FROM $1'; will not work reliably unless you use these functions.

45.13.2.3. Optimizing PL/pgSQL Functions

Postgres Pro gives you two function creation modifiers to optimize execution: “volatility” (whether the function always returns the same result when given the same arguments) and “strictness” (whether the function returns null if any argument is null). Consult the CREATE FUNCTION reference page for details.

When making use of these optimization attributes, your CREATE FUNCTION statement might look something like this: 

CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

45.13.3. Appendix

This section contains the code for a set of Oracle-compatible instr functions that you can use to simplify your porting efforts. 

--
-- instr functions that mimic Oracle's counterpart
-- Syntax: instr(string1, string2 [, n [, m]])
-- where [] denotes optional parameters.
--
-- Search string1, beginning at the nth character, for the mth occurrence
-- of string2.  If n is negative, search backwards, starting at the abs(n)'th
-- character from the end of string1.
-- If n is not passed, assume 1 (search starts at first character).
-- If m is not passed, assume 1 (find first occurrence).
-- Returns starting index of string2 in string1, or 0 if string2 is not found.
--

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
BEGIN
    RETURN instr($1, $2, 1);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search_for IN temp_str);

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + beg_index - 1;
        END IF;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                RETURN beg;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF occur_index <= 0 THEN
        RAISE 'argument ''%'' is out of range', occur_index
          USING ERRCODE = '22003';
    END IF;

    IF beg_index > 0 THEN
        beg := beg_index - 1;
        FOR i IN 1..occur_index LOOP
            temp_str := substring(string FROM beg + 1);
            pos := position(string_to_search_for IN temp_str);
            IF pos = 0 THEN
                RETURN 0;
            END IF;
            beg := beg + pos;
        END LOOP;

        RETURN beg;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                occur_number := occur_number + 1;
                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
Prev Up Next
45.12. Tips for Developing in PL/pgSQL Home Chapter 46. PL/Tcl — Tcl Procedural Language