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