Grid Guides

Explore How System On Grid Can Work For You

Grid Guide Topics

How to Install and Use PostgreSQL on Ubuntu 18.04

Table Of Contents


    ## Table of Contents

    – [Introduction](#introduction
    – [Prerequisites](#prerequisites
    – [Install and Use PostgreSQL on Ubuntu 18.04](#install-and-use-postgresql-on-ubuntu-18-04
    – [Install PostgreSQL](#install-postgresql
    – [Use PostgreSQL Roles and Database](#use-postgresql-roles-and-database
    – [Create a New Role](#create-a-new-role
    – [Create a New Database](#create-a-new-database
    – [Open a Postgres Prompt with the New Role](#open-a-postgres-prompt-with-the-new-role
    – [Create and Delete Tables](#create-and-delete-tables
    – [Add and Delete Data in a Table](#add-and-delete-data-in-a-table
    – [Add and Delete Columns in a Table](#add-and-delete-columns-in-a-table
    – [Update Data in a Table](#update-data-in-a-table
    – [Conclusion](#conclusion

    ## Introduction

    In this guide, we will explain to you how to install and use PostgreSQL on Ubuntu 18.04.

    [PostgreSQL](https://www.postgresql.org/ is one of the most advanced open-source database management systems. It provides an implementation of the SQL querying language and also allows you to create complex web applications.

    ## Prerequisites

    You should have access to an Ubuntu 18.04 server and created a non-root user account with sudo privileges by following our guide, [Initial server setup with Ubuntu 18.04](https://systemongrid.com/support/guides/how-to-do-initial-server-setup-with-ubuntu-18.04.

    ## Install and Use PostgreSQL on Ubuntu 18.04

    ## Install PostgreSQL

    By default, Ubuntu has the Postgres packages in its repositories. Update the packages using the “apt” command to get the latest version of the repository listings.

    “`
    $ sudo apt update
    “`

    Now, install the Postgres package using the “apt” command. Enter “Y” when you are asked if you want to continue the installation.

    “`
    $ sudo apt install postgresql postgresql-contrib
    “`

    With this, you have installed the PostgreSQL.

    ## Use PostgreSQL Roles and Database

    Similar to the accounts in a Unix environment, Postgres uses “roles” to handle the authentication and authorization procedures. A role represents a database user or a group of database users.

    During the installation process, a user account called “Postgres” was created without a password. It is the default database administrator user name.

    The following are the two ways to use the “Postgres” user to access Postgres.

    ## Switch to the Postgres Account

    Use the below command to switch to the Postgres account.

    “`
    $ sudo -i -u postgres
    “`

    Use the below command to open the PostgreSQL prompt.

    “`
    $ psql
    “`

    The PostgreSQL prompt must have been opened. Here, you can access the Postgres database management system.

    Use the below command to exit out of the PostgreSQL prompt.

    “`
    postgres=# q
    “`

    ## Access the PostgreSQL Prompt Without Switching Account

    You can also access the PostgreSQL prompt without switching to the “Postgres” account.

    For example, in the above step, you first switched to the “Postgres” user and then used the psql command to access the PostgreSQL prompt. You can do this in one step using the below command.

    “`
    $ sudo -u postgres psql
    “`

    You must have logged into the PostgreSQL prompt directly.

    “`
    postgres=# q
    “`

    ## Create a New Role

    You can create a new role (user using the –interactive flag, which will ask you to enter the name of the new role and if you would like to give superuser permissions to it.

    If you are logged into the Postgres account, create a new role using the below command.

    “`
    postgres@server:~$ createuser –interactive
    “`

    Alternately, if you want to create a new role without switching to the Postgres account, use the below command.

    “`
    $ sudo -u postgres createuser –interactive
    “`

    You will get the following output in both the ways.

    Output:

    ![enternameofrole](https://grid.media/assets/images/enter-name-of-role-02132019.png

    We created a new role called “systemongrid” and wanted to give superuser permissions to the new role.

    ## Create a New Database

    Now, you have a new role and its time for you to create a new database. In this guide, we will create a new database called “systemongrid”.

    If you are logged into the Postgres account, create a new database using the below command.

    “`
    postgres@server:~$ createdb systemongrid
    “`

    Alternately, if you want to create a new database without switching to the Postgres account, use the below command.

    “`
    $ sudo -u postgres createdb systemongrid
    “`

    ## Open a Postgres Prompt with the New Role

    During the installation, Postgres is configured to use the “ident” authentication, which means you will a Linux system user with the same name as your Postgres role (user and database.

    Assuming that you have a Linux system user called “systemongrid” just to match the Postgres role and database we are using in this guide.

    Connect to the database using the below commands.

    “`
    $ sudo -i -u systemongrid
    $ psql
    “`

    Or, you can connect to the database directly using the below command.

    “`
    $ sudo -u systemongrid psql
    “`

    Or, if you want to connect to a different database, specify the name of the database like below.

    “`
    $ psql -d systemongrid
    “`

    Once you are connected, check the current connection information if you are connected to non-default users or non-default databases.

    “`
    systemongrid=# conninfo
    “`

    Output:

    ![connectedtosystemongriduser](https://grid.media/assets/images/connected-to-systemongrid-user-02132019.png

    ## Create and Delete Tables

    Now, its time for you to learn how to create and delete tables in Postgres.

    The following is the basic syntax to create a table.

    “`
    CREATE TABLE table_name (
    column_name1 col_type (field_length column_constraints,
    column_name2 col_type (field_length,
    column_name3 col_type (field_length
    ;
    “`

    Let’s create a sample table by following the above syntax. To do so, paste the following lines in the new role prompt (systemongrid=#.

    “`
    CREATE TABLE pg_equipment (
    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
    ;
    “`

    Use the below command to see your new table.

    “`
    systemongrid=# d
    “`

    Output:

    ![listofrelations](https://grid.media/assets/images/list-of-relations-02132019.png

    Use the below command if you want to see the table without the sequence.

    “`
    systemongrid=# dt
    “`

    Output:

    ![posgresqllistofrelations](https://grid.media/assets/images/postgresql-list-of-relations-02132019.png

    ## Add and Delete Data in a Table

    Now, add some data in the table. For example, add a rotation and a slide in the table you want to add to, name the columns and provide data for each column like the following.

    “`
    systemongrid=# INSERT INTO pg_equipment (type, color, location, install_date VALUES (‘rotation’, ‘green’, ‘north’, ‘2019-01-26’;

    systemongrid=# INSERT INTO pg_equipment (type, color, location, install_date VALUES (‘slide’, ‘blue’, ‘east’, ‘2019-01-25’;
    “`

    Use the below command to retrieve the data you have added.

    “`
    systemongrid=# SELECT * FROM pg_equipment;
    “`

    Output:

    ![retrieveinformationfromtheuser](https://grid.media/assets/images/retrieve-information-from-the-user-02132019.png

    Now, let’s delete the data in a table. Use the below command to delete the “rotation” row in the above table.

    “`
    systemongrid=# DELETE FROM pg_equipment WHERE type = ‘rotation’;
    “`

    Output:

    ![postgresqlretrieveinformation](https://grid.media/assets/images/postgresql-retrieve-infromation-from-the-user-02132019.png

    ## Add and Delete Columns in a Table

    Now, let’s add and delete columns in a table. Use the below command to add a column called “repair” in the above table.

    “`
    systemongrid=# ALTER TABLE pg_equipment ADD repair date;
    “`

    Use the below command to retrieve the data you have added.

    “`
    systemongrid=# SELECT * FROM pg_equipment;
    “`

    Output:

    ![addanddeletecolumns](https://grid.media/assets/images/add-and-delete-columns-in-a-table-02132019.png

    Now, Use the below command to delete the repair column in the above table.

    “`
    systemongrid=# ALTER TABLE pg_equipment DROP repair;
    “`

    Output:

    ![postgresqldroprepair](https://grid.media/assets/images/postgresql-drop-repair-02132019.png

    ## Update Data in a Table

    Now, let’s update the already existing data in a table. For example, change the color of the slide to “pink”.

    “`
    systemongrid=# UPDATE pg_equipment SET color = ‘pink’ WHERE type = ‘slide’;
    “`

    Output:

    ![postgresqlupdatedata](https://grid.media/assets/images/update-data-in-table-02132019.png

    ## Conclusion

    In this guide, you have learned how to install PostgreSQL on Ubuntu 18.04 and to create and manage tables in PostgreSQL.