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

Как получить DDL запрос на создание аналогичной таблице по таблице в postgres?

Есть таблица. Можно ли получить запрос DDL на создание точно такой-же?

Вопрос задан более двух лет назад
1236 просмотров

Комментировать

Решения вопроса 1

ky0

Миллиардер, филантроп, патологический лгун

pg_dump -t 'schema-name.table-name' --schema-only database-name

Ответ написан более двух лет назад

ORA600

В PostgreSQL есть удобный способ получения DDL объектов из командной строки с помощью pg_dump, пример далее.
Но иногда требуется получить DDL непосредстванно из SQL-запроса. Есть ли у PostgreSQL с его расширениями способ получения DDL, аналогичный оракловой функции dbms_metadata.get_ddl() с требуемым форматированием?

Пример получения DDL таблицы с помощью pg_dump

$ pg_dump -s -t "bookings.airports_data" -U postgres demo -- -- PostgreSQL database dump -- -- Dumped from database version 15.4 -- Dumped by pg_dump version 15.4 SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: airports_data; Type: TABLE; Schema: bookings; Owner: postgres -- CREATE TABLE bookings.airports_data ( airport_code character(3) NOT NULL, airport_name jsonb NOT NULL, city jsonb NOT NULL, coordinates point NOT NULL, timezone text NOT NULL ) WITH (fillfactor='50', autovacuum_enabled='true', toast.autovacuum_enabled='true'); ALTER TABLE bookings.airports_data OWNER TO postgres; -- -- Name: TABLE airports_data; Type: COMMENT; Schema: bookings; Owner: postgres -- COMMENT ON TABLE bookings.airports_data IS 'Airports (internal data)'; -- -- Name: COLUMN airports_data.airport_code; Type: COMMENT; Schema: bookings; Owner: postgres -- COMMENT ON COLUMN bookings.airports_data.airport_code IS 'Airport code'; -- -- Name: COLUMN airports_data.airport_name; Type: COMMENT; Schema: bookings; Owner: postgres -- COMMENT ON COLUMN bookings.airports_data.airport_name IS 'Airport name'; -- -- Name: COLUMN airports_data.city; Type: COMMENT; Schema: bookings; Owner: postgres -- COMMENT ON COLUMN bookings.airports_data.city IS 'City'; -- -- Name: COLUMN airports_data.coordinates; Type: COMMENT; Schema: bookings; Owner: postgres -- COMMENT ON COLUMN bookings.airports_data.coordinates IS 'Airport coordinates (longitude and latitude)'; -- -- Name: COLUMN airports_data.timezone; Type: COMMENT; Schema: bookings; Owner: postgres -- COMMENT ON COLUMN bookings.airports_data.timezone IS 'Airport time zone'; -- -- Name: airports_data airports_data_pkey; Type: CONSTRAINT; Schema: bookings; Owner: postgres -- ALTER TABLE ONLY bookings.airports_data ADD CONSTRAINT airports_data_pkey PRIMARY KEY (airport_code); -- -- PostgreSQL database dump complete --

Транзакционный DDL и блокирование строк в PostgreSQL

Как известно, СУБД PostgreSQL поддерживает транзакции при выполнении операций DDL, причем чаще всего мы можем выполнять внутри одной транзакции несколько DDL-запросов, придерживаясь стратегии «всё или ничего». Однако у этого подхода есть большой минус: если мы меняем несколько объектов, нам придется заблокировать все из них. А блокировка нескольких таблиц, с одной стороны, делает возможной взаимную блокировку (deadlock), а с другой — вынуждает пользователей ожидать выполнения всей транзакции. Именно поэтому рекомендуется использовать отдельную транзакцию для каждого запроса.

Тут стоит отметить, что параллельное создание индексов является особым случаем. СУБД PostgreSQL запрещает выполнение CREATE INDEX CONCURRENTLY внутри явно описанной транзакции — зато вместо этого PostgreSQL создает транзакции самостоятельно и управляет ими. Если же по каким-нибудь причинам построение индекса будет прервано до успешного завершения, то может возникнуть необходимость вручную удалить его, прежде чем попытаться еще раз. Однако такой индекс в любом случае не будет никогда применяться для обслуживания запросов.

Блокирование строк

У PostgreSQL существует много различных уровней блокировки, однако нас в рамках данной статьи будут интересовать в большей степени блокировки уровня таблицы, т. к. DDL обычно оперирует на этом уровне: • ACCESS EXCLUSIVE: запрещается любое использование заблокированной таблицы; • SHARE ROW EXCLUSIVE: запрещаются DDL-команды, которые выполняются параллельно, а также модификация строк (при этом чтение разрешено); • SHARE UPDATE EXCLUSIVE: запрещаются лишь DDL-команды, которые выполняются параллельно.

Важно отметить, что понятие “команды DDL, которые выполняются параллельно”, в этом контексте включают операции VACUUM и ANALYZE.

