DoltgreSQL 1.3 on Ubuntu 24.04 on Azure User Guide

Overview

This guide covers the deployment and use of DoltgreSQL 1.3 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

DoltgreSQL is a database that does two things at once. It speaks the PostgreSQL wire protocol, so the ordinary psql client and standard PostgreSQL drivers connect to it unchanged. And it stores its tables in a Dolt repository, so the data itself is version controlled: you commit a set of rows, branch the database, change the branch without touching the trunk, diff the two branches row by row, merge one into the other, and read any table as it stood at an earlier commit.

All of that is driven from SQL. There is no separate command line tool to learn and nothing extra to install on the client side, just a handful of functions and system tables.

What is included:

• DoltgreSQL 1.3.3, installed from the official upstream release as a single statically linked binary at /usr/local/bin/doltgres, running under systemd as doltgres.service

• A ready made demo database called versioned that already contains a commit history, a second branch and a divergent change, so a real branch, diff and merge can be run the moment the VM boots

• The stock postgresql-client package, so psql is already present and the PostgreSQL compatibility can be demonstrated with the standard client rather than a bundled one

• A per VM superuser password generated on first boot into a root only credentials file, with no default password at any point

• Loopback only networking: the only port reachable from the network is TCP 22

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

Every VM gets its own database. The captured image contains the DoltgreSQL binary and its configuration but no database at all. On first boot each VM creates its own data directory with its own generated superuser password, so no two deployments share a password, a commit history or a server identity.

What this is not

DoltgreSQL targets the PostgreSQL 15 dialect and is a young project. It is genuinely PostgreSQL compatible for a large and useful subset of SQL, but it is not a drop in replacement for PostgreSQL, and you should check your workload against the gaps before you migrate anything. The Compatibility with PostgreSQL section below lists what is missing in this version. Read it early rather than late.

Prerequisites

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

• Subscription to this listing on Azure Marketplace

• A Network Security Group allowing TCP 22 from your administration network. Nothing else needs to be opened: DoltgreSQL is bound to loopback in the shipped image.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) is sufficient for evaluation and for the small, curated datasets this database suits best. For larger working sets choose a memory rich size such as Standard_E2s_v5 and attach a premium data disk for /var/lib/doltgres.

Deploy the virtual machine

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

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

First boot creates the database and generates the superuser password, so give it a minute before connecting. You can watch it complete with systemctl status doltgres-firstboot.service.

Retrieve your per VM credentials

On the first boot the VM creates its own database and generates its own superuser password. SSH in and read the root only credentials file:

sudo cat /root/doltgres-credentials.txt

The file records the connection details, the generated password, and the two commit hashes that the demo database was seeded with:

doltgres.host=127.0.0.1
doltgres.port=5432
doltgres.user=postgres
doltgres.password=<DOLTGRES_PASSWORD>
doltgres.database=versioned
doltgres.version=1.3.3

This password is unique to this VM. There is no default password to change, and the same file on another deployment of this image will contain a different one.

Confirm the service is healthy

systemctl is-active doltgres.service

Expected output:

active

Confirm that the database is bound to loopback and that SSH is the only port reachable from the network:

ss -lnt

Expected output, abridged:

State  Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0      4096       127.0.0.1:5432      0.0.0.0:*
LISTEN 0      4096         0.0.0.0:22        0.0.0.0:*

DoltgreSQL is on 127.0.0.1:5432, not on the VM's network address.

Connect with the standard PostgreSQL client

The image ships the stock psql client. Connect with the password from the credentials file:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned

To confirm you are talking to a PostgreSQL compatible server over the standard wire protocol:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -c 'SELECT version();'

Expected output:

     version
-----------------
 PostgreSQL 15.5
(1 row)

That is an ordinary PostgreSQL client, unmodified, talking to DoltgreSQL. Your existing drivers and tooling connect the same way.

The demo database

The versioned database ships with a small employees table, already committed:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c 'SELECT * FROM employees ORDER BY id;'

Expected output:

 id | last_name | first_name
----+-----------+------------
  1 | Sehn      | Tim
  2 | Hendriks  | Brian
(2 rows)

View the commit history

Version control state is exposed as ordinary tables in the dolt schema, so you read it with plain SELECT:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c 'SELECT commit_hash, committer, message FROM dolt.log ORDER BY date DESC;'

Expected output:

           commit_hash            | committer |              message
----------------------------------+-----------+------------------------------------
 gsmc6k3j8lloevu2a2alhtbo6iiofhq6 | postgres  | Create employees and seed two rows
 5fap2cbpggsvk0d7onh89ip2jhhnlb0g | postgres  | CREATE DATABASE
 jha1f5ebjgqkv8tcuo5q9tt9imvel5ih | doltgres  | Initialize data repository
