How to print the table structure from postgresql?

Question

I am using phpPgAdmin in the browser and PgAdmin III for Windows. Is there anyway to take the printout of the table structure for the entire database?

Answer 1

The standard way of exporting database schema is pg_dump:

#!/bin/sh
pg_dump --schema-only MYDBNAME > output-file.sql

Sligtly better way combines pg_dump with pg_restore list filtering:

#!/bin/sh
dump=`mktemp`
list=`mktemp`
pg_dump --schema-only MYDBNAME -Fc -f $dump
pg_restore -l $dump | grep ' TABLE ' > $list
pg_restore -L $list $dump > output-file.sql
rm $list $dump

If you prefer GUI wizards, pg_dump command can be generated in PgAdmin III:

• right click the database in object browser, select "Backup"
• select destination filename (common extension is .sql or .txt)
• Choose "Plain" format. (that is, text format)
• on "Dump Options #1" tab, tick "Only Schema"
• click "Backup"

Note: the resulting file will have not only tables, but also all other objects (views, functions, etc.). If you need only the minimal printout, you can edit this file in text editor and remove unneeded stuff. Leave only "Type: TABLE;" items.

Answer 2

If you are on Windows and pgAdmin, you should have psql somewhere in C:\Program files\postgresql\<version>\bin\psql.

Run psql and then you have \d which prints all tables and indexes and \d <table_name> which gives you details about one table.

Answer 3

You can do them one at a time as you need them. Right click on a table in pgAdminIII, go to Reports and select "Data Dictionary Report".

For the output format, select "XHTML 1.0 Transitional", choose "Embed the default stylesheet" option, give it a file name and click OK.

Open the XML file in your browser and print.