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.