Tutorial

How To Install and Use PostgreSQL on Ubuntu 14.04

Published on April 28, 2014
How To Install and Use PostgreSQL on Ubuntu 14.04
Not using Ubuntu 14.04?Choose a different version or distribution.
Ubuntu 14.04

Introduction

Relational database management systems are a key component of many web sites and applications. They provide a structured way to store, organize, and access information.

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

In this guide, we will demonstrate how to install Postgres on an Ubuntu 14.04 VPS instance and go over some basic ways to use it.

Installation

Ubuntu’s default repositories contain Postgres packages, so we can install them without a hassle using the apt packaging system.

Since we haven’t updated our local apt repository lately, let’s do that now. We can then get the Postgres package and a “contrib” package that adds some additional utilities and functionality:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Now that our software is installed, we can go over how it works and how it may be different from similar database management systems you may have used.

Using PostgreSQL Roles and Databases

By default, Postgres uses a concept called “roles” to aid in authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term “role”.

Upon installation Postgres is set up to use “ident” authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a Postgres role exists, it can be signed in by logging into the associated Linux system account.

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, we’ll need to log into that account. You can do that by typing:

sudo -i -u postgres

You will be asked for your normal user password and then will be given a shell prompt for the postgres user.

You can get a Postgres prompt immediately by typing:

psql

You will be auto-logged in and will be able to interact with the database management system right away.

However, we’re going to explain a little bit about how to use other roles and databases so that you have some flexibility as to which user and database you wish to work with.

Exit out of the PostgreSQL prompt by typing:

\q

You should now be back in the postgres Linux command prompt.

Create a New Role

From the postgres Linux account, you have the ability to log into the database system. However, we’re also going to demonstrate how to create additional roles. The postgres Linux account, being associated with the Postgres administrative role, has access to some utilities to create users and databases.

We can create a new role by typing:

createuser --interactive

This basically is an interactive shell script that calls the correct Postgres commands to create a user to your specifications. It will only ask you two questions: the name of the role and whether it should be a superuser. You can get more control by passing some additional flags. Check out the options by looking at the man page:

man createuser

Create a New Database

The way that Postgres is set up by default (authenticating roles that are requested by matching system accounts) also comes with the assumption that a matching database will exist for the role to connect to.

So if I have a user called test1, that role will attempt to connect to a database called test1 by default.

You can create the appropriate database by simply calling this command as the postgres user:

<pre> createdb <span class=“highlight”>test1</span> </pre>

Connect to Postgres with the New User

Let’s assume that you have a Linux system account called test1 (you can create one by typing: adduser test1), and that you have created a Postgres role and database also called test1.

You can change to the Linux system account by typing:

<pre> sudo -i -u <span class=“highlight”>test1</span> </pre>

You can then connect to the test1 database as the test1 Postgres role by typing:

psql

This will log in automatically assuming that all of the components have been configured.

If you want your user to connect to a different database, you can do so by specifying the database like this:

<pre> psql -d <span class=“highlight”>postgres</span> </pre>

You can get information about the Postgres user you’re logged in as and the database you’re currently connected to by typing:

\conninfo

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

This can help remind you of your current settings if you are connecting to non-default databases or with non-default users.

Create and Delete Tables

Now that you know how to connect to the PostgreSQL database system, we will start to go over how to complete some basic tasks.

First, let’s create a table to store some data. Let’s create a table that describes playground equipment.

The basic syntax for this command is something like this:

<pre> CREATE TABLE <span class=“highlight”>table_name</span> ( <span class=“highlight”>column_name1</span> <span class=“highlight”>col_type</span> (<span class=“highlight”>field_length</span>) <span class=“highlight”>column_constraints</span>, <span class=“highlight”>column_name2</span> <span class=“highlight”>col_type</span> (<span class=“highlight”>field_length</span>), <span class=“highlight”>column_name3</span> <span class=“highlight”>col_type</span> (<span class=“highlight”>field_length</span>) ); </pre>

