PostgreSQL 16 with PGroonga on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of PostgreSQL 16 with PGroonga on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images. It pairs the PostgreSQL 16 relational database with PGroonga (pgroonga), an extension that adds fast, accurate, multilingual full-text search inside the database engine, backed by the Groonga search engine.

The idea is simple and powerful: you build a USING pgroonga index on a text column, then query it with the &@~ full-text operator. PostgreSQL returns the matching rows using Groonga's inverted index — so search stays fast as the table grows, and it works across natural languages. PGroonga is especially strong on CJK text (Japanese, Chinese, Korean), where naive LIKE and the built-in tsvector tokenizers struggle, but it handles English and other languages equally well.

What you get on this image:

• Fast full-text search — the &@~ operator uses the PGroonga index rather than a sequential scan, so queries stay quick on large tables.
• Multilingual out of the box — the same index searches English and CJK text with no extra configuration. A search for 全文検索 finds the Japanese documents; a search for accurate finds the English one.
• Relevance ranking — pgroonga_score() returns a relevance score for each match, so you can ORDER BY the best hits first.

PostgreSQL 16 is installed from the official PostgreSQL PGDG repository. PGroonga is version 4.0.8, installed from the Groonga project's own package repository — the upstream-supported channel for this extension. The extension is already created in a demo database, and the image ships a small multilingual document corpus with PGroonga indexes already built, so you can see full-text search working within a minute of first boot.

Security by design — no baked credential. Every role in the shipped image is password-less by construction, and TLS is off, so the database is not reachable off-box at all in the image itself. On first boot each VM mints a unique password for the postgres superuser and for the least-privilege application role search_app, generates a unique self-signed TLS server certificate, writes everything to the root-only file /root/pgroonga-credentials.txt, then enables TLS. Two VMs launched from this image never share a secret. A credential guard runs on every boot and refuses to leave the database serving if a published or example credential is ever actually in effect.

What is included:

• PostgreSQL 16 from the official PGDG repository, running under systemd as postgresql.service

• PGroonga 4.0.8 from the Groonga project's repository, ready to CREATE EXTENSION pgroonga (no shared_preload_libraries required)

• A demo database fulltext with a documents table of mixed English and Japanese text and two USING pgroonga indexes already built

• A least-privilege application role search_app with DML on the demo schema, for your application to run full-text queries

• Per-VM passwords for both roles and a per-VM TLS certificate generated on first boot, written to a root-only credentials file

• A boot-time credential guard that fails closed if a published or example credential is in effect

• Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

• Active Azure subscription, an SSH public key, and a VNet + subnet in the target region

• Subscription to this listing on Azure Marketplace

• A Network Security Group allowing TCP 22 (administration) and TCP 5432 (the PostgreSQL wire protocol) from your client network. In production, restrict 5432 to your application subnet.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development, evaluation and light workloads. For larger corpora and higher query rates, choose a memory-rich size such as Standard_E2s_v5 or larger.

Deploy the virtual machine

Create the VM from the image, opening only SSH and the PostgreSQL port to your own network:

az vm create \
  --resource-group my-rg \
  --name pgroonga-1 \
  --image <this-marketplace-image> \
  --size Standard_B2s \
  --admin-username azureuser \
  --generate-ssh-keys \
  --public-ip-sku Standard
az vm open-port --resource-group my-rg --name pgroonga-1 --port 5432 --priority 900

Retrieve your per VM credentials

On the first boot the VM mints its own passwords and TLS certificate. SSH in and read the root-only credentials file:

sudo cat /root/pgroonga-credentials.txt

You will see the postgres superuser password, the search_app application role password, and ready-to-paste connection strings. The file is mode 0600, owned by root, and the values in it exist only on this VM.

The postgres.host line is filled in from the VM's own network metadata. Azure's instance metadata service returns an empty value for Standard SKU public IPs, so the image falls back to the load-balancer metadata and then to the VM's private address. When connecting from outside Azure, use the VM's public IP address from the portal or az vm list-ip-addresses.

Confirm the service is healthy

Check that PostgreSQL and the credential guard are in good order, and that the extension is loaded:

systemctl is-active postgresql.service pgroonga-credguard.service
sudo -u postgres psql -tAc "SELECT version();"
sudo -u postgres psql -d fulltext -tAc "SELECT extname || ' ' || extversion FROM pg_extension WHERE extname='pgroonga';"
sudo -u postgres psql -d fulltext -tAc "SELECT a.amname FROM pg_class c JOIN pg_am a ON a.oid = c.relam WHERE c.relname = 'pgroonga_documents_body';"

The last query must return pgroonga — that is the proof the demo index really uses the PGroonga access method, so &@~ queries against it are index-backed rather than sequential scans.

Full-text search: English and Japanese, one index

This is the heart of the product. The fulltext database ships a documents table with a mix of English and Japanese rows and a USING pgroonga index on the body column. The &@~ operator runs a full-text query against that index.

Search the English text — a query for full text search returns the documents that are about it:

