PostgreSQL Installation Guide

PostgreSQL, often referred to as Postgres, stands as a robust relational database management system offering a comprehensive implementation of the SQL querying language. This system adheres to industry standards and boasts an array of sophisticated features, including dependable transaction support and seamless concurrency handling without the need for read locks.

This guide serves to showcase a streamlined process for swiftly initiating PostgreSQL on an Ubuntu 20.04 server. The journey spans from the installation of PostgreSQL to the establishment of a fresh user and database. For a more comprehensive tutorial on the installation and proficient management of a PostgreSQL database, we recommend delving into the guide titled “How To Install and Use PostgreSQL on Ubuntu 20.04.”

Prerequisites:

For a seamless experience with this tutorial, ensure you have an Ubuntu 20.04 server at your disposal, which has been set up by following the guidelines outlined in our “Initial Server Setup for Ubuntu 20.04” manual. Upon successfully completing these preliminary instructions, your server will feature a non-root user endowed with sudo privileges, alongside a fundamental firewall configuration.

Step 1 - Installing PostgreSQL

To begin the PostgreSQL installation process, commence by updating your server’s local package index:

$ sudo apt update

Next, proceed to install the PostgreSQL package, accompanied by a corresponding -contrib package, which introduces supplementary utilities and enhanced functionalities:

$ sudo apt install postgresql postgresql-contrib

Confirm the initiation of the service:

$ sudo systemctl start postgresql.service

…or

$ sudo service postgresql status

Step 2 — Using PostgreSQL Roles and Databases

PostgreSQL operates on the basis of “roles,” a core concept governing authentication and authorization. In some respects, roles mirror conventional Unix-style users and groups.

After installation, PostgreSQL employs ident authentication by default. This implies that it associates PostgreSQL roles with corresponding Unix/Linux system accounts. If a role is present within PostgreSQL, a Unix/Linux username matching that role can seamlessly authenticate as that specific role.

Method # 1

The installation process establishes a user account named ‘postgres,’ intrinsically linked with the default PostgreSQL role. You have multiple avenues to utilize this account for accessing PostgreSQL. One approach involves transitioning to the ‘postgres’ account on your server, executed through the subsequent command:

$ sudo -i -u postgres

Subsequently, you can gain entry into the PostgreSQL prompt using:

$ psql

This command will provide immediate access to the PostgreSQL prompt, allowing direct engagement with the database management system.

To exit the PostgreSQL prompt, execute:

postgres=# \q

This will return you to the Linux command prompt associated with the ‘postgres’ user. To revert to your standard system user, simply execute:

$ exit

Method # 2

Another method for accessing the PostgreSQL prompt is to utilize the ‘psql’ command directly as the ‘postgres’ account, facilitated by ‘sudo’:

$ sudo -u postgres psql

This approach facilitates direct access to PostgreSQL, without any intermediary bash shell.

As before, conclude your interactive PostgreSQL session with:

postgres=# \q

Step 3 — Creating a New Role

Method #1

Should you be logged in as the ‘postgres’ account, initiating a new role entails executing the subsequent command:

$ sudo -i -u postgress
$ createuser --interactive

Method #2

Alternatively, if you prefer to leverage ‘sudo’ for each command while remaining in your regular account, execute the following:

$ sudo -i -u postgres createuser --interactive

Irrespective of the chosen method, the script will present you with various options and, contingent on your responses, undertake the pertinent PostgreSQL commands to forge a user tailored to your specifications.

Enter name of role to add: paco
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Step 4 - Accessing a Postgres Prompt with the Newly Created Role

To log in using ident-based authentication, you’ll require a Linux user with an identical name as your Postgres role and database.

In case a corresponding Linux user isn’t available, you can generate one using the ‘adduser’ command. This task should be accomplished from your non-root account equipped with sudo privileges (i.e., not logged in as the ‘postgres’ user):

$ sudo adduser paco

Once this new account is in place, you have two approaches to connect to the database:

Method #1

Switch over and connect to the database:

$ sudo -i -u paco
$ psql

Method #2

Or, perform this in-line:

$ sudo -u paco psql

This command will facilitate automatic login, assuming all the necessary components have been configured appropriately.

Should you wish your user “paco” to connect to a distinct database (i.e., logged in as the ‘paco’ user), you can do so by specifying the database in the following manner:

$ psql -d postgres

Once successfully logged in, you can assess your current connection details by executing:

postgres=# \conninfo

You are connected to the database "postgres" as user "paco" via socket in "/var/run/postgresql" at port "5432".

Step 5 - Establishin a Fresh Database

By default, the Postgres authentication system operates under the assumption that for any role employed for login purposes, an associated database bearing the same name should be accessible to that role.

Consequently, if the user fashioned in the preceding section is denoted as “paco,” this role will naturally seek to establish a connection with a database also christened “paco.” To craft the requisite database, the ‘createdb’ command comes into play.

Method #1

For those logged in as the ‘postgres’ account, the command might resemble the following:

$ sudo -i -u paco
$ createdb paco

Method #2

Conversely, should you lean towards utilizing ‘sudo’ for each command while refraining from altering your normal account context, the procedure would be as follows:

$ sudo -i -u paco createdb paco

Step 6 - Accessing database from remote SQL Client

If you need to access a database using a role from a remote SQL Client (i.e.: pgAdmin 4), you will need to define a password for the role. For example, let’s define a password to the role ‘paco’ as ‘paco123’:

Method #1

$ sudo -i -u postgres
$ psql -c "ALTER ROLE paco PASSWORD 'paco123';"

Method #2

$ sudo -i -u postgres psql -c "ALTER ROLE paco PASSWORD 'paco123';"

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.

Please find the provided snippet here: PostgreSQL Installation Guide

@lumarseg

@lumarseg