PostgreSQL

SereneDB can attach an entire PostgreSQL database — no extra setup required. Provide a PostgreSQL connection string and the TYPE postgres option, and every table in the PostgreSQL database becomes queryable as if it were a regular SereneDB catalog — so you can join live PostgreSQL rows against your local search and analytics in a single query. For the general ATTACH and DETACH syntax, see ATTACH AND DETACH.

Tip

This works with any PostgreSQL-compatible database, not just PostgreSQL itself. Because SereneDB speaks the PostgreSQL wire protocol, you can also attach another SereneDB instance the same way — point the connection string at it and query its tables alongside your local data.

Attach a PostgreSQL database using a connection string:

Query

ATTACH 'host=localhost port=5432 dbname=postgres user=postgres' AS pg (TYPE postgres);

Query a table from the attached database using its fully qualified name:

Query

SELECT id, name, price FROM pg.shop.products ORDER BY id;

Result

 id | name   | price----+--------+-------  1 | widget |  9.99  2 | gadget | 19.99  3 | gizmo  |  4.25

Attach a PostgreSQL database in read only mode:

Query

ATTACH 'host=localhost port=5432 dbname=postgres user=postgres' AS pg (TYPE postgres, READ_ONLY);

Detach the PostgreSQL database:

Query

DETACH pg;

Once attached, the PostgreSQL database behaves like any other catalog: its tables can be read, written and joined against your local data.

Connection string

The connection string is a list of {key}={value} arguments. The most common ones are:

Name	Description	Default
host	Name of host to connect to	localhost
hostaddr	Host IP address	localhost
port	Port number	5432
user	PostgreSQL user name	[OS user name]
password	PostgreSQL password
dbname	Database name	[user]
passfile	Name of file passwords are stored in	~/.pgpass