Как выгрузить ddl таблицы postgresql

Миграция данных из Oracle в PostgreSQL

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

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

Итак, задача

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

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

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

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

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

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

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

1. Установим репозиторий Postgres.
2. Установим Postgres 9.4 сервер.
3. Создадим БД и настроим доступ.
4. Установим Postgres как сервис и запустим его.
5. Установим instant клиент Oracle.
6. Установим утилиту 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. В обеих хорошо соотносятся типы данных, и там, и там есть такое понятие, как схема. Воспользуемся этим и будем переносить данные «посхемно». Процесс миграции будет состоять из следующих шагов.

1. Создание проекта миграции с помощью Оra2pg.
2. Правка файла конфигурации ora2pg.conf.
3. Выгрузка DDL таблиц, индексов, constraints из Oracle.
4. Создание БД в Postgres.
5. Импорт DDL таблиц, подготовленный на 3-м шаге.
6. Копирование данных.
7. Импорт 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».

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

Первый подводный камень уже был упомянут выше: тип number() без указания точности конвертируется в bigint , но это легко исправить правильной конфигурацией.

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

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

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

Generate DDL programmatically on Postgresql

How can I generate the DDL of a table programmatically on Postgresql? Is there a system query or command to do it? Googling the issue returned no pointers.

6 Answers 6

Use pg_dump with this options:

Sorry if out of topic. Just wanna help who googling "psql dump ddl" and got this thread.

You can use the pg_dump command to dump the contents of the database (both schema and data). The —schema-only switch will dump only the DDL for your table(s).

Why would shelling out to psql not count as «programmatically?» It’ll dump the entire schema very nicely.

Anyhow, you can get data types (and much more) from the information_schema (8.4 docs referenced here, but this is not a new feature):

The answer is to check the source code for pg_dump and follow the switches it uses to generate the DDL. Somewhere inside the code there’s a number of queries used to retrieve the metadata used to generate the DDL.

Here is a good article on how to get the meta information from information schema, http://www.alberton.info/postgresql_meta_info.html.

I saved 4 functions to mock up pg_dump -s behaviour partially. Based on \d+ metacommand. The usage would be smth alike:

Of course you have to create functions prior.

The Overflow Blog

Linked

Related

Most asked in [postgresql]

Hot Network Questions

Subscribe to RSS

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.3.11.43304

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Extracting “CREATE TABLE” DDL From PostgreSQL

Oracle and MySQL both make it easy to extract CREATE TABLE DDL statements. For Oracle the function get_ddl in the package dbms_metadata can be used while MySQL offers the very useful SHOW CREATE TABLE statement. I was surprised to find that PostgreSQL did not have anything equivalent to either the MySQL or Oracle offerings. However, one of its attractions is the ease with which it can be extended using powerful scripting languages such as Perl and Python. Extensions can be created for these and other languages that allow them to be used like the default PostgreSQL procedural language PL/pgSQL to write functions that can then be called from SQL. I chose to use Python as a language extension for PostgreSQL because I have some familiarity with it and quite like it.

The Function Code

The following example was created in PostgreSQL 9.2 running on Ubuntu with Python 2.7 installed. The code was entered into the PostgreSQL GUI client pgAdmin III.

Having created a database using the old Oracle scott schema containing two small tables called emp and dept, I created the Python language extension by executing this command as the postgres superuser:

CREATE LANGUAGE plpythonu;

Assuming this step runs successfully, a python function can now be created. This is once again created using the postgres accountHere is code for a function that takes three arguments and returns the CREATE TABLE DDL statement:

CREATE OR REPLACE FUNCTION get_table_ddl (p_schema VARCHAR,
p_database_name VARCHAR,
p_table_name VARCHAR)
RETURNS VARCHAR
AS $$
import subprocess
import re
pg_dump_output = subprocess.check_output([“pg_dump”,
“–schema-only”,
“–schema=” + p_schema,
“–table=” + p_table_name,
p_database_name])
regex_pat = r'(^CREATE TABLE.+?\);$)’
matches = re.findall(regex_pat, pg_dump_output, re.DOTALL|re.MULTILINE)
ddl = matches[0]
return ddl
$$ LANGUAGE plpythonu SECURITY DEFINER;

