Расширение PostgreSQL

Расширение postgres позволяет DuckDB напрямую читать и записывать данные из работающей базы данных PostgreSQL. Данные можно запросить непосредственно из базовой базы данных PostgreSQL. Данные можно загружать из таблиц PostgreSQL в таблицы DuckDB и наоборот. Подробности реализации и контекст см. в официальном объявлении.

Установка и Загрузка

Расширение postgres будет прозрачно автоматически загружаться при первом использовании из официального репозитория расширений. Если вы хотите установить и загрузить его вручную, выполните:

INSTALL postgres;
LOAD postgres;

Подключение

Чтобы сделать базу данных PostgreSQL доступной для DuckDB, используйте команду ATTACH с типом POSTGRES или POSTGRES_SCANNER.

Для подключения к схеме public экземпляра базы данных PostgreSQL, работающего на localhost в режиме чтения/записи, выполните:

ATTACH '' AS postgres_db (TYPE POSTGRES);

Для подключения к экземпляру PostgreSQL с заданными параметрами в режиме только для чтения, выполните:

ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE POSTGRES, READ_ONLY);

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

ATTACH 'dbname=postgres user=postgres host=127.0.0.1' AS db (TYPE POSTGRES, SCHEMA 'public');

Настройка

Команда ATTACH принимает в качестве входных данных строку подключения libpq или URI PostgreSQL.

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

dbname=postgresscanner
host=localhost port=5432 dbname=mydb connect_timeout=10

Имя	Описание	Значение по умолчанию
`dbname`	Имя базы данных	[user]
`host`	Имя хоста для подключения	`localhost`
`hostaddr`	IP-адрес хоста	`localhost`
`passfile`	Имя файла, в котором хранятся пароли	`~/.pgpass`
`password`	Пароль PostgreSQL	(пустое)
`port`	Номер порта	`5432`
`user`	Имя пользователя PostgreSQL	текущий пользователь

Пример URI: postgresql://username@hostname/dbname.

Настройка через секреты

Информацию о подключении к PostgreSQL также можно указать с помощью секретов. Следующий синтаксис можно использовать для создания секрета.

CREATE SECRET (
    TYPE POSTGRES,
    HOST '127.0.0.1',
    PORT 5432,
    DATABASE postgres,
    USER 'postgres',
    PASSWORD ''
);

Информация из секрета будет использоваться при вызове ATTACH. Мы можем оставить строку подключения Postgres пустой, чтобы использовать всю информацию, хранящуюся в секрете.

ATTACH '' AS postgres_db (TYPE POSTGRES);

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

ATTACH 'dbname=my_other_db' AS postgres_db (TYPE POSTGRES);

По умолчанию созданные секреты временные. Секреты можно сделать постоянными, используя команду CREATE PERSISTENT SECRET. Постоянные секреты можно использовать в разных сеансах.

Управление несколькими секретами

Имена секретов можно использовать для управления подключениями к нескольким экземплярам баз данных Postgres. Секретам можно присваивать имя при создании.

CREATE SECRET postgres_secret_one (
    TYPE POSTGRES,
    HOST '127.0.0.1',
    PORT 5432,
    DATABASE postgres,
    USER 'postgres',
    PASSWORD ''
);

Затем секрет можно явно указать с помощью параметра SECRET в команде ATTACH.

ATTACH '' AS postgres_db_one (TYPE POSTGRES, SECRET postgres_secret_one);

Настройка через переменные среды

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

export PGPASSWORD="secret"
export PGHOST=localhost
export PGUSER=owner
export PGDATABASE=mydatabase

Затем, для подключения, запустите процесс duckdb и выполните:

ATTACH '' AS p (TYPE POSTGRES);

Использование

Таблицы в базе данных PostgreSQL можно читать так, как если бы они были обычными таблицами DuckDB, но основанные данные считываются непосредственно из PostgreSQL во время запроса.

SHOW ALL TABLES;

name
uuids

SELECT * FROM uuids;

u
6d3d2541-710b-4bde-b3af-4711738636bf
NULL
00000000-0000-0000-0000-000000000001
ffffffff-ffff-ffff-ffff-ffffffffffff

Может быть желательно создать копию баз данных PostgreSQL в DuckDB, чтобы предотвратить непрерывное повторное чтение таблиц из PostgreSQL, особенно для больших таблиц.

Данные можно скопировать из PostgreSQL в DuckDB, используя стандартные SQL-запросы, например:

CREATE TABLE duckdb_table AS FROM postgres_db.postgres_tbl;

Запись данных в PostgreSQL

В дополнение к чтению данных из PostgreSQL, расширение позволяет создавать таблицы, загружать данные в PostgreSQL и выполнять другие изменения в базе данных PostgreSQL с помощью стандартных SQL-запросов.

Это позволяет использовать DuckDB, например, для экспорта данных, хранящихся в базе данных PostgreSQL, в Parquet или для чтения данных из файла Parquet в PostgreSQL.

Ниже приведен краткий пример того, как создать новую таблицу в PostgreSQL и загрузить данные в нее.

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);
INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');

