cbbrowne / autodoc

PostgreSQL Autodoc - dumps a Postgres schema in several useful documentary forms

PostgreSQL Autodoc

This is a utility which will run through PostgreSQL system tables and returns HTML, DOT, and several styles of XML which describe the database.

As a result, documentation about a project can be generated quickly and be automatically updatable, yet have a quite professional look if you do some DSSSL/CSS work.

Requirements

This program requires the following perl modules:

• DBI
• HTML::Template
• Term::ReadKey
• DBD::Pg

Installation

Ubuntu

(Debian should be similar/identical)

from the universe repository

$ sudo apt install postgresql-autodoc

from github

$ sudo apt install libdbi-perl libhtml-template-perl libterm-readkey-perl libdbd-pg-perl

$ git clone https://github.com/cbbrowne/autodoc.git

$ cd autodoc

$ sudo make install

Docker

Install [Docker](https://www.docker.com/)

Create a Docker Image:

$ docker build . -t autodoc

Running Docker:

Create an env file by copying the example file and filling in details:

$ cp env-example env

Run a Docker container (with default command in Dockerfile):

$ docker run --mount src=`pwd`,target=/app,type=bind --network=host --env-file ./env -v $pwd:/app autodoc

Note: –network=host is for accessing a database on localhost

Run a custom container (see Usage below; if you want the files to appear in current directory, use the `–mount` arg above):

$ docker run autodoc postgresql_autodoc [options]

Usage

postgresql_autodoc [options]

Options

−d

<dbname> - Specify database name to connect to (default: current user)

−f

<file> Specify output file prefix (default: current user)

−h

<host> Specify database server host (default: localhost)

−p

<port> Specify database server port (default: 5432)

−u

<username> Specify database username (default: current user)

−−password=<pw>

Specify database password (default: blank)

−w

Use ~/.pgpass for authentication (overrides all other password options)

−l

<path> Path to the templates (default: @@TEMPLATE-DIR@@)

−t

<output> Type of output wanted (default: All in template library)

html

The HTML is human readable (via web browser), representing the entire schema within a single HTML document, and includes referenceable labels for each object.

dia

This remaps the schema into XML using the XML schema of Dia, an interactive diagramming tool. It does not do any automated layout, so making the diagram usable would require manual work, so this is often not terribly useful.

xml

The second type of XML is similar to HTML, but is in DocBook 4 format. It enables you to mix schema documentation with other DocBook documentation via the XREFs, generating PDFs, HTML, RTF, or other formatted documents. Object references can be made between these tools and JavaDoc with use of appropriate XREFs (see xreflabel elements in the XML).

neato

This generates the schema in the form accepted by GraphViz neato, which draws the schema as an undirected graph

zigzag.dia

This generates a diagram for Dia in another form

−s

<schema> Specify a specific schema to match. Technically this is a regular expression but anything other than a specific name may have unusual results.

−m

<regexp> Show only tables/objects with names matching the specified regular expression.

−−table=<args>

Tables to export. Multiple tables may be provided using a comma-separated list, i.e. table,table2,table3.

−−statistics

In 7.4 and later, with the contrib module pgstattuple installed we can gather statistics on the tables in the database (average size, free space, disk space used, dead tuple counts, etc.) This is disk intensive on large databases as all pages must be visited.

--comment-filter=<program>

Processes comments with a program. This is useful when comments are written using some markup languague (wiki/markdown), it allows conversion of structured text to desired output format. For each comment is executed command <program> <output> <tmp_file>, where <output> is the desired output format as specified by -t, <tmp_file> is path to file with original comment text. Processor should read this file, do processing and write result to standard output.

AUTHOR

Originally, this code base was derived from the CVS repository on pgFoundry, shifted to GitHub due to the popularity of maintenance via Git.

The presence of further contributions to the codebase seems to confirm that this was a decent idea.

Rod Taylor <[email protected]>

autodoc's Issues

Change url from http://www.rbt.ca/autodoc to https://github.com/cbbrowne/autodoc ?

Current :

Generated by PostgreSQL Autodoc

New :

Generated by PostgreSQL Autodoc

Should this be done? There is no content at the old url (rbc.ca).

Please delete. opened in error.

Please delete. Opened in error.

Examples

Hello,
are there any sample outputs?

diagram

Thanks for a fast easy to use no mess tool!!
I am exporting to dia (and zigzag.dia) but the result is all tables stacked on top of each other (~90 tables) how can i spread them out so i can see the full picture?

Returning all columns of tables

Maybe I've missed an option, but is there a way to return all columns of tables?

PS: Thanks for the great tool.

Datatype missing on v12 with Ubuntu 20.04

Hi, receiving some error messages and missing data types for columns on .eps exports.

Repeats dozens of times:

DBD::Pg::st fetchrow_hashref failed: no statement executing at /usr/bin/postgresql_autodoc line 689.
DBD::Pg::st execute failed: ERROR:  column "adsrc" does not exist
LINE 40:                 adsrc
                         ^ at /usr/bin/postgresql_autodoc line 687.
DBD::Pg::st fetchrow_hashref failed: no statement executing at /usr/bin/postgresql_autodoc line 689.

Use of uninitialized value in numeric comparison (<=>) at /usr/bin/postgresql_autodoc line 991.
Use of uninitialized value in numeric comparison (<=>) at /usr/bin/postgresql_autodoc line 1466.

Add Mermaid Support

Any chance you could add mermaid support?
https://mermaidjs.github.io/

OSX instructions?

postgresql_autodoc
Can't locate HTML/Template.pm in @INC (you may need to install the HTML::Template module) (@INC contains: /Library/Perl/5.30/darwin-thread-multi-2level /Library/Perl/5.30 /Network/Library/Perl/5.30/darwin-thread-multi-2level /Network/Library/Perl/5.30 /Library/Perl/Updates/5.30.2 /System/Library/Perl/5.30/darwin-thread-multi-2level /System/Library/Perl/5.30 /System/Library/Perl/Extras/5.30/darwin-thread-multi-2level /System/Library/Perl/Extras/5.30) at /usr/local/bin/postgresql_autodoc line 56.
BEGIN failed--compilation aborted at /usr/local/bin/postgresql_autodoc line 56.

also the docker version can't connect to my postgres.

Process comments with external tool to produce HTML from structured text.

It would be nice to allow process comments by external tool. For example of comments are markdown string it can be converted into HTML or something.

field text overlap type when override font

when field name are a bit long there can happen they overlaps between field and type
see here below the image png produced by the dot
this is caused by the font override ( helvetica ) that takes much space
so if is not possible to compute the font aprior could helpful add together font family override another factor to do right adjustment.

(original font)

(overriden font)

Doc name error "HTML::Templates" to "HTML::Template"

Ubuntu 22.04 looks in wrong spot for template files, `/build/postgresql-autodoc-...`

After doing an apt install and running on my new system, I unexpectedly hit this error.

$ postgresql_autodoc -h localhost -u shane --password=password -d nt -f nt -t dot


Templates files not found in /build/postgresql-autodoc-XbwYdk/postgresql-autodoc-1.41+20200921/debian/postgresql-autodoc/usr/share/postgresql-autodoc

Maintained?

Hi @cbbrowne is this project still being developed/maintained?
Are there docs or a usage example?

Autodoc fails on PostgreSQL 12

postgresql_autodoc -h localhost -u postgres -p 7012 -d mydb

when pointed at PostgreSQL 12

generates a bunch of messages of the form...

DBD::Pg::st execute failed: ERROR:  column "adsrc" does not exist
LINE 40:                 adsrc
                         ^ at /usr/local/bin/postgresql_autodoc line 695.

The cause is documented in the PostgreSQL 12 release notes:

Remove obsolete pg_attrdef.adsrc column (Peter Eisentraut)

This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a default-value expression from pg_attrdef is pg_get_expr(adbin, adrelid).

Unable to connect with psql 10.10 (could not connect to server: No such file or directory)

Hi all,

love the work that's been done! I've just had one small issue, I think it's related to upgrading my Linux to postgres 10.10.

here's the error:

DBI connect('dbname=nutra','alephdog',...) failed: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? at /usr/bin/postgresql_autodoc line 260.


Unable to connect due to: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

but when i run this, i have no issues:

psql postgresql://alephdog:@localhost:5432/nutra

System info:

ubuntu 18.04
psql/pg_ctl 10.10
Postgres Auto-Doc Version 1.40