» . Снять (частичный) дамп проблемной таблицы — вот тут Вы и помучаетесь. :( а это как?

» . Снять (частичный) дамп проблемной таблицы — вот тут Вы и помучаетесь. :( а это как?

Путём использования COPY. А, кстати, schema dump этой таблицы снимите, сначала.

а pg_dumpall не работает для данных. зависает и все. пришлось переключиться на pg_dump

спасибо, попробуем.

Сорри, это появилось не в 10,. а в 9.6: https://paquier.xyz/postgresql-2/postgres-9-6-feature-highlight-remote-apply/ 1. Можно установить synchronous_commit=remote_apply тогда мастер будет жадть пока транзакция не применится на реплике: https://www.postgresql.org/docs/current/static/warm-standby.html#SYNCHRONOUS-REPLICATION 2. causal reads: дождаться пока конкретная тарнзакция не примемнится на реплике

Да, действительно в 9.6 (а то я всё гадал), спасибо! В 10-ке, кажется, появилась возможность ожидания ANY(x) nodes.

а pg_dumpall не работает для данных. зависает и все. пришлось переключиться на pg_dump

Да globals им снимите! Или Вам пользователи и per-database настройки уже не нужны? ;)

спасибо, попробуем.

Что Вы собираетесь пробовать? ;) Вы знаете, как снимать дампы с "битых" таблиц?

Что Вы собираетесь пробовать? ;) Вы знаете, как снимать дампы с "битых" таблиц?

мы COPY попробуем. вроде в этом был совет, не? )

мы COPY попробуем. вроде в этом был совет, не? )

Одним этим не обойдётся, естественно. Если таблица не читается, то и COPY её не прочитает. :( Вам придётся как-то пытаться "вытащить" то, что можно.

всегда мечтал научится читать бинарный формат постгре =)

всегда мечтал научится читать бинарный формат постгре =)

Я, например, знаю только простые способы, но в нетривиальных случаях ими много не вытащишь. :( Но тут могут быть люди, которые этим занимаются. :) Вам насколько важны эти данные? Если "очень", может, стоит даже к кому-нибудь обратиться? (Впрочем, если хотите, можно попробовать простые способы, чтобы понять, сколько так можно вытащить... хотя бы будете знать, насколько всё плохо).

про "важность" мы пока не знаем. нужные люди отсутствуют. мы пока просто пытаемся сделать все, что в наших силах)

про "важность" мы пока не знаем. нужные люди отсутствуют. мы пока просто пытаемся сделать все, что в наших силах)

Так у Вас эта битая база запущена? Если да, вот, например, один простой способ: В этой базе делаете так: CREATE FUNCTION chk(tid) RETURNS boolean LANGUAGE plpgsql AS $f$ DECLARE r text; BEGIN r := (SELECT d FROM <yourtable> AS d WHERE ctid=$1); return false; EXCEPTION WHEN others THEN return true; end; $f$; -- Потом делаете так: SELECT ctid, <id> FROM <yourtable> WHERE chk(ctid); Если всё ещё не очень плохо (не завершится падением psql или PostgreSQL), Вы увидите, сколько/какие у Вас битые записи.

спасибо, попробуем

спасибо, попробуем

Если сработает, кстати, то "нормальные" записи потом можно вытащить как-то так: \copy (SELECT * FROM <yourtable> WHERE NOT chk(ctid)) TO 'yourtable.dat' Если не помогает, можно ещё на zero_damaged_pages посмотреть (но тут уж Вы точно что-то потеряете).

не, оба варианта не прокатили.

не, оба варианта не прокатили.

А в каком смысле "не прокатили"? PostgreSQL падает? И какие "оба"? ;) Эта функция и zero_damaged_pages?

А в каком смысле "не прокатили"? PostgreSQL падает? И какие "оба"? ;) Эта функция и zero_damaged_pages?

запрос не отдает данные) виснет и все.

постгрес стартует в любом случае

