On this short tutorial we will learn how to install PostgreSQL 13 on Ubuntu 20.04 LTS Server.
Introduction
Postgresql database has been widely used in various critical applications, ranging from e-commerce, finance, energy, education and many more. PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance. On this tutorial we will learn how to install PostgreSQL version 13.4 on Ubuntu 20.04 LTS operating system.
PostgreSQL 13 Installation On Ubuntu 20.04
On this tutorial, we will use PostgreSQL 13 source to be installed on Ubuntu 20.04 LTS server. For this purpose we need several dependencies package. The installation will be arranged as following steps :
- Prerequisites
- Adding PostgreSQL Repository To Ubuntu 20.04
- Installing PostgreSQL 13 on Ubuntu 20.04
- Initialize and start database service
- Set PostgreSQL admin user’s password
- Enabling Remote Access
Prerequisite
Before we are going to the install PostgreSQL database engine on Ubuntu 20.04, we have to prepare the environment first as mentioned below :
- Ubuntu 20.04 System with updated repository
- a user with sudo privilege
- Sufficient disk space and good internet access
Adding PostgreSQL Repository To Ubuntu 20.04
The PostgreSQL repository will be integrated with our normal systems and patch management and provide automatic updates for all supported versions of PostgreSQL throughout the support lifetime of PostgreSQL. On this step, we will import the repository signing GPG key to our system, by typing :
mpik@app01:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' mpik@app01:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - OK
We will verify the PostgreSQL packages by submitting command line below :
$ apt show postgresql -a
The output is as shown :
mpik@app01:~$ apt show postgresql -a Package: postgresql Version: 12+214ubuntu0.1 Priority: optional Section: database Source: postgresql-common (214ubuntu0.1) Origin: Ubuntu Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com> Original-Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org> Bugs: https://bugs.launchpad.net/ubuntu/+filebug Installed-Size: 67.6 kB Depends: postgresql-12 Suggests: postgresql-doc Task: postgresql-server Download-Size: 3,924 B APT-Sources: http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages Description: object-relational SQL database (supported version) This metapackage always depends on the currently supported PostgreSQL database server version. . PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Some of the features are: ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many programming languages are available as well. Package: postgresql Version: 12+214 Priority: optional Section: database Source: postgresql-common (214) Origin: Ubuntu Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com> Original-Maintainer: Debian PostgreSQL Maintainers <team+postgresql@tracker.debian.org> Bugs: https://bugs.launchpad.net/ubuntu/+filebug Installed-Size: 67.6 kB Depends: postgresql-12 Suggests: postgresql-doc Task: postgresql-server Download-Size: 4,004 B APT-Sources: http://us.archive.ubuntu.com/ubuntu focal/main amd64 Packages Description: object-relational SQL database (supported version) This metapackage always depends on the currently supported PostgreSQL database server version. . PostgreSQL is a fully featured object-relational database management system. It supports a large part of the SQL standard and is designed to be extensible by users in many aspects. Some of the features are: ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many programming languages are available as well.
2. Installing PostgreSQL 13 on Ubuntu 20.04
Before we initiate the installation process, we will update our local repository by submitting command line :
$ sudo apt-get update
The output will be shown below :
mpik@app01:~$ sudo apt-get update Get:1 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease [86.7 kB] Get:2 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB] Hit:3 http://ppa.launchpad.net/ondrej/php/ubuntu focal InRelease Hit:5 http://us.archive.ubuntu.com/ubuntu focal InRelease Hit:6 http://ppa.launchpad.net/uroni/urbackup/ubuntu focal InRelease Get:7 http://us.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB] Get:8 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages [214 kB] Get:9 http://security.ubuntu.com/ubuntu focal-security/main amd64 DEP-11 Metadata [27.6 kB] Get:10 http://security.ubuntu.com/ubuntu focal-security/universe amd64 DEP-11 Metadata [61.3 kB] Get:11 http://security.ubuntu.com/ubuntu focal-security/multiverse amd64 DEP-11 Metadata [2,464 B] Get:12 https://dlm.mariadb.com/repo/mariadb-server/10.6/repo/ubuntu focal InRelease [6,264 B] Get:14 http://us.archive.ubuntu.com/ubuntu focal-backports InRelease [101 kB] Hit:4 https://downloads.mariadb.com/Tools/ubuntu focal InRelease Hit:13 https://downloads.mariadb.com/MaxScale/6.1.1/apt focal InRelease Get:15 http://us.archive.ubuntu.com/ubuntu focal-updates/main i386 Packages [536 kB] Get:16 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages [1,214 kB] Get:17 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 DEP-11 Metadata [283 kB] Get:18 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 Packages [855 kB] Get:19 http://us.archive.ubuntu.com/ubuntu focal-updates/universe amd64 DEP-11 Metadata [352 kB] Get:20 http://us.archive.ubuntu.com/ubuntu focal-updates/multiverse amd64 DEP-11 Metadata [944 B] Get:21 http://us.archive.ubuntu.com/ubuntu focal-backports/universe amd64 DEP-11 Metadata [10.4 kB] Fetched 3,978 kB in 28s (143 kB/s) Reading package lists... Done
After we got the repository, then we will install PostgreSQL database server by typing command line :
$ sudo apt-get -y install postgresql
The output will be shown below :
mpik@app01:~$ sudo apt-get -y install postgresql
Reading package lists… Done
Building dependency tree
Reading state information… Done
The following packages were automatically installed and are no longer required:
libevent-core-2.1-7 libevent-pthreads-2.1-7 libmecab2 libzip5 linux-headers-5.4.0-26 linux-headers-5.4.0-26-generic
linux-image-5.4.0-26-generic linux-modules-5.4.0-26-generic linux-modules-extra-5.4.0-26-generic mecab-ipadic mecab-ipadic-utf8 mecab-utils
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libtypes-serialiser-perl pgdg-keyring postgresql-13 postgresql-client-13
postgresql-client-common postgresql-common sysstat
Suggested packages:
postgresql-doc postgresql-doc-13 isag
The following NEW packages will be installed:
libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libtypes-serialiser-perl pgdg-keyring postgresql postgresql-13
postgresql-client-13 postgresql-client-common postgresql-common sysstat
0 upgraded, 12 newly installed, 0 to remove and 402 not upgraded.
Need to get 17.8 MB of archives.
After this operation, 59.4 MB of additional disk space will be used.
Get:1 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 libpq5 amd64 13.4-1.pgdg20.04+1 [179 kB]
Get:2 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libcommon-sense-perl amd64 3.74-2build6 [20.1 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-perl all 4.02000-2 [80.9 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 pgdg-keyring all 2018.2 [10.7 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-common all 226.pgdg20.04+1 [90.6 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-13 amd64 13.4-1.pgdg20.04+1 [1,507 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-xs-perl amd64 4.020-1build1 [83.7 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 sysstat amd64 12.2.0-2ubuntu0.1 [448 kB]
Get:10 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-common all 226.pgdg20.04+1 [246 kB]
Get:11 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-13 amd64 13.4-1.pgdg20.04+1 [15.1 MB]
Get:12 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql all 13+226.pgdg20.04+1 [65.2 kB]
Fetched 17.8 MB in 25s (723 kB/s)
Preconfiguring packages
…
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
pg_ctlcluster 13 main start
Ver Cluster Port Status Owner Data directory Log file
13 main 5432 down postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log
update-alternatives: using /usr/share/postgresql/13/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
Setting up postgresql (13+226.pgdg20.04+1) ...
Processing triggers for systemd (245.4-4ubuntu3.11) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9) ...
3. Initialize and Starting PostgreSQL
By default, the PostgreSQL database service is up while installation, to verify it we will type the command line :
$ sudo systemctl status postgresql
The output will be shown below :
mpik@app01:~$ sudo systemctl status postgresql
[sudo] password for mpik:
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2021-09-17 21:41:34 PDT; 6min ago
Main PID: 6430 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 9436)
Memory: 0B
CGroup: /system.slice/postgresql.service
Sep 17 21:41:34 app01.otodiginet.com systemd[1]: Starting PostgreSQL RDBMS...
Sep 17 21:41:34 app01.otodiginet.com systemd[1]: Finished PostgreSQL RDBMS.
On this step, we have PostgreSQL is running.
4. Set PostgreSQL admin user’s password
On this step, we will set the PostgreSQL admin user’s password. This step need us to log in to the database and by using sql statement we will update the admin password. We will present it below :
mpik@app01:~$ sudo su postgres
[sudo] password for mpik:
postgres@app01:/home/mpik$ psql
psql (13.4 (Ubuntu 13.4-1.pgdg20.04+1))
Type "help" for help.
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
(3 rows)
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
After loging in, we modifty password by using SQL statement. On this case, we will update user postgres’ password to be Otodiginet2021.
postgres=# alter user postgres with password 'Otodiginet2021'; ALTER ROLE
Showing PostgreSQL version
To show PostgreSQL version, we will use the SQL command line as shown below :
postgres=# SELECT version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.4 (Ubuntu 13.4-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)
postgres=# SHOW server_version;
server_version
----------------------------------
13.4 (Ubuntu 13.4-1.pgdg20.04+1)
(1 row)
postgres=# \! psql -V
psql (PostgreSQL) 13.4 (Ubuntu 13.4-1.pgdg20.04+1)
Enabling Remote Connection
By default, PostgreSQL only accepts connections from the localhost, while the connection from remote is still disable. To enable remote connection, we just update the /etc/postgresql/13/main/postgresql.conf file and /etc/postgresql/13/main/pg_hba.conf file for accepting remote connections.
After these files are updated then we just restart PostgreSQL service by typing command line :
$ sudo systemctl restart postgresql
Conclusion
On this short guidance, we have tried to install PostgreSQL 13 on Ubuntu 20.04 LTS operating system successfully.
(Visited 45 times, 1 visits today)
1 thought on “How To Install PostgreSQL 13 On Ubuntu 20.04 LTS”