Tutorial

How To Install and Use PostgreSQL on Ubuntu 12.04

Published on August 1, 2013
How To Install and Use PostgreSQL on Ubuntu 12.04
Not using Ubuntu 12.04?Choose a different version or distribution.
Ubuntu 12.04

Status: Deprecated

This article covers a version of Ubuntu that is no longer supported. If you are currently operate a server running Ubuntu 12.04, we highly recommend upgrading or migrating to a supported version of Ubuntu:

Reason: Ubuntu 12.04 reached end of life (EOL) on April 28, 2017 and no longer receives security patches or updates. This guide is no longer maintained.

See Instead:
This guide might still be useful as a reference, but may not work on other Ubuntu releases. If available, we strongly recommend using a guide written for the version of Ubuntu you are using. You can use the search functionality at the top of the page to find a more recent version.

Install Postgres

Before we install postgres, we should quick perform a quick update of the apt-get repository:

apt-get update

Once apt-get has updated go ahead and download Postgres and its helpful accompanying dependencies:

sudo apt-get install postgresql postgresql-contrib

With that, postgres is installed on your server.

Create Your PostgreSQL Roles and Databases

Once Postgres has been installed on your server, you can start to configure the database.

Postgres uses the concept of roles to distinguish the variety of users that can connect to a database. When it is first installed on a server, the default postgres user is actual named “postgres”. The other users are specified in one of variety of ways. The common methods are ident and md5. The postgres default is to use ident authentication, tying each server user to a Postgres account. The alternative which can be set in the authentication configuration, located in “/etc/postgresql/9.1/main/pg_hba.conf “ is md5 which asks the client to supply an encrypted password.

To begin creating custom users, first switch into the default user:

sudo su – postgres

Once logged in as this user, you can move forward to create more roles in your PostgreSQL system:

createuser
Enter name of role to add: newuser
Shall the new role be a superuser? (y/n) y

To outfit your user with a password, you can add the words –pwprompt to the createuser command:

createuser --pwprompt

Connecting to the Postgres Databases

With the users that you want to use to log into your Postgres shell set up, you can proceed to make a database for them to use.

You can create the Postgres database as a superuser. In this case, we will use the default super user.

Go ahead and switch into the postgres user once again:

su – postgres

As postgres, you can start to create your first usable postgres database:

 createdb events

And with that you can finally connect to the postgres shell.

How to Create and Delete a Postgres Tables

Once we log into the correct database (using the psql -d events command where events is that database's name), we can create tables within it.

Let’s imagine that we are planning a get together of friends. We can use Postgres to track the details of the event.

Let’s create a new Postgres table:

CREATE TABLE potluck (name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1), 
signup_date DATE);

This command accomplishes a number of things:

  1. It has created a table called potluck within the database, newdb.
  2. We have set up 4 columns in the table—name, food, confirmed, and signup date.
  3. The “name” column has been limited by the VARCHAR command to be under 20 characters long.
  4. The “food” column designates the food each person will bring. The VARCHAR limits text to be under 30 characters.
  5. The “confirmed” column records whether the person has RSVP’d with one letter, Y or N.
  6. The “date” column will show when they signed up for the event. Postgres requires that dates be written as yyyy-mm-dd

Once entered, postgres should confirm the table creation with the following line:

CREATE TABLE

You can additionally see all of the tables within the database with the following command:

\dt

The result, in this case, should look like this:

postgres=# \dt
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | potluck | table | root
(1 row)

How to Add Information to a Postgres Table

We have a working table for our party. Now it’s time to start filling in the details.

Use this format to insert information into each row:

INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('John', 'Casserole', 'Y', '2012-04-11');

Once you input that in, you will see the words:

INSERT 0 1

Let’s add a couple more people to our group:

INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Sandy', 'Key Lime Tarts', 'N', '2012-04-14');
INSERT INTO potluck (name, food, confirmed, signup_date)VALUES ('Tom', 'BBQ','Y', '2012-04-18');
INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('Tina', 'Salad', 'Y','2012-04-18');

We can take a look at our table:

SELECT * FROM potluck;
 name  |      food      | confirmed | signup_date 
-------+----------------+-----------+-------------
 John  | Casserole      | Y         | 2012-04-11
 Sandy | Key Lime Tarts | N         | 2012-04-14
 Tom   | BBQ            | Y         | 2012-04-10
 Tina  | Salad          | Y         | 2012-04-18
(4 rows)

Should we want to, then, follow up by removing an unlucky attendee, in this John and his casserole, from our potluck we can accomplish this with the Delete command:

 DELETE FROM potluck WHERE name = 'John' ;

How to Add and Delete a Column

We are creating a handy chart, but it is missing some important information: our attendees’ emails.

We can easily add this:

ALTER TABLE potluck ADD email VARCHAR(40);

This command puts the new column called "email" at the end of the table by default, and the VARCHAR command limits it to 40 characters.

Just as you can add a column, you can delete one as well:

ALTER TABLE potluck DROP email;

I guess we will never know how to reach the picnickers.

How to Update Information in the Table

Now that we have started our potluck list, we can address any possible changes.

For example: Sandy has confirmed that she is attending, so we are going to update that in the table.

 UPDATE potluck set confirmed = 'Y' WHERE name = 'Sandy';

You can also use this command to add information into specific cells, even if they are empty.

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

I followed steps up to ‘createuser’. I can’t do anything without being prompted for a password for this account, but given that it doesn’t have one, I’m stick - now what!?

Very interesting and useful post. To know how to configure Postgre database server, you can also check this post: https://www.ricohidc.com/kb/how-to-configure-postgre-database/ . It is really helpful :-)

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
July 3, 2014

@ahmed.rabie: You can either log in as root first and then run that command (it won’t ask you for a password in that case), or change the ‘postgres’ user’s password by running passwd postgres as root.

hen i try su - postgres It prompts for a password. I tried my own password and ‘postgres’ and even leaving it blank but it brings Authentication failure what can i do.

I have tried this on my server and posgresql server does not start. I have even tried to re-run this tutorial steps on fresh droplet and got same results.

At the end it printed this error:

Error: could not create default cluster. Please create it manually with

pg_createcluster 9.1 main --start

Running this command didn’t do anything Running “service postgresql start” does not do anything, no errors

here’s full output https://gist.github.com/jurgens/9669210

BTW here’s how to restart/stop postgresql: sudo /etc/init.d/postgresql restart sudo /etc/init.d/postgresql stop

Thanks…!

Kamal Nasser
DigitalOcean Employee
DigitalOcean Employee badge
February 8, 2014

@hector-garcia: Check out ayoubpats’s comment above (November 5th, 2013 10:02)—does his solution help?

Getting a “FATAL: Peer authentication failed for user “*****”” . I added “host all all 0.0.0.0/0 md5” to pg_hba.conf and restarted and still getting the same error.

I don’t know if this is the recommended way of doing it, but I did sudo passwd postgres and changed the password that way, then was able so su - postgres.

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Get our biweekly newsletter

Sign up for Infrastructure as a Newsletter.

Hollie's Hub for Good

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Become a contributor

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

Welcome to the developer cloud

DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

Learn more
DigitalOcean Cloud Control Panel