Install/setup Postgres 9.1 on Ubuntu 12.04

Start by installing Postgres:

sudo apt-get install postgresql

After installing the first thing that needs to be done is adjust the connections postgres will accept. open the file /etc/postgresql/9.1/main/posrgresql.conf and turn on the listen addres

listen_addresses = 'localhost'

Then turn password encryption on

password_encryption = on

Then restart postgresql for these changes to take effect.

/etc/init.d/postgresql restart

Now the server is ready to access. First thing to do is to create a database user to use for all of your work.

sudo -u postgres createuser

Enter name of role to add: username

Shall the new role be a superuser? (y/n) n

Shall the new role be allowed to create databases? (y/n) n

Shall the new role be allowed to create more new roles? (y/n) n

 Then create the database you are going to use:

sudo -u postgres createdb somedb

After the database has been created a setup a password for that user and grant all privaleges for that user on the database.

sudo -u postgres psql

postgres=# alter user username with encrypted password 'passwd';


postgres=# grant all privileges on database somedb to username;


Now it is time to install the postgres client.

sudo apt-get install postgresql-client

Last it might be necessary to change the connection protocol in /etc/postgresql/9.1/main/pg_hba.conf. I was getting an error related to not having the privileges to access the database.

On installation, your pg_hba.conf file will look like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD 

# "local" is for Unix domain socket connections only 

local   all             all                                     peer 

# IPv4 local connections: 

host    all             all               ident 

# IPv6 local connections: 

host    all             all             ::1/128                 ident 

# Allow replication connections from localhost, by a user with the 

# replication privilege. 

#local   replication     postgres                                peer 

#host    replication     postgres            ident 

#host    replication     postgres        ::1/128                 ident  

Change the METHOD to md5 as shown below:

# TYPE  DATABASE        USER            ADDRESS                 METHOD 

# "local" is for Unix domain socket connections only 

local   all             all                                     md5 

# IPv4 local connections: 

host    all             all               md5 

# IPv6 local connections: 

host    all             all             ::1/128                 md5  

In order for the change to take effect, reload the pg_hba.conf file.

sudo -u postgres psql

posgres=# select pg_reload_conf();  




(1 row) 


Now it should be possible to connect to your database with  psql -d somedb -U username It will ask you for your password. If you want to create a tablespace for your database: I just used the same hard-drive I installed it on, if you computer has more than one hard-drive you might want to distribute databases across table spaces. By default on Ubuntu postgres store the default tablespace in /var/lib/postgresql/9.1/main. You can double check this with the command

\( sudo -u postgres psql<br /><br />psql (9.1.7)<br /><br />Type "help" for help.<br /><br /><br /><br />postgres=# show data_directory;<br /><br />        data_directory       <br /><br />------------------------------<br /><br /> /var/lib/postgresql/9.1/main<br /><br />(1 row)<br /><br /><br /><br />postgres=#<br /></code></pre><br /><br />This next section gives direction on how to a table_space for postgres to store all of the information for table in a particular directory.<br /><br />You can just create a new directory in /var/lib/postgresql/9.1<br /><br /><pre class="prettyprint"><code class="language-bash"><br />sudo mkdir /var/lib/postgresql/9.1/somedir<br /><br /></code></pre><br />This will result in<br /><pre class="prettyprint"><code class="language-bash"><br />\) ls -la /var/lib/postgresql/9.1/

total 16

drwxr-xr-x  4 postgres postgres 4096 Feb 21 21:40 .

drwxr-xr-x  3 postgres postgres 4096 Feb  7 12:05 ..

drwx------ 13 postgres postgres 4096 Feb 21 20:43 main

drwxr-xr-x  2 root     root     4096 Feb 21 21:40 somedir

The permissions need to be changed for postgres to be able to access the tablespace store correctly

\( sudo chown postgres /var/lib/postgresql/9.1/somedir<br /><br />\) sudo chgrp postgres /var/lib/postgresql/9.1/somedir

\( ls -la /var/lib/postgresql/9.1/<br /><br />total 16<br /><br />drwxr-xr-x  4 postgres postgres 4096 Feb 21 21:40 .<br /><br />drwxr-xr-x  3 postgres postgres 4096 Feb  7 12:05 ..<br /><br />drwx------ 13 postgres postgres 4096 Feb 21 20:43 main<br /><br />drwxr-xr-x  2 postgres postgres 4096 Feb 21 21:40 somedir<br /></code></pre><br /><br />Now your tablespace has a home the postgres has access to. At this point there should be no issue in creating a new tablespace.<br /><br /><pre class="prettyprint"><code class="language-bash"><br />\) sudo -u postgres psql

psql (9.1.7)

Type "help" for help.


postgres=# CREATE TABLESPACE table_store OWNER username  LOCATION '/var/lib/postgresql/9.1/somedir/';



All Set.