Dblink postgresql что это
dblink — выполняет запрос в удалённой базе данных
Синтаксис
dblink(text connname, text sql [, bool fail_on_error]) returns setof record dblink(text connstr, text sql [, bool fail_on_error]) returns setof record dblink(text sql [, bool fail_on_error]) returns setof record
Описание
dblink выполняет запрос (обычно SELECT , но это может быть и любой другой оператор SQL, возвращающий строки) в удалённой базе данных.
Когда этой функции передаются два аргумента типа text , первый сначала рассматривается как имя постоянного подключения; если такое подключение находится, команда выполняется для него. Если не находится, первый аргумент воспринимается как строка подключения, как для функции dblink_connect , и заданное подключение устанавливается только на время выполнения этой команды.
Аргументы
Имя используемого подключения; опустите этот параметр, чтобы использовать безымянное подключение. connstr
Строка подключения, описанная ранее для dblink_connect sql
SQL-запрос, который вы хотите выполнить в удалённой базе данных, например select * from foo . fail_on_error
Если равен true (это значение по умолчанию), в случае ошибки, выданной на удалённой стороне соединения, ошибка также выдаётся локально. Если равен false, удалённая ошибка выдаётся локально как ЗАМЕЧАНИЕ, и функция не возвращает строки.
Возвращаемое значение
Эта функция возвращает строки, выдаваемые в результате запроса. Так как dblink может выполнять произвольные запросы, она объявлена как возвращающая тип record , а не некоторый определённый набор столбцов. Это означает, что вы должны указать ожидаемый набор столбцов в вызывающем запросе — в противном случае PostgreSQL не будет знать, чего ожидать. Например:
SELECT * FROM dblink('dbname=mydb options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
В части « псевдонима » предложения FROM должны указываться имена столбцов и типы, которые будет возвращать функция. (Указание имён столбцов в псевдониме таблицы предусмотрено стандартом SQL, но определение типов столбцов является расширением PostgreSQL .) Это позволяет системе понять, во что должно разворачиваться обозначение * , и на что ссылается proname в предложении WHERE , прежде чем пытаться выполнять эту функцию. Во время выполнения произойдёт ошибка, если действительный результат запроса из удалённой базы данных не будет содержать столько столбцов, сколько указано в предложении FROM . Однако имена столбцов могут не совпадать, так же, как dblink не настаивает на точном совпадении типов. Функция завершится успешно, если возвращаемые строки данных будут допустимыми для ввода в тип столбца, объявленный в предложении FROM .
Замечания
Использовать dblink с предопределёнными запросами будет удобнее, если создать представление. Это позволит скрыть в его определении информацию о типах столбцов и не выписывать её в каждом запросе. Например:
CREATE VIEW myremote_pg_proc AS SELECT * FROM dblink('dbname=postgres options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text); SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';
Примеры
SELECT * FROM dblink('dbname=postgres options=-csearch_path=', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteain | byteain byteaout | byteaout (12 rows) SELECT dblink_connect('dbname=postgres options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT * FROM dblink('select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteain | byteain byteaout | byteaout (12 rows) SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path='); dblink_connect ---------------- OK (1 row) SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%'; proname | prosrc ------------+------------ bytearecv | bytearecv byteasend | byteasend byteale | byteale byteagt | byteagt byteage | byteage byteane | byteane byteacmp | byteacmp bytealike | bytealike byteanlike | byteanlike byteacat | byteacat byteaeq | byteaeq bytealt | bytealt byteain | byteain byteaout | byteaout (14 rows)
| Пред. | Наверх | След. |
| dblink_disconnect | Начало | dblink_exec |
GIS-LAB
PostgreSQL: удаленные источники данных средствами dblink
Дежин Александр, 19.08.2010
Недавно в этом блоге вы могли наблюдать заметку о публикации данных в формате PostGIS, в том числе и данных OSM. Конечно, это не отменяет публикуемых shape-файлов, однако скачивать их при каждом обновлении может быть не всегда удобно, особенно если вам нужен какой-либо специфический срез этих данных. Поэтому хотелось бы рассказать о одном, как мне кажется, довольно интересном способе получения этих данных встроенными средствами PostgreSQL и PostGIS.
Самый очевидный способ, получить копию таблицы – это воспользоваться pg_dump -t , а затем pg_restore и зарегистрировать колонку геометрий – однако это скучно и не интересно, да и фильтры там наложить негде.
Для PostgreSQL есть расширение dblink, которое позволяет выполнять запросы к удаленным базам данных непосредственно из SQL, не прибегая к помощи внешних скриптов.
Чтобы включить это расширение нужно посмотреть папку, в которую у вас были установлены расширения (точный путь может зависеть от дистрибутива), и выполнить dblink.sql из этой папки на вашей БД. Например, так:
То есть процедура установки, очень похожа на установку PostGIS. После этого, можно напрямую обращаться к удаленным базам из своих SQL-скриптов, например, так:
SELECT * FROM dblink('dbname=osm host=gis-lab.info user=guest password=guest', 'SELECT osm_id, waterway, name, way FROM osm_line WHERE waterway IN (''river'', ''stream'')' ) AS rivers(osm_id int, tag varchar(50), name varchar(200), way geometry);
- ‘dbname=osm host=gis-lab.info user=guest password=guest’ – это строка подключения к удаленной базе
- ‘SELECT osm_id, waterway, name, way FROM osm_line WHERE . ‘ – запрос, который вы хотите выполнить (escape-последовательность для одинарных кавычек – » )
- rivers(osm_id int, tag varchar(50), name varchar(200), way geometry) – псевдоним в текущем запросе и описание полей с их типами
При необходимости выбрать данные по охвату, можно поступить например так:
SELECT * FROM dblink('dbname=osm host=gis-lab.info user=guest password=guest', 'SELECT osm_id, name, way FROM osm_point WHERE place IN (''city'',''town'') AND way && ST_SetSRID(ST_MakeBox2D(ST_Point(37.32, 56.53), ST_Point(41.20, 58.96)), 4326)' ) AS rivers(osm_id int, name varchar(50), geom geometry);
Где, ST_SetSRID(ST_MakeBox2D(ST_Point(37.32, 56.53), ST_Point(41.20, 58.96)), 4326) – охват ярославской области, а && – оператор пересечения. Таким образом можно выбрать все крупные населенный пункты ярославской области, обозначенные как place=city|town.
Если по каким-то причинам вам не удобно указывать строку соединения каждый раз, то можно воспользоваться dblink_connect для того чтобы создать подключение и в дальнейшем его использовать.
Поделиться ссылкой:
- Click to share on Twitter (Opens in new window)
- Click to share on Facebook (Opens in new window)
- Click to share on Google+ (Opens in new window)
Dblink postgresql что это
dblink_connect — открывает постоянное подключение к удалённой базе данных dblink_connect_u — открывает постоянное подключение к удалённой базе данных, небезопасно dblink_disconnect — закрывает постоянное подключение к удалённой базе данных dblink — выполняет запрос в удалённой базе данных dblink_exec — выполняет команду в удалённой базе данных dblink_open — открывает курсор в удалённой базе данных dblink_fetch — возвращает строки из открытого курсора в удалённой базе данных dblink_close — закрывает курсор в текущей базе данных dblink_get_connections — возвращает имена всех открытых именованных подключений dblink dblink_error_message — выдаёт сообщение последней ошибки для именованного подключения dblink_send_query — передаёт асинхронный запрос в удалённую базу данных dblink_is_busy — проверяет, не выполняется ли через подключение асинхронный запрос dblink_get_notify — выдаёт асинхронные уведомления подключения dblink_get_result — получает результат асинхронного запроса dblink_cancel_query — отменяет любой активный запрос в заданном подключении dblink_get_pkey — возвращает позиции и имена полей первичного ключа отношения dblink_build_sql_insert — формирует оператор INSERT из локального кортежа, заменяя значения полей первичного ключа переданными альтернативными значениями dblink_build_sql_delete — формирует оператор DELETE со значениями, передаваемыми для полей первичного ключа dblink_build_sql_update — формирует оператор UPDATE из локального кортежа, заменяя значения первичного ключа переданными альтернативными значениями
Модуль dblink обеспечивает подключения к другим базам данных PostgreSQL из сеанса базы данных.
См. также описание модуля postgres_fdw, который предоставляет примерно ту же функциональность, но через более современную и стандартизированную инфраструктуру.
| Пред. | Наверх | След. |
| F.9. cube | Начало | dblink_connect |
Синхронизация данных Drupal сайтов через dblink`и PostgreSQL
Развитие PostgreSQL идет огромными шагами, функциональный возможности нарастают как снежный ком, катящийся с горы. До Oracle постгрес может еще не дорос, но как фриварная и бесплатная база данных Постгрес успешно живет и конкурирует не только как «база для Друпал», но и как хранилище данных энтерпрайз уровня, например 1С. Такие технологии как: кластеризация, репликация, стендбай и горячие бэкапы успешно работают и развиваются. Большой вклад в развитие PostgreSQL внес всем известный Skype, разработав и успешно применив у себя технологию репикации данных. На основе данной технологии успешно реализуется резервирование баз на серверах с любой архитектурой(*BSD, Linux, AIX, Solaris, Winows и т.д.) и без привязки к версии базы данных, при этом не важно сколько схем имеется в конкретной базе — можно реплицировать только те данные, которые необходимо, что делает данную технологию незаменимой при реализации standby для сервера, на котором расположены несколько проектов. Синхронизация master со standby производится только для нужных схем(по простому баз), а не полностью всех данных, как это делается, если использовать технологию standby от PostgreSQL, что позволяет размещать на резервном сервере базы, отличные от баз на master сервере.
Про систему репликации данных от Skype — Londiste(skytools) написано достаточно много, тут же я хочу коснутся репликации выборочных данных на уровне таблиц. Данная технология использовалась в одном из проектов, при создании двух смежных сайтов, для синхронизации учетных записей новых и существующих пользователей их сессий и т.д.
За основу взята технология DBLink, позволяющая связывать несколько баз данных между собой, чтение и запись к связанной базе производится через стандартные select`ы, insert`ы и update`ы.
1. включение поддержки dblink`ов для postgresql < 9.1:(для postgresql >= 9.1 установка еще проще, необходимо выполнить CREATE EXTENSION dblink;)
# cd /usr/ports/databases/postgresql90-contrib; make install clean
2. создание dblink`а, необходимо выставить параметры коннекта и выполнить скрипт create_dblink.sql в базе сайта-источника(site1) под админом
\set C_USER ‘site1’
— site2 db conn params —
\set DB_NAME ‘site2’
\set U_PASS ‘123123’
\set S_NAME ‘site2link’
\set U_NAME :DB_NAME
— create dblink mapping —
CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
CREATE SERVER :S_NAME FOREIGN DATA WRAPPER postgresql OPTIONS (dbname :’DB_NAME’);
CREATE USER MAPPING FOR :C_USER SERVER :S_NAME OPTIONS (user :’U_NAME’, password :’U_PASS’);
GRANT USAGE ON FOREIGN SERVER :S_NAME TO :C_USER;
3. выполнить скрипт replication.sql на базе сайта-источника(site1) под владельцем схемы создается процедура перликации данных(пример) c site1 на site2 :
CREATE OR REPLACE FUNCTION drupal_users_session_replication() RETURNS trigger
LANGUAGE plpgsql AS $
BEGIN
PERFORM dblink_connect(‘site2link’);
SELECT tableowner FROM pg_tables WHERE tablename=’users’ INTO db_name;
SELECT * FROM dblink(‘SELECT replication_uid FROM drupal_users_replication_list WHERE uid=’||NEW.uid||’
AND db_name=’||quote_literal((db_name))||’;’) AS t(uid bigint) INTO uid;
SELECT * FROM dblink(‘SELECT uid FROM sessions WHERE sid=’||quote_literal((NEW.sid))||’;’) AS t(uid bigint) INTO ses_uid;
IF (ses_uid IS NULL) THEN
PERFORM dblink_exec(‘INSERT INTO sessions (uid,sid,hostname,timestamp,cache,session) VALUES(‘||uid||’,’||quote_literal((NEW.sid))||’,’||quote_literal((NEW.hostname))||’,’||NEW.timestamp||’, ‘||quote_literal((NEW.cache))||’,’||quote_literal((NEW.session))||’);’);
PERFORM dblink_exec(‘UPDATE sessions SET uid=’||uid||’, hostname=’||quote_literal((NEW.hostname))||’, timestamp=’||NEW.timestamp||’, cache=’||quote_literal((NEW.cache))||’, session=’||quote_literal((NEW.session))||’
WHERE sid=’||quote_literal((NEW.sid))||’;’); END IF;
PERFORM dblink_disconnect();
RETURN NEW;
END;
и на нужные таблицы навешиваются тригеры, например:
CREATE TRIGGER drupal_users_session_replication
AFTER INSERT OR UPDATE ON sessions
FOR EACH ROW EXECUTE PROCEDURE drupal_users_session_replication();
4. выполнить скрипт drupal_users_repl_dest.sql на базе сайта-приемника(site2) под владельцем схемы создается синхронизирующая таблица, где будут хранится связи:
CREATE TABLE drupal_users_replication_list (
uid bigint NOT NULL,
trainer_uid bigint NOT NULL,
db_name varchar(32) NOT NULL
ALTER TABLE drupal_users_replication_list
ADD CONSTRAINT drupal_users_replication_list_pkey PRIMARY KEY (uid, trainer_uid, db_name);
————-
CREATE OR REPLACE FUNCTION drupal_users_repl_dest() RETURNS TRIGGER AS$
DELETE FROM drupal_users_replication_list WHERE trainer_uid=OLD.uid;
RETURN OLD;
END;
CREATE TRIGGER drupal_users_repl_dest
AFTER DELETE ON users FOR EACH ROW EXECUTE PROCEDURE drupal_users_repl_dest();
В результате сего шаманства, при входе пользователя на site1 данные его сессии переносятся в базу site2, куда пользователь сможет зайти без введения каких-либо логинов-паролей.
Это только пример, постить весь код не интересно и бесполезно, но на его основе легко можно реализовать доступ к данным между любым количеством баз.
В заключение:Кто то может сказать — «а зачем так сложно, ведь можно все сделать на php», возражать не стану — можно, так же как можно timestamp инсертить в базу, получая его в PHP, вместо того что бы использовать все возможности баз данных, но это приемлемо для небольших сайтов, а на серьезных сайтах(например ГИБДД) это повлечет непозволительные временные затраты.
Похожие статьи:
- Удаленное определение движка сайта, CMS
- Обновление Django на FreeBSD сервере
- Перенос Joomla сайтов на хостинг
- Генерация карты сайта sitemap.xml в Joomla
- Автоматическое создание пользователя в wordpres при его регистрации в phpBB