Deployment guide

pg DuckDB

Overview

This guide covers deploying and using pg_duckdb with PostgreSQL 17 on Ubuntu 24.04 on Azure, from SecureAnyCloud Azure Marketplace images. pg_duckdb is the official PostgreSQL extension for DuckDB. It embeds DuckDB's vectorized, columnar execution engine directly inside the PostgreSQL backend process.

An analytical query runs on that analytics engine instead of PostgreSQL's row-at-a-time executor. The data does not move, and your SQL does not change.

Two capabilities follow, and they are the point of this image:

Analytics on the data you already have. Set duckdb.force_execution = true, and your existing GROUP BY, window function and large join queries against ordinary PostgreSQL heap tables are planned into a DuckDB scan. There is no export step, no second system to keep in sync, and no ETL.

Data lake files as tables. read_parquet(), read_csv() and related functions make Parquet, CSV, JSON, Iceberg and Delta Lake files queryable as tables. You can join them against your PostgreSQL tables in one statement. Files can be on local disk or in S3, GCS, Azure Blob or R2.

Everything runs on a single VM. There is no separate analytics cluster, no container runtime and no external service. PostgreSQL listens on port 5432, and that one port is the whole product.

pg_duckdb has no web interface. It is a PostgreSQL extension, so you reach it with psql or any PostgreSQL client driver, like any other PostgreSQL database. Everything in this guide happens at the SQL prompt. Any BI tool, notebook or application that already speaks PostgreSQL can use it unchanged.

Sample data ships with the image. The analyticsdb database contains a 50,000-row analytics.orders table. The same 50,000 rows are also written out as a Parquet file and a CSV file on local disk. You can try the data lake capability on a brand new VM with no cloud account, no credentials and no configuration, before you connect any object storage of your own.

Security by design. On first boot, each VM generates a unique password for the postgres superuser, a unique password for the analyst application role, and a unique self-signed TLS certificate. It writes them to the root-only file /root/pg-duckdb-credentials.txt. Nothing is baked into the image.

Until those secrets exist, the database listens only on loopback, so an instance can never serve a routable port before it has its own credentials. Remote connections are then accepted only over TLS with scram-sha-256.

What you get:

PostgreSQL 17 from the official PostgreSQL PGDG repository, running under systemd as postgresql.service
pg_duckdb 1.1.1, compiled from the pinned upstream release together with its vendored DuckDB engine and loaded through shared_preload_libraries. The SQL extension object reports extversion 1.1.0. That is upstream's extension script version, and it is expected on the v1.1.1 release
A database analyticsdb with an analytics schema, a 50,000-row orders table, and matching Parquet and CSV sample files under /var/lib/sac/data/pg_duckdb-samples/
The duckdb_users role, which controls who may drive DuckDB execution, with the analyst application role already a member
Per-VM passwords and a per-VM TLS certificate, all generated on first boot and written to a root-only credentials file
pg-duckdb-selfcheck, which verifies the whole appliance end to end, including that queries really run on DuckDB
Unattended security upgrades left enabled, so the appliance keeps receiving patches

Prerequisites

An active Azure subscription, an SSH public key, and a VNet and subnet in the target region
A subscription to this listing on Azure Marketplace
A Network Security Group that allows TCP 22 for administration. Open TCP 5432 as well only if you need to reach the database from outside the VM, and in production restrict it to your client subnet
Recommended virtual machine size: Standard_B2s with 2 vCPU and 4 GB RAM for development and evaluation. DuckDB is memory-hungry by design. For real analytical workloads, choose a memory-optimized size such as Standard_E2s_v5 or above, and raise duckdb.max_memory to match, as described under Tuning below

Deploy the virtual machine

In the Azure Portal, select the image from Azure Marketplace, choose your VM size, and supply your SSH public key for the azureuser account. Or deploy from the Azure CLI.

Run these commands on your own workstation, not on the VM:

