Skip to content

Postgres

CLI

The official Postgres CLI is psql.

Commands

From the command prompt, the following commands enable you to quickly orientate yourself...

  • \l - lists the databases (or SELECT datname FROM pg_database;)
  • \c mydb - connect to specific database instance
  • \dt - list the tables (including a +, e.g. \dt+ gives you some additional metadata such as table size on disk and description)
  • \d <tablename> - show the table's schema

Querying

A few example queries as follows...note the need for a semi-colon at the end of each line...

SELECT * FROM mytable;

Saving results / output to a file

More details here.

db=>\o out.txt
db=>\dt
db=>\q

Database Dump

pg_dump

Best Practices

  • https://philbooth.me/blog/nine-ways-to-shoot-yourself-in-the-foot-with-postgresql
  • https://news.ycombinator.com/item?id=35684220