Install and Connect to PostgreSQL 10 on Ubuntu 16.04

PostgreSQL (pronounced “post-gress-Q-L”) is a household name for open source relational database management systems. Its object-relational meaning that you’ll be able to use objects, classes database schemas and in the query language.  In this tutorial, we will show you how to install and connect to your PostgreSQL database on Ubuntu 16.04.

 

Step 1: Install PostgreSQL

First, we’ll obtain the authentication keys need to validate packages from the PostgreSQL repo.

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/PostgreSQL.list'

As a best practice, we will update our server before installing PostgreSQL.

apt-get -y update

After the update is complete, we’ll run the following command to install PostgreSQL

apt-get install postgresql-10

 

Step 2: Logging into PostgreSQL

Once installed PostgreSQL creates a default user named “postgres”.  This user works in a way different to that of other popular databases like MySQL.  PostgreSQL users can change the method of authentication, but by default, it uses a mode called ident. Ident takes your OS username and compares it with the allowed database usernames.

You must first switch to the default Postgres user

su - postgres

You’ll now see that you are logged in as that user via the prompt change

postgres@host2:~$

Afterward, you can then enter the PostgreSQL terminal by typing:

psql

You’ll know you are connected by the message below:

psql (9.5.14)
Type "help" for help.
postgres=#

 

Step 3: Logging out of PostSQL

To exit out of your postgresql environment use the following command

\q

Now that you’ve created your PostgreSQL world it’s time to stretch your feet!  Let’s start creating and listing databases using our world renown Cloud VPS servers.

 

Creating and Deleting a PostgreSQL Database

PostgreSQL (pronounced “post-gress-Q-L”) is a household name for open source relational database management systems. Its object-relational meaning that you’ll be able to use objects, classes in database schemas and the query language.  In this tutorial, we will be demonstrating some essentials like creating, listing and deleting a database.

If you have already installed PostgreSQL to your Ubuntu 16.04 server using our last tutorial, your next step is to create a database.  You’ll be accomplishing this task by using the default superuser, postgres, to log in.

Step 1: Login as the Postgres User

su - postgres

Step 2: Enter the PostgreSQL Environment

psql

With the psql command, you’ll be greeted by its current version and command prompt.

psql (9.5.14)
Type "help" for help.
postgres=#

Step 3: Creating the PostgreSQL Database

Let’s create our first database by typing in the command below.  Replace dbname with the database name of your choice.

CREATE DATABASE dbname;

 

Verify Creation of PostgreSQL Database

Using the following command allows us to view the databases in our PostgreSQL instance (you can ignore, delete or utilize the default databases: postgres, template0, template1)

postgres=# \list

 

Deleting a PostgreSQL Database

Once you’ve backed up your removing your PostgreSQL database is a cinch!  Its similar to creating a database but we will be using the drop command. In my command line example, the database name is “dbname”. By using the list command in the previous section, you’ll be able to view your databases’ names. Replace dbname with your database’s name in the command below.

DROP DATABASE dbname;

 

Listing and Switching Databases in PostgreSQL

PostgreSQL (pronounced “post-gress-Q-L”) is a household name for open source relational database management systems. Its object-relational meaning that you’ll be able to use objects, classes in database schemas and the query language. As part of our PostgreSQL series, we’ll show you how to list and switch between databases quickly.

Pre-flight

Log into your Ubuntu 16.04 server

Step 1: Login to your Database
su - postgres

Step 2: Enter the PostgreSQL environment
psql

With the psql command, you’ll be greeted by its current version and command prompt.

psql (9.5.14)
Type "help" for help.
postgres=#

Step 3: List Your PostgreSQL databases
Often, you’ll need to switch from database to database, but first, we will list the available database in PostgreSQL

postgres=# \list

By default, PosgreSQL has 3 databases: postgres, template0 and template1

Step 4: Switching Between Databases in PostgreSQL
Switching between databases is another way of saying you are closing one connection and opening another. When you need to change between databases, you’ll use the “connect” command, which is conveniently shortened to \c, followed by the database name.

\connect dbname

Or:

\c dbname