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
0 Comments.