Choose a different version or distribution
Before we begin talking about how to install PostgreSQL on Ubuntu 20.04, let’s briefly understand - What is PostgreSQL?
PostgreSQL is a relational database management system. It provides an implementation of the SQL querying language. Moreover, it is a popular choice for many small and large projects. It gives the advantage of being standards-compliant. It even has many advanced features like reliable transactions and concurrency without the read locks.
In this tutorial, you will install PostgreSQL on Ubuntu 20.04. We will also address some of the FAQs related to the PostgreSQL installation.
Advantages of PostgreSQL
- Advanced features: PostgreSQL offers a wide range of advanced features like native JSON support, full-text search, and geospatial capabilities.
- High performance: It delivers excellent performance for complex queries and concurrent workloads, thanks to its efficient query optimizer and multi-version concurrency control (MVCC).
- Robustness and reliability: With its ACID-compliant architecture, PostgreSQL ensures data consistency, durability, and fault tolerance, making it a reliable choice for critical applications.
- Extensibility: It provides a flexible architecture that allows users to define custom data types, functions, and extensions, enabling seamless integration with other tools and libraries.
- Community and support: PostgreSQL has an active and vibrant community that provides continuous support, regular updates, and a large number of available libraries and plugins.
1) Ubuntu 20.04 server
2) Non-root user with sudo privileges
3) Basic firewall
Step 1 - Install PostgreSQL on Ubuntu
1) Ubuntu’s default repositories have Postgres packages. Therefore, you will install these by
apt package manager system.
2) Since it will be your first time using apt, it will be good to refresh your local package index. After that, install the Postgres package along with
-contrib package. It will add a few additional utilities and functionality.
sudo apt update sudo apt install postgresql postgresql-contrib -y
Step 2 - Using the PostgreSQL Roles and Databases
1) In Postgres, there is a concept known as roles which are used to handle authentication and authorization. It doesn't distinguish between users and groups instead it prefers the more flexible term "role".
Postgres uses ident authentication which means it associates Postgres roles with a matching Unix-style account. Unix/Linux username can simply sign in with the same name if a role exists with Postgres.
The installation procedure will create a user account known as Postgres. It is associated with the default Postgres role. To use Postgres, you can log into that account.
Next, we will discuss a few ways to utilize this account to access Postgres.
Switch to the Postgres Account
1) To switch over to postgres account on your server, type:
sudo -i -u postgres
2) Now access a Postgres prompt immediately by typing:
Now you are logged into PostgreSQL and you can freely interact with DBMS right away.
3) T0 exit out of the PostgreSQL, type the following command:
It will bring you back to
postgres Linux command prompt.
Access Postgres Prompt without Switching Accounts
1) Instead of switching the account and then interacting with Postgres, you can simply open the Postgres prompt with a single command:
sudo -u postgres psql
2) This way you will be able to log in directly into Postgres without
bash shell in between.
5) To exit the interactive Postgres session, type the following command:
Step 3 - Creating a New Role
1) At present, you just have postgres role configuration within the database. To, create new roles use the
createrole command. You can set the name of the new role and configure if it can have superuser permissions with the
2) So, if you are logged in as postgres account, create a new user by typing:
3) If, you prefer to use
sudo for each command without switching from your normal account, then type:
sudo -u postgres createuser --interactive
4) Next, the script will prompt you with a few choices and based on your responses, execute the correct Postgres commands. It will create a user as per your specifications.
Firstly, you will be asked to specify a name for the new role. You can keep any name of your choice, here we are keeping it as John.
Output Enter name of role to add: john
Next, you need to decide on the role permission. If you ill grant superuser permission then it can bypass almost all the permission checks.
If you would like John to have superuser permission then follow the below example:
Output Enter name of role to add: john Shall the new role be a superuser? (y/n) y
You can create new superuser roles only if you are creating them as a role that is already a superuser.
5) To gain more control pass some additional flags. You can check the variety of options with the below command:
Step 4 - Creating a New Database
1) Postgres authentication system makes an assumption that the name of the database will be similar to the role you have used to log in.
For example, if you are logged in with user john then it will automatically try to search and connect with the database named john.
2) If you are logged in as postgres account, type:
3) If, you want to use
sudo for each command without switching from your normal account, then type:
sudo -u postgres createdb john
Step 5 - Opening Postgres Prompt with New Role
1) You will need a Linux user with the same name as your Postgres role and database to log in with
ident based authentication.
2) Create a new user with the
adduser command if you don't have a matching Linux user. Do it with your non-root account with
sudo privileges. It means you should not be logged in as a postgres user.
sudo adduser john
3) Switch over and connect to the database with the following command:
sudo -i -u john psql
Or, you can also do this inline, by:
sudo -u john psql
You will be logged in automatically with the assumption that all the components are properly configured.
4) Also, if you want your user to connect to a different database, then specify the database like:
psql -d postgres
5) Once you are logged in then check the current connection information by typing:
Output You are connected to database "john" as user "john" via socket in "/var/run/postgresql" at port "5432".
It is helpful if you are connecting to a non-default database or with a non-default user.
Step 6 - Enabling Remote Access to PostgreSQL
1) PostgreSQL server listens only on the local interface
127.0.0.1. Open the configuration file
postgresql.conf and add
listen_addresses = '*' in
CONNECTIONS AND AUTHENTICATION section to enable remote access to your PostgreSQL server.
sudo vim /etc/postgresql/12/main/postgresql.conf
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
2) After that, save the file and restart the PostgreSQL service using the following command:
sudo service postgresql restart
3) Now, use the
ss utility to verify the changes:
ss -nlt | grep 5432
Output LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
It is clearly visible from the above output that the PostgreSQL server is listening on all interfaces.
Now, edit the
pg_hba.conf file so that the server can accept remote connections.
FAQs to Install PostgreSQL on Ubuntu 20.04
How do I verify if PostgreSQL is installed on my Ubuntu 20.04 system?
You can verify the installation by running the command
psql --version. It will display the version of PostgreSQL installed on your system.
What is the default username and password for PostgreSQL after installation?
PostgreSQL creates a user named "postgres" during installation. You can switch to this user using
sudo -iu postgres. To access PostgreSQL prompt, you can use the command
psql, which does not require a password.
How do I create a new PostgreSQL user and database?
After switching to the "postgres" user, you can create a new user and database using the command
createuser --interactive to create a user and
createdb <database-name> to create a database.
What is the default location of the PostgreSQL data directory?
By default, the PostgreSQL data directory is located at
How do I enable remote access to PostgreSQL on Ubuntu 20.04?
To enable remote access, you need to modify the PostgreSQL configuration file located at
/etc/postgresql/<version>/main/postgresql.conf and update the
listen_addresses parameter to include the IP addresses or network ranges you want to allow.
Can I have multiple versions of PostgreSQL installed on Ubuntu 20.04?
Yes, you can have multiple versions installed simultaneously. Ubuntu provides distinct packages for different PostgreSQL versions, allowing you to install and manage them independently.
How can I secure my PostgreSQL installation on Ubuntu 20.04?
You can secure your installation by configuring a strong password for the "postgres" user, using SSL/TLS for connections, and configuring firewall rules to only allow trusted connections to the PostgreSQL port (usually 5432).
We hope this detailed guide helped you to install PostgreSQL on Ubuntu 20.04.
If you have any queries or doubts, please leave them in the comment below. We'll be happy to address them.