This short guide will help you to Install PostgreSQL 11 on Ubuntu 20.04/18.04/16.04. PostgreSQL Server is a robust open source and highly-extensible database server. PostgreSQL provides object-relational database system allowing you to manage extensive SQL datasets.
Key PostgreSQL 11 Enhancements:
- Improvements to partitioning functionality
- SQL stored procedures that support embedded transactions
- Improvements to parallelism
- Window functions now support all framing options shown in the SQL:2011 standard
- Optional Just-in-Time (JIT) compilation for some SQL code, speeding evaluation of expressions
- Performance improvements, including the ability to avoid a table rewrite for ALTER TABLE … ADD COLUMN with a non-null column default
- Covering indexes can now be created, using the INCLUDE clause of CREATE INDEX
For CentOS / Fedora, refer to:
How to install PostgreSQL 11 on Fedora
How to install PostgreSQL 11 on CentOS 7
How to install PostgreSQL 11 on CentOS 8 / RHEL 8
The Release page highlights all the new features available in PostgreSQL 11. Follow the steps provided in the next sections to install PostgreSQL 11 on Ubuntu 20.04/18.04/16.04.
Step 1: Update system and install dependencies
It is recommended to update your current system packages if it is a new server instance.
sudo apt update && sudo apt -y upgrade
sudo rebootOnce the system is rebooted, install vim and wget if not already installed.
sudo apt install -y wget vimStep 2: Add PostgreSQL 11 APT repository
Before adding repository content to your Ubuntu 20.04/18.04/16.04 system, you need to import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -After importing GPG key, add repository contents to your Ubuntu 20.04/18.04/16.04 system:
RELEASE=$(lsb_release -cs)
echo "deb http://apt.postgresql.org/pub/repos/apt/ ${RELEASE}"-pgdg main | sudo tee /etc/apt/sources.list.d/pgdg.listVerify repository file contents
$ cat /etc/apt/sources.list.d/pgdg.list
deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
Step 3: Install PostgreSQL 11 on Ubuntu 20.04/18.04/16.04
The last installation step is for PostgreSQL 11 packages. Run the following commands to install PostgreSQL 11 on Ubuntu 20.04/18.04/16.04.
sudo apt update
sudo apt -y install postgresql-11
Step 4: Allow access to PostgreSQL from remote hosts
By default, access to PostgreSQL database server is only from localhost.
$ sudo ss -tunelp | grep 5432
tcp LISTEN 0 128 127.0.0.1:5432 0.0.0.0:* users:(("postgres",pid=15785,fd=3)) uid:111 ino:42331 sk:6 <->
To allow network access, edit configuration file:
sudo vim /etc/postgresql/11/main/postgresql.conf
Add below line under CONNECTIONS AND AUTHENTICATION section.
listen_addresses = '*'
You can also specify server IP Address
listen_addresses = '192.168.17.12'
See below screenshot.
Don’t forget to restart postgresql service after making the change
sudo systemctl restart postgresql
Confirm the bind address for PostgreSQL:
$ sudo ss -tunelp | grep 5432
tcp LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* users:(("postgres",pid=16066,fd=3)) uid:111 ino:42972 sk:8 <-> tcp LISTEN 0 128 [::]:5432 [::]:* users:(("postgres",pid=16066,fd=6)) uid:111 ino:42973 sk:9 v6only:1 <->
If you have an active UFW firewall, allow port 5432
sudo ufw allow 5432/tcp
Step 5: Set PostgreSQL admin user’s password and do testing
Set a password for the default admin user
$ sudo su - postgres
[email protected]:~$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE
You can also add other database users:
createuser dbuser1
Add test database:
[email protected]:~$ createdb testdb -O dbuser1
Do a test operationg by logging in as a dbuser1 and operating on testdb
~$ psql -l | grep testdb testdb | dbuser1 | LATIN1 | en_US | en_US |
Set user password:
$ psql
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.
postgres=# alter user dbuser1 with password 'DBPassword';
ALTER ROLE
Create table and add some dummy data:
testdb=# create table test_table ( id int,first_name text, last_name text );
CREATE TABLE
testdb=# insert into test_table (id,first_name,last_name) values (1,'John','Doe');
INSERT 0 1
Show table data
testdb=# select * from test_table; id | first_name | last_name ----+------------+----------- 1 | John | Doe (1 row)
Drop our test table
testdb=# DROP TABLE test_table; DROP TABLE testdb=# \q
Drop test database
[email protected]:~$ dropdb testdb;
Step 6: Install Web Management Tool (Optional)
For easy administration, consider installing pgAdmin 4 onto your Ubuntu system.
How to install pgAdmin4 on Ubuntu
You have successfully installed PostgreSQL database server on Ubuntu 18.04 / Ubuntu 16.04 and performed a couple tests.
Related guides:
Install PostgreSQL 11 on Debian
Install PostgreSQL 11 on CentOS 7
Install PostgreSQL 11 on CentOS 8
