PostgreSQL: Уникальные ключи для распределенной базы. Практика / Хабр |
您所在的位置：网站首页 › bigint50 › PostgreSQL: Уникальные ключи для распределенной базы. Практика / Хабр |
|
По следам статьи «Уникальный ключ в условиях распределенной БД». У нас есть база которую мы хотим разделить. В идеальном случае хочется сделать master-master. Один из самых сложных моментов, это обеспечение уникальности ключей на всех серверах. И хорошо если база изначально проектировалась с учетом масштабирования… Опять же, это что-то из области идеала, который встречается, скажем так — не часто. Итак у нас есть база которую нужно подготовить к синхронизации master-master — сделаем все ключи в нашей базе уникальными в пределах проекта. В упомянутой статье рассматривались несколько вариантов, но мы остановимся на одном предложенным Instagram Шаг 1 — Перевод всех ключей в bigint Здесь подразумевается, что все наши primary ключи называются id, и соответственно поля которые ссылаются на эти ключи названы подобным образом: order_id, client_id, table_id…Создадим функцию которая переводит поле integer в bigint DROP FUNCTION IF EXISTS "field_int2big" (field text, tablename text, table_schema text); CREATE OR REPLACE FUNCTION "field_int2big" (field text, tablename text, table_schema text) RETURNS bigint AS $body$ DECLARE BEGIN EXECUTE 'ALTER TABLE '|| table_schema || '."'|| tablename || '" ALTER COLUMN "'|| field || '" TYPE bigint;' ; return 1; END; $body$ LANGUAGE 'plpgsql';Дальше выбираем все integer поля и конвертирум их: select *, field_int2big(column_name, table_name, table_schema) from (select table_catalog, table_schema, table_name, column_name, data_type from information_schema.columns where table_schema in ('public', 'myscheme') and data_type in ('integer', 'oid') and (position('id' in column_name)>0 OR column_name in ('key', 'myfield')) order by table_catalog, table_schema, table_name, column_name limit 10 offset 0) c Несколько вещей на которые нужно обратить внимание: Вы можете/должны добавить свои схемы: table_schema in ('public', 'myscheme') Также можете добавлять свои поля именованные не «стандартно»: column_name in ('key', 'myfield') Обратите внимание на limit 10 для больших баз таблиц нужно его уменьшать вплоть до 1 — смена типа требует времени и не малого Запрос нужно запускать несколько раз, каждый раз он будет находить оставшиеся не переведенные поляШаг 2 — Перевод функциональных индексов в которых есть прямое указание типа Вообще если этого не сделать — принесет проблем в будущем, их крайне трудно обнаружить: выдает ошибку которую не видно в исполняемом запросе. DROP FUNCTION IF EXISTS "index_int2big" (idx text, declare_idx text); CREATE OR REPLACE FUNCTION "index_int2big" (idx text, declare_idx text) RETURNS text AS $body$ DECLARE new_idx text; BEGIN EXECUTE 'DROP INDEX IF EXISTS ' || idx; SELECT replace(declare_idx, 'integer', 'bigint') INTO new_idx; EXECUTE new_idx ; return new_idx; END; $body$ LANGUAGE 'plpgsql'; select *, index_int2big(indname, inddef) from (SELECT n.nspname as table_schema, c.relname as table_name, c2.relname AS indname, i.indisprimary, i.indisunique, i.indisclustered, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS inddef FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i, pg_namespace n WHERE n.oid=c.relnamespace and c.oid = i.indrelid AND i.indexrelid = c2.oid and n.nspname in ('bucardo', 'public') and position('integer' in pg_catalog.pg_get_indexdef(i.indexrelid, 0, true))>0 limit 10 offset 0) c Вы можете заметить что я проверяю в схеме bucardo. Если у вас уже работает синхронизация на основе этой технологии, тогда этот шаг стает крайне важным. Также смотрите замечание с прошлого шага. Шаг 3 — Создание новых последовательностей для всех ключевых полей В предложенном Instagram варианте используется уникальное число для каждой схемы/сервера. т.е. необходимо иметь в каждой схеме свою функцию с своим уникальным номером. Я немного изменил функцию и генерирую уникальный ключ по IP сервера. CREATE OR REPLACE FUNCTION inet2num(inet) RETURNS numeric AS $$ DECLARE a text[] := string_to_array(host($1), '.'); BEGIN RETURN a[1]::numeric * 16777216 + a[2]::numeric * 65536 + a[3]::numeric * 256 + a[4]::numeric; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; DROP FUNCTION IF EXISTS next_id(tbl text, tableschema text); CREATE OR REPLACE FUNCTION next_id(tbl text, tableschema text = 'public') returns bigint AS $$ DECLARE our_epoch bigint := 1314220021721; seq_id bigint; now_millis bigint; shard_id bigint; result bigint; BEGIN SELECT nextval(tableschema||'."' || tbl || '_id_seq"') % 1024 INTO seq_id; /* select substring(regexp_replace(md5(current_database()||inet_server_addr()||version()), '[^\\\d]+', '', 'g')::text from 1 for 6)::int into shard_id;*/ SELECT inet2num(inet_server_addr()) into shard_id; SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis; result := (now_millis - our_epoch) |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |