PostgreSQL-Erweiterung

Die postgres-Erweiterung ermöglicht DuckDB, Daten direkt aus einer laufenden PostgreSQL-Datenbankinstanz zu lesen und zu schreiben. Die Daten können direkt aus der zugrunde liegenden PostgreSQL-Datenbank abgefragt werden. Daten können aus PostgreSQL-Tabellen in DuckDB-Tabellen geladen werden oder umgekehrt. Siehe die offizielle Ankündigung für Implementierungsdetails und Hintergrund.

Installation und Laden

Die postgres-Erweiterung wird beim ersten Gebrauch transparent aus dem offiziellen Erweiterungs-Repository automatisch geladen. Wenn Sie sie manuell installieren und laden möchten, führen Sie aus:

INSTALL postgres;
LOAD postgres;

Verbindung herstellen

Damit eine PostgreSQL-Datenbank für DuckDB zugänglich ist, verwenden Sie den Befehl ATTACH mit dem Typ postgres oder postgres_scanner.

Um sich im Lese-Schreib-Modus mit dem Schema public der auf localhost laufenden PostgreSQL-Instanz zu verbinden, führen Sie aus:

ATTACH '' AS postgres_db (TYPE postgres);

Um sich mit der PostgreSQL-Instanz mit den angegebenen Parametern im Nur-Lesen-Modus zu verbinden, führen Sie aus:

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

Standardmäßig werden alle Schemas angehängt. Bei großen Instanzen kann es sinnvoll sein, nur ein bestimmtes Schema anzuhängen. Das kann mit dem Befehl SCHEMA erreicht werden.

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

Deprecated Die alte Funktion postgres_attach ist veraltet. Es wird empfohlen, auf die neue ATTACH-Syntax umzusteigen.

Konfiguration

Der Befehl ATTACH nimmt als Eingabe entweder eine libpq-Verbindungszeichenkette oder eine PostgreSQL-URI.

Nachfolgend einige Beispiel-Verbindungszeichenketten und häufig verwendete Parameter. Eine vollständige Liste verfügbarer Parameter finden Sie in der PostgreSQL-Dokumentation.

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

Name	Beschreibung	Standard
dbname	Datenbankname	[user]
host	Name des Hosts, mit dem verbunden werden soll	localhost
hostaddr	Host-IP-Adresse	localhost
passfile	Name der Datei, in der Passwörter gespeichert sind	~/.pgpass
password	PostgreSQL-Passwort	(empty)
port	Portnummer	5432
user	PostgreSQL-Benutzername	current user

Ein Beispiel für eine URI ist postgresql://username@hostname/dbname.

Konfiguration über Secrets

PostgreSQL-Verbindungsinformationen können auch über Secrets angegeben werden, Details siehe auf der entsprechenden Seite.

Konfiguration über Umgebungsvariablen

PostgreSQL-Verbindungsinformationen können auch über Umgebungsvariablen angegeben werden. Das kann in einer Produktionsumgebung nützlich sein, in der die Verbindungsinformationen extern verwaltet und an die Umgebung übergeben werden.

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

Um sich dann zu verbinden, starten Sie den Prozess duckdb und führen Sie aus:

ATTACH '' AS p (TYPE postgres);

Verwendung

Die Tabellen in der PostgreSQL-Datenbank können gelesen werden, als wären sie normale DuckDB-Tabellen, die zugrunde liegenden Daten werden jedoch zur Abfragezeit direkt aus PostgreSQL gelesen.

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

Es kann wünschenswert sein, eine Kopie der PostgreSQL-Datenbanken in DuckDB zu erstellen, damit das System die Tabellen nicht ständig erneut aus PostgreSQL liest, insbesondere bei großen Tabellen.

Daten können mit Standard-SQL von PostgreSQL nach DuckDB kopiert werden, zum Beispiel:

CREATE TABLE duckdb_table AS FROM postgres_db.postgres_tbl;

Daten nach PostgreSQL schreiben

Zusätzlich zum Lesen von Daten aus PostgreSQL ermöglicht die Erweiterung das Erstellen von Tabellen, das Laden von Daten in PostgreSQL und andere Änderungen an einer PostgreSQL-Datenbank mit Standard-SQL-Abfragen.

Damit können Sie DuckDB beispielsweise verwenden, um in einer PostgreSQL-Datenbank gespeicherte Daten nach Parquet zu exportieren oder Daten aus einer Parquet-Datei in PostgreSQL zu lesen.

Nachfolgend ein kurzes Beispiel, wie Sie eine neue Tabelle in PostgreSQL erstellen und Daten darin laden.

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');

Viele Operationen auf PostgreSQL-Tabellen werden unterstützt. Alle diese Operationen ändern die PostgreSQL-Datenbank direkt, und das Ergebnis nachfolgender Operationen kann dann mit PostgreSQL gelesen werden. Beachten Sie: Wenn Änderungen nicht erwünscht sind, kann ATTACH mit der Eigenschaft READ_ONLY ausgeführt werden, die Änderungen an der zugrunde liegenden Datenbank verhindert. Zum Beispiel:

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

Nachfolgend eine Liste unterstützter Operationen.

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