# Accept the Marketplace terms once per subscription (required before the first CLI deploy)
az vm image terms accept --urn sac1647283583153:pg-duckdb-ubuntu-24-04:default:latest
az vm create \
  --resource-group my-resource-group \
  --name my-pgduckdb-vm \
  --image sac1647283583153:pg-duckdb-ubuntu-24-04:default:latest \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-resource-group --name my-pgduckdb-vm --port 5432

Connect over SSH once the VM is running:

ssh azureuser@<vm-ip>

Retrieve your per-VM credentials

First boot generates every secret for this VM and writes them to a root-only file. Read the file first, because the passwords appear in plain text only there.

sudo cat /root/pg-duckdb-credentials.txt

The file records the connection URL, the database name, the postgres superuser password, the analyst application role password, and the paths of the bundled sample files.

You can verify the whole appliance end to end at any time with the bundled self-check. It confirms that pg_duckdb is preloaded and that your per-VM password works over TLS, while blank and well-known passwords are refused. It also confirms that a query really runs on DuckDB and that DuckDB can read the local Parquet and CSV files:

sudo pg-duckdb-selfcheck

Confirm the extension is live

PostgreSQL starts automatically on boot. Check the service, confirm that pg_duckdb is preloaded, and confirm that the extension exists in the analytics database.

The shared_preload_libraries line is the one that matters. Without it, the extension's planner hook is never installed and no query reaches DuckDB.

systemctl is-active postgresql
sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;'
sudo -u postgres psql -d analyticsdb -tAc "SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_duckdb';"
sudo -u postgres psql -d analyticsdb -tAc "SELECT r['v'] FROM duckdb.query(\$\$SELECT version() AS v\$\$) r;"

Expected output:

active pg_duckdb pg_duckdb|1.1.0 v1.4.3

Run your first analytical query through DuckDB

On the VM itself, the Unix socket needs no password. Running sudo -u postgres psql -d analyticsdb opens an interactive session against the sample database. The examples below pass their SQL with -c, so you can paste them as they are.

The analytics.orders table holds 50,000 rows of sample order data. Run an ordinary aggregate over it with duckdb.force_execution turned on, and DuckDB executes it:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = true;" -c "SELECT region, count(*) AS orders, round(sum(amount), 2) AS revenue FROM analytics.orders GROUP BY region ORDER BY region;"

Expected output:

SET region | orders | revenue --------+--------+----------- amer | 12500 | 892173.48 apac | 12500 | 892144.77 emea | 12500 | 892226.91 latam | 12500 | 892116.06 (4 rows)

The SQL is completely ordinary. Nothing in it is specific to DuckDB, and the same statement runs unchanged against stock PostgreSQL.

Prove the query really ran on DuckDB

This check is worth learning, because it is the only one that distinguishes this image from plain PostgreSQL. Ask for the query plan. When DuckDB executes a query, the plan contains a Custom Scan (DuckDBScan) node:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = true;" -c "EXPLAIN (COSTS FALSE) SELECT region, count(*), sum(amount) FROM analytics.orders GROUP BY region;"

Expected output

SET QUERY PLAN ------------------------------- Custom Scan (DuckDBScan) DuckDB Execution Plan: ┌───────────────────────────┐ │ HASH_GROUP_BY │ │ ──────────────────── │ │ Groups: #0 │ │ │ │ Aggregates: │ │ count_star() │ │ sum(#1) │ │ │ │ ~31,606 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PROJECTION │ │ ──────────────────── │ │ region │ │ amount │ │ │ │ ~50,000 rows │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ PGDUCKDB_POSTGRES_SCAN │ │ ──────────────────── │ │ Table: orders │ │ │ │ Projections: │ │ region │ │ amount │ │ │ │ ~50,000 rows │ └───────────────────────────┘ (35 rows)

Now run the identical query with DuckDB execution turned off. The plan reverts to PostgreSQL's own executor:

sudo -u postgres psql -d analyticsdb -c "SET duckdb.force_execution = false;" -c "EXPLAIN (COSTS FALSE) SELECT region, count(*), sum(amount) FROM analytics.orders GROUP BY region;"

