lolor

lolor is an extension that makes Postgres' Large Objects compatible with Logical Replication.

Table of Contents

• lolor Overview
• Building and Installing lolor
• Basic Configuration
• Using lolor
• Limitations
• Using pg_upgrade with lolor
• Release Notes

PostgreSQL supports large objects as related chunks as described in the pg_largeobject table. Large objects provide stream-style access to user data stored in a special large-object structure in the catalog. Large objects stored in catalog tables require special handling during replication; the lolor extension allows for the storage of large objects in non-catalog tables, aiding in replication of large objects.

lolor creates and manages large object related tables in the lolor schema:

lolor.pg_largeobject
lolor.pg_largeobject_metadata

PostgreSQL large objects allow you to store huge files within the database. Each large object is recognised by an OID that is assigned at the time of its creation. lolor stores objects in smaller segments within a separate system table and generates associated OIDs for large objects that are distinct from those of native large objects.

Use of the lolor extension requires Postgres 16 or newer.

Building and Installing lolor

You can also compile and install the extension from the source code, with the same guidelines as any other Postgres extension constructed using PGXS. Make sure that your PATH environment variable includes the directory where pg_config (under your Postgres installation) is located.

export PATH=/usr/pgsql-17/bin:$PATH

# compile
make USE_PGXS=1
# install, might be requiring sudo for the installation step
make USE_PGXS=1 install

After installing the lolor extension, connect to your Postgres database and create the extension with the command:

CREATE EXTENSION lolor;

Configuring lolor

You must set the lolor.node parameter before using the extension. The value can be from 1 to 2^28; the value is used to help in generation of new large object OID.

lolor.node = 1

You can also change the search_path to pick large object related tables from the lolor schema:

set search_path=lolor,"$user",public,pg_catalog

Any existing methods in pg_catalog.lo_* are renamed to pg_catalog.lo_*_orig, and new versions of these methods are introduced. If you remove the extension, the renamed pg_catalog.lo_*_orig functions are restored to their initial names.

While using pgedge replication with large objects, you must have the tables pg_largeobject and pg_largeobject_metadata in your replication set; use the following commands to add the tables:

./pgedge spock repset-add-table spock_replication_set 'lolor.pg_largeobject' lolor_db
./pgedge spock repset-add-table spock_replication_set 'lolor.pg_largeobject_metadata' lolor_db

Migrating large objects

Migration from native to lolor is manual; migration back is automatic on DROP EXTENSION so no objects are ever lost.

Migrate existing native large objects into lolor storage (requires superuser):

CREATE EXTENSION lolor;
SELECT lolor.migrate_from_native();

Reverse migration happens automatically when the extension is dropped, or can be triggered manually:

SELECT lolor.migrate_to_native();  -- manual
DROP EXTENSION lolor;              -- automatic

Both directions preserve original OIDs, owners, ACLs, and data.

When the spock extension is installed, both migration functions run under spock.repair_mode(), so the row-shuffling migration DML is not replicated to other nodes. This is essential for migrate_to_native(): its deletes from the lolor tables would otherwise replicate while the native re-creation stayed local, destroying large objects on the other nodes.

Without spock, the migration DML cannot be excluded from logical decoding, so both functions refuse to migrate while logical replication slots exist in the database: migrate_from_native() raises a WARNING and returns -1 without doing anything (0 is reserved for "nothing to migrate"), while migrate_to_native() (and therefore DROP EXTENSION lolor) raises an ERROR. Drop all logical replication slots before migrating; merely disabling a subscription is not sufficient, since its slot retains the changes and delivers them when replication resumes.

Limitations

• Native large object functionality cannot be used while you are using the lolor extension.
• lolor does not support the following statements: ALTER LARGE OBJECT, GRANT ON LARGE OBJECT, COMMENT ON LARGE OBJECT, and REVOKE ON LARGE OBJECT.
• Large object migration is node-local. Native large objects live in pg_catalog.pg_largeobject, which is never replicated, so each node holds an independent set and migrate_from_native() migrates only the local node's objects; with spock installed, the migration DML runs in repair mode and is not replicated. Run the migration on every node that holds native large objects — for example with spock.replicate_ddl('SELECT lolor.migrate_from_native()'), which queues the command so that each node executes it locally. Migrated objects keep their original native OIDs, which are not node-encoded: if different nodes hold different objects under the same OID, the nodes' lolor contents will diverge and later replicated changes to those objects can conflict. Newly created large objects are collision-free, since new OIDs are node-encoded via lolor.node and checked against existing rows.