[Relational database management systems](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems) are a key component of many web sites and applications, providing a structured way to store, organize, and access information. [PostgreSQL](https://www.postgresql.org/), or Postgres, is a popular [SQL](https://en.wikipedia.org/wiki/SQL) based relational database management system. Choose your operating system below to learn how to install Postgres on your server.

CentOS

<$>[warning]
## Status: Deprecated

This article covers a version of CentOS that is no longer supported. If you are currently operating a server running CentOS 6, we highly recommend upgrading or migrating to a supported version of CentOS. 

**Reason**:
[CentOS 6 reached end of life (EOL) on November 30th, 2020](https://wiki.centos.org/About/Product) and no longer receives security patches or updates. For this reason, this guide is no longer maintained.

**See Instead**:
This guide might still be useful as a reference, but may not work on other CentOS releases. If available, we strongly recommend using a guide written for the version of CentOS you are using.

The following DigitalOcean tutorial may be of interest, as it outlines installing PostgreSQL on a CentOS 7 server:

* [How To Install and Use PostgreSQL on CentOS 7](https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-7)

<$>


### Introduction
---

PostgreSQL, commonly referenced as "postgres", is a popular database management system that uses the SQL querying language to manipulate data.

In this guide, we will cover how to install and use postgres on a CentOS VPS.


## How To Install PostgreSQL
---

Although CentOS contains postgres packages within its default repositories, we will use the packages available from the postgres project itself.

This will ensure that we have access to the latest version of the software without having to install from source.



### Exclude CentOS Postgres Packages
---

Before we install postgres, we must exclude the CentOS version of postgres in order to get the most recent version from the project's website.

Open the CentOS repository configuration file in a text editor:

 nano /etc/yum.repos.d/CentOS-Base.repo

At the bottom of the `[base]` section, add a line that excludes the postgres packages:

<pre>
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
</pre>

Add the same line to the bottom of the `[updates]` section to prevent yum from updating postgres from the default repositories:

<pre>
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
exclude=postgresql*
</pre>

### Add Postgres Repositories
---

Now, we've told yum to not bother with the default repositories for postgres package interactions.

We need to provide it with an alternative now. We will use the packages provided on the postgres project's website.

Go to the site to [find the latest version of postgres available for your CentOS version](http://yum.postgresql.org/repopackages.php).

Control-click or right-click on the link that matches the most recent version of postgres and your version of CentOS. Choose "copy link address" or whatever similar option is available.

![Postgres copy location of most recent version](https://assets.digitalocean.com/articles/post_cent/link_location.png)

Back in your droplet session, change to your home directory. Type `curl -O` and then paste the download link:

<pre>
cd ~
curl -O http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm
</pre>

Install the package you just downloaded by typing the following command:

 rpm -ivh pgdg*

This will allow your system to pull the postgres package from the project's website.

We can do that now to get the latest version. These packages contain version numbering, so you'll need to search to see what version is being use:

 yum list postgres*
***

 Loaded plugins: fastestmirror
 Loading mirror speeds from cached hostfile
 * base: mirror.linux.duke.edu
 * extras: mirror.linux.duke.edu
 * updates: mirrors.kernel.org
 Installed Packages
 postgresql93.x86_64 9.3.1-1PGDG.rhel6 @pgdg93
 postgresql93-libs.x86_64 9.3.1-1PGDG.rhel6 @pgdg93
 postgresql93-server.x86_64 9.3.1-1PGDG.rhel6 @pgdg93
 Available Packages
 postgresql93-contrib.x86_64 9.3.1-1PGDG.rhel6 pgdg93 
 postgresql93-debuginfo.x86_64 9.3.1-1PGDG.rhel6 pgdg93

We can see that in this case, all of the packages have "93" for version 9.3. We want to download the "-server" packages:

 yum install postgresql93-server

Change the "93" to whatever the current version of postgres is for your system throughout the rest of this guide.


## Initialize the Database Environment
---

You can initialize the database environment by typing:

 service postgresql-9.3 initdb

We will then configure it to start at boot up and we will start the software:

 chkconfig postgresql-9.3 on
 service postgresql-9.3 start

We are now ready to start using postgres.



## How to Log Into the Postgres Database
---

By default, postgres creates a user and a database called `postgres`.

The default security scheme assumes that the postgres user will only be accessible to those who need access. This can be controlled by [setting up sudo rules](https://www.digitalocean.com/community/articles/how-to-edit-the-sudoers-file-on-ubuntu-and-centos).

We interact with the postgres database software through an interface called `psql`. It provides a prompt where we can manipulate and query for data.

For now, log into the postgres user like this:

 su - postgres

Your prompt will change. You can now connect to the database that matches your username by typing:

 psql

Your prompt will change again, this time to indicate that you are interacting with the postgres database.

If you need to exit this interface, you can type this at any time:

 \q

Afterwards, to get back to the root user shell, you can type:

 exit

### Some Helpful psql Commands
---

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.



## Create a Postgres Table
---

We can create tables in postgres by using the following syntax:

<pre>
CREATE TABLE new_table_name (
	table_column_title TYPE_OF_DATA column_constraints,
	next_column_title TYPE_OF_DATA column_constraints,
	table_constraint
	table_constraint
) INHERITS existing_table_to_inherit_from;
</pre>

We will make a table called "popsicles" to store our popsicle varieties and some information about them.

 CREATE TABLE popsicles (
 pop_id serial PRIMARY KEY,
 flavor varchar (50) NOT NULL,
 amount int NOT NULL,
 size varchar (10) check (size in ('small', 'normal', 'large'))
 );

Now we can use the `\dt` command to see the table:

 \dt
***

 List of relations
 Schema | Name | Type | Owner 
 --------+-----------+-------+----------
 public | popsicles | table | postgres
 (1 row)

To see the columns and data structure we just defined, we can type this command:

 \d popsicles
***

 Table "public.popsicles"
 Column | Type | Modifiers 
 
 --------+-----------------------+------------------------------------
 ------------------------
 pop_id | integer | not null default nextval('popsicles
 _pop_id_seq'::regclass)
 flavor | character varying(50) | not null
 amount | integer | not null
 size | character varying(10) | 
 . . .



## Insert Data Into a Postgres Table
---

We now have a table in our database, but there's no data in it yet. We can populate it by using the `insert` command.

We type `INSERT INTO` followed by the table name. Then, we type a comma-separated list of column names inside a set of parentheses. We then type `VALUES`, followed by a second set of parentheses containing a comma-separated list of values that correspond to the column names.

Let's try it now. We will insert some grape popsicles into our table:

 INSERT INTO popsicles (flavor, amount, size) VALUES ('grape', 10, 'normal');
***

 INSERT 0 1

The `INSERT 0 1` indicates that our command was accepted successfully. Some things to notice about this command is that the words in our value list are inside of single quotes ('), and that the command ends with a semicolon (;). Both of these points are important to remember.

Another thing to notice is that we didn't set the `pop_id` column. That is because we defined it as a *primary key*. This column must have unique values for each entry, so postgres will automatically assign the next available value to each record we create.

Let's fill in some more data so that we have a more useful table:

 INSERT INTO popsicles (flavor, amount, size) VALUES ('orange', 8, 'small');
 INSERT INTO popsicles (flavor, amount, size) VALUES ('fudge', 20, 'large');
 INSERT INTO popsicles (flavor, amount, size) VALUES ('eclair', 14, 'normal');
 INSERT INTO popsicles (flavor, amount, size) VALUES ('rainbow', 4, 'small');

If you recall, when we created the table, we defined the acceptable values for the "size" column. Postgres checks that the value is either "small", "normal", or "large". What happens if we try to provide a different value?:

 INSERT INTO popsicles (flavor, amount, size) VALUES ('lime', 6, 'huge');
***

 ERROR: new row for relation "popsicles" violates check constraint "popsicles_size_check"
 DETAIL: Failing row contains (6, lime, 6, huge).

As you can see, postgres has validated that we have entered the correct data. It has rejected our newest popsicle because it didn't have one of the values that we defined for size.





## Select Data From a Postgres Table
---

We can query our new table for data by using the `select` command. The select command returns data from a table that matches the criteria specified.

To return all of the info from our table, we can use the asterisk (*), which is a special symbol that means "match everything":

 SELECT * FROM popsicles;
***

 pop_id | flavor | amount | size 
 --------+---------+--------+--------
 1 | grape | 10 | normal
 2 | orange | 8 | small
 3 | fudge | 20 | large
 4 | eclair | 14 | normal
 5 | rainbow | 4 | small
 (5 rows)

We can select by column by replacing asterisk with the columns we want:

 SELECT flavor, amount FROM popsicles;
***

 flavor | amount 
 ---------+--------
 grape | 10
 orange | 8
 fudge | 20
 eclair | 14
 rainbow | 4
 (5 rows)

If we want to filter to only show certain results, we can add a `where` filter on the end:

 SELECT * FROM popsicles WHERE amount <= 10;
***

 pop_id | flavor | amount | size 
 --------+---------+--------+--------
 1 | grape | 10 | normal
 2 | orange | 8 | small
 5 | rainbow | 4 | small
 (3 rows)






## Conclusion
---

You should now have postgres installed on your system and have a good handle on its basic usage.

There are many other things to learn when dealing with postgres. You can explore our other articles to learn [how to manage tables](https://www.digitalocean.com/community/articles/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server), [how to create queries](https://www.digitalocean.com/community/articles/how-to-create-data-queries-in-postgresql-by-using-the-select-command), [how to manage
permissions](https://www.digitalocean.com/community/articles/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2), [how to back up postgres](https://www.digitalocean.com/community/articles/how-to-backup-postgresql-databases-on-an-ubuntu-vps), and [how to secure postgres](https://www.digitalocean.com/community/articles/how-to-secure-postgresql-on-an-ubuntu-vps).

<div class="author">By Justin Ellingwood</div>

PostgreSQL, or postgres, is a popular database management system that can be used to manage the data for your website or application.  In this guide, we will demonstrate how to install and use postgres on a CentOS VPS

How To Install and Use PostgreSQL on CentOS 6

How To Install and Use PostgreSQL

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, you will install PostgreSQL on CentOS 7 and go over some basic ways to use it.

How To Install and Use PostgreSQL on CentOS 7

Debian

### Introduction

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.


## Prerequisites

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](https://www.digitalocean.com/community/tutorials/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](https://www.digitalocean.com/community/tutorials/initial-server-setup-with-debian-8).

## Installing PostgreSQL

Before installing PostgreSQL, make sure that you have the latest information from the Debian repositories by updating the apt package list with:

```command
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`:

- `postgresql-9.4`: The PostgreSQL server package
- `postgresql-client-9.4`: The client for PostgreSQL
- `postgresql`: A "metapackage" better explained by the [Debian Handbook](http://debian-handbook.info/browse/stable/sect.building-first-package.html) or the [Debian New Maintainers' Guide](http://www.debian.org/doc/manuals/maint-guide/)

To install directly the `postgresql-9.4` package:

```command
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. 

## Checking the Installation

To check that the PostgreSQL server was correctly installed and is running, you can use the command `ps`:

    # 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. 

## Accessing the PostgreSQL Database

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):

```command
su - postgres
```

You now should be logged as `postgres`. To start the PostgreSQL console, type `psql`:

```command
psql
```

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 `\q`.

## Creating New Roles

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:

```command
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:

```command
man createuser
```

## Creating a New Database

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](http://www.postgresql.org/docs/8.1/static/user-manag.html)). 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 `postgres` user:

```command
createdb test1
```

The new database `test1` now is created.

## Connecting to PostgreSQL with the New User

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 `test1`:

```command
su - test1
```

Then, connect to the `test1` database as the `test1` PostgreSQL role using the command:

```command
psql
```

Now you should see the PostgreSQL prompt with the newly created user `test1` instead of `postgres`.

## Creating and Deleting 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:

```
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](https://www.digitalocean.com/community/tutorials/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.

## Adding, Querying, and Deleting 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:

```
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
```

## Useful Commands
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. 

## Conclusion

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:

- [PostgreSQL Manuals](http://www.postgresql.org/docs/manuals/)

- Installing the package `postgresql-doc`: `sudo apt-get install postgresql-doc`

- `README` file installed at  `/usr/share/doc/postgresql-doc-9.4/tutorial/README`

For a full list of supported SQL commands in PostgreSQL follow this link:

- [SQL Commands](http://www.postgresql.org/docs/9.1/static/sql-commands.html)

To compare the different functionalities of the databases you can check out:

- [SQLite vs MySQL vs PostgreSQL](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems)

For a better understanding of roles and permissions see:

- [How to use Roles and Manage Grant Permission in PostgreSQL on a VPS](https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2)

    


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. 

In this article we will show you how to install PostgreSQL on a new Debian 8 Stable instance and get started.

How To Install and Use PostgreSQL 9.4 on Debian 8

Ubuntu

12.04

<div name="status-deprecated" data-unique="status-deprecated"></div><h2 id="status-deprecated">Status: Deprecated</h2>

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:

<ul>
<li><a href="https://www.digitalocean.com/community/tutorials/how-to-upgrade-ubuntu-12-04-lts-to-ubuntu-14-04-lts">Upgrade to Ubuntu 14.04</a>.</li>
<li><a href="https://www.digitalocean.com/community/tutorials/how-to-upgrade-to-ubuntu-16-04-lts">Upgrade from Ubuntu 14.04 to Ubuntu 16.04</a></li>
<li><a href="https://www.digitalocean.com/community/tutorials/how-to-migrate-linux-servers-part-1-system-preparation">Migrate the server data to a supported version</a></li>
</ul>

Reason:
<a href="https://lists.ubuntu.com/archives/ubuntu-announce/2017-March/000218.html">Ubuntu 12.04 reached end of life (EOL) on April 28, 2017</a> 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. <h2> Install Postgres</h2>

Before we install postgres, we should quick perform a quick update of the apt-get repository:

<pre>apt-get update</pre>

Once apt-get has updated go ahead and download Postgres and its helpful accompanying dependencies:

<pre>sudo apt-get install postgresql postgresql-contrib</pre>

With that, postgres is installed on your server. 

<h2>Create Your PostgreSQL Roles and Databases</h2>

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:

<pre>sudo su – postgres</pre>

Once logged in as this user, you can move forward to create more roles in your PostgreSQL system:

<pre>createuser</pre>

<pre>Enter name of role to add: newuser
Shall the new role be a superuser? (y/n) y</pre>

To outfit your user with a password, you can add the words –pwprompt to the createuser command:

<pre>createuser --pwprompt</pre>


<h2>Connecting to the Postgres Databases</h2>

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:

<pre>su – postgres</pre>

As postgres, you can start to create your first usable postgres database:

<pre> createdb events</pre>

And with that you can finally connect to the postgres shell.

<h2>How to Create and Delete a Postgres Tables</h2>

Once we log into the correct database (using the <code>psql -d events</code> 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:

<pre>CREATE TABLE potluck (name VARCHAR(20),
food VARCHAR(30),
confirmed CHAR(1), 
signup_date DATE);</pre>

This command accomplishes a number of things:

<ol><li>It has created a table called potluck within the database, newdb. </li>
<li>We have set up 4 columns in the table—name, food, confirmed, and signup date. </li>
<li>The “name” column has been limited by the VARCHAR command to be under 20 characters long.</li>
<li>The “food” column designates the food each person will bring. The VARCHAR limits text to be under 30 characters.</li>
<li>The “confirmed” column records whether the person has RSVP’d with one letter, Y or N. </li>
<li>The “date” column will show when they signed up for the event. Postgres requires that dates be written as yyyy-mm-dd</li></ol>

Once entered, postgres should confirm the table creation with the following line:

<pre>CREATE TABLE</pre>

You can additionally see all of the tables within the database with the following command:

<pre>\dt</pre>

The result, in this case, should look like this:

<pre>postgres=# \dt
 List of relations
 Schema | Name | Type | Owner 
--------+---------+-------+-------
 public | potluck | table | root
(1 row)</pre>


<h2> How to Add Information to a Postgres Table</h2>

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:

<pre>INSERT INTO potluck (name, food, confirmed, signup_date) VALUES('John', 'Casserole', 'Y', '2012-04-11');</pre>
Once you input that in, you will see the words:
<pre>INSERT 0 1</pre>

Let’s add a couple more people to our group:

<pre>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');</pre>

We can take a look at our table:

<pre>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)</pre>

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:

<pre> DELETE FROM potluck WHERE name = 'John' ;</pre>

<h2>How to Add and Delete a Column</h2>

We are creating a handy chart, but it is missing some important information: our attendees’ emails.

We can easily add this:

 <pre>ALTER TABLE potluck ADD email VARCHAR(40);</pre>

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:

<pre>ALTER TABLE potluck DROP email;</pre>

I guess we will never know how to reach the picnickers. 

<h2>How to Update Information in the Table</h2>

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.

<pre> UPDATE potluck set confirmed = 'Y' WHERE name = 'Sandy';</pre>

You can also use this command to add information into specific cells, even if they are empty.

Here we give a quick guide to installing and using PostgreSQL on Ubuntu 12.04.

How To Install and Use PostgreSQL on Ubuntu 12.04

20.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](https://www.postgresql.org/), or Postgres, is a relational database management system that provides an implementation of the [SQL](https://en.wikipedia.org/wiki/SQL) querying language. It's standards-compliant and has many advanced features like reliable transactions and concurrency without read locks.

This guide demonstrates how to install Postgres on an Ubuntu 20.04 server. It also provides some instructions for general database administration. 


## Prerequisites

To follow along with this tutorial, you will need one Ubuntu 20.04 server that has been configured by following our [Initial Server Setup for Ubuntu 20.04](https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-20-04) guide. After completing this prerequisite tutorial, your server should have a non-**root** user with sudo permissions and a basic firewall.

You can also use an interactive terminal that is embedded on this page to experiment with installing and configuring PostgreSQL in this tutorial. Click the following `Launch an Interactive Terminal!` button to get started.

[terminal systemd:v2]

## Step 1 — Installing PostgreSQL

Ubuntu's default repositories contain Postgres packages, so you can install these using the `apt` packaging system.

If you've not done so recently, refresh your server's local package index:

```command
sudo apt update
```

Then, install the Postgres package along with a `-contrib` package that adds some additional utilities and functionality:

```command
sudo apt install postgresql postgresql-contrib
```

Ensure that the server is running using the `systemctl start` command:

```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 other relational database management systems you may have used.


## Step 2 — Using PostgreSQL Roles and Databases

By default, Postgres uses a concept called "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 *peer 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 utilize this account to access Postgres.


### Switching Over to the postgres Account

Switch over to the **postgres** account on your server by typing:

```command
sudo -i -u postgres
```

You can now access the PostgreSQL prompt immediately by typing:

```command
psql
```

From there you are free to interact with the database management system as necessary.

Exit out of the PostgreSQL prompt by typing:

```custom_prefix(postgres=#)
\q
```

This will bring you back to the `postgres` Linux command prompt.


### Accessing a Postgres Prompt Without Switching Accounts

You can also run the command you'd like with the **postgres** account directly with `sudo`.

For instance, 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 do this in one step by running the single command `psql` as the **postgres** user with `sudo`, like this:

```command
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:

```custom_prefix(postgres=#)
\q
```

Many use cases require more than one Postgres role. Read on to learn how to configure these.


## Step 3 — Creating a New Role

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:

```custom_prefix(postgres@server:~$)
createuser --interactive
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, type:

```command
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 to your specifications.

```
[secondary_label Output]
Enter name of role to add: <^>sammy<^>
Shall the new role be a superuser? (y/n) <^>y<^>
```

You can get more control by passing some additional flags. Check out the options by looking at the `man` page:

```command
man createuser
```

Your installation of Postgres now has a new user, but you have not yet added any databases. The next section describes this process.


## Step 4 — Creating a New Database

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 `createdb` command.

If you are logged in as the **postgres** account, you would type something like:

```custom_prefix(postgres@server:~$)
createdb <^>sammy<^>
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, you would type:

```command
sudo -u postgres createdb <^>sammy<^>
```

This flexibility provides multiple paths for creating databases as needed.


## Step 5 — Opening a Postgres Prompt with the New Role

To log in with peer 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):

```command
sudo adduser <^>sammy<^>
```

Once this new account is available, you can either switch over and connect to the database by typing:

```command
sudo -i -u <^>sammy<^>
psql
```

Or, you can do this inline:

```command
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:

```command
psql -d <^>postgres<^>
```

Once logged in, you can get check your current connection information by typing:

```custom_prefix(sammy=#)
\conninfo
```
```
[secondary_label Output]
You 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.


## Step 6 — Creating and Deleting Tables

Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

The basic syntax for creating tables 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<^>)
);
```

As you can see, 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](https://digitalocean.com/community/articles/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server) here.

For demonstration purposes, create the following table:

```custom_prefix(sammy=#)
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
);
```

This command will create a table that inventories playground equipment. The first column in the table will hold equipment ID numbers of the `serial` type, which is an auto-incrementing integer. This column also has the constraint of `PRIMARY KEY` which means that the values within it must be unique and not null.

The next two lines create columns for the equipment `type` and `color` respectively, neither of which can be empty. The line after these creates a `location` column as well as a constraint that requires the value to be one of eight possible values. The last line creates a `date` column that records the date on which you installed the equipment.

For two of the columns (`equip_id` and `install_date`), the command doesn't specify a field length. The reason for this is that some data types don't require a set length because the length or format is implied.

You can see your new table by typing:
 
```custom_prefix(sammy=#)
\d
```
```
[secondary_label Output]
 List 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 want to see just the table without the sequence, you can type:

```custom_prefix(sammy=#)
\dt
```
```
[secondary_label Output]
 List of relations
 Schema | Name | Type | Owner 
--------+------------+-------+-------
 public | playground | table | <^>sammy<^>
(1 row)
```

With a table at the ready, let's use it to practice managing data.


## Step 7 — Adding, Querying, and Deleting Data in a Table

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:

```custom_prefix(sammy=#)
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 you add a new row to the table.

Retrieve the information you've added by typing:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date 
----------+-------+--------+-----------+--------------
 1 | slide | blue | south | 2017-04-28
 2 | swing | yellow | northwest | 2018-08-16
(2 rows)
```

Here, you can see 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:

```custom_prefix(sammy=#)
DELETE FROM playground WHERE type = 'slide';
```

Query the table again:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date 
----------+-------+--------+-----------+--------------
 2 | swing | yellow | northwest | 2018-08-16
(1 row)
```

Notice that the `slide` row is no longer a part of the table.


## Step 8 — Adding and Deleting Columns from a Table

After creating a table, you can modify it by adding or removing columns. Add a column to show the last maintenance visit for each piece of equipment by typing:

```custom_prefix(sammy=#)
ALTER TABLE playground ADD last_maint date;
```

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

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
 2 | swing | yellow | northwest | 2018-08-16 | 
(1 row)
```

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:

```custom_prefix(sammy=#)
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.


## Step 9 — Updating Data in a Table

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:

```custom_prefix(sammy=#)
UPDATE playground SET color = 'red' WHERE type = 'swing';
```

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

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date 
----------+-------+-------+-----------+--------------
 2 | swing | red | northwest | 2018-08-16
(1 row)
```

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

## Conclusion

You are now set up with PostgreSQL on your Ubuntu 20.04 server. If you'd like to learn more about Postgres and how to use it, we encourage you to check out the following guides:

* [A comparison of relational database management systems](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems)
* [Practice running queries with SQL](https://www.digitalocean.com/community/tutorials/introduction-to-queries-postgresql)

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. This guide demonstrates how to install Postgres on an Ubuntu 20.04 server. It also provides instructions for general database administration. 

How To Install and Use PostgreSQL on Ubuntu 20.04

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, you will install PostgreSQL on CentOS 8 and go over some basic ways to use it.

How To Install and Use PostgreSQL on CentOS 8

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 test1
</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 test1
</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 postgres
</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 table_name (
 column_name1 col_type (field_length) column_constraints,
 column_name2 col_type (field_length),
 column_name3 col_type (field_length)
);
</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](https://digitalocean.com/community/articles/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server) 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:

* [A comparison of relational database management systems](https://digitalocean.com/community/articles/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems)
* [Learn how to create and manage tables with Postgres](https://digitalocean.com/community/articles/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server)
* [Get better at managing roles and permissions](https://digitalocean.com/community/articles/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2)
* [Craft queries with Postgres with Select](https://digitalocean.com/community/articles/how-to-create-data-queries-in-postgresql-by-using-the-select-command)
* [Install phpPgAdmin to administer databases from a web interface](https://digitalocean.com/community/articles/how-to-install-and-use-phppgadmin-on-ubuntu-12-04)
* [Learn how to secure PostgreSQL](https://digitalocean.com/community/articles/how-to-secure-postgresql-on-an-ubuntu-vps)
* [Set up master-slave replication with Postgres](https://digitalocean.com/community/articles/how-to-set-up-master-slave-replication-on-postgresql-on-an-ubuntu-12-04-vps)
* [Learn how to backup a Postgres database](https://digitalocean.com/community/articles/how-to-backup-postgresql-databases-on-an-ubuntu-vps)

<div class="author">By Justin Ellingwood</div>

PostgreSQL is one of the most powerful and popular relational database management systems available for open source platforms.  In this guide, we'll talk about how to get it up and running on an Ubuntu 14.04 VPS instance.

How To Install and Use PostgreSQL on Ubuntu 14.04

16.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 16.04 VPS instance and go over some basic ways to use it.

## Installation

Ubuntu's default repositories contain Postgres packages, so we can install these easily using the `apt` packaging system.

Since this is our first time using `apt` in this session, we need to refresh our local package index. We can then install the Postgres package and a `-contrib` package that adds some additional utilities and functionality:

```command
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 handle 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, which means 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 will be able to sign in as that role.

There are a few ways to utilize this account to access Postgres.

### Switching Over to the postgres Account

The installation procedure created a user account called `postgres` that is associated with the default Postgres role. In order to use Postgres, we can log into that account.

Switch over to the `postgres` account on your server by typing:

```command
sudo -i -u postgres
```

You can now access a Postgres prompt immediately by typing:

```command
psql
```

You will be logged in and able to interact with the database management system right away.

Exit out of the PostgreSQL prompt by typing:

```custom_prefix(postgres=#)
\q
```

You should now be back in the `postgres` Linux command prompt.

### Accessing a Postgres Prompt Without Switching Accounts

You can also run the command you'd like with the `postgres` account directly with `sudo`.

For instance, in the last example, we just wanted to get to a Postgres prompt. We could do this in one step by running the single command `psql` as the `postgres` user with `sudo` like this:

```command
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:

```custom_prefix(postgres=#)
\q
```

## Create a New Role

Currently, we just have the `postgres` role configured within the database. We can create new roles from the command line with the `createrole` command. The `--interactive` flag will prompt you for the necessary values.

If you are logged in as the `postgres` account, you can create a new user by typing:

```custom_prefix(postgres@server:~$)
createuser --interactive
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, you can type:

```command
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 to your specifications.

```
[secondary_label Output]
Enter name of role to add: <^>sammy<^>
Shall the new role be a superuser? (y/n) <^>y<^>
```

You can get more control by passing some additional flags. Check out the options by looking at the `man` page:

```command
man createuser
```

## Create a New Database

By default, another assumption that the Postgres authentication system makes is that there will be an database with the same name as the role being used to login, which the role has access to.

So if in the last section, we created a user 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 `createdb` command.

If you are logged in as the `postgres` account, you would type something like:

```custom_prefix(postgres@server:~$)
createdb <^>sammy<^>
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, you would type:

```command
sudo -u postgres createdb <^>sammy<^>
```

## Open a Postgres Prompt with the New Role

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 an account with `sudo` privileges (not logged in as the `postgres` user):

```command
sudo adduser <^>sammy<^>
```

Once you have the appropriate account available, you can either switch over and connect to the database by typing:

```command
sudo -i -u <^>sammy<^>
psql
```

Or, you can do this inline:

```command
sudo -u <^>sammy<^> psql
```

You will be logged 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:

```command
psql -d <^>postgres<^>
```

Once logged in, you can get check your current connection information by typing:

```custom_prefix(sammy=#)
\conninfo
```
```
[secondary_label Output]
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".
```

This can be useful 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 can to go over how to complete some basic tasks.

First, we can 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](https://digitalocean.com/community/articles/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server) 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 (`equip_id` and `install_date`), 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 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:

```custom_prefix(sammy=#)
\d
```
```
[secondary_label Output]
 List of relations
 Schema | Name | Type | Owner 
--------+-------------------------+----------+-------
 public | playground | table | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)
```

Our playground table is here, 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 and is created automatically for columns of this type.

If you want to see just the table without the sequence, you can type:

```custom_prefix(sammy=#)
\dt
```
```
[secondary_label Output]
 List of relations
 Schema | Name | Type | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)
```

## Add, Query, and Delete Data in a Table

Now that we have a table, 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:

```custom_prefix(sammy=#)
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 take care when entering the data to avoid a few common hangups. 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:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 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 the slide on the playground breaks and we have to remove it, we can also remove the row from our table by typing:

```custom_prefix(sammy=#)
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:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 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:

```custom_prefix(sammy=#)
ALTER TABLE playground ADD last_maint date;
```

If you view your table information again, you will see the new column has been added (but no data has been entered):

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 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:

```custom_prefix(sammy=#)
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 the swing set a paint job:

```custom_prefix(sammy=#)
UPDATE playground SET color = 'red' WHERE type = 'swing';
```

We can verify that the operation was successful by querying our data again:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 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 16.04 server. However, there is still *much* more to learn with Postgres. Here are some more guides that cover how to use Postgres:

* [A comparison of relational database management systems](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems)
* [Learn how to create and manage tables with Postgres](https://www.digitalocean.com/community/tutorials/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server)
* [Get better at managing roles and permissions](https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2)
* [Craft queries with Postgres with Select](https://www.digitalocean.com/community/tutorials/how-to-create-data-queries-in-postgresql-by-using-the-select-command)
* [Learn how to secure PostgreSQL](https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-on-an-ubuntu-vps)
* [Learn how to backup a Postgres database](https://www.digitalocean.com/community/tutorials/how-to-backup-postgresql-databases-on-an-ubuntu-vps)

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...

How To Install and Use PostgreSQL on Ubuntu 16.04

18.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](https://www.postgresql.org/), 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.


## Prerequisites

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](https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-18-04) guide. After completing this prerequisite tutorial, your server should have a non-**root** user with sudo permissions and a basic firewall.

You can also use an interactive terminal that is embedded on this page to experiment with installing and configuring PostgreSQL in this tutorial. Click the following `Launch an Interactive Terminal!` button to get started.

[terminal systemd:v2]

## Step 1 — Installing PostgreSQL

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:

```command
sudo apt update
sudo apt install postgresql postgresql-contrib
```

Ensure that the server is running using the `systemctl start` command:

```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.

## Step 2 — Using PostgreSQL Roles and Databases

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.


### Switching Over to the postgres Account

Switch over to the **postgres** account on your server by typing:

```command
sudo -i -u postgres
```

You can now access a Postgres prompt immediately by typing:

```command
psql
```

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:

```custom_prefix(postgres=#)
\q
```

This will bring you back to the `postgres` Linux command prompt.


### Accessing a Postgres Prompt Without Switching Accounts

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:

```command
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:

```custom_prefix(postgres=#)
\q
```

Many use cases require more than one Postgres role. Read on to learn how to configure these.


## Step 3 — Creating a New Role

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:

```custom_prefix(postgres@server:~$)
createuser --interactive
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, type:

```command
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:

```
[secondary_label Output]
Enter 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:

```
[secondary_label Output]
Enter 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` page:

```command
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.


## Step 4 — Creating a New Database

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 `createdb` command.

If you are logged in as the **postgres** account, you would type something like:

```custom_prefix(postgres@server:~$)
createdb <^>sammy<^>
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, you would type:

```command
sudo -u postgres createdb <^>sammy<^>
```

This flexibility provides multiple paths for creating databases as needed.


## Step 5 — Opening a Postgres Prompt with the New Role

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):

```command
sudo adduser <^>sammy<^>
```

Once this new account is available, you can either switch over and connect to the database by typing:

```command
sudo -i -u <^>sammy<^>
psql
```

Or, you can do this inline:

```command
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:

```command
psql -d <^>postgres<^>
```

Once logged in, you can get check your current connection information by typing:

```custom_prefix(sammy=#)
\conninfo
```
```
[secondary_label Output]
You 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.

## Step 6 — Creating and Deleting Tables

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](https://digitalocean.com/community/articles/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server) here.

For demonstration purposes, create a sample table like this:

```custom_prefix(sammy=#)
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 (`equip_id` and `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 `type` and `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:

```
[secondary_label Output]
CREATE TABLE
```

You can find a list of tables within this database by typing:

```custom_prefix(sammy=#)
\d
```
```
[secondary_label Output]
 List 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:

```custom_prefix(sammy=#)
\dt
```
```
[secondary_label Output]
 List of relations
 Schema | Name | Type | Owner
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)
```


## Step 7 — Adding, Querying, and Deleting Data in a Table

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:

```custom_prefix(sammy=#)
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:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_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:

```custom_prefix(sammy=#)
DELETE FROM playground WHERE type = 'slide';
```

Query the table again:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_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.


## Step 8 — Adding and Deleting Columns from a 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:

```custom_prefix(sammy=#)
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):

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_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:

```custom_prefix(sammy=#)
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.


## Step 9 — Updating Data in a Table

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:

```custom_prefix(sammy=#)
UPDATE playground SET color = 'red' WHERE type = 'swing';
```

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

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_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.

## Conclusion

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:

* [A comparison of relational database management systems](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems)
* [Learn how to create and manage tables with Postgres](https://www.digitalocean.com/community/tutorials/how-to-create-remove-manage-tables-in-postgresql-on-a-cloud-server)
* [Get better at managing roles and permissions](https://www.digitalocean.com/community/tutorials/how-to-use-roles-and-manage-grant-permissions-in-postgresql-on-a-vps--2)
* [Craft queries with Postgres with Select](https://www.digitalocean.com/community/tutorials/how-to-create-data-queries-in-postgresql-by-using-the-select-command)
* [Learn how to secure PostgreSQL](https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-on-an-ubuntu-vps)
* [Learn how to backup a Postgres database](https://www.digitalocean.com/community/tutorials/how-to-backup-postgresql-databases-on-an-ubuntu-vps)

This tutorial provides instructions on how to install the PostgreSQL database software on an Ubuntu 18.04 server. It also includes instructions for basic database management.

How To Install and Use PostgreSQL on Ubuntu 18.04

22.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](https://www.postgresql.org/), or Postgres, is a relational database management system that provides an implementation of the [SQL](https://en.wikipedia.org/wiki/SQL) querying language. It's standards-compliant and has many advanced features like reliable transactions and concurrency without read locks.

This guide demonstrates how to install Postgres on an Ubuntu 22.04 server. It also provides some instructions for general database administration. 


## Prerequisites

To follow along with this tutorial, you will need one Ubuntu 22.04 server that has been configured by following our [Initial Server Setup for Ubuntu 22.04](https://www.digitalocean.com/community/tutorials/initial-server-setup-with-ubuntu-22-04) guide. After completing this prerequisite tutorial, your server should have a non-**root** user with sudo permissions and a basic firewall.

You can also use an interactive terminal that is embedded on this page to experiment with installing and configuring PostgreSQL in this tutorial. Click the following `Launch an Interactive Terminal!` button to get started.

[terminal systemd:jammy]

## Step 1 — Installing PostgreSQL

Ubuntu's default repositories contain Postgres packages, so you can install these using the `apt` packaging system.

If you've not done so recently, refresh your server's local package index:

```command
sudo apt update
```

Then, install the Postgres package along with a `-contrib` package that adds some extra utilities and functionality:

```command
sudo apt install postgresql postgresql-contrib
```

Ensure that the server is running using the `systemctl start` command:

```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 other relational database management systems you may have used.


## Step 2 — Using PostgreSQL Roles and Databases

By default, Postgres uses a concept called *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 *peer 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.


### Switching Over to the postgres Account

Switch over to the **postgres** account on your server by typing:

```command
sudo -i -u postgres
```

You can now access the PostgreSQL prompt immediately by typing:

```custom_prefix(postgres@server:~$)
psql
```

From there you are free to interact with the database management system as necessary.

Exit out of the PostgreSQL prompt by typing:

```custom_prefix(postgres=#)
\q
```

This will bring you back to the **postgres** Linux user's command prompt.


### Accessing a Postgres Prompt Without Switching Accounts

You can also run the command you'd like with the **postgres** account directly with `sudo`.

For instance, 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 do this in one step by running the single command `psql` as the **postgres** user with `sudo`, like this:

```command
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:

```custom_prefix(postgres=#)
\q
```

Many use cases require more than one Postgres role. Read on to learn how to configure these.


## Step 3 — Creating a New Role

Currently, you have the **postgres** role configured within the database. You can create new roles from the command line with the `createuser` 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:

```custom_prefix(postgres@server:~$)
createuser --interactive
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, type:

```command
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 to your specifications.

```
[secondary_label Output]
Enter name of role to add: <^>sammy<^>
Shall the new role be a superuser? (y/n) <^>y<^>
```

You can get more control by passing some additional flags. Check out the options by looking at the `man` page for the `createuser` command:

```command
man createuser
```

Your installation of Postgres now has a new user, but you have not yet added any databases. The next section describes this process.


## Step 4 — Creating a New Database

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 also called “sammy” by default. You can create the appropriate database with the `createdb` command.

If you are logged in as the **postgres** account, you would type something like:

```custom_prefix(postgres@server:~$)
createdb <^>sammy<^>
```

If, instead, you prefer to use `sudo` for each command without switching from your normal account, you would type:

```command
sudo -u postgres createdb <^>sammy<^>
```

This flexibility provides multiple paths for creating databases as needed.


## Step 5 — Opening a Postgres Prompt with the New Role

To log in with peer 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):

```command
sudo adduser <^>sammy<^>
```

Once this new account is available, you can either switch over and connect to the database by typing:

```command
sudo -i -u <^>sammy<^>
psql
```

Or, you can do this inline:

```command
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:

```command
psql -d <^>postgres<^>
```

Once logged in, you can get check your current connection information by typing:

```custom_prefix(sammy=#)
\conninfo
```
```
[secondary_label Output]
You 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.


## Step 6 — Creating and Deleting Tables

Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

The basic syntax for creating tables 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<^>)
);
```

This command gives the table a name, and then defines the columns as well as the column type and the max length of the field data. Optionally, you can add constraints for each column.

You can learn more about creating tables by following our guide on [How To Create and Manage Tables in SQL](https://www.digitalocean.com/community/tutorials/how-to-create-and-manage-tables-in-sql).

For demonstration purposes, create the following table:

```custom_prefix(sammy=#)
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
);
```

This command will create a table that inventories playground equipment. The first column in the table will hold equipment ID numbers of the `serial` type, which is an auto-incrementing integer. This column also has the constraint of `PRIMARY KEY` which means that the values within it must be unique and not null.

The next two lines create columns for the equipment `type` and `color` respectively, neither of which can be null. The line after these creates a `location` column with a constraint that requires the value to be one of eight possible values. The last line creates a `date` column that records the date on which you installed the equipment.

For two of the columns (`equip_id` and `install_date`), the command doesn't specify a field length. The reason for this is that some data types don't require a set length because the length or format is implied.

Examine your new table by typing:

```custom_prefix(sammy=#)
\d
```
```
[secondary_label Output]
 List 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 want to view just the table without the sequence, you can type:

```custom_prefix(sammy=#)
\dt
```
```
[secondary_label Output]
 List of relations
 Schema | Name | Type | Owner 
--------+------------+-------+-------
 public | playground | table | <^>sammy<^>
(1 row)
```

With a table at the ready, let's use it to practice managing data.


## Step 7 — Adding, Querying, and Deleting Data in a Table

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:

```custom_prefix(sammy=#)
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 you add a new row to the table.

Retrieve the information you've added by typing:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date 
----------+-------+--------+-----------+--------------
 1 | slide | blue | south | 2017-04-28
 2 | swing | yellow | northwest | 2018-08-16
(2 rows)
```

Notice 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:

```custom_prefix(sammy=#)
DELETE FROM playground WHERE type = 'slide';
```

Query the table again:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date 
----------+-------+--------+-----------+--------------
 2 | swing | yellow | northwest | 2018-08-16
(1 row)
```

Notice that the `slide` row is no longer a part of the table.


## Step 8 — Adding and Deleting Columns from a Table

After creating a table, you can modify it by adding or removing columns. Add a column to show the last maintenance visit for each piece of equipment by typing:

```custom_prefix(sammy=#)
ALTER TABLE playground ADD last_maint date;
```

View your table information again. A new column has been added but no data has been entered:

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date | last_maint 
----------+-------+--------+-----------+--------------+------------
 2 | swing | yellow | northwest | 2018-08-16 | 
(1 row)
```

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:

```custom_prefix(sammy=#)
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.


## Step 9 — Updating Data in a Table

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:

```custom_prefix(sammy=#)
UPDATE playground SET color = 'red' WHERE type = 'swing';
```

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

```custom_prefix(sammy=#)
SELECT * FROM playground;
```
```
[secondary_label Output]
 equip_id | type | color | location | install_date 
----------+-------+-------+-----------+--------------
 2 | swing | red | northwest | 2018-08-16
(1 row)
```

The slide is now registered as being red.

## Conclusion

You are now set up with PostgreSQL on your Ubuntu 22.04 server. If you'd like to learn more about Postgres and how to use it, we encourage you to check out the following guides:

* [A comparison of relational database management systems](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems)
* [Practice running queries with SQL](https://www.digitalocean.com/community/tutorial_series/how-to-use-sql)

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. This guide demonstrates how to install Postgres on an Ubuntu 22.04 server. It also provides instructions for general database administration. 

How To Install and Use PostgreSQL on Ubuntu 22.04

Senior Technical Editor

Manager, Developer Education

Associate Technical Writer

Sign up for Infrastructure as a Newsletter.

Working on improving health and education, reducing inequality, and spurring economic growth? DigitalOcean would like to help.

Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

You get paid; we donate to tech nonprofits.

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.