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

Как получить DDL запрос пакетом DBMS_METADATA без дополнительных деталей?

Можно ли получить DDL запрос без дополнительных деталей, например, STORAGE ? _{Свободный перевод вопроса Problem with getting an Oracle table ddl but with out the extra detail from the current schema от участника @Hoopoe}

Отслеживать
задан 25 сен 2021 в 18:31
51.6k 200 200 золотых знаков 61 61 серебряный знак 242 242 бронзовых знака
ассоциация:stackoverflow.com/q/69327505
25 сен 2021 в 18:33

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Это возможно, установив параметры процедурой SET_TRANSFORM_PARAM.

Так будет с параметрами по умолчанию:

create table t ( id int primary key, val varchar(16), flag char (1) check (flag in ('y','n'))) / set long 9999 select dbms_metadata.get_ddl ('TABLE', 'T', user) t_ddl from dual; T_DDL -------------------------------------------------------------------------------- CREATE TABLE "DB"."T" ( "ID" NUMBER(*,0), "VAL" VARCHAR2(16), "FLAG" CHAR(1), CHECK (flag in ('y','n')) ENABLE, PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USERS" ENABLE ) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "USERS" 

Теперь установив параметры (подробнее см. по ссылке выше):

begin dbms_metadata.set_transform_param ( dbms_metadata.session_transform, 'SQLTERMINATOR', true); dbms_metadata.set_transform_param ( dbms_metadata.session_transform, 'PRETTY', true); dbms_metadata.set_transform_param ( dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false); dbms_metadata.set_transform_param ( dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false); dbms_metadata.set_transform_param ( dbms_metadata.session_transform, 'STORAGE', false); end; / select dbms_metadata.get_ddl ('TABLE', 'T', user) t_ddl from dual; T_DDL -------------------------------------------------------------------------------- CREATE TABLE "DB"."T" ( "ID" NUMBER(*,0), "VAL" VARCHAR2(16), "FLAG" CHAR(1), CHECK (flag in ('y','n')) ENABLE, PRIMARY KEY ("ID") USING INDEX ENABLE ) ; 

_{Свободный перевод ответа от участника @0xdb}

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 запрос на создание аналогичной таблице по таблице в postgres?

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

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

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

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

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

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

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

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

Нравится Комментировать

Ответы на вопрос 0

Ваш ответ на вопрос

Войдите, чтобы написать ответ

• PostgreSQL

Как сделать grouping?

• 1 подписчик
• 27 нояб.
• 50 просмотров

• React
• +2 ещё

Как правильно распределить последовательность при создании веб приложения?

• 2 подписчика
• 25 нояб.
• 87 просмотров

• Java
• +2 ещё

Как иерархические данные из таблицы преобразовать в объекты Java?

• 1 подписчик
• 25 нояб.
• 71 просмотр

• PostgreSQL

Что за файлы, имена которых начинается с символа «?» (AstraLinux SE 1.7.4)?

• 1 подписчик
• 24 нояб.
• 146 просмотров

• PostgreSQL

Каким образом корректно извлечь первый подходящий элемент массива jsonb в Postgresql?

• 1 подписчик
• 24 нояб.
• 58 просмотров

• Windows
• +1 ещё

PostgreSQL. Как подтянуть уже имеющийся каталог с базами?

• 1 подписчик
• 21 нояб.
• 100 просмотров

• PostgreSQL

Какие есть способы отключить историю команд в psql?

• 1 подписчик
• 20 нояб.
• 76 просмотров

• PostgreSQL

Как можно получить изменения во VIEW, когда изменяется базовая таблица?

• 2 подписчика
• 19 нояб.
• 158 просмотров

• PostgreSQL

Какие есть сервисы для визуализации базы данных через веб интерфейс?

• 1 подписчик
• 18 нояб.
• 104 просмотра

• PostgreSQL
• +1 ещё

Как добиться корректной остановки postgres при остановке контейнера docker?

• 2 подписчика
• 18 нояб.
• 286 просмотров

от 280 000 до 300 000 ₽

от 100 000 ₽

30 нояб. 2023, в 02:08

500 руб./в час

30 нояб. 2023, в 02:00

10000 руб./за проект

30 нояб. 2023, в 00:25

1000 руб./за проект

Минуточку внимания

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

• Как сгенерировать изображения с большим количеством текста?
  • 3 подписчика
  • 1 ответ
  • 3 подписчика
  • 3 ответа
  • 2 подписчика
  • 1 ответ
  • 2 подписчика
  • 0 ответов
  • 1 подписчик
  • 0 ответов
  • 2 подписчика
  • 0 ответов
  • 2 подписчика
  • 1 ответ
  • 2 подписчика
  • 2 ответа
  • 2 подписчика
  • 0 ответов
  • 2 подписчика
  • 1 ответ

  Резервное копирование и восстановление PostgreSQL: pg_dump, pg_restore, wal-g

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

  Эта инструкция — часть курса «PostgreSQL для новичков».

  Смотреть весь курс

  

  Задача резервного копирования — одна из основных при сопровождении и поддержке PostgreSQL. Для резервного копирования логической схемы и данных можно использовать как встроенные инструменты СУБД, так и внешние. В этой статье мы разберем оба варианта.

  Для начала подготовим сервер. Для демо-стенда закажем виртуальный сервер в Облачной платформе. Для этого откроем панель управления my.selectel.ru, перейдем в меню Облачная платформа и нажмем на кнопку Создать сервер.

  

  В статье будем использовать виртуальный сервер с конфигурацией 2 vCPU, 4 ГБ RAM и 10 ГБ HDD с операционной системой CentOS 8 Stream 64-bit.

  

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

  

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

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

  Облачные базы данных Selectel

  Доверьте нам развертывание и администрирование баз данных в облаке.

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

  В этом разделе мы расскажем как сделать дамп базы данных PostgreSQL в консоли при подключении по SSH, разберем синтаксис и покажем примеры использования утилит pg_dump, pg_dumpall, pg_restore, pg_basebackup и wal-g.

  Утилита pg_dump

  В PostgreSQL есть встроенный инструмент для создания резервных копий — утилита pg_dump. Утилита имеет простой синтаксис:

  # pg_dump  >

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

  # pg_dump zabbix > /tmp/zabbix.dump

  Если требуется авторизация под определенным пользователем, можно воспользоваться ключом -U:

  # pg_dump -U zabbix -W zabbix > /tmp/zabbix.dump # pg dump u postgres

  Ключ -U определяет пользователя, а -W обязывает ввести пароль.

  Чтобы сэкономить место на диске, можно сразу же сжимать дамп:

  # pg_dump -U zabbix -W zabbix | gzip > /tmp/zabbix.gz

  Резервное копирование обычно выполняется по расписанию, например, ежедневно в 3 часа ночи. Нижеприведенный пример скрипта не только выполняет бэкап, но и удаляет все файлы старше 61 дня (за исключением 15-го числа месяца).

  #!/bin/sh PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin PGPASSWORD=some_password export PGPASSWORD pathB=/mnt/backup dbUser=dbadmin database=zabbix find $pathB \( -name "*-1[^5].*" -o -name "*-[023]?.*" \) -ctime +61 -delete pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date "+%Y-%m-%d").sql.gz unset PGPASSWORD

  Чтобы настроить регулярное выполнение, выполним следующую команду в планировщике crontab:

  # crontab -e 3 0 * * * /etc/scripts/pgsql_dump.sh # postgres pg dump

  Чтобы выполнить аналогичную команду на удаленном сервере, достаточно добавить ключ -h:

  # pg_dump -h 192.168.56.101 zabbix > /tmp/zabbix.dump

  Ключ -t задает таблицу, для которой нужно создать резервную копию:

  # pg_dump -t history zabbix > /tmp/zabbix.dump # postgres dump table

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

  # pg_dump --schema-only zabbix > /tmp/zabbix.dump # pg_dump --data-only zabbix > /tmp/zabbix.dump

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

  # pg_dump -Fc zabbix > /tmp/zabbix.bak

  Чтобы создать архив — -Ft:

  # pg_dump -Ft zabbix > /tmp/zabbix.tar

  Чтобы сохранить в directory-формате — -Fd:

  # pg_dump -Fd zabbix > /tmp/zabbix.dir

  Резервное копирование в виде каталогов позволяет выполнять процесс в многопоточном режиме.

  Ниже мы перечислим возможные параметры утилиты pg_dump.

  -d , —dbname=имя_бд — база данных, к которой выполняется подключение.

  -h , —host=сервер — имя сервера.

  -p , —port=порт — порт для подключения.

  -U , —username=пользователь) — учетная запись, используемое для подключения.

  -w, —no-password — деактивация требования ввода пароля.

  -W, —password — активация требования ввода пароля.

  —role=имя роли — роль, от имени которой генерируется резервная копия.

  -a, —data-only — вывод только данных, вместо схемы объектов (DDL).

  -b, —blobs — параметр добавляет в выгрузку большие объекты.

  -c, —clean — добавление команд DROP перед командами CREATE в файл резервной копии.

  -C, —create — генерация реквизитов для подключения к базе данных в файле резервной копии.

  -E , —encoding=кодировка — определение кодировки резервной копии.

  -f , —file=файл — задает имя файла, в который будет сохраняться вывод утилиты.

  -F , —format=формат — параметр определяет формат резервной копии. Доступные форматы:

  • p, plain) — формирует текстовый SQL-скрипт;
  • c, custom) — формирует резервную копию в архивном формате;
  • d, directory) — формирует копию в directory-формате;
  • t, tar) — формирует копию в формате tar.

  -j , —jobs=число_заданий — параметр активирует параллельную выгрузку для одновременной обработки нескольких таблиц (равной числу заданий). Работает только при выгрузке копии в формате directory.

  -n , —schema=схема — выгрузка в файл копии только определенной схемы.

  -N , —exclude-schema=схема — исключение из выгрузки определенных схем.

  -o, —oids — добавляет в выгрузку идентификаторы объектов (OIDs) вместе с данными таблиц.

  -O, —no-owner — деактивация создания команд, определяющих владельцев объектов в базе данных.

  -s, —schema-only —добавление в выгрузку только схемы данных, без самих данных.

  -S , —superuser=пользователь — учетная запись привилегированного пользователя, которая должна использоваться для отключения триггеров.

  -t , —table=таблица — активация выгрузки определенной таблицы.

  -T , —exclude-table=таблица —исключение из выгрузки определенной таблицы.

  -v, —verbose — режим подробного логирования.

  -V, —version — вывод версии pg_dump.

  -Z 0..9, —compress=0..9 — установка уровня сжатия данных. 0 — сжатие выключено.

  Утилита pg_dumpall

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

  # pg_dumpall > /tmp/instance.bak

  Чтобы сразу сжать резервную копию экземпляра базы данных, нужно передать вывод на архиватор gzip:

  # pg_dumpall | gzip > /tmp/instance.tar.gz

  Ниже приведены параметры, с которыми может вызываться утилита pg_dumpall.

  -d , —dbname=имя_бд — имя базы данных.

  -h , —host=сервер — имя сервера.

  -p , —port=порт — TCP-порт, на который принимаются подключения.

  -U , —username=пользователь — имя пользователя для подключения.

  -w, —no-password — деактивация требования ввода пароля.

  -W, —password — активация требования ввода пароля.

  —role= — роль, от имени которой генерируется резервная копия.

  -a, —data-only — создание резервной копии без схемы данных.

  -c, —clean — добавление операторов DROP перед операторами CREATE.

  -f , —file=имя_файла — активация направления вывода в указанный файл.

  -g, —globals-only — выгрузка глобальных объектов без баз данных.

  -o, —oids — выгрузка идентификаторов объектов (OIDs) вместе с данными таблиц.

  -O, —no-owner — деактивация генерации команд, устанавливающих принадлежность объектов, как в исходной базе данных.

  -r, —roles-only — выгрузка только ролей без баз данных и табличных пространств.

  -s, —schema-only — выгрузка только схемы без самих данных.

  -S , —superuser=имя_пользователя — привилегированный пользователь, используемый для отключения триггеров.

  -t, —tablespaces-only — выгрузка табличных пространства без баз данных и ролей.

  -v, —verbose — режим подробного логирования.

  -V (—version — вывод версии утилиты pg_dumpall.

  Утилита pg_restore

  Утилита позволяет восстанавливать данные из резервных копий. Например, чтобы восстановить только определенную БД (в нашем примере zabbix), нужно запустить эту утилиту с параметром -d:

  # pg_restore -d zabbix /tmp/zabbix.bak

  Чтобы этой же утилитой восстановить определенную таблицу, нужно использовать ее с параметром -t:

  # pg_restore -a -t history /tmp/zabbix.bak

  Также утилитой pg_restore можно восстановить данные из бинарного или архивного файла. Соответственно:

  # pg_restore -Fc zabbix.bak # pg_restore -Ft zabbix.tar

  При восстановлении можно одновременно создать новую базу:

  # pg_restore -Ft -С zabbix.tar

  Восстановить данные из дампа также возможно при помощи psql:

  # psql zabbix < /tmp/zabbix.dump

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

  # psql -U zabbix -W zabbix < /tmp/zabbix.dump

  Ниже приведен синтаксис утилиты pg_restore.

  -h , —host=сервер — имя сервера, на котором работает база данных.

  -p , —port=порт — TCP-порт, через база данных принимает подключения.

  -U , —username=пользователь — имя пользователя для подключения..

  -w, —no-password — деактивация требования ввода пароля.

  -W, —password — активация требования ввода пароля.

  —role=имя роли — роль, от имени которой выполняется восстановление резервная копия.

  — расположение восстанавливаемых данных.

  -a, —data-only — восстановление данных без схемы.

  -c, —clean — добавление операторов DROP перед операторами CREATE.

  -C, —create — создание базы данных перед запуском процесса восстановления.

  -d , —dbname=имя_бд — имя целевой базы данных.

  -e, —exit-on-error — завершение работы в случае возникновения ошибки при выполнении SQL-команд.

  -f , —file=имя_файла — файл для вывода сгенерированного скрипта.

  -F , —format=формат — формат резервной копии. Допустимые форматы:

  • p, plain — формирует текстовый SQL-скрипт;
  • c, custom — формирует резервную копию в архивном формате;
  • d, directory — формирует копию в directory-формате;
  • t, tar — формирует копию в формате tar.

  -I , —index=индекс — восстановление только заданного индекса.

  -j , —jobs=число-заданий — запуск самых длительных операций в нескольких параллельных потоках.

  -l, —list) — активация вывода содержимого архива.

  -L , —use-list=файл-список — восстановление из архива элементов, перечисленных в файле-списке в соответствующем порядке.

  -n , —schema=схема — восстановление объектов в указанной схеме.

  -O, —no-owner — деактивация генерации команд, устанавливающих владение объектами по образцу исходной базы данных.

  -P <имя-функции(тип-аргумента[, …])>, —function=имя-функции(тип-аргумента[, …]) — восстановление только указанной функции.

  -s, —schema-only — восстановление только схемы без самих данных.

  -S , —superuser=пользователь — учетная запись привилегированного пользователя, используемая для отключения триггеров.

  -t , —table=таблица — восстановление определенной таблицы.

  -T , —trigger=триггер — восстановление конкретного триггера.

  -v, —verbose — режим подробного логирования.

  -V, —version — вывод версии утилиты pg_restore.

  Утилита pg_basebackup

  Утилитой pg_basebackup можно выполнять резервное копирования работающего кластера баз данных PostgreSQL. Результирующий бинарный файл можно использовать для репликации или восстановления на определенный момент в прошлом. Утилита создает резервную копию всего экземпляра базы данных и не дает возможности создавать слепки данных отдельных сущностей. Подключение pg_basebackup к PostgreSQL выполняется при помощи протокола репликации с полномочиями суперпользователя или с правом REPLICATION.

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

  # pg_basebackup -D /tmp

  Чтобы создать сжатые файлы из табличных пространств, добавим параметры -Ft и -z:

  # pg_basebackup -D /tmp -Ft -z 

  То же самое, но со сжатием bzip2 и для экземпляра базы с общим табличным пространством:

  # pg_basebackup -D /tmp -Ft | bzip2 > backup.tar.bz2

  Ниже приведен синтаксис утилиты pg_basebackup.

  -d , —dbname=строка_подключения — определение базы данных в виде строки для подключения.

  -h , —host=сервер — имя сервера с базой данных.

  -p , —port=порт — TCP-порт, через база данных принимает подключения.

  -s , —status-interval=интервал — количество секунд между отправками статусных пакетов.

  -U , —username=пользователь — установка имени пользователя для подключения.

  -w, —no-password — отключение запроса на ввод пароля.

  -W, —password — принудительный запрос пароля.

  -V, —version — вывод версии утилиты pg_basebackup.

  -?, —help — вывод справки по утилите pg_basebackup.

  -D каталог, —pgdata=каталог — директория записи данных.

  -F , —format=формат — формат вывода. Допустимые варианты:

  • p, plain — значение для записи выводимых данных в текстовые файлы;
  • t, tar — значение, указывающее на необходимость записи в целевую директорию в формате tar.

  -r , —max-rate=скорость_передачи — предельная скорость передачи данных в Кб/с.

  -R, —write-recovery-conf — записать минимальный файл recovery.conf в директорию вывода.

  -S , —slot=имя_слота — задание слота репликации при использовании WAL в режиме потоковой передачи.

  -T , —tablespace-mapping=каталог_1=каталог_2 — активация миграции табличного пространства из одного каталога в другой каталог при копировании.

  —xlogdir=каталог_xlog — директория хранения журналов транзакций.

  -X , —xlog-method=метод — активация вывода файлов журналов транзакций WAL в резервную копию на основе следующих методов:

  • f, fetch — включение режима сбора файлов журналов транзакций при окончании процесса копирования;
  • s, stream — включение передачи журнала транзакций в процессе создания резервной копии.

  -z, —gzip — активация gzip-сжатия результирующего tar-файла.

  -Z , —compress=уровень — определение уровня сжатия механизмом gzip.

  -c , —checkpoint=fast|spread — активация режима реперных точек.

  -l , —label=метка — установка метки резервной копии.

  -P, —progress — активация в вывод отчета о прогрессе.

  -v, —verbose — режим подробного логирования.

  Утилита wal-g

  Wal-g — утилита для резервного копирования и восстановления базы данных PostgreSQL. При помощи wal-g можно выполнять сохранение резервных копий на хранилищах S3 или просто на файловой системе. Ниже мы разберем установку, настройку и работу с утилитой. Покажем как выполнить резервное копирование в Объектное хранилище S3 от Selectel.

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

  

  Дополнительную информацию можно получить в нашей Базе знаний. Первую часть логина изменить нельзя — это идентификатор пользователя в панели управления. Вторая часть логина задается произвольно. Например, 123456_wal-g:

  

  Теперь перейдем к установке wal-g. Скачаем готовый установочный пакет из репозитория на github.com, распакуем и скопируем папку содержающую исполняемые файлы:

  # cd /tmp # curl -L "https://github.com/wal-g/wal-g/releases/download/v0.2.19/wal-g.linux-amd64.tar.gz" -o "wal-g.linux-amd64.tar.gz # tar -xzf wal-g.linux-amd64.tar.gz # mv wal-g /usr/local/bin/

  Заполним конфигурационный файл wal-g и изменим его владельца на учетную запись postgres:

  # cat > /var/lib/pgsql/.walg.json EOF # chown postgres: /var/lib/pgsql/.walg.json

  Далее настроим автоматизированное создание резервных копий в PostgreSQL и перезагрузим процессы базы данных:

  # echo "wal_level=replica" >> /var/lib/pgsql/data/postgresql.conf # echo "archive_mode=on" >> /var/lib/pgsql/data/postgresql.conf # echo "archive_command='/usr/local/bin/wal-g wal-push \"%p\" >> /var/log/postgresql/archive_command.log 2>&1' " >> /var/lib/pgsql/data/postgresql.conf # echo “archive_timeout=60” >> /var/lib/pgsql/data/postgresql.conf # echo "restore_command='/usr/local/bin/wal-g wal-fetch \"%f\" \"%p\" >> /var/log/postgresql/restore_command.log 2>&1' " >> /var/lib/pgsql/data/postgresql.conf # killall -s HUP postgres

  Теперь проверим корректность проведения настроек и загрузим резервную копию в хранилище:

  # su - postgres -c '/usr/local/bin/wal-g backup-push /var/lib/pgsql/data'

  После выполнения процесса резервного копирования, в созданном контейнере появится директория с резервными копиями баз данных:

  

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

  Утилита pgAdmin

  Управлять созданием резервных копий возможно также и в графическом интерфейсе. Для этого мы будем использовать утилиту pgAdmin (в примере — работа с утилитой на локальном устройстве, но то же самое можно сделать на сервере). Актуальную версию для Windows или другой поддерживаемой ОС можно свободно скачать с официального сайта.

  

  После скачивания утилиту нужно установить и запустить. Она работает в виде веб-приложения через браузер.

  

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

  

  После выполнения команды Backup резервная копия сохраняется в заранее определенную директорию.

  

  Работа с облачной базой данных в панели управления Selectel

  В облачной платформе Selectel есть возможность создавать управляемые базы данных (Managed Databases). Такие БД разворачиваются в несколько кликов мыши, однако, их основные преимущества — автоматическое резервное копирование, отказоустойчивость, быстрое масштабирование и управление различными характеристиками из графического интерфейса. Ниже мы создадим экземпляр управляемой базы данных, создадим резервную копию базы данных на виртуальном сервере и восстановим ее в управляемую базу данных.

  Чтобы создать управляемую базу данных, перейдем в меню Базы данных и нажмем кнопку Создать кластер:

  

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

  

  Обращаем внимание на блок Резервные копии, в котором указаны частота резервного копирования, время и срок хранения выгрузок. Под капотом используется механизм wal-g, о котором мы писали выше.

  

  Автоматическое создание резервных копий отключить нельзя.

  Следующий шаг — создание пользователя, от имени которого мы позже будем обращаться к базе данных. Для этого перейдем на вкладку Пользователи и нажмем на кнопку Создать пользователя.

  

  После этого появится приглашение ввести имя пользователя и пароль. После ввода этих данных нажимаем Сохранить.

  

  Пользователь создан и отображается в списке пользователей.

  

  Теперь создадим базу данных. Для этого перейдем на вкладку Базы данных и нажмем на кнопку Создать базу данных.

  

  Заполняем необходимые поля и нажимаем кнопку Сохранить.

  

  База данных создана и отображается в списке баз данных.

  

  Теперь проверим возможность подключения. Для этого откроем консоль и вводим реквизиты:

  # psql "host=192.168.0.3 \ port=6432 \ user=rosella \ dbname=zabbix \ sslmode=disable"

  В консоли должно появиться приглашение к вводу SQL-запроса или других управляющих команд.

  

  Выполним резервное копирование при помощи команды pg_dump:

  # pg_dump zabbix > /tmp/zabbix.dump

  И следом резервное восстановление в созданную управляемую базу данных:

  # psql -h 192.168.0.3 -U rosella -d zabbix < /tmp/zabbix.dump

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

  Чтобы воспользоваться восстановлением из резервной копии, которая автоматически создается на платформе Selectel, необходимо нажать на символ с тремя точками. В открывшемся меню нужно нажать на опцию Восстановить. После этого появится модальное окно, в котором можно выбрать резервную копию, а также дату и время, на которое нужно восстановить базу данных. Это так называемый Point-in-Time Recovery из WAL-файлов.

  

  Услуга «Управляемые базы данных в облаке» позволяет перенести существующий кластер PostgreSQL на сервис управляемых баз данных бесшовно и без простоя, обратившись в техническую поддержку. Инженеры Selectel готовы помочь с переносом, а также проконсультировать по всем связанным с этим процессом вопросам.

  Заключение

  Мы рассмотрели возможности выполнения резервного копирования и показали отличия утилит pg_dump, pg_dumpall, pg_restore, pg_basebackup и wal-g. Вы увидели как можно создать управляемую базу данных, чтобы переложить часть административных задач на облачного провайдера.

  Узнать подробнее об управляемых базах данных можно в документации Selectel.

  Как настроить репликацию в PostgreSQL