Более опытные в вопросах postgresql коллеги, прошу вашей помощи - сам только осваиваю азы администрирования этой СУБД.

Сам я не администратор, а разработчик и эти основы нужные мне, чтобы добавить работу с postgresql в свою программу администрирования баз 1С.

Текущее окружение: Centos 7, PostgreSQL 9.6.9 (версия с патчами от 1С с сайта postgrespro.ru). Но версия ОС в рамках этой задачи не так важна, проблема повторяется и на Windows.

Сейчас передо мной стоят следующие задачи:

1. Написать скрипт выгрузки дампа базы в формате sql через pg_dump (архивация)
2. Написать скрипт загрузки этого дампа в эту же самую базы, выполнив этот скрипт через psql (восстановление базы из архива)

При этом вариант удаления и создания чистой базы перед восстановлением не подходит, так как эта операция потребовала бы остановки кластера 1с (drop table не отрабатывает при соединениях с базой). И вообще сам вариант удаления и повторного создания базы для её восстановления кажется мне не совсем подходящим.

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

Скрипт архивации получился такой:
pg_dump.exe --quote-all-identifiers --format=plain --clean --dbname=postgresql://"USER":"PASSWORD"@"HOST:PORT"/"BASE1С_NAME" --file="backup.sql"

Скрипт восстановления такой:
psql.exe --single-transaction --echo-errors --quiet --set="ON_ERROR_STOP=on" --file="backup.sql" --dbname=postgresql://"USER":"PASSWORD"@"HOST:PORT"/"BASE1С_NAME"

Ключик --clean, который мы передали на вход pg_dump как раз и отвечает за генерацию SQL-кода в дампе, который выполняет полную очистку базы перед загрузкой в неё архивной копии.

И проблема возникает именно в этой части sql-дампа. Точная ошибка гласит: тип "public.mvarchar" не существует.

Знаю, что этот тип как раз и был добавлен патчами от 1С.

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

Судя по всему нужно делать постобработку (в части mvarchar) выгруженного дампа перед тем как отдавать его на выполнение pssql.

>>drop table не отрабатывает при соединениях с базой
То есть, Вы хотите при подключенных к базе пользователях произвести восстановление базы из резервной копии?

(1) нет, пользователи будут отключены, конечно

но при работающем кластере 1С drop table всё равно не срабатывает

(1) Нормальный скуль сам дропает полльвателей )))

(3) mssql например не дропает

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

уже затем запускается восстановление через СУБД

(5) а на стороне субд убиваются соединения?

(6) всё это на стороне кластера 1С делается

(6) извините, не увидел "а"

На стороне СУБД ничего не убивается. Задача в том, чтобы избежать уничтожения и повторного создания базы. Чтобы грузить архивную копию в уже имеющуюся, просто очистив её перед загрузкой и всё это в одной транзакции, чтобы избежать полной потери базы при ошибках.

Описанный мною способ соотв. документации postgresql, но спотыкается на тип mvarchar от 1С.

Идти путём удаления, создания и загрузки можно. Но ИМХО это неправильный путь. Ведь его не сделать в рамках одной транзакции. Поэтому если на одном из этапов возникнет ошибка, то на выходе не останется даже той базы, что была до восстановления. А это плохой сценарий для пользователя.

(3) А потом что? Сеансы пользователей остаются в кэше сеансовых данных сервера приложений и админ этого хозяйства в панике пишет на Мисту с Инфостартом "Ошибка формата потока!!!Что делать???", ну, и другие междометия и восклицания.

(9) Надо удалить ИБ в консоли администрирования 1С(или через ras/rac), затем createdb такое_же_имя_базы, затем pg_restore и создать ИБ на сервере приложений, опять же через rac. все довольно неплохо автоматизируется скриптами на Linux сервере и останавливать ничего не надо.

(11) и так тоже могу сделать, но как же "Ведь его не сделать в рамках одной транзакции. Поэтому если на одном из этапов возникнет ошибка, то на выходе не останется даже той базы, что была до восстановления. А это плохой сценарий для пользователя."

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