(3 rows)

Your commit hashes will differ from these: this VM generated its own history on first boot.

Branches

The image ships a second branch, feature, which already carries a change that main does not:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c 'SELECT name, latest_commit_message FROM dolt.branches ORDER BY name;'

Expected output:

  name   |       latest_commit_message
---------+------------------------------------
 feature | Add Aaron on the feature branch
 main    | Create employees and seed two rows
(2 rows)

To work on a branch, put it in the database name, as <database>/<branch>. This is how DoltgreSQL selects a branch over the PostgreSQL wire protocol, and it is the one piece of syntax that has no PostgreSQL equivalent:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned/feature \
  -c 'SELECT * FROM employees ORDER BY id;'

Expected output:

 id | last_name | first_name
----+-----------+------------
  1 | Sehn      | Tim
  2 | Hendriks  | Brian
  3 | Son       | Aaron
(3 rows)

Three rows on feature, two on main. The branches have genuinely diverged, and neither can see the other's uncommitted work.

Create your own branch with dolt_branch:

SELECT dolt_branch('my-branch');

Note that the version control operations are functions, invoked with SELECT. Calling them with CALL is rejected.

Diff two branches

dolt_diff returns a row by row difference between any two branches or commits:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c "SELECT to_id, to_last_name, to_first_name, from_id, diff_type FROM dolt_diff('main','feature','employees');"

Expected output:

 to_id | to_last_name | to_first_name | from_id | diff_type
-------+--------------+---------------+---------+-----------
     3 | Son          | Aaron         |         | added
(1 row)

One row differs, and it is described as added: the row that exists on feature but not on main. The to_ columns are the state on the second branch and the from_ columns the state on the first, so an edited row shows both and a deleted row shows only from_.

Merge a branch

Merge feature into main with dolt_merge:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c "SELECT dolt_merge('feature');"

Expected output:

                        dolt_merge
-----------------------------------------------------------
 (mffka8l1qi76g45ujnfesjje0isdfpui,1,0,"merge successful")
(1 row)

The returned tuple is the new commit hash, a fast forward flag, a conflict count and a status message. A conflict count above zero means rows changed incompatibly on both sides and must be resolved before the merge can be committed.

main now carries the merged row:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c 'SELECT * FROM employees ORDER BY id;'

Expected output:

 id | last_name | first_name
----+-----------+------------
  1 | Sehn      | Tim
  2 | Hendriks  | Brian
  3 | Son       | Aaron
(3 rows)

Time travel to an earlier commit

AS OF reads a table as it stood at a given commit. Use the base commit hash recorded in your credentials file, which is the commit before the merge:

BASE=$(sudo sed -n 's/^demo\.base_commit=//p' /root/doltgres-credentials.txt)
PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c "SELECT * FROM employees AS OF '$BASE' ORDER BY id;"

Expected output:

 id | last_name | first_name
----+-----------+------------
  1 | Sehn      | Tim
  2 | Hendriks  | Brian
(2 rows)

Two rows at the earlier commit, three at HEAD. The table has not been altered; you are reading an earlier version of it.

AS OF sees committed state only. Changes sitting in a branch's working set that have not been committed are not visible to it.

A per table history view gives the same information from the other direction, one row per version per commit:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d versioned \
  -c 'SELECT commit_hash, id, last_name FROM dolt_history_employees ORDER BY commit_hash, id;'

Expected output:

           commit_hash            | id | last_name
----------------------------------+----+-----------
 gsmc6k3j8lloevu2a2alhtbo6iiofhq6 |  1 | Sehn
 gsmc6k3j8lloevu2a2alhtbo6iiofhq6 |  2 | Hendriks
 mffka8l1qi76g45ujnfesjje0isdfpui |  1 | Sehn
 mffka8l1qi76g45ujnfesjje0isdfpui |  2 | Hendriks
 mffka8l1qi76g45ujnfesjje0isdfpui |  3 | Son
(5 rows)

Commit your own changes

Changes are staged and committed much as they are in Git. dolt.status shows what has changed but is not yet committed:

SELECT * FROM dolt.status;
SELECT dolt_add('-A');
SELECT dolt_commit('-m', 'Describe what changed');

dolt_commit returns the new commit hash. Until you commit, your changes live in the branch's working set: visible to queries on that branch, invisible to AS OF, and not yet part of the history.

Reach the database from your workstation

