How To Install PostgreSQL On Ubuntu 20.04 May 6, 2020 Raj 4 min read CONTENTS Add PostgreSQL repository Install PostgreSQL Configure PostgreSQL Server Access PostgreSQL Secure PostgreSQL database Set password for Unix user (postgres) Set password for DB administrator (postgres) PostgreSQL DB Commands Create Database Create DB user List Database users List Database Login to Database Delete User Delete Database Conclusion SHARE THIS DOCUMENT IS ALSO AVAILABLE FOR CentOS 8 CentOS 7 Debian 10 Debian 9 Ubuntu 18.04 Ubuntu 16.04 PostgreSQL (aka Postgres) is an open-source object-relational database management system (ORDBMS), developed by PostgreSQL Global Development Group, a group of individual volunteers and corporate companies. PostgreSQL is available for Linux, Microsoft Windows, FreeBSD, OpenBSD, Solaris, and Mac OS. Here, we will see how to install PostgreSQL on Ubuntu 20.04. Add PostgreSQL repository PostgreSQL is available from both Ubuntu (sudo apt update && sudo apt install -y postgresql) and PostgreSQL’s repository. PostgreSQL’s repository packages are fresher than those available in the Ubuntu repository. So, here we will install PostgreSQL from the PostgreSQL’s repository. Import the PostgreSQL’s signing key. wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add - Add the PostgreSQL repository information to postgresql.list file. echo "deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list Install PostgreSQL Execute the below command to update the repository index. sudo apt update Install the PostgreSQL package using the apt command. ### PostgreSQL 12 ### sudo apt install -y postgresql-12 ### PostgreSQL 11 ### sudo apt install -y postgresql-11 By default, the PostgreSQL service is started automatically after the installation. To check the status of PostgreSQL service, run: Replace status with start, stop, restart to star, stop, and restart PostgreSQL service respectively. sudo systemctl status postgresql Output: ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Tue 2020-05-05 06:02:21 EDT; 4min 25s ago Main PID: 3603 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 2319) Memory: 0B CGroup: /system.slice/postgresql.service May 05 06:02:21 ubun2004 systemd[1]: Starting PostgreSQL RDBMS... May 05 06:02:21 ubun2004 systemd[1]: Finished PostgreSQL RDBMS. Configure PostgreSQL Server PostgreSQL server listens on localhost (127.0.0.1) by default. Sometimes, we need to configure PostgreSQL to listen on all adaptors or system IP address to allow applications hosted on external machines to connect to the database ### PostgreSQL 12 ### sudo nano /etc/postgresql/12/main/postgresql.conf ### PostgreSQL 11 ### sudo nano /etc/postgresql/11/main/postgresql.conf Set the listen_addresses to * or <IPAddress>. listen_addresses = '*' Restart the PostgreSQL service. sudo systemctl restart postgresql Confirm that the PostgreSQL listening on port 5432 using the netstat command. sudo netstat -antup | grep 5432 Output: tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 6172/postgres tcp6 0 0 :::5432 :::* LISTEN 6172/postgres READ: netstat command not found on Ubuntu 20.04 – Quick Fix Access PostgreSQL To manage the database, you need to log in as the postgres user. sudo su -l postgres Use the psql command to enter into the interactive shell for working with the PostgreSQL database. psql Output: postgres@ubuntu2004:~$ psql psql (12.2 (Ubuntu 12.2-4)) Type "help" for help. postgres=# Secure PostgreSQL database We will now set a password for postgres (Unix User) as well as postgres (DB administrator). Set password for Unix user (postgres) sudo passwd postgres Set password for DB administrator (postgres) sudo su - postgres psql On psql shell, run below command to change the database administrator (postgres) password. postgres=# \password OR postgres=# \password postgres Output: postgres=# \password postgres Enter new password: Enter it again: PostgreSQL DB Commands Create Database To create a database: postgres=# CREATE DATABASE test; Create DB user To creating a user: postgres=# CREATE USER raj WITH SUPERUSER LOGIN PASSWORD 'password'; List Database users Check whether the user has been created or not. postgres=# \du Output: postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} raj | Superuser | {} List Database To list databases: postgres=# \l Output: postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows) Login to Database Log in with the user “raj”. $ psql -h localhost -d test -U raj Output: psql (12.2 (Ubuntu 12.2-4)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. test=# Delete User To remove a user: postgres=# DROP USER raj; Delete Database To remove a database: postgres=# DROP DATABASE test; Conclusion That’s All. You have successfully installed PostgreSQL on Ubuntu 20.04. ubuntu-20.04 postgresql database Prev Post How To Install Linux, Apache, MariaDB, PHP (LAMP Stack) on Ubuntu 20.04 Next Post How To Setup Let's Encrypt SSL Certificate With Nginx on Ubuntu 20.04 RELATED POSTS How To Install MySQL 8.0 on Ubuntu 20.04 How To Install MariaDB on Ubuntu 20.04 How To Upgrade To Ubuntu 20.04 From Ubuntu 18.04 / Ubuntu 19.10 [Detailed Guide] How To Install Linux, Apache, MariaDB, PHP (LAMP Stack) on Ubuntu 20.04 How To Install phpMyAdmin with Nginx on Ubuntu 20.04 How To Install Visual Studio Code On Ubuntu 20.04 Please enable JavaScript to view the comments powered by Disqus. comments powered by Disqus RECENT POSTS How To Install VirtualBox On Linux Mint 20 How To Backup and Restore Ubuntu & Linux Mint With Timeshift How To Upgrade To Linux Mint 20 From Linux Mint 19 [Detailed Guide] How To Install KVM On Ubuntu 20.04 / Linux Mint 20 How To Install Plex Media Server On Ubuntu 20.04 / Linux Mint 20 How To Install Android Studio on Ubuntu 20.04 TOP POSTS Install xrdp on CentOS 7 / RHEL 7 Install Gnome GUI on CentOS 7 / RHEL 7 Change default network name (ens33) to old “eth0” on Ubuntu… Install μTorrent (uTorrent) on Ubuntu 14.04 How To Configure High-Availability Cluster on CentOS 7 /… How To Install and Configure VNC Server in CentOS 7 / RHEL 7