Возможно это кому-то пригодится, получилось примерно так:

1. Делаем бэкап:
    pg_dump.exe --quote-all-identifiers --format=plain --dbname=postgresql://"USER":"PASSWORD"@"HOST:PORT"/"BASE1C" --file="BACKUP.sql"

2. Создаём новую базу с временным именем (пусть сейчас это будет temp)
    createdb.exe --encoding=utf8 --template=template1 --username="USER" --host="HOST" --port=PORT temp

3. Далее программно блокируем и выгоняем всех пользователей и фоновые. задания из базы через кластер 1с.

4. Далее программно через кластер очищаем свойство "база данных" у нашей базы. Это нужно, чтобы кластер отпустил базу в СУБД.

5. Далее переименовываем базу BASE1C в BASE1C_TEMP:

    psql.exe --single-transaction --echo-errors --quiet --set="ON_ERROR_STOP=on" --dbname=postgresql://"USER":"PASSWORD"@"HOST:PORT"/"template1" --command="ALTER DATABASE BASE1C RENAME TO BASE1C_TEMP;"

6. Далее переименовываем нашу temp в BASE1C:

    psql.exe --single-transaction --echo-errors --quiet --set="ON_ERROR_STOP=on" --dbname=postgresql://"USER":"PASSWORD"@"HOST:PORT"/"template1" --command="ALTER DATABASE temp RENAME TO BASE1C;"
    
7. Далее снова программно прописываем в свойства базы в кластере имя BASE1C

8. Наконец, удаляем базу BASE1C_TEMP:
    dropdb.exe --username="USER" --host="HOST" --port=PORT BASE1C_TEMP

9. Пускаем всех обратно в базу.

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

Забыл добавить шаг 2.1:

2.1. Загружаем в temp наш архив:
psql.exe --single-transaction --echo-errors --quiet --set="ON_ERROR_STOP=on" --file="BACKUP.sql" --dbname=postgresql://"USER":"PASSWORD"@"HOST:PORT"/"temp"

Постгрес, как мне кажется, самый отвратительный sql в плане администрирования из массовых. Лучше бы 1с сделала ставку на firebird, он на порядки удобнее.

(15) а как у него с большими базами? Я помню, что какие-то там вопросы были и по производительности, и по надежности, когда речь начинала идти даже около десятка гигов

(16) пока база не очень большая - я только "за", он мне очень нравится

(16) У него есть свои нюансы, которые надо учитывать, например однонаправленные индексы. То есть, вместо одного индекса нужно создавать два - вверх и вниз, потому что при выборке в обратном индексу порядке индекс не задействуется.

(12)Можно просто удалить ИБ, не удаляя саму базу в СУБД.
>>тип "public.mvarchar" не существует
Если это возникает при "drop что-нибудь", то на эти ошибки можно не обращать внимания(как указано в описании параметра --clean) или добавить --if-exists.

Сделал на тестовой базе
pg_dump -clean test > test.sql
Потом
psql test <test.sql в эту же базу(без удаления самой базы и создания ее заново).
Ошибок при этом не возникало, база получается работоспособной.

(19) --if-exists пробовал, на некоторых базах ошибок не возникало, а на некоторых были проблемы. Именно после этого я и бросил попытку очистки базы перед загрузкой.

"то на эти ошибки можно не обращать внимания". Да, загрузка проходит несмотря на предупреждения, но я пишу универсальный инструмент и не могу не обращать внимание на какие-то ошибки и предупреждения. Ведь код возврата в этом случае будет 1 (ошибка). И игнорируя этот класс ошибок я буду пропускать и новые ошибки, не связанные с mvchar.

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

(19) "Можно просто удалить ИБ, не удаляя саму базу в СУБД."
Вот эту фразу я не совсем понял, прошу пояснить. Какой смысл в удалении ИБ из кластера?

