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

CREATE DATABASE

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';

ALTER ROLE

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

GRANT

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             127.0.0.1/32            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        127.0.0.1/32            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             127.0.0.1/32            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();  



 pg_reload_conf 

---------------- 

 t 

(1 row) 

 

postgres=# 

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

psql (9.1.7)

Type "help" for help.



postgres=# show data_directory;

        data_directory       

------------------------------

 /var/lib/postgresql/9.1/main

(1 row)



postgres=#

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.

You can just create a new directory in /var/lib/postgresql/9.1

sudo mkdir /var/lib/postgresql/9.1/somedir

This will result in

$ 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

$ sudo chgrp postgres /var/lib/postgresql/9.1/somedir

$ 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 postgres postgres 4096 Feb 21 21:40 somedir

Now your tablespace has a home the postgres has access to. At this point there should be no issue in creating a new tablespace.

$ 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/';

CREATE TABLESPACE

postgres=# 

All Set.

References:

• http://blog.lodeblomme.be/2008/03/15/move-a-postgresql-database-to-a-different-tablespace/
• http://www.postgresql.org/docs/9.1/static/sql-commands.html
• http://linuxpoison.blogspot.ca/2012/01/how-to-install-configure-postgresql.html
• http://www.davidghedini.com/pg/entry/install_postgresql_9_on_centos