https://github.com/hexacluster/pg_dbms_metadata/
pg_dbms_metadata is a PostgreSQL extension to extract DDL of database objects in a way compatible to Oracle DBMS_METADATA package. This extension serves a dual purpose—not only does it provide compatibility with the Oracle DBMS_METADATA package, but it also establishes a systematic approach to programmatically retrieve DDL for objects. You now have the flexibility to generate DDL for an object either from a plain SQL query or from PL/pgSQL code. This also enables the extraction of DDL using any client that can execute plain SQL queries. These features distinguishes it from standard methods like pg_dump.
The PostgreSQL RPM repository uses PostgreSQL version numbers in the RPM packages. For example pg_dbms_metadata for PostgreSQL 17 is named as pg_dbms_metadata_17. Similarly pg_dbms_metadata for PostgreSQL 15 is named as pg_dbms_metadata_15.
Install PGDG repository from: https://yum.postgresql.org/repopackages/
| OS | Command |
|---|---|
| RHEL / Rocky Linux / AlmaLinux / Fedora | dnf install pg_dbms_metadata_17 (or change the version as described above) |
Connect to the database that you want to create the extension as superuser and run:
CREATE EXTENSION pg_dbms_metadata;
| PostgreSQL version(s) | OS version | Arch | Status |
|---|---|---|---|
| 17,16,15,14,13 | RHEL / Rocky Linux / AlmaLinux 9 | x86_64, aarch64 | Available |
| 17,16,15,14,13 | RHEL / Rocky Linux / AlmaLinux 8 | x86_64, aarch64 | Available |
| 17,16,15,14,13 | Fedora Linux | x86_64 | Available |