Поддерживаются многие операции над таблицами PostgreSQL. Все эти операции непосредственно изменяют базу данных PostgreSQL, и результат последующих операций можно затем прочитать с помощью PostgreSQL. Обратите внимание, что если изменения нежелательны, ATTACH может быть запущено с свойством READ_ONLY, что предотвращает внесение изменений в базу данных. Например:

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES, READ_ONLY);

Ниже приведен список поддерживаемых операций.

`CREATE TABLE`

CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);

`INSERT INTO`

INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');

`SELECT`

SELECT * FROM postgres_db.tbl;

id	name
42	DuckDB

`COPY`

Вы можете копировать таблицы туда и обратно между PostgreSQL и DuckDB:

COPY postgres_db.tbl TO 'data.parquet';
COPY postgres_db.tbl FROM 'data.parquet';

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

COPY 'data.parquet' TO 'pg.bin' WITH (FORMAT POSTGRES_BINARY);

Сгенерированный файл будет эквивалентен копированию файла в PostgreSQL с помощью DuckDB и последующему выгрузке из PostgreSQL с помощью psql или другого клиента:

DuckDB:

COPY postgres_db.tbl FROM 'data.parquet';

PostgreSQL:

\copy tbl TO 'data.bin' WITH (FORMAT BINARY);

Вы также можете создать полную копию базы данных, используя COPY FROM DATABASE:

COPY FROM DATABASE postgres_db TO my_duckdb_db;

`UPDATE`

UPDATE postgres_db.tbl
SET name = 'Woohoo'
WHERE id = 42;

`DELETE`

DELETE FROM postgres_db.tbl
WHERE id = 42;

`ALTER TABLE`

ALTER TABLE postgres_db.tbl
ADD COLUMN k INTEGER;

`DROP TABLE`

DROP TABLE postgres_db.tbl;

`CREATE VIEW`

CREATE VIEW postgres_db.v1 AS SELECT 42;

`CREATE SCHEMA` / `DROP SCHEMA`

CREATE SCHEMA postgres_db.s1;
CREATE TABLE postgres_db.s1.integers (i INTEGER);
INSERT INTO postgres_db.s1.integers VALUES (42);
SELECT * FROM postgres_db.s1.integers;

i
42

DROP SCHEMA postgres_db.s1;

`DETACH`

DETACH postgres_db;

Транзакции

CREATE TABLE postgres_db.tmp (i INTEGER);
BEGIN;
INSERT INTO postgres_db.tmp VALUES (42);
SELECT * FROM postgres_db.tmp;

Это возвращает:

i
42

ROLLBACK;
SELECT * FROM postgres_db.tmp;

Это возвращает пустую таблицу.

Выполнение SQL-запросов в PostgreSQL

Функция таблицы `postgres_query`

Функция таблицы postgres_query позволяет выполнять произвольные запросы чтения в подключённой базе данных. postgres_query принимает имя подключенной базы данных PostgreSQL для выполнения запроса, а также SQL-запрос для выполнения. Результат запроса возвращается. Строки с одинарными кавычками экранируются, повторяя одинарную кавычку дважды.

postgres_query(attached_database::VARCHAR, query::VARCHAR)

Например:

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
SELECT * FROM postgres_query('postgres_db', 'SELECT * FROM cars LIMIT 3');

brand	model	color
Ferrari	Testarossa	red
Aston Martin	DB2	blue
Bentley	Mulsanne	gray

Функция `postgres_execute`

Функция postgres_execute позволяет выполнять произвольные запросы в PostgreSQL, включая инструкции, которые изменяют схему и содержимое базы данных.

ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
CALL postgres_execute('postgres_db', 'CREATE TABLE my_table (i INTEGER)');

Настройки

Расширение предоставляет следующие параметры конфигурации.

Имя	Описание	Значение по умолчанию
`pg_array_as_varchar`	Читать массивы PostgreSQL как varchar - позволяет читать массивы смешанной размерности	`false`
`pg_connection_cache`	Использовать кэш подключений	`true`
`pg_connection_limit`	Максимальное количество одновременных подключений к PostgreSQL	`64`
`pg_debug_show_queries`	НАСТРОЙКА ОТЛАДКИ: выводить все запросы к PostgreSQL в stdout	`false`
`pg_experimental_filter_pushdown`	Использовать фильтрацию на стороне сервера (в настоящее время экспериментальная функция)	`false`
`pg_pages_per_task`	Количество страниц на задачу	`1000`
`pg_use_binary_copy`	Использовать двоичную копию для чтения данных	`true`
`pg_null_byte_replacement`	При записи нулевых байтов в Postgres, заменить их указанным символом	`NULL`
`pg_use_ctid_scan`	Параллелизовать сканирование с использованием ctids таблиц	`true`

Кэш схемы

Чтобы избежать постоянного получения данных схемы из PostgreSQL, DuckDB сохраняет информацию о схеме — такие как имена таблиц, их столбцы и т. д. — в кэше. Если изменения внесены в схему через другое подключение к экземпляру PostgreSQL, например, добавлены новые столбцы в таблицу, кэшированная информация о схеме может быть устаревшей. В этом случае можно выполнить функцию pg_clear_cache для очистки внутренних кэшей.

CALL pg_clear_cache();

Устарело Старая функция postgres_attach устарела. Рекомендуется перейти на новый синтаксис ATTACH.