Install PostgreSQL 11 on Ubuntu 20.04/18.04/16.04
source link: https://computingforgeeks.com/install-postgresql-11-on-ubuntu-linux/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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 reboot
Once the system is rebooted, install vim
and wget
if not already installed.
sudo apt install -y wget vim
Step 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.list
Verify 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
postgres@os1:~$ psql -c "alter user postgres with password 'StrongPassword'"
ALTER ROLE
You can also add other database users:
createuser dbuser1
Add test database:
postgres@ubuntu-01:~$ 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
postgres@ubuntu-01:~$ 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.
Best Video courses to Learn PostgreSQL Database:
Related guides:
Install PostgreSQL 11 on Debian
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK