Вставка первичного ключа начинается с нуля при отказе логической репликации в PostgreSQL10

Это известное ограничение логической репликации в PostgreSQL 10.

Вот отрывок из документации по адресу https://www.postgresql.org/docs/10/logical-replication-restrictions.html

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

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

У нас есть две функции, которые сбрасывают за нас последовательности. Возможно, они не идеальны, но они работают в нашей ситуации.

CREATE OR REPLACE FUNCTION public.update_sequence(
    IN tabschema text,
    IN tabname text,
    OUT tschema text,
    OUT tname text,
    OUT pkname text,
    OUT seqname text,
    OUT startval bigint,
    OUT minval bigint,
    OUT maxval bigint,
    OUT incr bigint,
    OUT maxseq bigint,
    OUT lastval bigint,
    OUT newseq bigint,
    OUT prevcalled boolean)
  RETURNS record AS
$BODY$
DECLARE
  seq_offset CONSTANT bigint := 0;
  seq_range CONSTANT bigint := 9999999999999;
BEGIN
  tschema := tabschema;
  tname := tabname;

  -- protect against concurrent inserts while you update the counter
  EXECUTE format('LOCK TABLE %I.%I IN EXCLUSIVE MODE', tabschema, tabname);

  SELECT column_name, table_name||'_'||column_name||'_seq' FROM information_schema.columns WHERE column_default IS NOT NULL AND data_type = 'bigint' AND column_default ilike 'nextval(%_seq''::regclass)' AND table_schema = tabschema AND table_name = tabname INTO pkname, seqname;
  SELECT start_value, min_value, max_value, increment_by FROM pg_sequences WHERE schemaname = tabschema AND sequencename = seqname INTO startval, minval, maxval, incr;
  EXECUTE format('SELECT last_value, is_called FROM %I.%I', tabschema, seqname) INTO lastval, prevcalled;
  EXECUTE format('SELECT max(%I) FROM %I.%I WHERE %I between $1 AND $2', pkname, tabschema, tabname, pkname) USING seq_offset+1, seq_offset+seq_range INTO maxseq;
  newseq := CASE WHEN maxseq IS NULL THEN seq_offset+incr ELSE coalesce(greatest(maxseq+incr, CASE WHEN prevcalled THEN lastval+incr ELSE lastval END), seq_offset+incr) END;

  EXECUTE format('ALTER SEQUENCE %I.%I MINVALUE %s START %s RESTART %s MAXVALUE %s;', tabschema, seqname, seq_offset+1, seq_offset+1, newseq, seq_offset+seq_range);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


CREATE OR REPLACE FUNCTION public.update_all_sequences()
  RETURNS TABLE(tabschema text, tabname text, pkname text, seqname text, startval bigint, minval bigint, maxval bigint, incr bigint, maxseq bigint, lastval bigint, newseq bigint, prevcalled boolean) AS
$BODY$
BEGIN
  RETURN QUERY WITH table_list (tschema, tname) AS (
    SELECT n.nspname, c.relname FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ('information_schema', 'pg_catalog') ORDER BY n.nspname, c.relname
  )
  SELECT a.* FROM table_list t JOIN update_sequence(tschema, tname) a on t.tschema = a.tschema and t.tname = a.tname;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Тогда все, что вам нужно сделать в рамках аварийного переключения, - это непосредственно перед переключением виртуального IP-адреса на новый мастер, это запустить update_all_sequences функция, чтобы убедиться, что последовательности находятся в правильном месте (всегда есть шанс, что некоторые последовательности будут пропущены, если ваш increment_by значение в последовательности> 1).

Значения seq_offset и seq_range присутствуют, если вы хотите начать с чего-то другого, кроме 1, что мы и делаем, поскольку у нас географически распределенная система, а базы данных из других центров обработки данных начинаются с других значений.

Мне нравится решение Малькольма как чисто postgres-вариант. В качестве альтернативы, вот основные функции, которые я перебирал в своих таблицах в Python, если вы взламываете это во что-то еще. Те, кто отказывается от подписки, предполагают, что ваш первичный сервер полностью мертв, поэтому вторичный не начинает извлекать из него, если / когда он просыпается.

ALTER SUBSCRIPTION [sub] DISABLE;
ALTER SUBSCRIPTION [sub] SET (slot_name = NONE);
COMMIT;
DROP SUBSCRIPTION [sub];

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

SELECT table_name,column_name FROM information_schema.columns WHERE column_default like('nextval%');

Затем я перебирал это, сначала собирая последний val:

SELECT MAX([column]) FROM [table];

Получение соответствующего серийного номера:

SELECT pg_get_serial_sequence('[table]','[column]');

И наконец,

SELECT setval('[sequence]','[maxvalue]');
COMMIT;

Мой путь уродливый, но он сработал.