In this short tutorial we will learn how to install PostgreSQL 14.1 on Ubuntu 20.04 LTS Server.
Introduction
Currently, the last stable PostgreSQL is version 14.1 which as released on 11 November 2021. 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 14 on Ubuntu 20.04 LTS operating system.
PostgreSQL 14 contains many new features and enhancements, including:
- Stored procedures can now return data via OUT parameters.
- The SQL-standard SEARCH and CYCLE options for common table expressions have been implemented.
- Subscripting can now be applied to any data type for which it is a useful notation, not only arrays. In this release, the jsonb and hstore types have gained subscripting operators.
- Range types have been extended by adding multiranges, allowing representation of noncontiguous data ranges.
- Numerous performance improvements have been made for parallel queries, heavily-concurrent workloads, partitioned tables, logical replication, and vacuuming.
- B-tree index updates are managed more efficiently, reducing index bloat.
- VACUUM automatically becomes more aggressive, and skips inessential cleanup, if the database starts to approach a transaction ID wraparound condition.
- Extended statistics can now be collected on expressions, allowing better planning results for complex queries.
- libpq now has the ability to pipeline multiple queries, which can boost throughput over high-latency connections.
PostgreSQL 14 Installation On Ubuntu 20.04 LTS
Prerequisites
Before we are starting PostgreSQL 14 installation, there are will be prerequisites to be fulfill, as mention below :
- Ubuntu 20.04 LTS System with sufficient disk space
- root or non-root user with administrative privileges
- firewall configured with firewalld
PostgreSQL Installation Steps
The PostgreSQL installaton will be consist of several steps :
- Adding PostgreSQL 14 Repository To Ubuntu 20.04
- Install PostgreSQL 14 on Ubuntu 20.04
- Set PostgreSQL admin user’s password
- Enabling Remote Access
1. Adding PostgreSQL Repository To Ubuntu 20.04
PostgreSQL (Postgres) is provided in the default Ubuntu repositories. To check the version provides, use this command:
$ sudo apt-cache search postgresql | grep postgresql
Output :
rapik@worker2:~$ sudo apt-cache search postgresql | grep postgresql [sudo] password for rapik: postgresql - object-relational SQL database (supported version) postgresql-12 - object-relational SQL database, version 12 server postgresql-client - front-end programs for PostgreSQL (supported version) postgresql-client-12 - front-end programs for PostgreSQL 12 postgresql-client-common - manager for multiple PostgreSQL client versions postgresql-common - PostgreSQL database-cluster manager postgresql-contrib - additional facilities for PostgreSQL (supported version) postgresql-doc - documentation for the PostgreSQL database management system postgresql-doc-12 - documentation for the PostgreSQL database management system akonadi-backend-postgresql - PostgreSQL storage backend for Akonadi autopostgresqlbackup - Automated tool to make periodic backups of PostgreSQL databases ... postgresql-all - metapackage depending on all PostgreSQL server packages postgresql-autodoc - Utility to create a PostgreSQL database schema overview in HTML, DOT and XML postgresql-comparator - efficient PostgreSQL table content comparison and synchronization postgresql-filedump - Dump PostgreSQL files in human-readable form postgresql-pgsphere - Spherical data types for PostgreSQL postgresql-plperl-12 - PL/Perl procedural language for PostgreSQL 12 postgresql-plpython3-12 - PL/Python 3 procedural language for PostgreSQL 12 postgresql-pltcl-12 - PL/Tcl procedural language for PostgreSQL 12 postgresql-q3c - PostgreSQL extension used for indexing the sky postgresql-server-dev-12 - development files for PostgreSQL 12 server-side programming postgresql-server-dev-all - extension build tool for multiple PostgreSQL versions puppet-module-puppetlabs-postgresql - Puppet module for PostgreSQL database pure-ftpd-postgresql - Secure and efficient FTP server with PostgreSQL user authentication python3-multicorn - multicorn utility module for postgresql-X.Y-python3-multicorn python3-postgresql - pgsql driver, cluster management tools, and client tools python3-testing.postgresql - Python testing.* framework - PostgreSQL helpers (Python 3) r-cran-rpostgresql - GNU R package providing database interface and driver for PostgreSQL rt4-db-postgresql - PostgreSQL database backend for request-tracker4 sisu-postgresql - SiSU dependencies for use with PostgreSQL database tarantool-lts-postgresql-module - Tarantool in-memory database - PostgreSQL connector tntdb-postgresql4v5 - PostgreSQL backend for tntdb database access library
From information above, PostgreSQL 14 is not included yet on the system. Then we will add PostgreSQL 14 by submitting command line :
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Output :
rapik@worker2:~$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' rapik@worker2:~$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - OK
2. Install PostgreSQL 14 on Ubuntu 20.04
On this stage we will install PostgreSQL on Ubuntu 20.04. For this purpose we will do following command lines.
$ sudo apt -y update $ sudo apt -y install postgresql-14
Output :
rapik@worker2:~$ sudo apt -y install postgresql-14 Reading package lists... Done Building dependency tree Reading state information... Done The following packages were automatically installed and are no longer required: gimp-data kodi-inputstream-adaptive kodi-inputstream-rtmp kodi-peripheral-joystick libamd2 libaom2 libass9 libbabl-0.1-0 libcamd2 libccolamd2 libcdio19 libcec4 libcholmod3 libcrossguid0 libdav1d5 libde265-0 libfstrcmp0 libgegl-0.4-0 libgegl-common libgimp2.0 libheif1 libiso9660-11 libmariadb3 libmetis5 libmicrohttpd12 libmng2 libmypaint-1.5-1 libmypaint-common libp8-platform2 libsdl2-2.0-0 libshairplay0 libtinyxml2.6.2v5 libudfread0 libumfpack5 libva-wayland2 libwayland-client++0 libwayland-cursor++0 libwayland-egl++0 libx265-192 mariadb-common python3-bluez python3-pycryptodome Use 'sudo apt autoremove' to remove them. The following additional packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libpq-dev libpq5 libssl-dev libssl1.1 libtypes-serialiser-perl pgdg-keyring postgresql-client-14 postgresql-client-common postgresql-common sysstat Suggested packages: postgresql-doc-14 libssl-doc isag The following NEW packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libssl-dev libtypes-serialiser-perl pgdg-keyring postgresql-14 postgresql-client-14 postgresql-client-common postgresql-common sysstat The following packages will be upgraded: libpq-dev libpq5 libssl1.1 3 upgraded, 11 newly installed, 0 to remove and 335 not upgraded. Need to get 21.5 MB of archives. After this operation, 69.7 MB of additional disk space will be used. Get:1 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 libpq-dev amd64 14.1-2.pgdg20.04+1 [137 kB] Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libssl1.1 amd64 1.1.1f-1ubuntu2.10 [1,322 kB] Get:3 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 libpq5 amd64 14.1-2.pgdg20.04+1 [170 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 232.pgdg20.04+1 [91.6 kB] Get:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-14 amd64 14.1-2.pgdg20.04+1 [1,602 kB] Get:6 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-client-14 amd64 14.1-2.pgdg20.04+1 [1,602 kB] Get:7 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-common all 232.pgdg20.04+1 [225 kB] Get:8 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 postgresql-14 amd64 14.1-2.pgdg20.04+1 [15.7 MB] Get:2 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libssl1.1 amd64 1.1.1f-1ubuntu2.10 [1,322 kB] Get:9 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libcommon-sense-perl amd64 3.74-2build6 [20.1 kB] Get:10 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-perl all 4.02000-2 [80.9 kB] Get:11 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libtypes-serialiser-perl all 1.0-1 [12.1 kB] Get:12 http://us.archive.ubuntu.com/ubuntu focal/main amd64 libjson-xs-perl amd64 4.020-1build1 [83.7 kB] Get:13 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 libssl-dev amd64 1.1.1f-1ubuntu2.10 [1,584 kB] Get:14 http://us.archive.ubuntu.com/ubuntu focal-updates/main amd64 sysstat amd64 12.2.0-2ubuntu0.1 [448 kB] Fetched 20.6 MB in 2min 1s (169 kB/s) Preconfiguring packages ... (Reading database ... 203680 files and directories currently installed.) Preparing to unpack .../00-libssl1.1_1.1.1f-1ubuntu2.10_amd64.deb ... Unpacking libssl1.1:amd64 (1.1.1f-1ubuntu2.10) over (1.1.1f-1ubuntu2.8) ... Selecting previously unselected package libcommon-sense-perl. Preparing to unpack .../01-libcommon-sense-perl_3.74-2build6_amd64.deb ... Unpacking libcommon-sense-perl (3.74-2build6) ... Selecting previously unselected package libjson-perl. Preparing to unpack .../02-libjson-perl_4.02000-2_all.deb ... Unpacking libjson-perl (4.02000-2) ... Selecting previously unselected package libtypes-serialiser-perl. Preparing to unpack .../03-libtypes-serialiser-perl_1.0-1_all.deb ... Unpacking libtypes-serialiser-perl (1.0-1) ... Selecting previously unselected package libjson-xs-perl. Preparing to unpack .../04-libjson-xs-perl_4.020-1build1_amd64.deb ... Unpacking libjson-xs-perl (4.020-1build1) ... Preparing to unpack .../05-libpq-dev_14.1-2.pgdg20.04+1_amd64.deb ... Unpacking libpq-dev (14.1-2.pgdg20.04+1) over (12.9-0ubuntu0.20.04.1) ... Preparing to unpack .../06-libpq5_14.1-2.pgdg20.04+1_amd64.deb ... Unpacking libpq5:amd64 (14.1-2.pgdg20.04+1) over (12.9-0ubuntu0.20.04.1) ... Selecting previously unselected package libssl-dev:amd64. Preparing to unpack .../07-libssl-dev_1.1.1f-1ubuntu2.10_amd64.deb ... Unpacking libssl-dev:amd64 (1.1.1f-1ubuntu2.10) ... Selecting previously unselected package pgdg-keyring. Preparing to unpack .../08-pgdg-keyring_2018.2_all.deb ... Unpacking pgdg-keyring (2018.2) ... Selecting previously unselected package postgresql-client-common. Preparing to unpack .../09-postgresql-client-common_232.pgdg20.04+1_all.deb ... Unpacking postgresql-client-common (232.pgdg20.04+1) ... Selecting previously unselected package postgresql-client-14. Preparing to unpack .../10-postgresql-client-14_14.1-2.pgdg20.04+1_amd64.deb ... Unpacking postgresql-client-14 (14.1-2.pgdg20.04+1) ... Selecting previously unselected package postgresql-common. Preparing to unpack .../11-postgresql-common_232.pgdg20.04+1_all.deb ... Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common' Unpacking postgresql-common (232.pgdg20.04+1) ... Selecting previously unselected package postgresql-14. Preparing to unpack .../12-postgresql-14_14.1-2.pgdg20.04+1_amd64.deb ... Unpacking postgresql-14 (14.1-2.pgdg20.04+1) ... Selecting previously unselected package sysstat. Preparing to unpack .../13-sysstat_12.2.0-2ubuntu0.1_amd64.deb ... Unpacking sysstat (12.2.0-2ubuntu0.1) ... Setting up pgdg-keyring (2018.2) ... Removing apt.postgresql.org key from trusted.gpg: OK Setting up libssl1.1:amd64 (1.1.1f-1ubuntu2.10) ... Setting up libpq5:amd64 (14.1-2.pgdg20.04+1) ... Setting up libcommon-sense-perl (3.74-2build6) ... Setting up libssl-dev:amd64 (1.1.1f-1ubuntu2.10) ... Setting up libtypes-serialiser-perl (1.0-1) ... Setting up libjson-perl (4.02000-2) ... Setting up sysstat (12.2.0-2ubuntu0.1) ... Creating config file /etc/default/sysstat with new version update-alternatives: using /usr/bin/sar.sysstat to provide /usr/bin/sar (sar) in auto mode Created symlink /etc/systemd/system/multi-user.target.wants/sysstat.service → /lib/systemd/system/sysstat.service. Setting up postgresql-client-common (232.pgdg20.04+1) ... Setting up libjson-xs-perl (4.020-1build1) ... Setting up libpq-dev (14.1-2.pgdg20.04+1) ... Setting up postgresql-client-14 (14.1-2.pgdg20.04+1) ... update-alternatives: using /usr/share/postgresql/14/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode Setting up postgresql-common (232.pgdg20.04+1) ... Adding user postgres to group ssl-cert Creating config file /etc/postgresql-common/createcluster.conf with new version Building PostgreSQL dictionaries from installed myspell/hunspell packages... en_us Removing obsolete dictionary files: Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /lib/systemd/system/postgresql.service. Setting up postgresql-14 (14.1-2.pgdg20.04+1) ... Creating new PostgreSQL cluster 14/main ... /usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/main --auth-local peer --auth-host scram-sha-256 --no-instructions The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale "en_US.UTF-8". The default database encoding has accordingly been set to "UTF8". The default text search configuration will be set to "english". Data page checksums are disabled. fixing permissions on existing directory /var/lib/postgresql/14/main ... ok 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 update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (po stmaster.1.gz) in auto mode 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) ...
By default, PosrgreSQL service will be started after installation was completed. We wil verfiry the installation was successful by checking its service, by submitting command line :
$ sudo systemctl status postgresql
Output :
rapik@worker2:~$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Sat 2022-01-01 06:55:53 PST; 1min 9s ago
Main PID: 31795 (code=exited, status=0/SUCCESS)
Tasks: 0 (limit: 8782)
Memory: 0B
CGroup: /system.slice/postgresql.service
Jan 01 06:55:53 worker2.otodiginet.com systemd[1]: Starting PostgreSQL RDBMS...
Jan 01 06:55:53 worker2.otodiginet.com systemd[1]: Finished PostgreSQL RDBMS.
rapik@worker2:~$
We can also verify the postgreSQL version we have installed with the command line:
$ /usr/lib/postgresql/14/bin/psql --version
Output :
rapik@worker2:~$ /usr/lib/postgresql/14/bin/psql --version psql (PostgreSQL) 14.1 (Ubuntu 14.1-2.pgdg20.04+1)
or by using command line below :
$ sudo -u postgres psql -c "SELECT version();"
Output :
rapik@worker2:~$ sudo -u postgres psql -c "SELECT version();" --------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.1 (Ubuntu 14.1-2.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)
3. Set PostgreSQL admin user’s password
In this section, we will set the superuser password. For this purpose we have to log in to PostgreSQL and do sql script for setting admin user (superuser) password.
Login to PostgreSQL database.
$ sudo -i -u postgres
Output :
rapik@worker2:~$ sudo -i -u postgres
postgres@worker2:~$ psql
psql (14.1 (Ubuntu 14.1-2.pgdg20.04+1))
Type "help" for help.
postgres=# SELECT version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 14.1 (Ubuntu 14.1-2.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=# CREATE ROLE root WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'password123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser, Create role, Create DB | {}
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
odoo15 | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root | Superuser, Create role, Create DB | {}
postgres=#
4. Enabling Remote Access
When we install PostgreSQL database engine on Ubuntu 20.04, it is only accepts connections from the localhost, while the connection from remote is still disable. To enable remote connections to our fresh PostgreSQL 14 database server we have to edit a configuration file for it, named as : postgresql.conf. We will modify $ sudo vi /etc/postgresql/14/main/postgresql.conf file, as shown below.
rapik@worker2:~$ sudo vi /etc/postgresql/14/main/postgresql.conf TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/32 scram-sha-256 # IPv6 local connections: host all all ::1/128 scram-sha-256 # Allow replication connections from localhost, by a user with the # replication privilege. local replication all peer host replication all 127.0.0.1/32 scram-sha-256 host replication all ::1/128 scram-sha-256 # Accept from anywhere host all all 0.0.0.0/0 md5
Conclusion
in this tutorial, we have learned to install Postrgresql 14 database on ubuntu 20.04 successfully. Since PostgreSQL 14 is not yet in the default Ubuntu 20.04 repository, we have to add it manually.
Hopefully this simple tutorial is useful for those in need.
(Visited 163 times, 1 visits today)
Pingback: How to install Odoo 15 on Ubuntu 20.04 LTS - bckinfo.com