Introduction

How to administer PostGreSQL services, including users and databases.

Administration

PostGreSQL Server Admin Commands

1. Restart the PostGreSQL server:

   
   % sudo /usr/local/etc/rc.d/postgresql restart
   
   

2. postgres is the PostgreSQL server.

   
   % sudo /usr/local/bin/postgres --help
   
   

3. psql is the PostgreSQL interactive terminal.

   
   % sudo /usr/local/bin/psql --help
   
   

User Administration

1. Perform all administrative tasks as the pgsql user:

   
   [tethys] ~% sudo su pgsql
   $ whoami
   pgsql
   $
   
   

2. Create a new superuser. -P means assign a [p]assword to the role, -E means [e]ncrypt stored password.

   
   $ /usr/local/bin/createuser -PE cwmiller
   Enter password for new role: 
   Enter it again: 
   Shall the new role be a superuser? (y/n) y
   $ 
   
   

3. Create a new unprivileged user. -P means assign a [p]assword to the role, -E means [e]ncrypt stored password.

   
   $ /usr/local/bin/createuser -PE chomicki
   Enter password for new role: 
   Enter it again: 
   Shall the new role be a superuser? (y/n) n
   Shall the new role be allowed to create databases? (y/n) y
   Shall the new role be allowed to create more new roles? (y/n) n
   $ 
   
   

4. Drop a user:

   
   $ /usr/local/bin/dropuser chomicki
   
   

Database Administration

1. Perform all administrative tasks as the pgsql user:

   
   [tethys] ~% sudo su pgsql
   $ whoami
   pgsql
   $
   
   

2. Create a database (name it with the user's username, because that's what Postgres looks for by default):

   
   $ /usr/local/bin/createdb cwmiller
   
   

Grant or Revoke Privileges:

References

1. http://www.postgresql.org/docs/8.4/interactive/index.html
2. http://wiki.postgresql.org/
3. http://wiki.postgresql.org/wiki/Frequently_Asked_Questions