Once this function is created, it can be called from SQL like so:

If all goes well, the DDL should be visible in the output.

Code Notes

• This function is crude but does what it is expected to do. It could be enhanced by multiple ways by, for example, adding error handling or by checking its three arguments. Does the given database exist, does it contain the given schema and does the given schema contain the given table name? In a production setting, it would be modified to check these things or additional helper functions would be written to do argument checking.
• The call to the externalpg_dump utility using the Python subprocess module function check_output returns the DDL with extraneous information. This string is then processed and the DDL is extracted using a fairly simple Python regular expression.
• Python is described as an “untrusted” PostgreSQL language extension meaning that it can only be used by a superuser like postgres to create functions. The terminal “u” in the language name signifies its untrusted nature.
• The terminating SECURITY DEFINER in the function source code is important and allows the superuser to grant EXECUTE privileges on the function to less privileged users who are not able to create functions in Python.

Trouble Shooting

If the function does not work or throws errors check the following:

Can pg_dump be called from the command line as follows:

$ su postgres # become postgres user

$ pg_dump –schema-only –schema=public –table=emp scott # Should spew copious text

If the above works, then try Python by opening the Python shell and from there, type the following:

>>> from subprocess import check_output

>>> print ddl # Should get the same output as when calling pg_dump directly from the bash shell

Как выгрузить ddl таблицы postgresql

This guide describes how you can export data from and import data into a PostgreSQL database. You can learn more about this topic in the official PostgreSQL docs.

Data export with pg_dump

pg_dump is a native PostgreSQL utility you can use to export data from your PostgreSQL database. To see all the options for this command, run:

The idea behind this dump method is to generate a file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump. PostgreSQL provides the utility program pg_dump for this purpose. pg_dump is a regular PostgreSQL client application (albeit a particularly clever one). This means that you can perform this backup procedure from any remote host that has access to the database. But remember that pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in order to back up the entire database you almost always have to run it as a database superuser.

The basic syntax of the command looks like this:

You need to replace the DB_NAME and OUTPUT_FILE placeholders with the respective values for:

• your database name
• the name of the desired output file (should end in .sql for best interoperability)

For example, to export data from a database called mydb on a local PostgreSQL server into a file called mydb.sql , you can use the following command:

If your database schema uses Object Identifier Types (OIDs), you’ll need to run pg_dump with the —oids (short: -o ) option:

Providing database credentials

You can add the following arguments to specify the location of your PostgreSQL database server:

Argument	Default	Env var	Description
—host (short: -h )	localhost	PGHOST	The address of the server’s host machine
—port (short: -p )	—	PGPORT	The port of the server’s host machine where the PostgreSQL server is listening

To authenticate against the PostgreSQL database server, you can use the following argument:

Argument	Default	Env var	Description
—username (short: -U )	your current operating system user name	PGUSER	The name of the database user.

For example, if you want to export data from a PostgreSQL database that has the following connection string:

You can use the following pg_dump command:

Note that this command will trigger a prompt where you need to specify the password for the provided user.

Controlling the output

There might be cases where you don’t want to dump the entire database, for example you might want to:

• dump only the actual data but exclude the DDL (i.e. the SQL statements that define your database schema like CREATE TABLE . )
• dump only the DDL but exclude the actual data
• exclude a specific PostgreSQL schema
• exclude large files
• exclude specific tables

Here’s an overview of a few command line options you can use in these scenarios:

Argument	Default	Description
—data-only (short: -a )	false	Exclude any DDL statements and export only data.
—schema-only (short: -s )	false	Exclude data and export only DDL statements.
—blobs (short: -b )	true unless the -schema , —table , or —schema-only options are specified	Include binary large objects.
—no-blobs (short: -B )	false	Exclude binary large objects.
—table (short: -t )	includes all tables by default	Explicitly specify the names of the tables to be dumped.
—exclude-table (short: -T )	—	Exclude specific tables from the dump.

