Relational databases play a crucial role in various applications and websites, providing a structured framework for data storage. PostgreSQL stands out as a popular open-source alternative to MySQL, offering compatibility with a wide range of operating systems including Unix-based systems. This guide walks you through installing PostgreSQL on Ubuntu 18.04 using Ubuntu's default repository, followed by essential post-installation configuration including database creation and user role setup.

Background

PostgreSQL is an open-source relational database management system that provides seamless data querying and accessibility. The installation process uses Ubuntu's default repository with PostgreSQL packages, making it straightforward to set up. After installation, you'll need to configure user roles and create databases to ensure optimal functionality.

Prerequisites

  • SSH access to your Ubuntu 18.04 VPS
  • A non-root user account with sudo privileges

How to Install and Configure PostgreSQL

  1. Step 1: Update the package index

    Before installing PostgreSQL, update your local package index to ensure you get the latest version:

    sudo apt update

  2. Step 2: Install PostgreSQL and contrib package

    Install PostgreSQL along with the contrib package, which provides additional features and utilities:

    sudo apt install postgresql postgresql-contrib

    This command initiates the installation of PostgreSQL. Once installed, services will start automatically.

  3. Step 3: Verify the installation

    Confirm that PostgreSQL is installed correctly by checking the server version:

    sudo -u postgres psql -c "SELECT version();"

    To log in to PostgreSQL as the default "postgres" user, use:

    sudo su - postgres
psql

    To exit the PostgreSQL prompt, type \q and press Enter.

  4. Step 4: Create a new role

    Log in to the "postgres" account and create a new role using the interactive command:

    sudo -u postgres createuser --interactive

    You'll be prompted to name the role and specify if it should have superuser permissions. Alternatively, you can use createrole --interactive from within the PostgreSQL console.

  5. Step 5: Create a database

    PostgreSQL conventionally expects that the role name aligns with the database name. If you created a user named "testPostgres," create a matching database:

    sudo -u postgres createdb testPostgres

    Alternatively, from within the Postgres console, use:

    createdb testPostgres

  6. Step 6: Create a matching Linux user

    Create a Linux user with the same name as your PostgreSQL role and database:

    sudo adduser testPostgres

    Replace "testPostgres" with your desired username.

  7. Step 7: Connect to the database with the new role

    Switch to the new user account and connect to the database:

    sudo -u testPostgres psql

    To connect to a different database, specify the database name:

    psql -d sampleUser

    After logging in, check your current connection information:

    \conninfo

Basic PostgreSQL Operations

PostgreSQL uses syntax similar to other databases for fundamental operations. Here are some useful commands:

  • To describe a table's definition: \d <TableName>
  • To view a table without sequences: \dt <TableName>
  • To list all databases: \l
  • To list all roles: \du

Next Steps

You now have a working PostgreSQL installation on Ubuntu 18.04 with the basic setup for creating users, roles, and databases. You can begin creating tables, inserting data, and building your applications using PostgreSQL as your database backend.