Sie können Tabellen zwischen PostgreSQL und DuckDB hin- und herkopieren:

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

Diese Kopien verwenden die binäre PostgreSQL-Wire-Kodierung. DuckDB kann Daten mit dieser Kodierung auch in eine Datei schreiben, die Sie anschließend mit einem Client Ihrer Wahl in PostgreSQL laden können, wenn Sie die Verbindungsverwaltung selbst übernehmen möchten:

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

Die erzeugte Datei entspricht dem, was Sie erhalten, wenn Sie die Datei mit DuckDB nach PostgreSQL kopieren und sie dann mit psql oder einem anderen Client aus PostgreSQL ausgeben:

DuckDB:

COPY postgres_db.tbl FROM 'data.parquet';

PostgreSQL:

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

Sie können auch eine vollständige Kopie der Datenbank mit der Anweisung COPY FROM DATABASE erstellen:

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;

Transaktionen

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

Das gibt zurück:

i
42

ROLLBACK;
SELECT * FROM postgres_db.tmp;

Das gibt eine leere Tabelle zurück.

SQL-Abfragen in PostgreSQL ausführen

Die Tabellenfunktion postgres_query

Die Tabellenfunktion postgres_query ermöglicht es, beliebige Leseabfragen in einer angehängten Datenbank auszuführen. postgres_query nimmt den Namen der angehängten PostgreSQL-Datenbank, in der die Abfrage ausgeführt werden soll, sowie die auszuführende SQL-Abfrage entgegen. Das Ergebnis der Abfrage wird zurückgegeben. Einfache Anführungszeichen in Strings werden durch Wiederholen des einfachen Anführungszeichens escaped.

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

Zum Beispiel:

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

Die Funktion postgres_execute

Die Funktion postgres_execute ermöglicht das Ausführen beliebiger Abfragen in PostgreSQL, einschließlich Anweisungen, die Schema und Inhalt der Datenbank ändern.

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

Einstellungen

Die Erweiterung stellt die folgenden Konfigurationsparameter bereit.

Name	Beschreibung	Standard
pg_array_as_varchar	PostgreSQL-Arrays als varchar lesen – ermöglicht das Lesen von Arrays mit gemischter Dimensionalität	false
pg_connection_cache	Ob der Verbindungscache verwendet werden soll	true
pg_connection_limit	Die maximale Anzahl gleichzeitiger PostgreSQL-Verbindungen	64
pg_debug_show_queries	DEBUG-EINSTELLUNG: alle an PostgreSQL gesendeten Abfragen auf stdout ausgeben	false
pg_experimental_filter_pushdown	Ob Filter-Pushdown verwendet werden soll (derzeit experimentell)	true
pg_pages_per_task	Die Anzahl der Seiten pro Task	1000
pg_use_binary_copy	Ob BINARY-Copy zum Lesen von Daten verwendet werden soll	true
pg_null_byte_replacement	Beim Schreiben von NULL-Bytes nach Postgres diese durch das angegebene Zeichen ersetzen	NULL
pg_use_ctid_scan	Ob das Scannen über Tabellen-ctids parallelisiert werden soll	true

Schema-Cache

Um Schema-Daten nicht ständig aus PostgreSQL holen zu müssen, hält DuckDB Schema-Informationen – etwa Tabellennamen, ihre Spalten usw. – im Cache. Wenn Schemaänderungen über eine andere Verbindung zur PostgreSQL-Instanz vorgenommen werden, etwa neue Spalten zu einer Tabelle hinzugefügt werden, können die gecachten Schema-Informationen veraltet sein. In diesem Fall kann die Funktion pg_clear_cache ausgeführt werden, um die internen Caches zu leeren.

CALL pg_clear_cache();

In Version 1.5.5 wurde Unterstützung für die automatische Erkennung von Schemaänderungen über eine „Staleness Query“ hinzugefügt (Beitrag von Brandon Freeman in duckdb/duckdb-postgres#514):

• wenn die Option pg_staleness_query_enabled (BOOLEAN, Standard: FALSE) aktiviert ist, wird bei jedem Katalogzugriff eine Abfrage ausgeführt, die den Wert der Spalte pg_class.xmin von Postgres in jeder Tabelle prüft und den Cache automatisch neu lädt, wenn eine Änderung erkannt wird

• für Postgres-Wire-kompatible Datenbanken kann eine eigene „Staleness Query“ über die Option pg_staleness_query gesetzt werden.

Arbeiten mit hstore-Spalten

DuckDB gibt Daten aus hstore-Spalten als VARCHAR ihrer Textdarstellung zurück, etwa key=>value, foo=>bar. Sie können die Funktion postgres_hstore_get verwenden, um den Wert für einen gegebenen Schlüssel zu lesen, oder postgres_hstore_to_json, um das gesamte Schlüssel/Wert-Paar-Set für die weitere Verarbeitung nach JSON zu konvertieren.

SELECT postgres_hstore_get('a=>b, c=>d', 'a');
-- b

SELECT postgres_hstore_get('a=>b, c=>d', 'missingkey');
-- NULL

SELECT postgres_hstore_to_json('a=>b, c=>d, e => null');
-- {"a": "b", "c": "d", e: null}