Postgres pg_dump удаляет базу данных в другом порядке каждый раз

Ответ 1

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

Вы можете использовать опции "-a -d" для pg_dump, а затем "сортировать" вывод, но символы новой строки в данных сделают отсортированный вывод невозможен. Но для базового сравнения было ли все, что изменилось, было бы достаточно.

Ответ 2

Вот удобный script для предварительной обработки pg_dump вывода, чтобы сделать его более подходящим для различения и сохранения в управлении версиями:

https://github.com/akaihola/pgtricks

pg_dump_splitsort.py разделяет дамп на следующие файлы:

• 0000_prologue.sql: все до первой COPY
• 0001_<schema>.<table>.sql
  .
  .
  NNNN_<schema>.<table>.sql: данные для каждой таблицы, отсортированные по первому полю
• 9999_epilogue.sql: все после последнего COPY

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

$ cat *.sql | psql <database>

Я нашел, что хороший способ взглянуть на различия между дампами - использовать инструмент meld для всего каталога:

$ meld old-dump/ new-dump/

Сохранение дампа в управлении версиями также дает достойный обзор различий. Здесь, как настроить git использовать цвет в diffs:

# ~/.gitconfig
[color]
        diff = true
[color "diff"]
        frag = white blue bold
        meta = white green bold
        commit = white red bold

Примечание. Если вы создали/сбросили/переименовали таблицы, не забудьте удалить все файлы .sql перед последующей обработкой нового дампа.

Ответ 3

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

С другой стороны, данные сбрасываются в порядке диска. Обычно это то, что вы хотите, потому что вы хотите, чтобы дампы были быстрыми и не использовали сумасшедшие объемы ресурсов для сортировки. Что вы можете наблюдать, так это то, что когда вы "модифицируете БД", вы делаете UPDATE, который фактически удалит старое значение и добавит новое значение в конце. И это, разумеется, нарушит вашу стратегию дифференциации.

Инструмент, который может быть более подходящим для вашей цели, pg_comparator.

Ответ 4

По состоянию на май 2010 года существует patch to pg_dump, который может быть полезен всем, кто интересуется этим вопросом - он добавляет "--ordered" для этой утилиты:

Использование --ordered будет упорядочивать данные по первичный ключ или уникальный индекс, если один существует и используется "наименьший", упорядочение (то есть наименьшее число столбцы, требуемые для уникального порядка).

Обратите внимание, что --ordered может сокрушить ваш сервер базы данных, если вы попытаетесь заказать очень большие таблицы, поэтому используйте разумно.

Я не тестировал его, но, думаю, стоит попробовать.

Ответ 5

Нет ничего необычного в том, что PostgreSQL ведет себя недетерминированно - возможно, таймер вызвал процессы реорганизации или что-то подобное происходит в фоновом режиме. Далее я не знаю, как заставить pg_dump воспроизводить бит-идентичный вывод при последовательных прогонах.

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

Ответ 6

Если вас интересует только схема:

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

-s, --schema-only           dump only the schema, no data
-t, --table=TABLE           dump the named table(s) only

Чтобы сгенерировать список таблиц для подачи на приведенный выше, запросите information_schema.tables.

Ответ 7

Если производительность менее важна, чем порядок, который вы могли бы использовать:

COPY (select * from your_table order by some_col) to stdout
      with csv header delimiter ',';

См. COPY (9.5)