PostgreSQL

[[Category:Linux]]

==PostgreSQL==
[http://en.wikipedia.org/wiki/PostgreSQL PostgreSQL] is an object-relational database management system (ORDBMS).

==Installation==
*Install PostgreSQL
<pre>
$ sudo apt-get install postgresql 
</pre>

==Administration==
===Setup Root User Password===
*Set up the password of the PostgreSQL root user
 $ sudo -u postgres psql postgres 
 
 Welcome to psql 8.3.5, the PostgreSQL interactive terminal.
 
 Type:  \copyright for distribution terms
        \h for help with SQL commands
        \? for help with psql commands
        \g or terminate with semicolon to execute query
        \q to quit
 
 postgres=# '''ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';''' 
or
 postgres=# '''\password postgres''' 

Use <code>\quit</code> or <code>\q</code> to quit psql.
 postgres=# '''\q''' 

===Create User===
Creating a user in PostgreSQL is in fact creating a role. So there are two ways to do it. The first one is using <code>CREATE ROLE</code><ref>{{cite web |url=http://www.postgresql.org/docs/8.4/static/database-roles.html | title = PostgreSQL 8.4.4 Documentation - 20.1. Database Roles | date = 2009 | accessdate = 2010-08-02}}</ref> SQL command and the other one is using the programme, <code>createuser</code><ref>{{cite web|url= http://www.postgresql.org/docs/8.4/static/app-createuser.html | title= PostgreSQL 8.4.4 Documentation - createuser | date = 2009 | accessdate = 2010-08-02}}</ref>.
====CREATE ROLE====
 CREATE ROLE name; 


====createuser====
 createuser [option...] [username] 

So to create a new user and a db belonging to him/her.

 sudo -u postgres createuser -P username
 sudo -u postgres createdb -O username db_name

-P: it will ask you to enter the password for the new user ('''role''').


* Options<ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/app-createuser.html#AEN67555 | title = PostgreSQL 8.4.4 Documentation - 20.1. Database Roles#Options}}</ref>

createuser accepts the following command-line arguments:

 username
 
     Specifies the name of the PostgreSQL user to be created. This name must be different from all existing roles in this PostgreSQL installation. 
 -s
 --superuser
 
     The new user will be a superuser. 
 -S
 --no-superuser
 
     The new user will not be a superuser. This is the default. 
 -d
 --createdb
 
     The new user will be allowed to create databases. 
 -D
 --no-createdb
 
     The new user will not be allowed to create databases. This is the default. 
 -r
 --createrole
 
     The new user will be allowed to create new roles (that is, this user will have CREATEROLE privilege). 
 -R
 --no-createrole
 
     The new user will not be allowed to create new roles. This is the default. 
 -l
 --login
 
     The new user will be allowed to log in (that is, the user name can be used as the initial session user identifier). This is the default. 
 -L
 --no-login
 
     The new user will not be allowed to log in. (A role without login privilege is still useful as a means of managing database permissions.) 
 -i
 --inherit
 
     The new role will automatically inherit privileges of roles it is a member of. This is the default. 
 -I
 --no-inherit
 
     The new role will not automatically inherit privileges of roles it is a member of. 
 -c number
 --connection-limit number
 
     Set a maximum number of connections for the new user. The default is to set no limit. 
 -P
 --pwprompt
 
     If given, createuser will issue a prompt for the password of the new user. This is not necessary if you do not plan on using password authentication. 
 -E
 --encrypted
 
     Encrypts the user's password stored in the database. If not specified, the default password behavior is used. 
 -N
 --unencrypted
 
     Does not encrypt the user's password stored in the database. If not specified, the default password behavior is used. 
 -e
 --echo
 
     Echo the commands that createuser generates and sends to the server. 
 
 You will be prompted for a name and other missing information if it is not specified on the command line.
 
 createuser also accepts the following command-line arguments for connection parameters:
 
 -h host
 --host host
 
     Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. 
 -p port
 --port port
 
     Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. 
 -U username
 --username username
 
     User name to connect as (not the user name to create). 
 -w
 --no-password
 
     Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password. 
 -W
 --password
 
     Force createuser to prompt for a password (for connecting to the server, not for the password of the new user).
 
     This option is never essential, since createuser will automatically prompt for a password if the server demands password authentication. However, createuser will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt. 



====References====
{{Reflist|1}}


===Restart Server===
To restart the server, /etc/init.d/postgresql-8.x (depending on the version).
 sudo /etc/init.d/postgresql-8.4 restart


==Install pgAdmin III==
<pre>
$ sudo apt-get install pgadmin3 
</pre>


==References==
http://www.postgresql.org/docs/8.4/static