What is PgDD?
The PostgreSQL Data Dictionary (PgDD
) makes it easy to write simple SQL
queries to learn about your database. PgDD is an in-database solution
providing introspection via standard SQL query syntax. The goal is a
usable data dictionary directly available to all users of a PostgreSQL database.
This extension does not provide anything you cannot get directly from the
internal Postgres catalog tables (e.g. pg_catalog.pg_class
).
PgDD makes it easy to get to standard, default information most analysts,
developers, and even DBAs need to access from time to time.
Compatibility
PgDD has been tested to work for PostgreSQL 12 through 16.
This extension is built using the
pgrx framework. Postgres
version support is expected to provide 5 years of support, but is ultimately
determined by what pgrx
supports.
Installers are provided for a small number of Debian / Ubuntu systems using the Docker build system documented under create-installer.md.
Why not use ______
?
Why use PgDD when you could just use psql
's slash commands (e.g. \d
)
or directly query the tables/views in pg_catalog
?
If those tools provide what you need, great! PgDD was created to provide database insights without requiring a specific tool. It also attempts to be easy for end users that are not DBAs.
See the Quick Start and the Query guide for next steps.
Quick Start
Install from binary
Binaries for supported Postgres versions are made available for each release. See the individual release from the releases page for the full list of binaries. This currently includes binaries for two main LTS supported OS's using the AMD64 architecture. The latest Ubuntu LTS (currently Jammy, 22.04) and the "PostGIS" image (currently Debian 11). The PostGIS image is provided to allow inclusion in the PgOSM Flex project's Docker image.
Download and install for PgDD 0.5.2 for Postgres 16 on Ubuntu 22.04.
wget https://github.com/rustprooflabs/pgdd/releases/download/0.5.2/pgdd_0.5.2_focal_pg16_amd64.deb
sudo dpkg -i ./pgdd_0.5.2_jammy_pg16_amd64.deb
Create the extension in your database.
CREATE EXTENSION pgdd;
Database overview
SELECT * FROM dd.database;
┌─[ RECORD 1 ]────┬───────────┐
│ oid │ 2853066 │
│ db_name │ pgosm_dev │
│ db_size │ 2325 MB │
│ schema_count │ 16 │
│ table_count │ 107 │
│ size_in_tables │ 2294 MB │
│ view_count │ 27 │
│ size_in_views │ 11 MB │
│ extension_count │ 8 │
└─────────────────┴───────────┘
Views
Query dd.views
within the dd
schema (s_name
) to see the other PgDD views
included.
SELECT s_name, v_name, description
FROM dd.views
WHERE s_name = 'dd'
;
┌────────┬────────────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ s_name │ v_name │ description │
╞════════╪════════════════════╪════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ dd │ tables │ Data dictionary view: Lists tables, excluding system tables. │
│ dd │ schemas │ Data dictionary view: Lists schemas, excluding system schemas. │
│ dd │ views │ Data dictionary view: Lists views, excluding system views. │
│ dd │ columns │ Data dictionary view: Lists columns, excluding system columns. │
│ dd │ functions │ Data dictionary view: Lists functions, excluding system functions. │
│ dd │ partition_parents │ Data dictionary view: Lists parent partition tables with aggregate details about child partitions. │
│ dd │ partition_children │ Data dictionary view: Lists individual partitions (children) of partitioned tables. │
│ dd │ database │ Data dictionary view: Provides basic statistics for the current database. │
└────────┴────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────┘
Query PgDD
See the Query PgDD section for more examples.
Query PgDD
Connect to your database using your favorite SQL client. This could be psql, DBeaver, PgAdmin, or Python code... all you need is a place to execute SQL code and see the results.
The main interaction with PgDD is through the views in the dd
schema.
Database overview
SELECT * FROM dd.database;
┌─[ RECORD 1 ]────┬───────────┐
│ oid │ 2853066 │
│ db_name │ pgosm_dev │
│ db_size │ 2325 MB │
│ schema_count │ 16 │
│ table_count │ 107 │
│ size_in_tables │ 2294 MB │
│ view_count │ 27 │
│ size_in_views │ 11 MB │
│ extension_count │ 8 │
└─────────────────┴───────────┘
The dd.views
query can be used to query the views within a database.
SELECT s_name, v_name, description
FROM dd.views
WHERE s_name = 'dd'
;
┌────────┬────────────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ s_name │ v_name │ description │
╞════════╪════════════════════╪════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ dd │ tables │ Data dictionary view: Lists tables, excluding system tables. │
│ dd │ schemas │ Data dictionary view: Lists schemas, excluding system schemas. │
│ dd │ views │ Data dictionary view: Lists views, excluding system views. │
│ dd │ columns │ Data dictionary view: Lists columns, excluding system columns. │
│ dd │ functions │ Data dictionary view: Lists functions, excluding system functions. │
│ dd │ partition_parents │ Data dictionary view: Lists parent partition tables with aggregate details about child partitions. │
│ dd │ partition_children │ Data dictionary view: Lists individual partitions (children) of partitioned tables. │
│ dd │ database │ Data dictionary view: Provides basic statistics for the current database. │
└────────┴────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────┘
PgDD views wrap around functions with the same name and enforce
WHERE NOT system_object
. Query the functions to includesystem_object
results. e.g.SELECT s_name, v_name FROM dd.views() WHERE system_object;
Schema
The highest level of querying provided by pgdd
is at the schema level.
This provides counts of tables, views and functions along with the size on disk of the objects within the schema.
SELECT s_name, table_count, view_count, function_count,
size_plus_indexes, description
FROM dd.schemas
WHERE s_name = 'dd';
Yields results such as this.
┌─[ RECORD 1 ]──────┬────────────────────────────────────────────────────────────────────────────────┐
│ s_name │ dd │
│ table_count │ 3 │
│ view_count │ 5 │
│ function_count │ 6 │
│ size_plus_indexes │ 144 kB │
│ description │ Schema for Data Dictionary objects. See https://github.com/rustprooflabs/pgdd │
└───────────────────┴────────────────────────────────────────────────────────────────────────────────┘
Tables
The dd.tables
view to examine tables created and populated by pgbench
.
SELECT t_name, size_pretty, rows, bytes_per_row
FROM dd.tables
WHERE s_name = 'public'
AND t_name LIKE 'pgbench%'
ORDER BY size_bytes DESC;
┌──────────────────┬─────────────┬──────────┬───────────────┐
│ t_name │ size_pretty │ rows │ bytes_per_row │
╞══════════════════╪═════════════╪══════════╪═══════════════╡
│ pgbench_accounts │ 1281 MB │ 10000000 │ 134 │
│ pgbench_tellers │ 80 kB │ 1000 │ 82 │
│ pgbench_branches │ 40 kB │ 100 │ 410 │
│ pgbench_history │ 0 bytes │ 0 │ ¤ │
└──────────────────┴─────────────┴──────────┴───────────────┘
Columns
SELECT source_type, s_name, t_name, c_name, data_type
FROM dd.columns
WHERE data_type LIKE 'int%'
;
┌─────────────┬────────┬─────────────┬────────────────┬───────────┐
│ source_type │ s_name │ t_name │ c_name │ data_type │
╞═════════════╪════════╪═════════════╪════════════════╪═══════════╡
│ table │ dd │ meta_schema │ meta_schema_id │ int8 │
│ table │ dd │ meta_table │ meta_table_id │ int8 │
│ table │ dd │ meta_column │ meta_column_id │ int8 │
│ view │ dd │ schemas │ table_count │ int8 │
│ view │ dd │ schemas │ view_count │ int8 │
│ view │ dd │ schemas │ function_count │ int8 │
│ view │ dd │ schemas │ size_bytes │ int8 │
│ view │ dd │ tables │ size_bytes │ int8 │
│ view │ dd │ tables │ rows │ int8 │
│ view │ dd │ tables │ bytes_per_row │ int8 │
│ view │ dd │ views │ rows │ int8 │
│ view │ dd │ views │ size_bytes │ int8 │
│ view │ dd │ columns │ position │ int8 │
└─────────────┴────────┴─────────────┴────────────────┴───────────┘
Functions
SELECT s_name, f_name, argument_data_types, result_data_types
FROM dd.functions
;
Partitioned tables
There are two views, dd.partition_parents
and dd.partition_children
to provide
partition-focused details. Will display partitions for both
declarative partitions and inheritance based partitions
With the test data in this project for declarative partitions.
SELECT *
FROM dd.partition_parents
WHERE s_name = 'pgdd_test'
;
┌───────┬───────────┬────────┬────────────────┬────────────┬────────────┬─────────────┬────────────────────┬──────┬────────────────────┬───────────────────────────┬────────────────────┐
│ oid │ s_name │ t_name │ partition_type │ partitions │ size_bytes │ size_pretty │ size_per_partition │ rows │ rows_per_partition │ partitions_never_analyzed │ partitions_no_data │
╞═══════╪═══════════╪════════╪════════════════╪════════════╪════════════╪═════════════╪════════════════════╪══════╪════════════════════╪═══════════════════════════╪════════════════════╡
│ 25090 │ pgdd_test │ parent │ declarative │ 3 │ 40960 │ 40 kB │ 13 kB │ 15 │ 5 │ 0 │ 1 │
└───────┴───────────┴────────┴────────────────┴────────────┴────────────┴─────────────┴────────────────────┴──────┴────────────────────┴───────────────────────────┴────────────────────┘
Details for each child partition, including calculated percentages of the single partition against the totals for the parent partition.
SELECT *
FROM dd.partition_children
WHERE s_name = 'pgdd_test'
;
┌───────┬───────────┬─────────────┬────────────┬──────────────────┬──────┬────────────┬─────────────┬───────────────────┬───────────────┬───────────────────────────┬────────────────────────────┐
│ oid │ s_name │ t_name │ parent_oid │ parent_name │ rows │ size_bytes │ size_pretty │ size_plus_indexes │ bytes_per_row │ percent_of_partition_rows │ percent_of_partition_bytes │
╞═══════╪═══════════╪═════════════╪════════════╪══════════════════╪══════╪════════════╪═════════════╪═══════════════════╪═══════════════╪═══════════════════════════╪════════════════════════════╡
│ 25095 │ pgdd_test │ child_0_10 │ 25090 │ pgdd_test.parent │ 9 │ 16384 │ 16 kB │ 32 kB │ 1820 │ 0.6000 │ 0.4000 │
│ 25109 │ pgdd_test │ child_20_30 │ 25090 │ pgdd_test.parent │ 0 │ 8192 │ 8192 bytes │ 16 kB │ ¤ │ 0.0000 │ 0.2000 │
│ 25102 │ pgdd_test │ child_10_20 │ 25090 │ pgdd_test.parent │ 6 │ 16384 │ 16 kB │ 32 kB │ 2731 │ 0.4000 │ 0.4000 │
└───────┴───────────┴─────────────┴────────────┴──────────────────┴──────┴────────────┴─────────────┴───────────────────┴───────────────┴───────────────────────────┴────────────────────────────┘
Permissions for PgDD
Create Read-only group role to assign to users that need access to query (read-only) the PgDD objects.
CREATE ROLE dd_read WITH NOLOGIN;
COMMENT ON ROLE dd_read IS 'Group role to grant read-only permissions to PgDD views.';
GRANT USAGE ON SCHEMA dd TO dd_read;
GRANT SELECT ON ALL TABLES IN SCHEMA dd TO dd_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA dd GRANT SELECT ON TABLES TO dd_read;
Access can now be granted to other users using:
GRANT dd_read TO <your_login_user>;
For read-write access.
CREATE ROLE dd_readwrite WITH NOLOGIN;
COMMENT ON ROLE dd_readwrite IS 'Group role to grant write permissions to PgDD objects.';
GRANT dd_read TO dd_readwrite;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA dd TO dd_readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA dd GRANT INSERT, UPDATE, DELETE ON TABLES TO dd_readwrite;
This access can be granted using:
GRANT dd_readwrite TO <your_login_user>;
Upgrade PgDD
Upgrading PgDD versions currently requires DROP/CREATE
to upgrade.
This will not change until
pgrx #121 is resolved.
The first thing to do is check if you have data stored in the dd.meta_*
tables. Run the following query, if all three (3) rows return row_count = 0
you can simply drop and recreate the extension.
SELECT 'meta_table' AS src, COUNT(*) AS row_count
FROM dd.meta_table
WHERE s_name <> 'dd'
UNION
SELECT 'meta_column' AS src, COUNT(*) AS row_count
FROM dd.meta_column
WHERE s_name <> 'dd'
UNION
SELECT 'meta_schema' AS src, COUNT(*) AS row_count
FROM dd.meta_schema
WHERE s_name <> 'dd'
;
To drop and recreate the extension, run the following queries.
DROP EXTENSION pgdd;
CREATE EXTENSION pgdd;
Upgrade with data in dd
tables
If custom attributes are stored in the dd
tables you will need to use
pg_dump
to export the data and reload after recreating the extension
with pgrx. If any of the three (3) queries below return a count > 0
this applies to you.
Dump data from dd
tables
Set the target database name in the $DB_NAME
variable for later commands
to use.
export DB_NAME=pgosm_dev
Run pg_dump
against the target database, drop and create the extension,
and reload the data to the dd.meta_*
tables.
pg_dump -d $DB_NAME \
--schema dd --data-only \
-f ~/tmp/dd_upgrade_data.sql
psql -d $DB_NAME -c "DROP EXTENSION pgdd; CREATE EXTENSION pgdd;"
psql -d $DB_NAME -f ~/tmp/dd_upgrade_data.sql
Create Installer
This page covers two methods that can be used to build binary installers for PgDD. This documents how the pre-built binaries are provided for each versions and explains how end users can extend this to their operating systems and architectures.
The two methods that can be used to build binaries are the Docker build system
and manually installing and using pgrx
.
PgDD's installers are specific to three (3) details:
- CPU Architecture
- Operating System version
- Postgres version
Architecture supported
Project maintainers currently only provide pre-built installers for the AMD architecture. This matches the hardware easily available.
Operating Systems Supported
Pre-built binaries are provided for recent operating systems, typically ones in Long Term Support (LTS). Currently building for:
- Ubuntu 23.04
- Ubuntu 22.04
- Debian 11 (named PostGIS internally)
Docker Build System
The Docker build method uses OS specific Dockerfile
to provide one binary
installer for each supported Postgres version. The CPU architecture it is
built for matches the hardware building the installer.
The Docker build system is the best approach to use when the appropriate
Dockerfile
already exists.
The Docker build method was originally based on ZomboDB's build system.
To generate the full suite of binaries change into the ./build
directory
and run build.sh
. This currently creates 15 total binary installers for
3 different OSs (Postgres 12 - 16).
cd build/
time bash ./build.sh
Individual installers can be found under ./target/artifacts
. A package of
all installers is saved to ./build/pgdd-binaries.tar.gz
.
Tagged versions of PgDD include LTS OS binaries with their release notes.
Customize Docker Build system
The Docker build system can be adjusted locally to build a binary for a specific Postgres version and/or specific OS.
The ./build/build.sh
script has the logic to be adjusted to control this.
The Postgres versions can be altered manually by commenting out the line
with all versions and uncommenting the line with a specific Postgres version.
The two lines in the script are shown below.
PG_VERS=("pg12" "pg13" "pg14" "pg15" "pg16")
#PG_VERS=("pg16")
To only build for Postgres on a single OS, add a grep <osname
command to the
loop logic. The original file that runs for all OSs with Dockerfiles looks like
the following line.
for image in `ls docker/ ` ; do
To build for only lunar
(Ubuntu 23.04) add | grep lunar
as shown in the
following example.
for image in `ls docker/ | grep lunar ` ; do
Operating Systems supported
Pull requests are welcome for new Dockerfiles to add support for additional operating
systems. These PRs should operate in a similar manner (where possible) to
existing Dockerfile
for easiest maintenance.
Create Binary Installer w/out Docker
The following steps walk through creating a package on a typical
Ubuntu based system with Postgres 15. These manual instructions can be used
when you do not want to use the Docker based build system under ./build/
.
Prerequisites
The main perquisites for PgDD are pgrx
and its dependencies.
Install prereqs and ensure PostgreSQL dev tools are installed.
See the cargo pgrx documentation for more information on using
pgrx
.
sudo apt install postgresql-server-dev-all libreadline-dev zlib1g-dev curl \
libssl-dev llvm-dev libclang-dev clang \
graphviz
Install Rust and pgrx.
curl https://sh.rustup.rs -sSf | sh -s -- -y
source $HOME/.cargo/env
Install cargo-pgrx
regularly (see dev steps below for non-standard install).
cargo install --locked cargo-pgrx
Install cargo-deb
used for packaging binaries.
cargo install cargo-deb
Initialize pgrx
. Need to run this after install AND occasionally to get updates
to Postgres versions or glibc updates. Not typically required to follow pgrx
developments.
cargo pgrx init
The fpm
step requires the fpm
Ruby gem.
sudo apt install ruby-rubygems
sudo gem i fpm
Of course, the PgDD project itself is required.
mkdir ~/git
cd ~/git
git clone https://github.com/rustprooflabs/pgdd.git
cd ~/git/pgdd
Create package
Timing note:
cargo pgrx package
takes ~ 2 minutes on my main dev machine.
cargo pgrx package --pg-config /usr/lib/postgresql/15/bin/pg_config
cd target/release/pgdd-pg15/
find ./ -name "*.so" -exec strip {} \;
OUTFILE=pgdd.deb
rm ${OUTFILE} || true
fpm \
-s dir \
-t deb -n pgdd \
-v 0.5.0 \
--deb-no-default-config-files \
-p ${OUTFILE} \
-a amd64 \
.
sudo dpkg -i --force-overwrite ./pgdd.deb
pgrx Generate graphviz
cargo pgrx schema -d pgdd.dot
dot -Goverlap=prism -Gspline=ortho -Tjpg pgdd.dot > pgdd.jpg
Non-standard dev
When working against pgrx installed from a non-tagged branch, install pgrx using:
cargo install --locked --force --git "https://github.com/tcdi/pgrx" \
--branch "develop" \
"cargo-pgrx"
The following command can be used to force pgrx to overwrite the configs it needs to for various dev related changes.
Clean things out.
cargo clean
If you're doing the above, you probably should remove the Cargo.lock
file while you're at it. The more cautious may want to move it aside for a backup.
rm Cargo.lock
Force build the schema.
cargo pgrx schema -f
Non-standard In Docker
If testing this extension against non-standard pgrx install, update the Dockerfile to install from the specific branch.
Change
RUN /bin/bash rustup.sh -y \
&& cargo install --locked cargo-pgrx
To
RUN /bin/bash rustup.sh -y \
&& cargo install --locked --force --git "https://github.com/tcdi/pgrx" \
--branch "develop" \
"cargo-pgrx"
Developer Notes
Drop/recreate for upgrade
If pgrx #121
is resolved, the need to DROP EXTENSION pgdd; CREATE EXTENSION pgdd;
would
go away. In that case, we need to be aware of
the need to adjust for DDL changes made in src/lib.rs
in version-to-version upgrade (e.g. sql/pgdd-0.3.1--0.4.0.sql
).
Those scenarios may need review depending on if pgrx #120 is also resolved or not.
As it stands, drop/create
is the upgrade path.