Expected output

SET QUERY PLAN -------------------------- HashAggregate Group Key: region -> Seq Scan on orders (3 rows)

The presence of DuckDBScan in the first plan and its absence in the second is the proof. Both plans return the same answers, which you can confirm yourself:

sudo -u postgres psql -d analyticsdb -tAc "SET duckdb.force_execution = true; SELECT round(sum(amount), 2) FROM analytics.orders;"
sudo -u postgres psql -d analyticsdb -tAc "SET duckdb.force_execution = false; SELECT round(sum(amount), 2) FROM analytics.orders;"

Expected output:

SET 3568661.22 SET 3568661.22

When you do not need force_execution. As soon as a query uses a DuckDB-only feature such as read_parquet(), DuckDB execution is selected automatically. You need duckdb.force_execution only to push a query that touches nothing but PostgreSQL tables onto DuckDB.

Query Parquet and CSV files as tables

The image ships a Parquet file and a CSV file that hold the same 50,000 rows. You can try the data lake path immediately, with no object storage and no credentials:

ls -lh /var/lib/sac/data/pg_duckdb-samples/
sudo -u postgres psql -d analyticsdb -c "SELECT count(*) AS parquet_rows FROM read_parquet('/var/lib/sac/data/pg_duckdb-samples/orders.parquet');" -c "SELECT count(*) AS csv_rows FROM read_csv('/var/lib/sac/data/pg_duckdb-samples/orders.csv');"

Expected output:

total 2.1M -rw-r--r-- 1 postgres postgres 1.9M Aug 7 02:48 orders.csv -rw-r--r-- 1 postgres postgres 230K Aug 7 02:48 orders.parquet parquet_rows -------------- 50000 (1 row) csv_rows ---------- 50000 (1 row)

Select columns from a file with the r['column'] syntax, and give the function call a short alias:

sudo -u postgres psql -d analyticsdb -c "SELECT r['region'] AS region, count(*) AS orders, round(sum(r['amount']::numeric), 2) AS revenue FROM read_parquet('/var/lib/sac/data/pg_duckdb-samples/orders.parquet') r GROUP BY r['region'] ORDER BY 1;"

Expected output:

region | orders | revenue --------+--------+----------- amer | 12500 | 892173.48 apac | 12500 | 892144.77 emea | 12500 | 892226.91 latam | 12500 | 892116.06 (4 rows)

Join a file against a database table

This mixed transactional and analytical query is what the extension exists for. A live PostgreSQL table sits on one side and a data lake file on the other, joined in a single statement.

sudo -u postgres psql -d analyticsdb -c "SELECT p.region, p.db_orders, f.file_orders FROM (SELECT region, count(*) AS db_orders FROM analytics.orders GROUP BY region) p JOIN (SELECT r['region'] AS region, count(*) AS file_orders FROM read_parquet('/var/lib/sac/data/pg_duckdb-samples/orders.parquet') r GROUP BY r['region']) f ON f.region = p.region ORDER BY 1;"

Expected output:

region | db_orders | file_orders --------+-----------+------------- amer | 12500 | 12500 apac | 12500 | 12500 emea | 12500 | 12500 latam | 12500 | 12500 (4 rows)

Write query results back out to Parquet

The embedded engine writes files as well as reading them. This is how the bundled sample Parquet file was produced at build time:

sudo -u postgres psql -d analyticsdb -c "SELECT duckdb.raw_query(\$\$COPY (SELECT region, count(*) AS orders FROM read_parquet('/var/lib/sac/data/pg_duckdb-samples/orders.parquet') GROUP BY region) TO '/tmp/region_summary.parquet' (FORMAT parquet)\$\$);" -c "SELECT * FROM read_parquet('/tmp/region_summary.parquet');"

Expected output:

raw_query ----------- (1 row) NOTICE: result: Count BIGINT [ Rows: 1] 4 region | orders --------+-------- emea | 12500 amer | 12500 apac | 12500 latam | 12500 (4 rows)

