Pgsql Command-line Tools

Although I have been using pgsql for a number of years, every time I wanted to do something I do have to look up a reference as I'm not a regular user of pgsql. Here are a few command-line references which may be helpful to beginners.

To make a backup of a postgres database:

Login as a database user with read access to the db, and run the following command.

%> pg_dump -U {username} {database} > {database.sql}

To restore a backup from a SQL file,

%> psql -U {username} {database} < {database.sql}

To create a database

%> createdb {database}

To drop a database

%> dropdb {database}

To get a list of indexes (or constraints) in a database
\di

To delete an existing index
alter table {table_name} drop constraint {constraint_name};

To add a primary key:
alter table {table_name} add primary key ({key1}, {key2}...);
** If primary keys are more than one, than it's a composite key.