(21)>>drop table не отрабатывает при соединениях с базой
Не будет связи между сервером приложений и базой в СУБД и drop table отработает. Это если восстанавливать в существующую базу.
Впрочем, при выполненных условиях из (5) и принятии Вами(хоть и с сожалением) варианта восстановления резервной копии в чистую с точки зрения СУБД базу, смысла, скорее всего, нет.

(22) "смысла, скорее всего, нет."

Да, а то получается, что мы удаляем базу в кластере, чтобы разорвать связь с СУБД и затем всё равно удалить базу в СУБД :)

Я в (13) описал как можно удалять базу в СУБД не удаляя базу в кластере. Просто на время удаления и пересоздания базы в СУБД, надо очищать поле "база данных" в свойствах базы в кластере.

Вышел тестовый обновлятор от 9 августа: http://helpme1c.ru/testirovanie-novyx-versij-obnovlyatora

09.08.2016 Обновлятор научился создавать архивы средствами СУБД для серверных баз 1с на PostgreSQL: https://helpme1c.ru/nastrojka-sql-arxivov-ms-sql-server-i-postgrgesql-v-obnovlyatore Возможность восстановления архивов PostgreSQL прямо из обновлятора появится в ближайшей тестовой версии. Сейчас же созданные архивы можно восстанавливать выполнив их напрямую через psql в чистой базе, созданной не из 1с.

(0) pg_dump это НЕ резервная копия. Точно так же, как dt это не резервная копия. Это выгрузка. Пожалуйста, запомни это.

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

(25) Да, в части терминологии согласен с вами.

На выходе из pg_dump получается файл в формате sql, выполнив который в чистой базе воссоздаётся полная копия исходной базы. Но по сути это, конечно, та же выгрузка, только на уровне СУБД.

(25) При всём при этом под термином бэкап для баз на Postgres во всём мире подразумевают именно реализованную мною выгрузку через pg_dmp.

Откройте тот же PgAdmin (https://www.pgadmin.org/), выполните операцию Backup и увидите, что под этой операцией скрыта та самая выгрузка базы через pg_admin.

... тьфу, через pg_dump :)

Я только сейчас понял, что написал новость о выходе обновляторе не в его ветке :)

Прошу прощение у администраторов форума, сейчас опубликую новость в теме обновлятора.

(27) Да, PgAdmin  тоже плодит невежество. Это потому, что в оф. документации дамп тоже предлагается как один из методов бэкапа.

Тем не менее, это не бэкап. Бэкапом можно назвать только метод, который воссоздает копию БД, *идентичную* исходной. идентичную в смысле побайтово и побитово. А в случае с дампом это не так.