Connect from your own applications

Any PostgreSQL client works, because this is a PostgreSQL server. From a remote host, connect over TLS with the per-VM password from the credentials file. Replace <vm-ip> with your VM's address and <POSTGRES_PASSWORD> with the value from the file:

PGPASSWORD=<POSTGRES_PASSWORD> psql "host=<vm-ip> port=5432 dbname=analyticsdb user=postgres sslmode=require" -c "SELECT now();"

For applications, use the analyst role instead of the superuser. It owns the analytics schema and is a member of duckdb_users, so it can drive DuckDB execution:

PGPASSWORD=<ANALYST_PASSWORD> psql "host=<vm-ip> port=5432 dbname=analyticsdb user=analyst sslmode=require" -c "SET duckdb.force_execution = true;" -c "SELECT region, count(*) FROM analytics.orders GROUP BY region ORDER BY 1;"

Remote connections are accepted only over TLS. pg_hba.conf rejects a non-TLS connection attempt before any password is checked.

Connect object storage

Everything above needs no cloud account. When you are ready to point the engine at your own data lake, create a DuckDB secret from SQL, then read remote paths exactly as you read the local ones. Run this as postgres or another member of duckdb_users:

SELECT duckdb.create_simple_secret( type := 'S3', key_id := 'your_key', secret := 'your_secret', region := 'eu-west-2' ); SELECT count(*) FROM read_parquet('s3://your-bucket/your-data.parquet');

Azure Blob Storage, Google Cloud Storage and Cloudflare R2 work the same way. To use Iceberg and Delta Lake, install the matching DuckDB extension first, for example SELECT duckdb.install_extension('iceberg');.

Secrets created this way are visible to members of duckdb_users, so grant that role deliberately.

Tuning

The shipped configuration lives in /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf.

These settings ship with the image (setting, then shipped value):

shared_preload_libraries is 'pg_duckdb'. It is required, and removing it disables the extension entirely.
duckdb.max_memory is 1024, in megabytes of memory DuckDB may use per connection. DuckDB's own default is 80 percent of system RAM per connection, which would exhaust a small VM under any concurrency, so this image caps it. Raise it on a larger VM.
duckdb.postgres_role is 'duckdb_users'. Only members of this role, plus superusers, may drive DuckDB execution or manage secrets.
duckdb.force_execution is false. It applies at session level. Set it per session or per role, and avoid setting it globally unless every query on the instance is analytical.
duckdb.threads is the upstream default. It sets the DuckDB threads per connection, and the default is the CPU core count.

To make analytical execution the default for a specific role, instead of typing SET in every session, run:

sudo -u postgres psql -d analyticsdb -c "ALTER ROLE analyst SET duckdb.force_execution = true;"

Apply configuration file changes by running sudo systemctl restart postgresql.

Security posture

No credential is baked into the image. The postgres superuser and the analyst role both ship with no password at all, and the build gate asserts that no role anywhere in the cluster carries one. Both passwords are generated on first boot, so no two VMs share a secret.

The database is loopback-only until first boot completes. The shipped configuration pins listen_addresses to localhost with TLS off. First boot creates the per-VM TLS certificate and both passwords, and only then opens the listener to the network. A routable port is never served without this instance's own credentials behind it.

Remote access requires TLS. pg_hba.conf permits remote clients only through hostssl with scram-sha-256. A non-TLS remote connection is refused before any password is checked, and the file contains no trust rule.

DuckDB execution is role-gated. duckdb.postgres_role restricts DuckDB execution and secret management to members of duckdb_users. The analyst role is deliberately not granted pg_read_server_files or pg_write_server_files. DuckDB's local file system access therefore stays disabled for it, and reading files from the server's disk remains an explicit superuser action.

Restrict the Network Security Group. Leave port 5432 closed unless you need remote database access. When you do open it, allow only the client subnets that need it.