Итак, все DDL-операции блокируют таблицу одним из вышеописанных способов. Например, выполнив ALTER TABLE foos ADD COLUMN bar INTEGER; СУБД PostgreSQL попробует получить блокировку уровня ACCESS EXCLUSIVE, причем на всей таблице foos.

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

Основные подходы

Но вместо того чтобы надеяться на СУБД PostrgeSQL, вы можете выполнять явную блокировку самостоятельно — это даст возможность аккуратно контролировать время, на которое ваши запросы будут откладываться в очередь. Если же у вас не получается выполнить блокировку в течение нескольких секунд, то рекомендуют добавлять небольшую задержку непосредственно перед следующей попыткой. В результате вы позволите выполниться отложенным запросам, не создавая чересчур большую нагрузку в будущем. Кроме того, прежде чем пробовать запускать блокировку, запросите из pg_locks перечень долго выполняющихся запросов — это даст возможность избежать постановки в очередь тех команд, которые, по всей видимости, не выполнятся.

Нередко блокировка уровня ACCESS EXCLUSIVE по настоящему необходима лишь на весьма короткий период, требуемый PostgreSQL для обновления его catalog tables (таблицы с метаданными). Иногда, чтобы избежать долгой приостановки SELECT/INSERT/UPDATE/DELETE, вполне достаточно более слабой блокировки либо применения альтернативных подходов.

Важный момент: порой удержание блокировки уровня ACCESS EXCLUSIVE для чего-нибудь большего, чем, к примеру, обновление каталога (либо перезаписи), бывает оправданным. Допустим, когда размер таблицы довольно мал. Рекомендуют проверять конкретные случаи применения на реалистичных размерах данных и оборудовании, дабы воочию увидеть, достаточно ли быстрой является операция. Если в вашем распоряжении хорошее оборудование, а таблица без проблем помещается в память, то как полное сканирование таблицы, так и перезапись тысяч строк будут достаточно быстрыми.

Выгрузка данных в PostgreSQL

Выгрузка данных в PostgreSQL работает вместе с передачей накопительных данных — заказов. Осуществляется утилитами двух уровней: верхнего и нижнего.

Верхний уровень

Утилиты верхнего уровня выполняют предварительную обработку данных и вызывают утилиты нижнего уровня для выгрузки в PostgreSQL.

Начиная с версии 7.06.05 утилиты верхнего уровня размещаются в подпапке SQL. В версии 07.06.04 размещаются рядом с вызывающим модулем.

RefsToSQL.exe — утилита для выгрузки справочников в PostgreSQL
Shift2SQL.exe — утилита для выгрузки накопительных данных в PostgreSQL
RefsToSQL.exe — утилита для выгрузки журнала изменения справочников в PostgreSQL.

Нижний уровень

Утилиты нижнего уровня предназначены для вызова из утилит верхнего уровня, осуществляют выгрузку в конкретный SQL сервер.

Начиная с 7.06.05 утилиты нижнего уровня размещаются рядом с утилитами верхнего уровня в подпапке SQL. В версии 07.06.04 утилита нижнего уровня размещается в подпапке SQL от утилиты верхнего уровня.

Описание конфигурационных файлов

Конфигурационные файлы необходимы только для отладки. Имя файла совпадает с именем утилиты с заменой расширения на .ini.

shift2sql.ini, refstosql.ini

[SHIFT2SQLCONF] ;включите режим сохранения логов и передаваемых файлов. В этом режиме утилита будет записывать весь прогресс и сделает копии файлов, которые были использованы для работы, например конфиг базы и загружаемый файл данных. ;значение по умолчанию = 0 ;TESTMODE=1 ;путь для сохранения логов. В этой папке будут создаваться подпапки в формате _\, и в этих подпапках будет сохраняться весь прогресс тестового режима. ;значение по умолчанию = \files\ ;TESTPATH=FOLDER FOR LOGS AND COPIES ;В режиме SAVERAWLOG будут создаваться файлы в той же папке в формате shift2sql.ХХХХХ.log ;в этих файлах можно будет увидеть когда был обработан тот или иной файл с данными. ;SAVERAWLOG=1

data2msq.ini

[SHIFT2SQLCONF] ;не удаляйте логи после завершения работы ;по умолчанию = 0 ;SAVERAWLOG=1 ;ИЛИ ;TESTMODE=1 ;путь для сохранения логов ;по умолчанию = \ ;LOGPATH=FOLDER TO SAVE LOGS IN ;ИЛИ ;TESTPATH=FOLDER TO SAVE LOGS IN ;время ожидания выполнения запроса в секундах по умолчанию = -1 (без таймаута) ;QueryTimeout=100500 ;параметр для работы авто-тестов ;включите сохранение данных для авто-тестов и авто-тестового режима в целом ;по умолчанию = 0 ;ExportScriptData=1 ;параметр для работы авто-тестов ;указывается путь для сохранения данных, необходимых для тестирования, например конфиг подключения, список таблиц, список полей, ключевые поля ;по умолчанию = ;ExportScriptDataPath=FOLDER TO SAVE TEST DATA ;параметр для работы авто-тестов ;"должен" сохранять скрипт запроса по указанному пути в файл script.txt, но судя по всему на данный момент сохраняет только самый последний кусок, а не весь скрипт целиком (видимо, не обновлялось с тех пор как была реализована разбивка скрипта) ;по умолчанию = ;ExportScriptPath=FODLER TO SAVE SCRIPT (SQL-QUERY) ;список таблиц, которые должны быть проигнорированы при экспорте, через запятую ;по умолчанию = ;SKIPTABLES=LOGBOOK,TABLE ;сохранять вызывающийся sql-запрос в файл в папке логов ;на текущий момент работает только в data2pgs ;LOGSQL=1

