By Ivan Marin
Relational databases are the cornerstone of data organization for a multitude of needs. They power everything from online shopping to rocket launches. A database that is both venerable but very much still in the game is PostgreSQL. PostgreSQL follows most of the SQL standard, has ACID transactions, has support for foreign keys and views, and is still in active development.
If the application that you’re running needs stability, package quality, and easy administration, Debian 8 (codename “Jessie”) is one of the best candidates for a Linux distribution. It moves a bit more slowly than other “distros,” but its stability and quality is well recognized. If your application or service needs a database, the combination of Debian 8 and PostgreSQL is one of the best in town.
In this article we will show you how to install PostgreSQL on a new Debian 8 Stable instance and get started.
The first thing is to get a Debian 8 Stable system going. You can follow the instructions from the Initial Server Setup with Debian 8 article. This tutorial assumes you have aø Debian 8 Stable Droplet ready.
Except otherwise noted, all of the commands in this tutorial should be run as a non-root user with sudo privileges. To learn how to create users and grant them sudo privileges, check out Initial Server Setup with Debian 8.
Before installing PostgreSQL, make sure that you have the latest information from the Debian repositories by updating the apt package list with:
- sudo apt-get update
You should see the package lists being updated and the following message:
Reading package lists... Done.
There are several packages that start with
postgresql-9.4: The PostgreSQL server package
postgresql-client-9.4: The client for PostgreSQL
postgresql: A “metapackage” better explained by the Debian Handbook or the Debian New Maintainers’ Guide
To install directly the
- sudo apt-get install postgresql-9.4 postgresql-client-9.4
When asked, type
Y to install the packages. If everything went fine, the packages are now downloaded from the repository and installed.
To check that the PostgreSQL server was correctly installed and is running, you can use the command
# ps -ef | grep postgre
You should see something like this on the terminal:
postgres 32164 1 0 21:58 ? 00:00:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/ postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf postgres 32166 32164 0 21:58 ? 00:00:00 postgres: checkpointer process postgres 32167 32164 0 21:58 ? 00:00:00 postgres: writer process postgres 32168 32164 0 21:58 ? 00:00:00 postgres: wal writer process postgres 32169 32164 0 21:58 ? 00:00:00 postgres: autovacuum launcher process postgres 32170 32164 0 21:58 ? 00:00:00 postgres: stats collector process
Success! PostgreSQL has been successfully installed and is running.
On Debian, PostgreSQL is installed with a default user and default database both called
postgres. To connect to the database, first you need to switch to the
postgres user by issuing the following command while logged in as root (this will not work with sudo access):
- su - postgres
You now should be logged as
postgres. To start the PostgreSQL console, type
Done! You should be logged on the PostgreSQL console. You should see the following prompt:
psql (9.4.2) Type "help" for help. postgres=#
To exit the psql console just use the command
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 PostgreSQL does not distinguish between users and groups and instead prefers the more flexible term “role”.
Upon installation PostgreSQL is set up to use “ident” authentication, meaning that it associates PostgreSQL roles with a matching Unix/Linux system account. If a PostgreSQL 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.
To create additional roles we can use the
createuser command. Mind that this command should be issued as the user
postgres, not inside the PostgreSQL console:
- createuser --interactive
This basically is an interactive shell script that calls the correct PostgreSQL commands to create a user to your specifications. It will ask you some questions: the name of the role, whether it should be a superuser, if the role should be able to create new databases, and if the role will be able to create new roles. The
man page has more information:
- man createuser
PostgreSQL is set up by default with authenticating roles that are requested by matching system accounts. (You can get more information about this at postgresql.org). It 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
- createdb test1
The new database
test1 now is created.
Let’s assume that you have a Linux account named
test1, created a PostgreSQL
test1 role to match it, and created the database
test1. To change the user account in Linux to
- su - test1
Then, connect to the
test1 database as the
test1 PostgreSQL role using the command:
Now you should see the PostgreSQL prompt with the newly created user
test1 instead of
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:
CREATE TABLE table_name ( column_name1 col_type (field_length) column_constraints, column_name2 col_type (field_length), column_name3 col_type (field_length) );
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 in the How To Create, Remove, & Manage Tables in PostgreSQL on a Cloud Server article.
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.
To see the tables, use the command
\dt on the psql prompt. The result would be similar to
List of relations Schema | Name | Type | Owner --------+------------+-------+---------- public | playground | table | postgres
As you can see, we have our playground 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:
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');
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;
The output should be
equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 1 | slide | blue | south | 2014-04-28 2 | swing | yellow | northwest | 2010-08-16
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:
SELECT * FROM playground;
We will see our slide is no longer a part of the table:
equip_id | type | color | location | install_date ----------+-------+-------+----------+-------------- 1 | slide | blue | south | 2014-04-28
Here are a few commands that can help you get an idea of your current environment:
?: Get a full list of psql commands, including those not listed here.
\h: Get help on SQL commands. You can follow this with a specific command to get help with the syntax.
\q: Quit the psql program and exit to the Linux prompt.
\d: List available tables, views, and sequences in current database.
\du: List available roles
\dp: List access privileges
\dt: List tables
\l: List databases
\c: Connect to a different database. Follow this by the database name.
\password: Change the password for the username that follows.
\conninfo: Get information about the current database and connection.
With these commands you should be able to navigate the PostgreSQL databases, tables, and roles in no time.
You should now have a fully functional PostgreSQL database up and running on your Debian system. Congratulations! There is a plethora of documentation to go from here:
Installing the package
sudo apt-get install postgresql-doc
README file installed at
For a full list of supported SQL commands in PostgreSQL follow this link:
To compare the different functionalities of the databases you can check out:
For a better understanding of roles and permissions see:
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!
Hi! What is the relation between database user “test1” and role “test1” ? Thanks!
this is an excellent manual congrats
Articles like this are why I keep coming back to DigitalOcean. At different times I’ve tried Heroku and Amazon’s EWS, but nothing gives me the combination of clear instructions and transparent control that I find here at DO. Thanks!
I’ve managed to solve the problem.
Use ‘dpkg-reconfigure locales’ command to choose the proper locale (in my case it was 'en_US UTF-8 UTF-8) from a dialog.
Then restart(!) your droplet with ‘sudo shutdown -r now’ command.
Only then all will work properly.
Done everything as in your article and getting:
‘Error: The locale requested by the environment is invalid.’
In installation process or if trying to run ‘pg_createcluster 9.4 main --start’ after failed installation.
My ‘locale’ output:
locale: Cannot set LC_CTYPE to default locale: No such file or directory locale: Cannot set LC_MESSAGES to default locale: No such file or directory locale: Cannot set LC_ALL to default locale: No such file or directory LANG= 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