Rotate the passwords when you need to. Run sudo -u postgres psql -c "ALTER ROLE postgres PASSWORD '<new>'" and update the credentials file. The same applies to analyst.

Operations

Common tasks and the commands that run them:

Service status: systemctl status postgresql
Restart the database: sudo systemctl restart postgresql
Database logs: sudo tail -n 100 /var/log/postgresql/postgresql-17-main.log
Confirm the extension is preloaded: sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;'
Embedded engine version: sudo -u postgres psql -d analyticsdb -tAc "SELECT r['v'] FROM duckdb.query(\$\$SELECT version() AS v\$\$) r;"
Verify the appliance: sudo pg-duckdb-selfcheck
Read per-VM credentials: sudo cat /root/pg-duckdb-credentials.txt
Local SQL prompt: sudo -u postgres psql -d analyticsdb

Server components:

pg_duckdb 1.1.1: the PostgreSQL extension that embeds the DuckDB engine
DuckDB, vendored with pg_duckdb 1.1.1: the columnar, vectorized execution engine, with json, icu and httpfs built in
PostgreSQL 17: the relational database and storage engine, on port 5432
Ubuntu Server 24.04 LTS: the base operating system

Key paths:

/etc/postgresql/17/main/conf.d/10-pg_duckdb.conf: the pg_duckdb configuration, including shared_preload_libraries
/etc/postgresql/17/main/pg_hba.conf: the client authentication rules
/var/lib/postgresql/17/main/: the database data directory
/var/lib/sac/data/pg_duckdb-samples/: the bundled Parquet and CSV sample files
/root/pg-duckdb-credentials.txt: all per-VM secrets, readable only by root
/usr/local/sbin/pg-duckdb-selfcheck: the end-to-end appliance verification

Troubleshooting

CREATE EXTENSION pg_duckdb fails. The extension must be preloaded at postmaster start. Check that sudo -u postgres psql -tAc 'SHOW shared_preload_libraries;' returns pg_duckdb. If it does not, confirm that /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf is present, then restart with sudo systemctl restart postgresql.

EXPLAIN shows no DuckDBScan node. Either duckdb.force_execution is not set in that session, or the connected role is not a member of duckdb_users. Check membership with SELECT pg_has_role(current_user, 'duckdb_users', 'member'); and grant the role if needed.

A read_parquet() call on a local path returns a permission error. DuckDB's local file system is disabled for roles that are not superusers and not members of both pg_read_server_files and pg_write_server_files. Read local files as postgres. If an application genuinely needs server-side file access, grant those roles deliberately.

Queries fail with an out-of-memory error. DuckDB's memory ceiling applies per connection. Lower duckdb.max_memory in /etc/postgresql/17/main/conf.d/10-pg_duckdb.conf, or move to a memory-optimized VM size and raise it.

The database is unreachable from outside the VM. Confirm that the Network Security Group allows TCP 5432 from your client address. Confirm that sudo -u postgres psql -tAc 'SHOW listen_addresses;' returns *, and that your client connects with sslmode=require.

If listen_addresses still reports localhost, first boot has not completed. Inspect sudo journalctl -u pg-duckdb-firstboot -n 100 --no-pager and sudo cat /var/log/sac/sac-firstboot.log.

Advanced DuckDB types are rejected. STRUCT, MAP and UNION need a DuckDB execution context. Wrap those expressions in duckdb.query($$ ... $$), as described in the upstream types documentation.

Support

SecureAnyCloud provides 24/7 support for this image. Contact sales@secureanycloud.com with your Azure subscription ID and the VM name.

For pg_duckdb itself, the upstream documentation at github.com/duckdb/pg_duckdb covers the function reference, settings and data lake integration. The DuckDB documentation covers its SQL dialect.

Deploy on Azure

Launch pg_duckdb DuckDB Analytics for PostgreSQL 17 on Ubuntu 24.04 on Azure with 24/7 support from SecureAnyCloud.

View this image

Need Help?

Our support team is available 24/7.

sales@secureanycloud.com

Deploy on Azure