Getting Started with Postgres

I swap between databases all the time for various projects, and end up Googling and how to do the initial setup all the time, so the purpose of this document is to list a set of commands that anyone would end up using in getting started.

Creating a User

The simplest way to do this, without calling the psql utility and calling the CREATE ROLE and a bunch of other SQL Statements is to use the createuser utility. More Details Here

At it's simplest, you call the following.

$ createuser new_user

Create User and Database

$ createuser new_user --createdb

Create User and Database with Password

createuser new_user --createdb --pwprompt

Create a Super User, and Database with Password

createuser new_user --createdb --pwprompt --superuser

Drop a Database

dropdb db_name

Drop a User

dropuser my_user

Using `psql` utility

psql --host <hostname> \
     --port <port> \
     --username <username> \
     -W \
     --dbname <dbname>

If you want to just get a list of database names and quit, then run:

psql --host <hostname> \
     --port <port> \
     --username <username> \
     -W \
     --list

Frequeently used commands:

These commands can be run after successfully connecting to the database using psql. This means the authentication should have been successful.

How do I get all the tables to be printed out: \dt
How do I get the structure of a table: \d mytable or if you want more details: \d+ mytable
How do I quit out of psql: \q

davydany/getting-started-postgres.md