PostgreSQL is a database management system, similar to MySQL in many respects but with some key differences. Like MySQL, it's commonly hosted on Linux. In this guide, we'll show how to run a PostgreSQL server on Ubuntu 20.04 Focal Fossa, as well as installing the client version in case you just need to connect to an external PostgreSQL database.

In this tutorial you will learn:
  • How to install PostgreSQL Client and connect to a server
  • How to install and configure PostgreSQL Server
Ubuntu 20.04 PostgreSQL
Ubuntu 20.04 PostgreSQL
Software Requirements and Linux Command Line Conventions
Category Requirements, Conventions or Software Version Used
System Installed Ubuntu 20.04 or upgraded Ubuntu 20.04 Focal Fossa
Software PostgreSQL Server and Client
Other Privileged access to your Linux system as root or via the sudo command.
Conventions # - requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
$ - requires given linux commands to be executed as a regular non-privileged user

Install PostgreSQL Client


SUBSCRIBE TO NEWSLETTER
Subscribe to Linux Career NEWSLETTER and receive latest Linux news, jobs, career advice and tutorials.


The PostgreSQL Client can be used to connect to an external PostgreSQL database. Use this option if you already have a database server up and running, but need to be able to remotely access the database from one or more client systems.

  1. To get started, install the postgresql-client package by opening a terminal and entering the following command:
    $ sudo apt install postgresql-client
    
  2. When the installation of PostgreSQL client is complete, you can use the psql command to connect to a remote PostgreSQL server. You'll need to specify the hostname or IP address of the remote server (shown as postgre-server in the example below) and the username (postgre-user below) you're authenticating with:
    $ psql -h postgre-server -U postgre-user
    psql (12.2 (Ubuntu 12.2-1))
    SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
    Type "help" for help.
    

That's it for the client version. In the next section, we'll show how to set up a PostgreSQL server, which will be able to accept incoming client connections.

Install PostgreSQL Server



  1. To get started hosting your PostgreSQL database, install the postgresql package on Ubuntu with the following command:
    $ sudo apt install postgresql
    
  2. Once PostgreSQL Server has finished installing, you should be able to see it listening for incoming connections on port 5432. This is a good way to confirm that it's up and running as expected.
    $ ss -nlt
    
    PostgreSQL Server listening on port 5432
    PostgreSQL Server listening on port 5432

  3. SUBSCRIBE TO NEWSLETTER
    Subscribe to Linux Career NEWSLETTER and receive latest Linux news, jobs, career advice and tutorials.


  4. By default, PostgreSQL Server will start up automatically each time your system boots. If you'd like to change this behavior, you can always modify it with this command:
    $ sudo systemctl disable postgresql
    


    To re-enable it, just replace disable with enable.
  5. PostgreSQL Server only listens on local loopback interface 127.0.0.1 by default. If you plan to have one or more remote clients connect to your database server, you'll need to configure PostgreSQL to listen on a different network interface. To make this change, open PostgreSQL's configuration file by using nano or your preferred text editor:
    $ sudo nano /etc/postgresql/12/main/postgresql.conf
    
  6. In this file, add the following line somewhere under the "CONNECTIONS AND AUTHENTICATION" section. This will instruct PostgreSQL to listen on all network interfaces for incoming connections.
    listen_addresses = '*'
    
    Add this line to the PostgreSQL config file to listen on all network interfaces
    Add this line to the PostgreSQL config file to listen on all network interfaces
  7. Save your changes and exit the config file. Then, restart PostgreSQL Server for the changes to take effect.
    $ sudo systemctl restart postgresql
    
  8. You should now be able to see that PostgreSQL is listening on socket 0.0.0.0:5432. You can confirm this by executing the ss command again:
    $ ss -nlt
    

    SUBSCRIBE TO NEWSLETTER
    Subscribe to Linux Career NEWSLETTER and receive latest Linux news, jobs, career advice and tutorials.


    Confirming that PostgreSQL is now listening for connections from all interfaces on port 5432
    Confirming that PostgreSQL is now listening for connections from all interfaces on port 5432
  9. Next, you should add the following line to your /etc/postgresql/12/main/pg_hba.conf configuration file, which will allow incoming client connections to all databases and users. The md5 option specifies that the users must authenticate with a password.
    host    all          all            0.0.0.0/0  md5
    
    To add this line to your file with a single command, just execute:
    $ sudo bash -c "echo host    all          all            0.0.0.0/0  md5 >> /etc/postgresql/12/main/pg_hba.conf" 
    


  10. Lastly, if you have UFW firewall enabled, you can open PostgreSQL Server's listening port 5432 to any incoming TCP traffic by executing the command below:


    $ sudo ufw allow from any to any port 5432 proto tcp
    Rule added
    Rule added (v6)
    
    PostgreSQL Server running on Ubuntu 20.04 Focal Fossa
    PostgreSQL Server running on Ubuntu 20.04 Focal Fossa

Conclusion

In this article, we learned how to host a PostgreSQL Server on Ubuntu 20.04 Focal Fossa Linux. We also saw how to perform some initial configuration so our database would be able to accept incoming connections from any source and any user. In addition to this, we also saw how to use the PostgreSQL Client package to connect to a remote PostgreSQL server.

FIND LATEST LINUX JOBS on LinuxCareers.com
Submit your RESUME, create a JOB ALERT or subscribe to RSS feed.
LINUX CAREER NEWSLETTER
Subscribe to NEWSLETTER and receive latest news, jobs, career advice and tutorials.
DO YOU NEED ADDITIONAL HELP?
Get extra help by visiting our LINUX FORUM or simply use comments below.

You may also be interested in:



Comments and Discussions