Importing data from SQL files

After having used SQL Dump to export your PostgreSQL database as a SQL file, you can restore the state of the database by feeding the SQL file into psql :

You need to replace the DB_NAME and INPUT_FILE placeholders with the respective values for:

• your database name (a database with that name must be created beforehand!)
• the name of the target input file (likely ends with .sql )

To create the database DB_NAME beforehand, you can use the template0 (which creates a plain user database that doesn’t contain any site-local additions):

Exporting data from PostgreSQL and ingesting it again to recreate your data structures and populate databases is a good way to migrate data, back up and recover, or prepare for replication. Understanding how the pg_dump and psql tools work together to accomplish this task will help you transfer data across the boundaries of your databases.

Миграция данных из Oracle в PostgreSQL

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

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

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

Extracting “CREATE TABLE” DDL From PostgreSQL

Oracle and MySQL both make it easy to extract CREATE TABLE DDL statements. For Oracle the function get_ddl in the package dbms_metadata can be used while MySQL offers the very useful SHOW CREATE TABLE statement. I was surprised to find that PostgreSQL did not have anything equivalent to either the MySQL or Oracle offerings. However, one of its attractions is the ease with which it can be extended using powerful scripting languages such as Perl and Python. Extensions can be created for these and other languages that allow them to be used like the default PostgreSQL procedural language PL/pgSQL to write functions that can then be called from SQL. I chose to use Python as a language extension for PostgreSQL because I have some familiarity with it and quite like it.

The Function Code

The following example was created in PostgreSQL 9.2 running on Ubuntu with Python 2.7 installed. The code was entered into the PostgreSQL GUI client pgAdmin III.

Having created a database using the old Oracle scott schema containing two small tables called emp and dept, I created the Python language extension by executing this command as the postgres superuser:

CREATE LANGUAGE plpythonu;

Assuming this step runs successfully, a python function can now be created. This is once again created using the postgres accountHere is code for a function that takes three arguments and returns the CREATE TABLE DDL statement:

CREATE OR REPLACE FUNCTION get_table_ddl (p_schema VARCHAR,
p_database_name VARCHAR,
p_table_name VARCHAR)
RETURNS VARCHAR
AS $$
import subprocess
import re
pg_dump_output = subprocess.check_output([“pg_dump”,
“–schema-only”,
“–schema=” + p_schema,
“–table=” + p_table_name,
p_database_name])
regex_pat = r'(^CREATE TABLE.+?\);$)’
matches = re.findall(regex_pat, pg_dump_output, re.DOTALL|re.MULTILINE)
ddl = matches[0]
return ddl
$$ LANGUAGE plpythonu SECURITY DEFINER;

Once this function is created, it can be called from SQL like so:

If all goes well, the DDL should be visible in the output.

Code Notes

• This function is crude but does what it is expected to do. It could be enhanced by multiple ways by, for example, adding error handling or by checking its three arguments. Does the given database exist, does it contain the given schema and does the given schema contain the given table name? In a production setting, it would be modified to check these things or additional helper functions would be written to do argument checking.
• The call to the externalpg_dump utility using the Python subprocess module function check_output returns the DDL with extraneous information. This string is then processed and the DDL is extracted using a fairly simple Python regular expression.
• Python is described as an “untrusted” PostgreSQL language extension meaning that it can only be used by a superuser like postgres to create functions. The terminal “u” in the language name signifies its untrusted nature.
• The terminating SECURITY DEFINER in the function source code is important and allows the superuser to grant EXECUTE privileges on the function to less privileged users who are not able to create functions in Python.

Trouble Shooting

If the function does not work or throws errors check the following:

Can pg_dump be called from the command line as follows:

$ su postgres # become postgres user

$ pg_dump –schema-only –schema=public –table=emp scott # Should spew copious text

If the above works, then try Python by opening the Python shell and from there, type the following:

>>> from subprocess import check_output

>>> print ddl # Should get the same output as when calling pg_dump directly from the bash shell