Tutorial
How To Install and Use PostgreSQL on Ubuntu 12.04
Not using Ubuntu 12.04?Choose a different version or distribution.
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:
- Upgrade to Ubuntu 14.04.
- Upgrade from Ubuntu 14.04 to Ubuntu 16.04
- Migrate the server data to a supported version
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:
- It has created a table called potluck within the database, newdb.
- We have set up 4 columns in the table—name, food, confirmed, and signup date.
- The “name” column has been limited by the VARCHAR command to be under 20 characters long.
- The “food” column designates the food each person will bring. The VARCHAR limits text to be under 30 characters.
- The “confirmed” column records whether the person has RSVP’d with one letter, Y or N.
- 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.
About the author(s)
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!
Become a contributor for community
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.
DigitalOcean Documentation
Full documentation for every DigitalOcean product.
Resources for startups and SMBs
The Wave has everything you need to know about building a business, from raising funding to marketing your product.
Get our newsletter
Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.
New accounts only. By submitting your email you agree to our Privacy Policy
The developer cloud
Scale up as you grow — whether you're running one virtual machine or ten thousand.
Get started for free
Sign up and get $200 in credit for your first 60 days with DigitalOcean.*
*This promotional offer applies to new accounts only.
Minor correction, in the section “How to Create and Delete a Postgres Tables”, the text is referring to MySQL, should read Postgresql…
Nice catch, joelc! I’ve updated the article. Thanks!
I’m trying to connect to my Postgres database (hosted on DigitalOcean) from my Mac OSX machine. How can I ‘publish’ the Postgres install so anyone on the web can connect to it if they know the ip address/post and username/password? I’ve already tried setting the list_adresses property to *
listen_addresses = ‘*’
But it still refuses to connect.
Connection to Trueque - Production failed java.sql.SQLException: FATAL: no pg_hba.conf entry for host “201.222.115.2”, user “postgres”, database “foo_db”, SSL off at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:293) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125) at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30) at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22) at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32) at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) at org.postgresql.Driver.makeConnection(Driver.java:393) at org.postgresql.Driver.connect(Driver.java:267) in RemoteDriverImpl.connect(RemoteDriverImpl.java:27) at com.sun.proxy.$Proxy105.connect(Unknown Source) in LocalDataSource.getConnection(LocalDataSource.java:158)
@sergiotapia: Please see http://irwinj.blogspot.co.il/2010/07/no-pghbaconf-entry-for-host.html (specifically the last few lines).
for “createuser –pwprompt” it should be two – like this: “createuser -–pwprompt”
@2huaying: Thanks! updated.
When 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 go it had to execute this command sudo -u postgres psql postgres and set the password using \password postgres
[RoR] if i use bundle install, do i still need to create database and roles by myself or let the bundler do it?
@superadmin: It depends on your rails app, I recommend consulting the documentation for more info on that.
Hi
i installed postgresql and created user name and password and next when i try to run the command psql -U opentaps -d opentaps
i get the below error even though i give correct password . Password for user opentaps: psql: FATAL: password authentication failed for user “opentaps”
i set the peer to md5 authentication in pg_hba.conf but still no luck. i am trying to connect on windows using putty.
my pg_hba.conf looks like below
in my local all postgres md5
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
Allow replication connections from localhost, by a user with the
replication privilege.
#local replication postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5
@ayoubpats: Are you sure you entered the correct password? Are you running the psql command on the droplet or on your local pc?
yep am sure about the password …even if i give psql -u postgres -d postgres it gives same authentication failure message.i am trying from my local pc
@ayoubpats: You can’t connect to postgres from your local pc by default (assuming that postgres is installed on your droplet).
I should be able to connect remotely . Even when i try to connect through the console of droplet i get the same message psql: FATAL: password authentication failed for user “opentaps” something is wrong somewhere .for the user postgres also if i run this command psql -U postgres -d postgres in droplet console i get the same failure message after it asks for the password .i am supplying the correct password here .
Hi kamal
I was able to connect to the postgresql server on digitalocean using putty on my windows pc. i made changes in pg_hba.conf .changed md5 to trust and voila i was connected . cheers
In the procedures isn’t it necessary to run the command ‘psql’ sometime after ‘createdb events’ and before the CREATE TABLE … command? Otherwise it throws an error complaining of the ‘(’ open parentheses.
@hawkie.talk: Thanks, I’ve updated the article.
Hi,
After the installation I get an error that it couldn’t create cluster and I should try to create it manually (which I did but I get the same error).
Here is the error: Error: The locale requested by the environment is invalid.
Screenshot: http://cl.ly/image/041u1x3r2G1g http://cl.ly/image/353z383x2n0p
Thanks for the help! Paz.
@Fununa: Run the following commands as root: <pre>sudo locale-gen en_US en_US.UTF-8 dpkg-reconfigure locales </pre>
I had two issues with a DigitalOcean LAMP beta droplet on Ubuntu 12.04.
First was the locale issue, the fix above did not work, even reinstalling language pack, this guide sorted it:
http://ubuntuforums.org/showthread.php?t=1720356
sudo nano /etc/environment
added this to the end of the file:
LC_ALL=“en_US.utf-8”
Obviously adjust for your language, then reboot the server:
sudo reboot
The second issue was the same as user latimerscope above, postgres requiring a password, so I followed her tip and that worked:
sudo -u postgres psql postgres
Then set the password:
\password postgres
I’m having the same issue as hello and latimerscope. But when I do sudo -u postgres psql postgres and set the password, when i try and login in postgres I keep getting su: Authentication failure.
I’m not quite sure what to do to fix that. Or if I’m making an incorrect step somewhere.
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.
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.
@hector-garcia: Check out ayoubpats’s comment above (November 5th, 2013 10:02)—does his solution help?
Thanks…!
BTW here’s how to restart/stop postgresql: sudo /etc/init.d/postgresql restart sudo /etc/init.d/postgresql stop
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
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.
@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 postgresas root.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 :-)
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!?