sudo -u postgres psql -d fulltext -c "SELECT id, title FROM documents WHERE body &@~ 'full text search' ORDER BY id;"

Now search the Japanese text. The same index, queried with the CJK term 全文検索 (zenbun kensaku, "full-text search"), returns the Japanese and mixed-language documents — and none of the English-only ones:

sudo -u postgres psql -d fulltext -c "SELECT id, title, lang FROM documents WHERE body &@~ '全文検索' ORDER BY id;"

This multilingual behaviour is exactly what PGroonga adds over PostgreSQL's built-in search: CJK text is tokenized correctly, so a Japanese or Chinese query finds the right rows without any per-language configuration.

Both remote connections are over TLS. Connect as the least-privilege application role and verify that directly:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -tAc "SELECT ssl FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

Rank results by relevance

PGroonga scores each match, so you can order the most relevant documents first with pgroonga_score(). Run it as the application role over TLS:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -c "SELECT id, title, pgroonga_score(tableoid, ctid) AS score FROM documents WHERE body &@~ 'search' ORDER BY score DESC;"

pgroonga_score() takes the row's tableoid and ctid and returns a relevance score for the current &@~ query — a positive number for a match. Ordering by it descending puts the best hits first, which is what you want behind a search box.

Build your own full-text index

Adding PGroonga search to your own table is two statements: create the table, then build a USING pgroonga index on the text column you want to search. Create the table and index as the postgres owner (schema DDL is an owner operation); the least-privilege search_app role then reads and writes the rows:

sudo -u postgres psql -d fulltext -c "CREATE TABLE IF NOT EXISTS notes (id int PRIMARY KEY, memo text);"
sudo -u postgres psql -d fulltext -c "CREATE INDEX IF NOT EXISTS pgroonga_notes_memo ON notes USING pgroonga (memo);"
sudo -u postgres psql -d fulltext -c "GRANT SELECT, INSERT, UPDATE, DELETE ON notes TO search_app;"
PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -c "INSERT INTO notes (id, memo) VALUES (1, 'PGroonga handles English text'), (2, 'PGroongaは日本語も検索できます') ON CONFLICT (id) DO NOTHING;"

Now search your new index, in either language:

PGPASSWORD='<APP_PASSWORD>' psql "host=127.0.0.1 port=5432 dbname=fulltext user=search_app sslmode=require" -c "SELECT id, memo FROM notes WHERE memo &@~ '日本語';"

The &@~ operator is the query-syntax match. PGroonga also offers &@ for a single-keyword match and &@| for an OR of several keywords; see the upstream reference for the full operator set.

Security posture

• No baked credential. Every role ships with no password at all. Both role passwords are minted on first boot from a cryptographic random source and are unique per VM.

• No shared certificate. TLS is off in the image and the certificate is generated per VM on first boot, so no two VMs share a server key.

• TLS enforced for remote clients. pg_hba.conf accepts remote connections only through hostssl with scram-sha-256. There is no trust rule anywhere. On-box administration stays password-less through the local unix socket.

• A credential guard that fails closed. On every boot, pgroonga-credguard.service proves the recorded per-VM credentials genuinely authenticate, then attempts authentication with a list of published and example values. If any of them succeeds, the guard stops PostgreSQL rather than serve a database with a known credential.

• Least privilege for applications. The search_app role has DML on the demo schema but is not a superuser. Point your application at it rather than at postgres.

Rotate a password at any time. Generate a fresh random secret and record it in the credentials file in the same step, so the credential guard continues to agree with reality:

NEWPW="$(openssl rand -base64 40 | tr -dc 'A-Za-z0-9' | cut -c1-28)"
sudo -u postgres psql -c "ALTER ROLE search_app PASSWORD '${NEWPW}';"
sudo sed -i "s|^search_app.password=.*|search_app.password=${NEWPW}|" /root/pgroonga-credentials.txt
echo "search_app password rotated and recorded in the credentials file"

Always update /root/pgroonga-credentials.txt when you rotate. The credential guard verifies on every boot that the recorded password genuinely authenticates, and stops PostgreSQL if it does not — that check is what makes a stale or mismatched credential impossible to ignore. Never set a password to a value published in documentation: the guard actively tries a list of such values and refuses to serve if one of them works.

Operations

Service control and logs:

systemctl status postgresql.service --no-pager
sudo journalctl -u pgroonga-firstboot.service --no-pager | tail -20

Back up the demo database:

sudo -u postgres pg_dump -d fulltext -f /tmp/fulltext-backup.sql && ls -la /tmp/fulltext-backup.sql

Third-party licence attribution for PGroonga and the Groonga library is shipped inside the image:

cat /usr/share/doc/pgroonga-cloudimg/ATTRIBUTION.txt

Support

cloudimg images include 24/7 support. Raise an issue through the Azure Marketplace listing or contact cloudimg support with the VM name, region, and the output of systemctl status postgresql.service.

PGroonga is developed by the Groonga project and distributed under The PostgreSQL License; the Groonga library it links is LGPL-2.1+. Upstream documentation lives at pgroonga.github.io.