create a database in postgreSQL

Recently I was asked for a postgres database by a web application. Working with mysql all the time I didn’t have a clue about postgres commands, it was an easy procedure after all.

Howto create a database

There are two ways to create a new database

1st way

You first need to su to the postgres data owner, usually user postgres

su postgres

then issue the createdb command followed by the desired new database name

createdb newdatabasename

The owner for the new database is the user that issued the createdb command (postgres in our case)

you can specify the owner with the -O option

createdb newdatabasename -O user1

in this case user1 is the database owner (the user has to be a valid-existing postgres user)

2nd way

su to postgres and enter postgres cli, then issue the “CREATE DATABASE” command and finaly \q to exit postgres cli

su postgres

psql

CREATE DATABASE newdatabasename WITH OWNER user1;

\q

the user has to be a valid-existing user.

Howto create a user

There are two ways to create a new user

1st way

switch to postgres user and issue the “createuser” command

su postgres

createuser username

then you’ll be prompted to a wizard asking you about the privileges you wish to give to the new user.

2nd way

While you’re logged in as the postgres user, issue psql to enter postgres cli

su postgres

psql

and issue

CREATE USER myusername WITH password 'mypassword';

 

 

Some useful commands for the postgres command line

\q              #exits postgres command line

\l               #lists existing databases, owner of the db and encoding

\h              #lists available commands

\h command    #description of the given command (something like a tiny man page)

\c              #shows you the database & user you’re connected

\d             #lists tables of the database

Leave a Comment

NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>