As you can see, we give the table a name, and then define the columns that we want, as well as the column type and the max length of the field data. We can also optionally add table constraints for each column.

You can learn more about how to create and manage tables in Postgres here.

For our purposes, we’re going to create a simple table like this:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

We have made a playground table that inventories the equipment that we have. This starts with an equipment ID, which is of the serial type. This data type is an auto-incrementing integer. We have given this column the constraint of primary key which means that the values must be unique and not null.

For two of our columns, we have not given a field length. This is because some column types don’t require a set length because the length is implied by the type.

We then give columns for the equipment type and color, each of which cannot be empty. We then create a location column and create a constraint that requires the value to be one of eight possible values. The last column is a date column that records the date that we installed the equipment.

We can see our new table by typing this:

\d

                   List of relations
 Schema |          Name           |   Type   |  Owner   
--------+-------------------------+----------+----------
 public | playground              | table    | postgres
 public | playground_equip_id_seq | sequence | postgres
(2 rows)

As you can see, we have our playground table, but we also have something called playground_equip_id_seq that is of the type sequence. This is a representation of the “serial” type we gave our equip_id column. This keeps track of the next number in the sequence.

If you want to see just the table, you can type:

\dt

           List of relations
 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | playground | table | postgres
(1 row)

Add, Query, and Delete Data in a Table

Now that we have a table created, we can insert some data into it.

Let’s add a slide and a swing. We do this by calling the table we’re wanting to add to, naming the columns and then providing data for each column. Our slide and swing could be added like this:

<pre> INSERT INTO playground (type, color, location, install_date) VALUES (‘slide’, ‘blue’, ‘south’, ‘2014-04-28’); INSERT INTO playground (type, color, location, install_date) VALUES (‘swing’, ‘yellow’, ‘northwest’, ‘2010-08-16’); </pre>

You should notice a few things. First, keep in mind that the column names should not be quoted, but the column values that you’re entering do need quotes.

Another thing to keep in mind is that we do not enter a value for the equip_id column. This is because this is auto-generated whenever a new row in the table is created.

We can then get back the information we’ve added by typing:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2014-04-28
        2 | swing | yellow | northwest | 2010-08-16
(2 rows)

Here, you can see that our equip_id has been filled in successfully and that all of our other data has been organized correctly.

If our slide breaks and we remove it from the playground, we can also remove the row from our table by typing:

DELETE FROM playground WHERE type = 'slide';

If we query our table again, we will see our slide is no longer a part of the table:

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date 
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2010-08-16
(1 row)

How To Add and Delete Columns from a Table

If we want to modify a table after it has been created to add an additional column, we can do that easily.

We can add a column to show the last maintenance visit for each piece of equipment by typing:

<pre> ALTER TABLE playground ADD last_maint date; </pre>

If you view your table information again, you will see the new column has been added (but no data has been entered):

SELECT * FROM playground;

 equip_id | type  | color  | location  | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2010-08-16   | 
(1 row)

We can delete a column just as easily. If we find that our work crew uses a separate tool to keep track of maintenance history, we can get rid of the column here by typing:

ALTER TABLE playground DROP last_maint;

How To Update Data in a Table

We know how to add records to a table and how to delete them, but we haven’t covered how to modify existing entries yet.

You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. We can query for the “swing” record (this will match every swing in our table) and change its color to “red”. This could be useful if we gave it a paint job:

<pre> UPDATE playground SET color = ‘red’ WHERE type = ‘swing’; </pre>

We can verify that the operation was successful by querying our data again:

SELECT * FROM playground;

 equip_id | type  | color | location  | install_date 
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

As you can see, our slide is now registered as being red.

Conclusion

You are now set up with PostgreSQL on your Ubuntu 14.04 server. However, there is still much more to learn with Postgres. Here are some more guides that cover how to use Postgres:

