How to Install and Configure PostgreSQL on Ubuntu 20.04

In this tutorial, I will show how to install PostgreSQL from the apt repository and go over the basic configurations.

Bear in mind that the purpose of this article is training for those who have no experience at all. In the real world, the use of baremetal as a host for DB is uncommon.

Installation:

To use the apt repository, follow these steps:

• Create the file repository configuration.

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

• Import the repository signing key.

wget - quiet -O https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add

• Update the package lists.

sudo apt-get update

• Install the latest version of PostgreSQL.
  If you want a specific version, use ‘postgresql-12’ or something similar instead of ‘postgresql’.

sudo apt-get -y install postgresql

• Verify installation.

sudo systemctl status postgresql

Using PostgreSQL Roles and Databases:

The concept of “roles” is how Postgres handles authorization and authentication by default. These resemble typical Unix-style users and groups in certain aspects.

Postgres is configured to employ ident authentication by default, which links Postgres roles to corresponding Unix/Linux system accounts. A Unix/Linux username with the same name can log in as that role if it exists within Postgres.

During the installation process, a user account called postgres was created, and it is by default linked to the postgres role. You can use this account to access Postgres in a few different ways. You can use the following command to change your server’s account to Postgres:

sudo -i -u postgres

Then you can access the Postgres prompt by running:

psql

This will launch the PostgreSQL prompt, where you can immediately begin interacting with the database management system.
Use these commands to close the PostgreSQL prompt:

\q

This will bring you back to the Postgres Linux command prompt. To return to your regular system user, run the exit command:

exit

Another way to connect to the Postgres prompt is to run the psql command as the Postgres account directly with sudo:

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 executing the exit command:

exit

Creating a New Role:

If you are logged in as the postgres account, you can create a new role by running the following command:

createuser --interactive

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

sudo -u postgres createuser --interactive

In either case, the script will present you with a few options and then, in response to your selections, will run the appropriate Postgres commands to create a user according to your preferences.

OutputEnter name of role to add: danny
Shall the new role be a superuser? (y/n) y

Creating a New Database:

Another default assumption made by the Postgres authentication system is that any role used to log in would be able to access a database with the same name.

This implies that the role will try to connect to a database, which is likewise named “danny” by default, if the user you created in the previous section is called Danny. The createdb command can be used to construct the necessary database.

You would type something like this if you were logged into the Postgres account:

createdb danny

If you prefer to use sudo for each command without switching from your normal account, you would run

sudo -u postgres created danny

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

sudo adduser danny

Once this new account is available, you can either switch over or connect to the database by running the following:

sudo -i -u danny
psql

Or, you can do this inline:

sudo -u danny psql

Assuming that every component has been set up correctly, this command will immediately log you in.

You can define the database in the following way if you want your user to connect to a different database:

psql -d postgres

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

\conninfo

Output////
You are connected to database "danny" as user "danny" via socket in "/var/run/postgresql" at port "5432".

Verify that port 5432 is open to accept connections.

netstat -pln | grep 5432

Setting parameters in PostgreSQL:

20.1. Setting Parameters

sudo vim /etc/postgresql/15/main/postgresql.conf

Client authentication configuration file pg_hba.conf:

21.1. The pg_hba.conf File

sudo vim /etc/postgresql/15/main/pg_hba.conf

Connecting to a Remote PostgreSQL Database:

If your PostgreSQL database is installed on a separate server, you need to change the default settings in the postgresql.conf and pg_hba.conf files in the remote database.

• Change the listening address in the postgresql.conf file.

By default, PostgreSQL allows you to listen for the localhost connection.
It does not allow a remote TCP/IP connection.
To allow a remote TCP/IP connection, add the following entry to the /etc/postgresql/15/main/postgresql.conf file:

listen_addresses = '*.*.*.*'

If you have multiple interfaces on the server, you can specify a specific interface to be listened to.

• Add a client authentication entry to the pg_hba.conf file.

By default, PostgreSQL accepts connections only from the local host. It refuses remote connections.
This is controlled by applying an access control rule that allows a user to log in from an IP address after providing a valid password (the md5 keyword). To accept a remote connection, add the following entry to the /etc/postgresql/15/main/pg_hba.conf file:

host all all *.*.*.*/* md5

For example,192.168.104.24/26 trust.

Best Practices and Maintenance:

• Configuration manager

The PGTune website calculates the configuration for PostgreSQL based on the maximum performance for a given hardware configuration.

PGTune - calculate configuration for PostgreSQL based on the maximum performance for a given…

• Disaster Recovery

Barman allows for the implementation of disaster recovery solutions for PostgreSQL databases with high requirements for business continuity.

Barman

• Cluster managers

repmgr is an open-source tool suite for managing replication and failover in a cluster of PostgreSQL servers.

repmgr - Replication Manager for PostgreSQL clusters

Patroni is a cluster manager used to customize and automate the deployment and maintenance of PostgreSQL HA (high availability) clusters.

Patroni : Setting up a highly available PostgreSQL Cluster - CYBERTEC

• PostgreSQL security

Postgres adds transparent data encryption, additional advanced security, and new Oracle compatibility features.

Home

If you liked my articles, join my newsletter, and you will receive weekly DevOps tutorials, articles, and tips every Saturday.

As a bonus, you will receive a free step-by-step DevOps CI/CD project, which you can use in your portfolio.

Subscribe here: https://junior-devops-hub.ck.page