Create New Postgres Users and Database Maintenence

Procedure for creating a new postgres user with proper attributes:

Use either:

CREATE ROLE name LOGIN;
or
CREATE USER name;

(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default)

Some Important Attribute and Privilege Changes for Users:

to allow database creation for user:
ALTER ROLE name CREATEDB;

to set a password for user:
ALTER ROLE name PASSWORD 'password';

to grant/revoke public access on a table or database can replace ALL with select, update, delete, etc:
GRANT/REVOKE ALL on tablename to user;

Database Maintenece and Backup Procedures:

Performing an SQL dump and retrieving the dump(generates SQL files for all tables in database and dumps to a outfile for backup):
pg_dump dbname > outfile;
psql dbname < infile;

or use:

pg_dumpall > outfile;
psql -f infile postgres;

Performing VACUUM maintentence to recover disk space and update database statistics:
VACUUM ANALYZE;
or just VACUUM;

Reindexing:
REINDEX;


These tasks should be performed every week to make sure our system is backed up and running optimally.