How to Install PostgreSQL on Ubuntu 20.04
We hope this post helped you to find out How to Install PostgreSQL on Ubuntu 20.04
PostgreSQL or Postgres is an open-source general-purpose object-relational database administration system with many superior options that permits you to construct fault-tolerant environments or advanced purposes.
On this information, we are going to clarify learn how to set up the PostgreSQL database server on Ubuntu 20.04, and discover the fundamentals of PostgreSQL database administration.
To have the ability to set up packages, it is advisable be logged in as root or person with sudo privileges.
Set up PostgreSQL on Ubuntu #
On the time of writing this text, the newest model of PostgreSQL accessible from the official Ubuntu repositories is PostgreSQL model 10.4.
Run the next instructions to put in PostgreSQL server on Ubuntu:
sudo apt update
sudo apt install postgresql postgresql-contrib
We’re additionally putting in the PostgreSQL contrib package deal that gives a number of extra options for the PostgreSQL database system.
As soon as the set up is accomplished, the PostgreSQL service will begin routinely. Use the
psql device to confirm the set up by connecting to the PostgreSQL database server and printing its model:
sudo -u postgres psql -c "SELECT version();"
PostgreSQL 12.2 (Ubuntu 12.2-4) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-8ubuntu1) 9.3.0, 64-bit
That’s it. PostgreSQL has been put in, and you can begin utilizing it.
PostgreSQL Roles and Authentication Strategies #
Database entry permissions inside PostgreSQL are dealt with with the idea of roles. A job can characterize a database person or a bunch of database customers.
PostgreSQL helps a number of authentication methods. Probably the most commonly-used strategies are:
- Belief – A job can join with no password, so long as the situations outlined within the
- Password – A job can join by offering a password. The passwords might be saved as
- Ident – Solely supported on TCP/IP connections. It really works by acquiring the shopper’s working system person identify, with an non-obligatory person identify mapping.
- Peer – Identical as Ident, however it’s supported on native connections solely.
PostgreSQL shopper authentication is outlined within the configuration file named
pg_hba.conf. By default, PostgreSQL is utilizing the peer authentication technique for native connections.
postgres person is routinely created once you set up PostgreSQL. This person is the superuser for the PostgreSQL occasion, and it’s equal to the MySQL root person.
To log in to the PostgreSQL server because the
postgres person, first swap to the person after which entry the PostgreSQL immediate utilizing the
sudo su - postgres
From here, you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell type:
Another way to access the PostgreSQL prompt without switching users, is to use the
sudo -u postgres psql
Generally, you should log in to the database server as
postgres only from the localhost.
Creating PostgreSQL Role and Database #
Only superusers and roles with
CREATEROLE privilege can create new roles.
The following example shows how to create a new role named
john a database named
johndb and grant privileges on the database:
Create a new PostgreSQL role:
sudo su - postgres -c "createuser john"
Create a new PostgreSQL Database:
sudo su - postgres -c "createdb johndb"
To grant permissions to the user on the database, connect to the PostgreSQL shell:
sudo -u postgres psql
and run the following query:
grant all privileges on database johndb to john;
Enable Remote Access to PostgreSQL server #
By default, the PostgreSQL server listens only on the local interface (
To enable remote access to your PostgreSQL server open the configuration file
postgresql.conf and add
listen_addresses = '*' in the
CONNECTIONS AND AUTHENTICATION section.
sudo nano /etc/postgresql/12/main/postgresql.conf
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service:
sudo service postgresql restart
Verify the changes with the
ss -nlt | grep 5432
The output shows that the PostgreSQL server is listening on all interfaces (
LISTEN 0 244 0.0.0.0:5432 0.0.0.0:* LISTEN 0 244 [::]:5432 [::]:*
The next step is to configure the server to accept remote connections by editing the
Below are some examples showing different use cases:
# TYPE DATABASE USER ADDRESS METHOD # The user jane can access all databases from all locations using md5 password host all jane 0.0.0.0/0 md5 # The user jane can access only the janedb from all locations using md5 password host janedb jane 0.0.0.0/0 md5 # The user jane can access all databases from a trusted location (192.168.1.134) without a password host all jane 192.168.1.134 trust
The last step is to open the port
5432 in your firewall.
Assuming you are using
UFW to manage your firewall, and you want to allow access from the
192.168.1.0/24 subnet, you would run the following command:
sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432
Ensure your firewall is configured to simply accept connections solely from trusted IP ranges.
We’ve proven you learn how to set up and configure PostgreSQL on Ubuntu 20.04 server. Seek the advice of the PostgreSQL 12 Documentation for extra info on this subject.
We hope the How to Install PostgreSQL on Ubuntu 20.04 help you. If you have any query regarding How to Install PostgreSQL on Ubuntu 20.04 drop a comment below and we will get back to you at the earliest.
We hope this post helped you to find out How to Install PostgreSQL on Ubuntu 20.04 . You may also want to see – How to Install Apache on Ubuntu 20.04