Install/setup Postgres 9.1 on Ubuntu 12.04

Thu 21 February 2013
misc
Glen B

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

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