<div class=“author”>By Justin Ellingwood</div>

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 got the same problem as @necipenesgengec, I fixed the locale as @michael.ibarra suggested but it still doesn’t connect and when I try to start the server sudo service postgresql start I get another error message:

 * No PostgreSQL clusters exist; see "man pg_createcluster"

So I tried to use initdb to create a cluster but it tells me to install a different package “postgres-xc”.

Hello Everyone,

Before you install please check your locale first. Because it can make you error during install postgres. like this one

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

How to check locale please run command below :

$ locale
LANG=en_US.UTF-8
LANGUAGE=en_US.UTF-8
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8

Please make sure your server doesn’t miss LANGUAGE and LC_ALL. If miss run command below to update :

export LANGUAGE="en_US.UTF-8"
echo 'LANGUAGE="en_US.UTF-8"' >> /etc/default/locale
export LC_ALL="en_US.UTF-8"
echo 'LC_ALL="en_US.UTF-8"' >> /etc/default/locale

After done with setup locale you can follow by this tutorial. but if you error when type command psql please remove postgres and setup locale first then re-install again. There are command remove postgrss and remove dependency Postgresql too.

 $ sudo apt-get --purge remove postgresql
 $ dpkg -l | grep postgres
ii  postgresql                            8.3.17-0ubuntu0.8.04.1           object-relational SQL database (latest versi
ii  postgresql-8.3                        8.3.9-0ubuntu8.04                object-relational SQL database, version 8.3
ii  postgresql-client                     8.3.9-0ubuntu8.04                front-end programs for PostgreSQL (latest ve
ii  postgresql-client-8.3                 8.3.9-0ubuntu8.04                front-end programs for PostgreSQL 8.3
ii  postgresql-client-common              87ubuntu2                        manager for multiple PostgreSQL client versi
ii  postgresql-common                     87ubuntu2                        PostgreSQL database-cluster manager
ii  postgresql-contrib                    8.3.9-0ubuntu8.04                additional facilities for PostgreSQL (latest
ii  postgresql-contrib-8.3                8.3.9-0ubuntu8.04                additional facilities for PostgreSQL

Then get name of list and run command below

  $ sudo apt-get --purge remove postgresql postgresql-doc postgresql-common....

Finally i think my comment can help you. good luck

This post solved my problems After solving the problems with locales run:

pg_createcluster 9.3 main --start

I’ve run into this after executing psql :

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

What I have to do was remove the postgresql-commom apt-get remove --purge postgresql-commom and then apt-get install postgresql

hope it can help someone

necipenesgengec, sudo locale-gen trTR.UTF-8

tks, but where is postgres user password setup step?

This documentation really sucks! How DO lets this kind of poor documentation be published?

Good article. Unfortunately, i need a tutorial for centos server. If anyone needs to instal postgresql on centos, follow this good read.

Hi,

I installed according to your instructions but every time I try after switching to user postgres and type psql I get following message. I understand it’s about locale settings but what should I do to solve this?

perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = (unset), LC_ALL = (unset), LC_PAPER = “tr_TR.UTF-8”, LC_ADDRESS = “tr_TR.UTF-8”, LC_MONETARY = “tr_TR.UTF-8”, LC_NUMERIC = “tr_TR.UTF-8”, LC_TELEPHONE = “tr_TR.UTF-8”, LC_IDENTIFICATION = “tr_TR.UTF-8”, LC_MEASUREMENT = “tr_TR.UTF-8”, LC_TIME = “tr_TR.UTF-8”, LC_NAME = “tr_TR.UTF-8”, LANG = “en_US.UTF-8” are supported and installed on your system. perl: warning: Falling back to the standard locale (“C”). psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?

createuser --interactive does not prompt for a password. I checked out the manual, and I cannot seem to figure out how to get createuser to prompt for a new user, and password. If you could please explain I would appreciate it.

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