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.

Comments

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.