запрос не отдает данные) виснет и все.

А Вы уверены, что он виснет? Таблица там большая (должна быть)? И в логах ничего? А хоть "SELECT * FROM <table> LIMIT 1;" выполняется?

limit 2 работал. Таблица вроде небольшая. Виснет даже count(*).

limit 2 работал. Таблица вроде небольшая. Виснет даже count(*).

А в логах что? В pg_stat_activity процесс висит в каком состоянии? И да, Вы же писали, что раньше были какие-то ошибки... больше нет, просто виснет?

А в логах что? В pg_stat_activity процесс висит в каком состоянии? И да, Вы же писали, что раньше были какие-то ошибки... больше нет, просто виснет?

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

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

А куда делись, были же? Вы что-то изменили? (Вообще, запрос этот не быстрый, конечно.) А так, надо было состояние и логи смотреть...

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

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

А каких признаков жизни Вы хотели от этого запроса? ;) Может, там мало битых записей, и они в конце... Т.е. можно было бы смотреть pg_stat_activity, логи, ps (или аналог) в системе, наконец...

увы. может нам скинут дамп поглазеть)

увы. может нам скинут дамп поглазеть)

Так уже бесполезно, разве нет? В смысле, этой таблицы там уже нет. ;(

дамп файлов сохранили

дамп файлов сохранили

А, файловый backup. Да, из него ещё можно попробовать вытащить.

Доброе утро. Какие есть подходы, чтобы написать вот такой запрос? (схематично записал заготовку для пагинации) select * from todos where date >= CURRENT_DATE and city_id=1 order by date asc; if not found then select * from todos where date < CURRENT_DATE and city_id=1 order by date desc; end if;

Есть вот такая штука. https://postgrespro.ru/docs/postgrespro/10/sql-waitlsn Не знаю гасколько легко ставится в стандартный Postgres

Есть вот такая штука. https://postgrespro.ru/docs/postgrespro/10/sql-waitlsn Не знаю гасколько легко ставится в стандартный Postgres

Спасибо. Выглядит как сложный подход на первый взгляд

Спасибо. Выглядит как сложный подход на первый взгляд

Это максимально внутренний подход. Поймать LSN транзакции на мастере и ждать пока она проиграется на слейве.

Доброе утро. Какие есть подходы, чтобы написать вот такой запрос? (схематично записал заготовку для пагинации) select * from todos where date >= CURRENT_DATE and city_id=1 order by date asc; if not found then select * from todos where date < CURRENT_DATE and city_id=1 order by date desc; end if;

очень неоптимально (делал на tsql, могут быть ошибки на пг диалекте) select * /* исключить flag если что */ from (select 1 as flag, * from todos where date >= CURRENT_DATE and city_id=1 union all select 2 as flag, * from todos where date < CURRENT_DATE and city_id=1 ) s where ( s.flag =2 and not exists (select 1 from todos where date >= CURRENT_DATE and city_id=1) or ( s.flag =1 and exists (select 1 from todos where date >= CURRENT_DATE and city_id=1) order by date desc

очень неоптимально (делал на tsql, могут быть ошибки на пг диалекте) select * /* исключить flag если что */ from (select 1 as flag, * from todos where date >= CURRENT_DATE and city_id=1 union all select 2 as flag, * from todos where date < CURRENT_DATE and city_id=1 ) s where ( s.flag =2 and not exists (select 1 from todos where date >= CURRENT_DATE and city_id=1) or ( s.flag =1 and exists (select 1 from todos where date >= CURRENT_DATE and city_id=1) order by date desc

попробую в таком направление пособирать запрос. В будущее должно быть asc, в прошлое desc

попробую в таком направление пособирать запрос. В будущее должно быть asc, в прошлое desc

имхо, если нет причин почему это должно быть одним запросом, лучше сделать двумя с if-ом.

имхо, если нет причин почему это должно быть одним запросом, лучше сделать двумя с if-ом.

там пагинация предполагается потом. Двумя с if-ом внутри запроса или на уровне сервера?

Доброе утро. Какие есть подходы, чтобы написать вот такой запрос? (схематично записал заготовку для пагинации) select * from todos where date >= CURRENT_DATE and city_id=1 order by date asc; if not found then select * from todos where date < CURRENT_DATE and city_id=1 order by date desc; end if;

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

там пагинация предполагается потом. Двумя с if-ом внутри запроса или на уровне сервера?

кстати да. для сортировки тоже можно сделать вспомогательное поле(поля), по которому можно провести по-разному сортировку: select 1 as flag, datediff(ss, 'somedata' , date) as sort_field, * from .. select 2 as flag, datediff(ss, date, 'somedata' ) as sort_field, * from разница между порядком аргументов для datediff( это фунция tsql разница в секундах между двумя даттаймами), сделает так что для 2го куска подзапроса эта разница секунд будет расти тоже asc для обратного порядка поля date . собственно order by sort_field asc

хм. Сейчас попробую

Вообщем оказалось что обычным union all все хорошо работает. Спасибо @olegmakarikhin ( select * from todos where date >= CURRENT_DATE and city_id=1 order by date asc ) union all ( select * from todos where date < CURRENT_DATE and city_id=1 order by date desc )

Игорь Ишин будет жить. Поприветствуем!

аааа, нужна была своеобразная сортировка, я думал фильтрация "если есть записи первого типа, тогда показывать только их, если второго - то только их но с другой сортировкой)

Привет! Подскажите, плиз, как wal buffers помогает при записи wal логов? на момент выполнения commit из буффера все должно ведь перекинуться в wal segments? или тут основная оптимизация в том, что во время транзакции все быстро пишется в оперативку, а потом одним куском сразу на диск?

Лемма 1: данные на диск всё равно пишутся блоками. Если вам надо записать 1 байт, то файловой системе надо будет сначала прочитать блок с диска, потом скопировать туда ваш байт и записть блок на диск. Чтобы этого не происходило при записи в базу (или в WAL), Постгрес всегда пишет страницами. Лемма 2: при коммите данные должны оказаться в WAL-е до того, как мы вернём ответ клиенту. Соотвественно Постгрес буфферизует запист в WAL и пишет странички целиком. Если танзакций мало, то одна и та же страница WAL-а будет писаться на одно место несколько раз. Если же паралельных странзакцияй много, то Посгрес умеет одним write-ом записать на диск данные нескольких тарнзакций. Это позволяет резко повысить TPS-ы, потому как вообще-то среднее время записи случайной страницы на обычный HDD - порядка 10msec. Поэтому чем больше буферов, тем больше у Посгреса возможность комбинирования записей паралельных траназакций и тем больше будет скорость. Но это если есть паралельные тарнзакции. Если вы выполняете одноклиентный pgbench, то разницы в скорости при увеличении wal buffers не будет

Вообщем оказалось что обычным union all все хорошо работает. Спасибо @olegmakarikhin ( select * from todos where date >= CURRENT_DATE and city_id=1 order by date asc ) union all ( select * from todos where date < CURRENT_DATE and city_id=1 order by date desc )

а зачем вы в изначальном посте писали if not found? сбили с толку же

@knizhnik спасибо! данные нескольких транзакций он записывает, если включен commit_delay, на сколько я понимаю, т.е. по умолчанию такого не происходит

@knizhnik спасибо! данные нескольких транзакций он записывает, если включен commit_delay, на сколько я понимаю, т.е. по умолчанию такого не происходит

Представте что у вас транзакция, которая затрагивает много данных, гораздо больше чем объём WAL-buffers. И параллельно идут много мелких транзакций.

В WAL "параллельно" пишутся все текущие транзакции.

@SergeyPpro в таком случае мелкие будут прилипать к блокам большой? мелкие с мелкими все равно будут независимо скидываться? это какая-то внутренняя оптимизация pg?

@SergeyPpro в таком случае мелкие будут прилипать к блокам большой? мелкие с мелкими все равно будут независимо скидываться? это какая-то внутренняя оптимизация pg?

В WAL-buffers будут попадать мелкие вперемешку с большой или если угодно вперемешку мелкие. Скидываться на диск они обязаны по commit'у. Или WAL-writer будет превентивно сбрасывать WAL-buffers на диск. Вот здесь в лекции 8 описание postgrespro.ru/education/courses/DBA2

Спасибо!

А что быстрее будет: выборка и сортировка соединение с выборка и сортировка. Или сначала все соединили и потом отсортировали уже соединение? По идее оптимизатор ведь одинаково развернет это?

Надо смотреть, как данные в диске лежат

А учитывается планировщиком статистика по доступу к данным еще?

Время доступа имею ввиду

И еще такой момент : есть ли какие то элементы спекулятивного выполнения запроса на основе прогнозирования в постгрес или делались ли экспериментальные исследования в этом направлении?

А учитывается планировщиком статистика по доступу к данным еще?

В смысле? Текущая нагрузка — нет (это задача DBA задать адекватные costs). А независимые от неё эффекты — да (например, ожидаемый эффект кеширования при сканировании индекса, и т.п.).

И еще такой момент : есть ли какие то элементы спекулятивного выполнения запроса на основе прогнозирования в постгрес или делались ли экспериментальные исследования в этом направлении?

Опять-таки, что именно имеется в виду?

Опять-таки, что именно имеется в виду?

Я имею ввиду параллельно не занятым потоком выполнять запрос на основе прогноза и если последующий запрос предсказан верно то применять его результат сразу. Ну естественно учитывая acid

Как инструкции в процессоре

Я имею ввиду параллельно не занятым потоком выполнять запрос на основе прогноза и если последующий запрос предсказан верно то применять его результат сразу. Ну естественно учитывая acid

У вас или есть запрос, или нет

Нет "возможного будущего запроса"

Как инструкции в процессоре

Естественно, нет. Как Вам такое вообще в голову приходит!? ;) И даже исследований с подобными идеями я никогда не видел.

А учитывается планировщиком статистика по доступу к данным еще?

У посгресового оптимизатора есть много параметров, описывающий стоимость выполнения операции (случайный и последовательный доступ к диску, CPU, эффективный кэш,...) Все этим параметры приходится задавать ручками. Никакого объективного способа вычислить например cpu_tuple_cost нет. Было ряд исследований как можно использовть нервные сетки для оптимального подбора значений параметров под конкретную систему и запросы. Но чаще всего проколы оптимизатора связаны с неправильным предсказанием селективности. И тут тоже может помочь AI (https://pgconf.ru/2017/93634).

И еще такой момент : есть ли какие то элементы спекулятивного выполнения запроса на основе прогнозирования в постгрес или делались ли экспериментальные исследования в этом направлении?

Не совсем понятно, как можно "предсказать" запрос. Если это один из выполнявшихся ранее запросов, то вместо "спекулятивного выполнения" лучше попойдёт просто кэширование результатов выполнения запроса. Оно было в mySQL пока от него не отказались. Уж больно геморройно гарантировать актуальность прокэшировнных данных. И ситуаций, когда кэш реалтно полезен, вилимо не та уж много.

Естественно, нет. Как Вам такое вообще в голову приходит!? ;) И даже исследований с подобными идеями я никогда не видел.

Но инструкции в процессоре - еще более абстрактная и непредсказуемая вещь, но она достаточно неплохо предсказывается.

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

Но инструкции в процессоре - еще более абстрактная и непредсказуемая вещь, но она достаточно неплохо предсказывается.

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

Я об этом начал думать насмотревшись запросов в 1С.

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

Не совсем понятно, как можно "предсказать" запрос. Если это один из выполнявшихся ранее запросов, то вместо "спекулятивного выполнения" лучше попойдёт просто кэширование результатов выполнения запроса. Оно было в mySQL пока от него не отказались. Уж больно геморройно гарантировать актуальность прокэшировнных данных. И ситуаций, когда кэш реалтно полезен, вилимо не та уж много.

Да. Кеширование, но на запросе, которого еще нет. Рассмотреть запрос с точки зрения переменных и спрогнозировать будущее изменение переменных. Чаще это будет либо условие where, либо в комбинации с join

Если программа не меняется на прикладном уровне, то это будет конечное количество запросов с изменяемым значением переменных

И если это простой запрос, а при этом куча ядер и потоков простаивает, то можно опускать условие where и поднимать в кеш всевозможные значения результата выборки, а затем делать быстрый запрос на этих данных, проверив их непротиворечивость причинно-следственности

Что-то похожее на алгоритмы мультиклет процессоров

коллеги, у меня в подвале подземный стук есть приложенька (deis-controller), которую кубернетес раз в десять секунд проверяет на вшивость приложенька ходит к постгресу (9.4, don't ask), делает там SELECT 0 и в случае успеха говорит кубернетесу, что пока ещё жива и всё работало довольно долго (где-то с год), но вот сегодня всё сломалось, потому что у постгреса закончились коннекты картина примерно такая: # SELECT query from pg_stat_activity where state = 'idle'; │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ ... и вот этих ребят там max_connections - 1 вопрос: что вдруг начало мешать им умереть, и куда вообще бежать?

И если это простой запрос, а при этом куча ядер и потоков простаивает, то можно опускать условие where и поднимать в кеш всевозможные значения результата выборки, а затем делать быстрый запрос на этих данных, проверив их непротиворечивость причинно-следственности

Обычное кэширование страниц, почти наверняка, даст примерно тот же практический результат. Так зачем все эти сложности? ;)

Обычное кэширование страниц, почти наверняка, даст примерно тот же практический результат. Так зачем все эти сложности? ;)

Не знаю. Я пока только интересовался. Может кто уже на эту тему думал, чтобы думать было проще :)))

