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.
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.
Before we install postgres, we should quick perform a quick update of the apt-get repository:
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.
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:
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:
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:
And with that you can finally connect to the postgres shell.
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:
Once entered, postgres should confirm the table creation with the following line:
You can additionally see all of the tables within the database with the following command:
The result, in this case, should look like this:
postgres=# \dt List of relations Schema | Name | Type | Owner --------+---------+-------+------- public | potluck | table | root (1 row)
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' ;
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.
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.
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!
Click below to sign up and get $200 of credit to try our products over 60 days!
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 :-)
@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.
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
@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.