Настройки data2pgs идентичны data2msq за тем исключением, что на данный момент версия data2pgs отстает по ряду правок, из-за чего не работают следующие параметры: ExportScriptPath, ExportScriptDataPath.

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

Автоматическая работа

Для автоматической работы файл shift2sql.exe должен присутствовать в каталоге, где расположен *.exe файл сервера, осуществляющего выгрузку во внешнюю базу данных.

В версиях старше 07.04.21.289 файл присутствует в стандартной поставке, ручное обновление не требуется, если не тестируете новую версию.

Для версий ниже 07.05.04.xxx в конфигурационном файле сервера справочников добавьте в секции CONFIG опцию UseShift2SQL:

[CONFIG] UseShift2SQL = 1

Ручной запуск

Для ручного запуска необходимо в командной строке или исполняемом файле составить текст согласно следующему шаблону:

shift2sql.exe "ConnectionString" "shiftsFileName" "C:\SomePath\chckconv.xml" 0 "dataload" "sql\data2msq.exe"

Всего используется 5 параметров:

ConnectionString
shiftsFileName
dbExportConfigFile
parentProcIDToWait
sqlDataLoadingMode.

ConnectionString

Первый параметр — ConnectionString. Это строка соединения, вида:

"Provider=SQLNCLI10.1;Persist Security Info=True;Initial Catalog=RK7xBASEUPGx20;Data Source=310-RK7-ALAN\SQLEXPRESS;User

Полная строка подключения ADO включает в себя имя пользователя и пароль.

Или параметр dataBaseConnection — строка вида

ByHandle=2888

где значением параметра ByHandle является указатель открытого временного файла. Временный файл создается с флагом FILE_FLAG_DELETE_ON_CLOSE, с bInheritHandle=true в структуре _SECURITY_ATTRIBUTES

shiftsFileName

Второй Параметр — shiftsFileName. Имя файла выгружаемой смены — shift.udb.

Имя выгружаемого файла при необходимости выгрузить можно с полным путем, . Пример приведен для сервера MS SQL.

Или имя файла-источника данных — shiftsDataFileName

dbExportConfigFile

Третий параметр — полный путь к файлу описания структуры chckconv.xml. Файл dbExportConfigFile с правилами преобразования данных создается по умолчанию в папке /base/. Если сервер справочников используется и для отчетов, то файл создается после первой закачки смены с использованием утилиты shift2sql.exe.

Или с помощью LOGToSQL.xml в подпапке \base\refs2sql\

parentProcIDToWait

Четвертый параметр — parentProcIDToWait.

Это идентификатор родительского процесса ОС в виде числа.

При смерти родительского процесса shift2sql.exe тоже завершит свою работу.

Параметр опциональный, можно не указывать.

sqlDataLoadingMode

Пятый параметр — sqlDataLoadingMode. Это режим загрузки данных в SQL, который регулирует перезапись. Может иметь значение:

dataover — перезапись данных: данные во всех таблицах будут сперва удалены, а затем добавлены из указанного файла
datasync — сверка ключей записей и штампов RECSTAMP. Добавляет, удаляет или обновляет только то, что было изменено, synchronize
schmover — создание структуры: все таблицы будут пересозданы, все данные будут удалены, schmmake
dataload — добавление данных: данные добавляются к имеющимся в таблицах. Значения, пересекающиеся по ключам, будут обновлены
schmsync — создание структуры БД: существующие таблицы будут модифицированы. Опциональный — необязательный.

Путь к утилите

Шестой параметр — путь к утилите, которая будет производить непосредственную выгрузку на SQL-сервер.

sql\data2pgs.exe — подутилита, занимающаяся выгрузкой в PostgreSQL.

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

shift2sql.exe "ConnectionString" "shiftsFileName" "C:\SomePath\chckconv.xml" > logs.txt

Обработка нескольких смен

Для пакетной обработки нескольких смен можно использовать bat-файл со следующим содержимым:

@echo start for %%i in (shift*.udb) do ( echo %%i call "полный_путь_до_shift2sql.exe" "ConnectionString" "%%i" "полный_путь_до_chckconv.xml" >logs.txt ) pause

Bat-файл должен лежать в одной папке со сменами!

Настройка сервера справочников

Используйте флаг DelChckconv, чтобы отключить удаление xml-конфига chckconv.xml при импорте смен.

[CONFIG] DelChckconv = 0