Miscellaneous Postgres Commands

I switched over from SQLite to Postgres to power my site some months ago, and have found myself having to learn some new usage when interacting with the database. These are likely very old hat for anybody familiar with Postgres, but I find myself having to remind myself what they are.

Schema introspection commands

So, here's the list:

Command	Description
`\list` or `\l`	List all databases
`\c <dbname>` or `\connect <dbname>`	Connect to a database
`\dt`	List all tables in the current database using your search path
`\dt *`	List all tables in the current database, ignoring the search path

Determine what databases are available

The other thing I've needed to do is to find out what databases are present on the machine; this is useful when I want to blow away a database and recreate it. This is done via SQL:

SELECT schema_name
FROM information_schema.schemata

Most times, you'll be on public.

Restore from a backup

I have an automated process that backs up the database nightly using pg_dump, and gzips the result.

When I want to drop the database, I discovered that you need to (a) determine what database you're in (see previous section), and then (b) delete all tables in it:

DROP SCHEMA public CASCADE

Once done, recreate the database:

CREATE SCHEMA public

From there, you can restore from your backup. And regarding that, I discovered that in order to restore from a gzipped DB dump, you use psql, and not pg_restore.