The database listens on loopback only, and DoltgreSQL 1.3.3 has no host based access rules and no TLS on its SQL listener. An exposed database port would therefore be a plaintext, password only port with no network level controls, so this image does not expose one.

Use an SSH tunnel, which gives you an encrypted channel and reuses the SSH access you already have. Local port 15432 is used here so it cannot clash with a PostgreSQL you may already run locally:

ssh -L 15432:127.0.0.1:5432 azureuser@<vm-public-ip>

Then, from a second terminal on your workstation:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 15432 -U postgres -d versioned

If you must expose the port directly to an application subnet, understand what you are accepting: change listener.host in /etc/doltgres/config.yaml to 0.0.0.0, restart doltgres.service, and open TCP 5432 in the VM's Network Security Group to that subnet only, never to the internet. Credentials and query results will cross that network in plaintext.

Compatibility with PostgreSQL

DoltgreSQL targets the PostgreSQL 15 dialect. A large and useful subset works, including json and jsonb, uuid, numeric, interval, timestamptz, bytea and the serial types; views, composite and enum types; roles and GRANT; row level BEFORE and AFTER triggers; and CREATE FUNCTION and CREATE PROCEDURE with PL/pgSQL.

Not supported in 1.3.3. Check your workload against this list before migrating:

• Types: all geometric types (point, polygon, box, line, lseg, path, circle), all network types (inet, cidr, macaddr, macaddr8), all range and multirange types, all full text search types (tsvector, tsquery), plus money and xml

• Full text search: absent entirely, including to_tsvector, to_tsquery and the @@ operator

• Foreign data wrappers: absent entirely

• Statements: MERGE, SELECT INTO, anonymous DO blocks, SQL level cursors (DECLARE / FETCH / CLOSE), LISTEN / NOTIFY, two phase commit, CREATE POLICY (so no row level security), CREATE RULE, materialized views, SET ROLE, and most ALTER DATABASE, ALTER SCHEMA, ALTER TYPE, ALTER VIEW and ALTER INDEX forms

• Partially supported: locking clauses (FOR UPDATE, FOR SHARE) are not supported; COPY TO and the binary COPY format are not supported; CREATE INDEX supports btree only; partitions are parsed but ignored; COMMENT is accepted and silently ignored; VACUUM is a no op; CASCADE and RESTRICT are not supported on DROP

• Extensions: only uuid-ossp and vector (pgvector) are available. There is no PostGIS and no general extension mechanism

• Replication: DoltgreSQL cannot act as a replication primary for a PostgreSQL database

Because COPY TO is unsupported, treat pg_dump as an inbound migration tool — dumping from PostgreSQL and loading into DoltgreSQL — rather than as a backup route out of DoltgreSQL. Back up by copying /var/lib/doltgres/databases with the service stopped.

Security posture

• No default credential at any point. DoltgreSQL's documented default superuser is postgres with the password password, and that password can only be replaced at the moment the database is first created. This image therefore ships no database at all: the first boot of each VM creates one with a generated password already in place, so the published default never exists on your VM. First boot verifies this by attempting to log in with password and refusing to complete if it succeeds.

• The password never reaches a command line or a log. It is passed to the server on standard input, and is written only to /root/doltgres-credentials.txt, mode 0600, owned by root.

• The database service will not start before first boot has finished, so there is no window in which a partially initialised database is reachable.

• Loopback only. The database binds 127.0.0.1. The only port reachable from the network is TCP 22.

• Per VM identity. Each VM has its own data directory, its own commit history and its own SSH host keys and machine ID.

• The service runs as an unprivileged doltgres account with NoNewPrivileges, PrivateTmp, ProtectHome and a restricted writable path.

• SSH: root login disabled outright, password authentication disabled.

Operations

Service control:

sudo systemctl status doltgres.service
sudo systemctl restart doltgres.service
sudo journalctl -u doltgres.service -n 50

Configuration lives at /etc/doltgres/config.yaml. Restart the service after editing it.

Data lives at /var/lib/doltgres/databases. Back it up with the service stopped.

Rotate the superuser password (one line), then update the credentials file to match:

PGPASSWORD=<DOLTGRES_PASSWORD> psql -h 127.0.0.1 -p 5432 -U postgres -d postgres -c "ALTER USER postgres WITH PASSWORD '<new>'"

Upgrades. Security updates for the operating system are applied automatically. DoltgreSQL itself is pinned to the version this image was built with; upgrade it deliberately, after taking a copy of the data directory.

Support

cloudimg images come with 24/7 support. Contact us at support@cloudimg.co.uk with your VM size, region and the output of systemctl status doltgres.service.