Upgrading YugabyteDB from v2024.2 to v2025.2 includes a YSQL major-version upgrade from PostgreSQL 11 to PostgreSQL 15. YugabyteDB Anywhere runs a precheck to identify incompatible database objects before allowing the upgrade to proceed.
During the upgrade precheck, you may encounter a message similar to the following:
Checking for extensions in conflicting schemas
In database: appdb
pgcrypto installed in pg_catalog schema
In database: reportingdb
pgcrypto installed in pg_catalog schema
Your installation contains the 'pgcrypto' extension in the
conflicting 'pg_catalog' schema.
To proceed with the upgrade, please uninstall the extension
using DROP EXTENSION, and reinstall it into a different schema
(for example, public).
pgcrypto schema-placement finding reported during a YugabyteDB v2024.2 to v2025.2 upgrade precheck. Any other precheck findings must be handled separately.Why Is pgcrypto in pg_catalog a Problem?
In the PostgreSQL 11–based YugabyteDB v2024.2 release series, gen_random_uuid() is supplied by the pgcrypto extension.
When pgcrypto is installed in pg_catalog, the extension owns this function:
pg_catalog.gen_random_uuid()
PostgreSQL 15 provides its own built-in function with the same name and signature. YugabyteDB tracks this as an upgrade conflict and requires pgcrypto to be removed from pg_catalog before the upgrade.
How Could pgcrypto Have Been Installed in pg_catalog?
It may have been explicitly installed there:
CREATE EXTENSION pgcrypto WITH SCHEMA pg_catalog;
It might also have arrived through:
- ● A database template that already contained the extension
- ● A database clone
- ● A schema restore
- ● An application deployment script
- ● A migration from another environment
The Fix – Step 1: Confirm the Extension Placement
Connect to each database reported by the precheck and run:
SELECT
e.extname,
e.extversion,
n.nspname AS installed_schema,
e.extrelocatable,
pg_get_userbyid(e.extowner) AS extension_owner
FROM pg_extension e
JOIN pg_namespace n
ON n.oid = e.extnamespace
WHERE e.extname = 'pgcrypto';
An affected database may show:
.extname | extversion | installed_schema | extrelocatable | extension_owner
----------+------------+------------------+----------------+-----------------
pgcrypto | 1.3 | pg_catalog | t | yugabyte
The extrelocatable column indicates whether an extension generally supports being moved to another schema. PostgreSQL defines true as meaning the extension is relocatable.
Why Not Use ALTER EXTENSION SET SCHEMA?
For an extension in an ordinary schema, a relocatable extension can normally be moved with:
ALTER EXTENSION pgcrypto SET SCHEMA public;
PostgreSQL documents SET SCHEMA as the standard method for moving the objects belonging to a relocatable extension.
However, this does not work when pgcrypto is currently installed in the system-managed pg_catalog schema:
yugabyte=# ALTER EXTENSION pgcrypto SET SCHEMA public;
ERROR: cannot remove dependency on schema pg_catalog because it is a system object
Therefore, even though pgcrypto reports:
extrelocatable = true
it cannot be moved out of pg_catalog with ALTER EXTENSION ... SET SCHEMA.
DROP EXTENSION and, when it is still needed, recreate it in public. The Fix – Step 2: Determine Whether the Application Uses pgcrypto
Do not automatically recreate the extension. First determine why it is installed.
In addition to gen_random_uuid(), pgcrypto provides functions for hashing, password hashing, random-byte generation, and encryption, including:
digest()
hmac()
crypt()
gen_salt()
gen_random_bytes()
pgp_sym_encrypt()
pgp_sym_decrypt()
pgp_pub_encrypt()
pgp_pub_decrypt()
encrypt()
decrypt()
PostgreSQL 15 replaces the need for pgcrypto only for gen_random_uuid(). It does not replace the extension’s other cryptographic functions.
pgcrypto or another extension?
See
Lists Functions Created by an Extension
for a catalog query that lists each extension-owned function and the schema in which it was created.Find Stored Dependencies
Run the following query while the database is still on v2024.2:
WITH pgcrypto_members AS (
SELECT
d.classid,
d.objid,
d.objsubid
FROM pg_depend d
JOIN pg_extension e
ON e.oid = d.refobjid
AND d.refclassid = 'pg_extension'::regclass
WHERE e.extname = 'pgcrypto'
AND d.deptype = 'e'
)
SELECT DISTINCT
pg_describe_object(
d.classid,
d.objid,
d.objsubid
) AS dependent_object,
pg_describe_object(
d.refclassid,
d.refobjid,
d.refobjsubid
) AS referenced_pgcrypto_object
FROM pg_depend d
JOIN pgcrypto_members referenced_member
ON referenced_member.classid = d.refclassid
AND referenced_member.objid = d.refobjid
AND referenced_member.objsubid = d.refobjsubid
LEFT JOIN pgcrypto_members dependent_member
ON dependent_member.classid = d.classid
AND dependent_member.objid = d.objid
AND dependent_member.objsubid = d.objsubid
WHERE dependent_member.objid IS NULL
ORDER BY
dependent_object,
referenced_pgcrypto_object;
A common dependency is a column default:
CREATE TABLE application.orders (
order_id uuid DEFAULT gen_random_uuid()
);
The table description may display the expression without a schema:
yugabyte=# \d application.orders
Table "application.orders"
Column | Type | Collation | Nullable | Default
----------+------+-----------+----------+-------------------
order_id | uuid | | | gen_random_uuid()
The stored default is still bound to the specific function that existed when the default was created.
Check Application-Issued SQL
Catalog dependencies do not identify SQL submitted directly by an application. Search application code, deployment scripts, stored SQL files, and ORM definitions for calls such as:
gen_random_uuid()
digest()
hmac()
crypt()
gen_salt()
gen_random_bytes()
pgp_sym_encrypt()
pgp_sym_decrypt()
Pay particular attention to explicitly qualified calls:
pg_catalog.gen_random_uuid()
pg_catalog.digest(...)
pg_catalog.crypt(...)
Those calls will stop working on v2024.2 after pgcrypto is removed from pg_catalog.
When pg_stat_statements is enabled, it may provide additional evidence:
SELECT
calls,
query
FROM pg_stat_statements
WHERE query ~*
'\m(gen_random_uuid|digest|hmac|crypt|gen_salt|gen_random_bytes|pgp_sym_encrypt|pgp_sym_decrypt)\s*\('
ORDER BY calls DESC;
pgcrypto is unused: It means no stored database objects depend on the extension. The application may still call its functions at runtime. The Fix – Step 3: Choose the Correct Action
Recreating pgcrypto in public acts as a compatibility bridge. It keeps the required functions available while the universe is still running the PostgreSQL 11–based release and during the online upgrade, when application reads and writes can continue.
The Fix – Step 4: Prepare for the Change
Take a schema-only backup of each affected database:
/path/to/yugabyte/bin/ysql_dump \
-h 127.0.0.1 \
-p 5433 \
-U yugabyte \
-d appdb \
--schema-only \
--file appdb_before_pgcrypto_fix.sql
Record the extension owner and version:
SELECT
e.extname,
e.extversion,
n.nspname AS installed_schema,
pg_get_userbyid(e.extowner) AS extension_owner
FROM pg_extension e
JOIN pg_namespace n
ON n.oid = e.extnamespace
WHERE e.extname = 'pgcrypto';
The account that runs CREATE EXTENSION becomes the extension owner, so recreate it using the intended owner account.
Script any table defaults, views, stored functions, or other objects returned by the dependency query.
For example, temporarily remove a UUID default:
ALTER TABLE application.orders
ALTER COLUMN order_id DROP DEFAULT;
Also update application calls that explicitly reference the old schema. For example:
-- Old call
SELECT pg_catalog.digest('value', 'sha256');
-- Transitional call after reinstalling pgcrypto
SELECT public.digest('value', 'sha256');
The Fix – Step 5: Drop and Recreate pgcrypto
Connect to the affected database:
\c appdb
Drop the conflicting extension:
DROP EXTENSION pgcrypto;
CASCADE: If DROP EXTENSION reports dependent objects, stop and handle them explicitly. Using CASCADE could remove table defaults, views, stored functions, or other application objects. If the application still needs pgcrypto, recreate it in public:
CREATE EXTENSION pgcrypto WITH SCHEMA public;
CREATE EXTENSION installs the extension into the current database and places its objects in the specified schema when the extension permits relocation.
Restore the UUID default using the new extension schema:
ALTER TABLE application.orders
ALTER COLUMN order_id
SET DEFAULT public.gen_random_uuid();
Restore any other dependent objects and custom privileges, and then test the functions used by the application:
SELECT public.gen_random_uuid();
SELECT encode(
public.digest('YugabyteDB', 'sha256'),
'hex'
);
SELECT public.crypt(
'test-password',
public.gen_salt('bf')
);
The Fix – Step 6: Verify the New Placement
Check the extension:
\dx pgcrypto
Expected result:
. Name | Version | Schema | Description
-----------+---------+--------+-------------------------
pgcrypto | 1.3 | public | cryptographic functions
Confirm that no conflicting installation remains:
SELECT
current_database() AS database_name,
e.extname,
n.nspname AS installed_schema
FROM pg_extension e
JOIN pg_namespace n
ON n.oid = e.extnamespace
WHERE e.extname = 'pgcrypto'
AND n.nspname = 'pg_catalog';
Expected result:
(0 rows)
Extensions are installed independently in each database, so repeat the remediation for every database identified by the precheck.
After all affected databases have been corrected, rerun the YugabyteDB Anywhere upgrade precheck.
What Happens After the Upgrade to v2025.2?
If pgcrypto was recreated in public, two functions named gen_random_uuid() will exist after the upgrade:
SELECT
p.oid,
n.nspname AS function_schema,
p.proname AS function_name,
pg_get_function_identity_arguments(p.oid) AS arguments,
COALESCE(e.extname, 'PostgreSQL core') AS provided_by
FROM pg_proc p
JOIN pg_namespace n
ON n.oid = p.pronamespace
LEFT JOIN pg_depend d
ON d.classid = 'pg_proc'::regclass
AND d.objid = p.oid
AND d.refclassid = 'pg_extension'::regclass
AND d.deptype = 'e'
LEFT JOIN pg_extension e
ON e.oid = d.refobjid
WHERE p.proname = 'gen_random_uuid'
AND p.pronargs = 0
ORDER BY n.nspname;
Example result:
. oid | function_schema | function_name | arguments | provided_by
-------+-----------------+-----------------+-----------+-----------------
3432 | pg_catalog | gen_random_uuid | | PostgreSQL core
16598 | public | gen_random_uuid | | pgcrypto
(2 rows)
The OIDs will vary between databases.
This result is expected. The functions no longer conflict because they are in different schemas.
Under the normal schema search path, a new unqualified call:
SELECT gen_random_uuid();
resolves to:
pg_catalog.gen_random_uuid()
PostgreSQL implicitly searches pg_catalog before ordinary schemas when it is not explicitly positioned elsewhere in search_path.
However, a stored default recreated before the upgrade as:
DEFAULT public.gen_random_uuid()
remains dependent on the extension function in public.
pg_catalog.gen_random_uuid() after the upgrade does not change an existing default or stored expression that is bound to public.gen_random_uuid(). Keep or Remove pgcrypto After the Upgrade
Keep pgcrypto installed when the application uses its hashing, password-hashing, random-byte, or encryption functions.
If the extension was used only for UUID generation, migrate stored dependencies to the PostgreSQL 15 core function:
ALTER TABLE application.orders
ALTER COLUMN order_id
SET DEFAULT pg_catalog.gen_random_uuid();
Search the application for explicit references to:
public.gen_random_uuid()
Then rerun the dependency query from Step 2. When no stored objects or application calls require the extension, remove it without CASCADE:
DROP EXTENSION pgcrypto;
In PostgreSQL 15, the pgcrypto implementation of gen_random_uuid() is retained only as an obsolete wrapper around the core function of the same name.
Resources
Final Takeaway
The upgrade precheck detects pgcrypto in pg_catalog because its extension-owned gen_random_uuid() conflicts with the PostgreSQL 15 built-in function.
Although pgcrypto is marked relocatable, ALTER EXTENSION pgcrypto SET SCHEMA public cannot move it out of the system-managed pg_catalog schema.
The correct remediation is to:
- 1. Determine whether the application uses
pgcrypto. - 2. Remove stored dependencies temporarily.
- 3. Drop the extension without
CASCADE. - 4. Recreate it in
publiconly when it is still needed. - 5. Rerun the upgrade precheck.
- 6. After upgrading, keep
pgcryptofor its other cryptographic functions or remove it after migrating UUID dependencies topg_catalog.gen_random_uuid().
Have Fun!