Ага, я сейчас тоже полез в официальную документацию Postgres (https://www.postgresql.org/docs/9.6/static/backup.html) и вижу, что там SQL Dump описывается как один из видов бэкапа.

Я горячо поддерживаю непрерывную архивацию или архивации на уровне файловой системы (обновлятор кстати с недавнего времени поддерживает теневые копии, поэтому тоже умеет это), но я оставлю в обновляторе термин бэкап по следующим причинам:
- трудно спорить с тем, что такой термин уже сложился в мире и является общепринятым
- относительно той же выгрузки в dt - это вполне себе бэкап (так как делается уровнем ниже)
- обновлятор универсальный инструмент для очень широкого круга пользователей с достаточно низким порогом вхождения, а профессионал и сам понимает что к чему

В любом случае спасибо за ваши пояснения, они были полезными для меня.

(25) [pg_dump это НЕ резервная копия. Точно так же, как dt это не резервная копия. Это выгрузка. Пожалуйста, запомни это.]

Так а как тогда сделать нормальную резервную копию?
Я начал волноваться по поводу своих бэкапов...

(0) зачем автоматизировать восстановление, если это операция, потребность в которой ИСКЛЮЧИТЕЛЬНАЯ, и возникать вообще-то не должна. От слова совсем.

(33) Согласен и эта опция опциональная и выключена по умолчанию для серверных баз.

Но в некоторых случаях она действительно нужна: мой инструмент позволяет выполнять автоматическое обновление баз. И многие компании (например, хостеры 1с) просят, чтобы при неудачном обновлении выполнялся откат к первоначальному состоянию базы.

Иначе:
1. По расписанию базу обновили криво или обновили, но обработчики обновления выполниться не смогли.
2. И проще откатиться, чтобы сначала исправить что-то в базе и затем повторить обновление.
3. Но клиент после неудачного обновления уже успевает забить в базу данные и исправить ситуацию, теперь, на порядок сложнее.

Это лишь один из примеров.

(32) Рассмотрите для себя все виды бэкапов, которые предлагает Postgres (каждый со своими минусами и плюсами): https://www.postgresql.org/docs/9.6/static/backup.html

И выберите необходимую комбинацию, подходящую для своего случая.

То же самое на русском: https://postgrespro.ru/docs/postgresql/9.6/backup

(0)
SET PGBIN=C:\Program Files\PostgreSQL\9.4.2-1.1C\bin\
SET PGDATABASE=*******
SET PGHOST=127.0.0.1
SET PGPORT=5432
SET PGUSER=*****
SET PGPASSWORD=*****
"C:\Program Files\PostgreSQL\9.4.2-1.1C\bin\pg_dump.exe"  -i -b -v -E UTF-8 -f E:\PG_Backup\Dump\dump_base_****.sql


ручками грохаешь базу
SET PGBIN=C:\Program Files\PostgreSQL\9.4.2-1.1C\bin\
SET PGDATABASE=*******
SET PGHOST=127.0.0.1
SET PGPORT=5432
SET PGUSER=*****
SET PGPASSWORD=*****
createdb
psql.exe -f E:\dump_base_*****.sql

(36) если ты в курсе, то зачем (0)? Заходил-бы сразу с козырей.

(37) спасибо за пример скрипта

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

Но из-за специфики патчей от 1С, сделать этого так и не удалось. Хотя это всё возможно на не 1С базах для Postgres.

В итоге пришёл к схеме, описанной в (13)

Или вопрос был в том, почему я вообще решил добавлять возможность выгрузки дампа через pg_dump?

Это стандартная возможность у всех утилит по администрированию баз на postgres.

И с точки зрения создания страховочной копии перед опасной операцией с базой вполне подходит для моего инструмента.

Естественно это не исключает (и так часто всего и бывает) наличие основной схемы резервного копирования (WAL или на уровне файловой системы).

WAL скорее всего тоже появится в обновляторе (наравне с инкрементальными копиями для MS SQL), но это уже следующий шаг.

Бэкапить же на уровне файловой системы не всегда удобно (если надо отдельную базу, например) и возможно. Много ограничений, чтобы это полностью автоматизировать. Особенно, учитывая что мой инструмент only Windows, а субд чаще всего на другой ОС.

Если же postgres на Windows - можно задействовать возможность теневого копирования в обновляторе.

(33) Ты сейчас спросил примерно "зачем нужна скорая помошь?"

а как pg_dump относиться к ситуациям когда в базу кто-то пишет?

относится*

(42) pg_dump — это программа для создания резервных копий базы данных PostgreSQL. Она создаёт целостные копии, даже если база параллельно используется. Программа pg_dump не препятствует доступу других пользователей к базе данных (ни для чтения, ни для записи).

https://postgrespro.ru/docs/postgresql/9.6/app-pgdump

(44) спасибо.

Еще бы понять на уровне БД как будет работать.
Допустим пользователь пишет в базу набор записей регистра в 2Гб, и этот момент срабатывает pg_dump?

(45) везде пишут, что копия будет целостная

Рассуждения и объяснения как это работает, например, здесь: https://dba.stackexchange.com/questions/13742/does-running-pg-dump-on-live-db-produce-consistent-backups

(45) вообще то постгре версионник

(47) это как понять?

(48) https://ru.wikipedia.org/wiki/MVCC

Насколько я понимаю, именно за счёт указанного выше механизма pg_dump вычитывает определенную версию всех таблиц базы данных.