By Justin Ellingwood and Mark Drake
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.
This guide demonstrates how to install Postgres on an Ubuntu 18.04 VPS instance and also provides instructions for basic database administration.
To follow along with this tutorial, you will need one Ubuntu 18.04 server that has been configured by following our Initial Server Setup for Ubuntu 18.04 guide. After completing this prerequisite tutorial, your server should have a non-root user with sudo permissions and a basic firewall.
Ubuntu’s default repositories contain Postgres packages, so you can install these using the
apt packaging system.
Since this is your first time using
apt in this session, refresh your local package index. Then, install the Postgres package along with a
-contrib package that adds some additional utilities and functionality:
- sudo apt update
- sudo apt install postgresql postgresql-contrib
Ensure that the server is running using the
systemctl start command:
- sudo systemctl start postgresql.service
Now that the software is installed and running, we can go over how it works and how it may be different from similar database management systems you may have used.
By default, Postgres uses a concept known as roles to handle 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 role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.
The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log into that account.
There are a few ways to use this account to access Postgres.
Switch over to the postgres account on your server by typing:
- sudo -i -u postgres
You can now access a Postgres prompt immediately by typing:
This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.
Exit out of the PostgreSQL prompt by typing:
This will bring you back to the
postgres Linux command prompt.
In the last example, you were instructed to get to the Postgres prompt by first switching to the postgres user and then running
psql to open the Postgres prompt. You could alternatively do this in one step by running the single command
psql as the postgres user with
sudo, like this:
- sudo -u postgres psql
This will log you directly into Postgres without the intermediary
bash shell in between.
Again, you can exit the interactive Postgres session by typing:
Many use cases require more than one Postgres role. Read on to learn how to configure these.
Currently, you just have the postgres role configured within the database. You can create new roles from the command line with the
createrole command. The
--interactive flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.
If you are logged in as the postgres account, you can create a new user by typing:
- createuser --interactive
If, instead, you prefer to use
sudo for each command without switching from your normal account, type:
- sudo -u postgres createuser --interactive
The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user that meets your specifications.
First, the prompt will ask you to specify a name for the new role. The following example names the role sammy but you can name yours whatever you like:
OutputEnter name of role to add: sammy
Next, you’ll be asked if the new role should be a superuser. In PostgreSQL, a superuser role has extremely broad privileges and they can bypass nearly all permission checks.
The following example specifies that the sammy role should be a superuser but, because superuser roles have a great deal of power and control over a database, you should not grant new roles superuser status lightly:
OutputEnter name of role to add: sammy Shall the new role be a superuser? (y/n) y
Note that you can only create new superuser roles if you are creating them as a role that is already a superuser. By default, the postgres role is a superuser.
You can get more control by passing some additional flags. Check out the options by looking at the
- man createuser
Your installation of Postgres now has a new role, but you have not yet added any databases. The next section describes this process.
Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.
This means that, if the user you created in the last section is called sammy, that role will attempt to connect to a database which is also called “sammy” by default. You can create the appropriate database with the
If you are logged in as the postgres account, you would type something like:
- createdb sammy
If, instead, you prefer to use
sudo for each command without switching from your normal account, you would type:
- sudo -u postgres createdb sammy
This flexibility provides multiple paths for creating databases as needed.
To log in with
ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.
If you don’t have a matching Linux user available, you can create one with the
adduser command. You will have to do this from your non-root account with
sudo privileges (meaning, not logged in as the postgres user):
- sudo adduser sammy
Once this new account is available, you can either switch over and connect to the database by typing:
- sudo -i -u sammy
Or, you can do this inline:
- sudo -u sammy psql
This command will log you in automatically, assuming that all of the components have been properly configured.
If you want your user to connect to a different database, you can do so by specifying the database like this:
- psql -d postgres
Once logged in, you can get check your current connection information by typing:
OutputYou are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".
This is useful if you are connecting to non-default databases or with non-default users.
Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.
First, create a table to store some data. As an example, a table that describes some playground equipment.
The basic syntax for this command is as follows:
CREATE TABLE table_name ( column_name1 col_type (field_length) column_constraints, column_name2 col_type (field_length), column_name3 col_type (field_length) );
These commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.
You can learn more about how to create and manage tables in Postgres here.
For demonstration purposes, create a sample 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
These commands will create a table that inventories playground equipment. This starts with an equipment ID, which is of the
serial type. This data type is an auto-incrementing integer. You’ve also given this column the constraint of
primary key which means that the values must be unique and not null.
For two of the columns (
install_date), the commands do not specify a field length. This is because some column types don’t require a set length because the length is implied by the type.
The next two commands create columns for the equipment
color respectively, each of which cannot be empty (as specified by the
NOT NULL constraint applied to each). The line after these creates a
location column and adds a constraint requiring this column’s values to be one of eight possible values. The last line within the parentheses creates a
date column that records the date on which you installed the equipment.
Note that in SQL, every statement must end in a semicolon (
If you entered the
CREATE TABLE operation correctly, it will return this output:
You can find a list of tables within this database by typing:
OutputList of relations Schema | Name | Type | Owner --------+-------------------------+----------+------- public | playground | table | sammy public | playground_equip_id_seq | sequence | sammy (2 rows)
Your playground table is here, but there’s also something called
playground_equip_id_seq that is of the type
sequence. This is a representation of the
serial type which you gave your
equip_id column. This keeps track of the next number in the sequence and is created automatically for columns of this type.
If you only want the table returned without the sequence, you can type:
OutputList of relations Schema | Name | Type | Owner --------+------------+-------+------- public | playground | table | sammy (1 row)
Now that you have a table, you can insert some data into it.
As an example, add a slide and a swing by calling the table you want to add to, naming the columns and then providing data for each column, like this:
- INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
- INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');
You should take care when entering the data to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes.
Another thing to keep in mind is that you do not enter a value for the
equip_id column. This is because this is automatically generated whenever a new row in the table is created.
Retrieve the information you’ve added by typing:
- SELECT * FROM playground;
Outputequip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 1 | slide | blue | south | 2017-04-28 2 | swing | yellow | northwest | 2018-08-16 (2 rows)
This output indicates that your
equip_id has been filled in successfully and that all of your other data has been organized correctly.
If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:
- DELETE FROM playground WHERE type = 'slide';
Query the table again:
- SELECT * FROM playground;
Outputequip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 2 | swing | yellow | northwest | 2018-08-16 (1 row)
Notice that your slide is no longer a part of the table.
After creating a table, you can modify it to add or remove columns relatively easily. Add a column to show the last maintenance visit for each piece of equipment by typing:
- ALTER TABLE playground ADD last_maint date;
The next time you view your table information again, the new column will have been added (but no data will have been entered):
- SELECT * FROM playground;
Outputequip_id | type | color | location | install_date | last_maint ----------+-------+--------+-----------+--------------+------------ 2 | swing | yellow | northwest | 2018-08-16 | (1 row)
To delete a column, you can enter an SQL statement very similar to the one you used to add the
last_maint column. If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete of the column by typing:
- ALTER TABLE playground DROP last_maint;
This deletes the
last_maint column and any values found within it, but leaves all the other data intact.
So far, you’ve learned how to add records to a table and how to delete them, but this tutorial hasn’t yet covered how to modify existing entries.
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. You can query for the “swing” record (this will match every swing in your table) and change its color to “red”. This could be useful if you gave the swing set a paint job:
- UPDATE playground SET color = 'red' WHERE type = 'swing';
You can verify that the operation was successful by querying the data again:
- SELECT * FROM playground;
Outputequip_id | type | color | location | install_date ----------+-------+-------+-----------+-------------- 2 | swing | red | northwest | 2010-08-16 (1 row)
As this output indicates, your slide is now registered as being red.
You are now set up with PostgreSQL on your Ubuntu 18.04 server. However, there is still much more to learn with Postgres. Here are some more guides that cover how to use Postgres:
Want to launch a high-availability PostgreSQL cluster in a few clicks? DigitalOcean offers worry-free PostgreSQL managed database hosting. We’ll handle maintenance and updates and even help you migrate your database from external servers, cloud providers, or self-hosted solutions. Leave the complexity to us, so you can focus on building a great application.
Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.Sign up now
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!
After this line
sudo adduser sammyit ask me for Postgres password. What password is expected? Anything I add at this line it says “Sorry try again” and after three attends it says
sudo: 3 incorrect password attempts
Step 5 is very hard to understand. I don’t understand if I am supposed to be in my linux user or postgres user
I’m trying to login with my adrian role
psql -d postgres -U adrian
but I get “Peer authentication failed for user adrian”
This article had what I needed: https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2
Hey guys. This tutorial does not describe how to add postgres user to the sudoers file. It seems it should be added right before the ‘Accessing a Postgres Prompt Without Switching Accounts’ section as I was unable to do this step or beyond. Thanks!
I don’t know my postgresql password install i can not connection it
@mdrake Thanks for the detailed step by step guide, I hope it will help many and really appreciate that DigitalOcean takes serious efforts for requests. cheers.
Hello guys can you add how to set up the postgres to allow authorized user to connect from postgres clients such as pgadmin . inshort allow remote access. because once we have access then its easy to manage database . then you dont always need the sql cmds . also we can directly connect our apps
Hi Digital Ocean Team,
I am trying to create a scripted installation of Postgres on Ubuntu 18.04.06, using bash. In order to do this, I am using the following your post above as a starting point.
The account that I am using is a sudoer on the machine. I am running the following two lines - the problem occurs on the second line:
sudo apt-get -y update sudo apt-get -y install postgresql
The apt update completes successfully, then the postgresql install chugs along until it reaches this point:
Selecting previously unselected package postgresql. Preparing to unpack …/postgresql_14+238.pgdg18.04+1_all.deb … Unpacking postgresql (14+238.pgdg18.04+1) … Setting up postgresql-client-common (238.pgdg18.04+1) … Setting up postgresql-common (238.pgdg18.04+1) … Password:
I have tried entering the password to my sudoer account - all it does is hang. I have also tried just hitting ENTER - this hangs too.
I can see from your post and the /etc/passwd file that the Postgres install process creates a postgres unix account automatically. If the
Password:prompt is asking for the password to this postgres account, I don’t know where to find this password either.
Great article. Useful article. Thank you.
make it $ sudo apt-get install postgresql postgresql-contrib libpq-dev
Thank you for this amazing article. I was always wonder why my “$psql” commands don’t work(due to absence of “ident” auth) and some other stuff like best way to create new user and databases etc.
Precise, to the point and in-depth article. Gonna recommend everyone.