PostgreSQL cheat sheet

This post will be updated with any cool cheats/commands that I use on a daily basis, that may help others out.

Make a local user, superuser

Everytime I work on a new machine/environment I need to be able to create and delete databases as a local user quickly and easily, I am by no means a PostgreSQL expert, but I need to be able to create databases, ready for web application development. So the below code should get you started.

First, login as the potgres user, and access the psql shell:

$ sudo su - postgres
$ psql

Now, we need to create a new postgres user, for the sake of this guide, lets assume the name “luke” and obviously replace ‘somepassword’ with your own, secure password.

postgres=# create user luke with password 'somepassword';
CREATE ROLE

Now we have the user setup, lets give him ‘Super User’ credentials:

postgres=# ALTER USER luke with SUPERUSER;
ALTER ROLE

Now, open up a new terminal and you should be able to create and delete databases:

$ createdb staff
$ dropdb staff