коллеги, у меня в подвале подземный стук есть приложенька (deis-controller), которую кубернетес раз в десять секунд проверяет на вшивость приложенька ходит к постгресу (9.4, don't ask), делает там SELECT 0 и в случае успеха говорит кубернетесу, что пока ещё жива и всё работало довольно долго (где-то с год), но вот сегодня всё сломалось, потому что у постгреса закончились коннекты картина примерно такая: # SELECT query from pg_stat_activity where state = 'idle'; │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ │ SELECT 0 $ ... и вот этих ребят там max_connections - 1 вопрос: что вдруг начало мешать им умереть, и куда вообще бежать?

Это без pooler и т.п.? Если да, то приложение само должно соединения закрывать, PostgreSQL тут ни при чём.

вот вся магия ливнессчека: class ReadinessCheckView(View): """ Simple readiness check view to determine DB connection / query. """ def get(self, request): try: import django.db with django.db.connection.cursor() as c: c.execute("SELECT 0") except django.db.Error as e: raise ServiceUnavailable("Database health check failed") from e return HttpResponse("OK") head = get

Это без pooler и т.п.? Если да, то приложение само должно соединения закрывать, PostgreSQL тут ни при чём.

да, всё напрямую

джанга почему-то вдруг передумала закрывать коннекты

джанга почему-то вдруг передумала закрывать коннекты

Вряд ли это постгрес сам по себе.

Я так понимаю коннект висит, пока результат не придет, а результат не приходит, потому что очередь висит

Ну то есть скорее всего перезапуск субд вернет все на место. Или оно после этого снова воспроизводится?

Я так понимаю коннект висит, пока результат не придет, а результат не приходит, потому что очередь висит

результат приходит, потому что джанга рапортует в кубернетес, что всё ок

Ну то есть скорее всего перезапуск субд вернет все на место. Или оно после этого снова воспроизводится?

перезапустил и джангу и постгрес, без всякого результата