You may want to install PostgreSQL from an official repository, since it is updated more frequently than official Ubuntu sources.
First, you should install prerequisite software packages that will be used to download and install software certificates for a secure SSL connection.
sudo apt install wget ca-certificates
Then, get the certificate, add it to apt-key management utility and create a new configuration file with an official PostgreSQL repository address inside.
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
It is always a good idea to download information about all packages available for installation from your configured sources before the actual installation.
sudo apt update
apt install postgresql postgresql-contrib
Check PostgreSQL status
service postgresql status
Start Using PostgreSQL Command Line Tool
sudo -u postgres psql
Create new password
\password postgres
Create and Populate a New Database
You are now connected to your database server through psql command line tool with full access rights, so it’s time to create a new database.
CREATE DATABASE test_erp;
Setup PostgreSQL server access postgresql.conf configuration file of PostgreSQL version 14 by using vim text editor.
vim /etc/postgresql/14/main/postgresql.conf
Uncomment and edit the listen_addresses attribute to start listening to start listening to all available IP addresses.
listen_addresses = '*'
Now edit the PostgreSQL access policy configuration file.
vim /etc/postgresql/14/main/pg_hba.conf
Append a new connection policy (a pattern stands for [CONNECTION_TYPE][DATABASE][USER] [ADDRESS][METHOD]) in the bottom of the file.
host all all 0.0.0.0/0 md5
It is now time to restart your PostgreSQL service to load your configuration changes.
systemctl restart postgresql
Let’s now connect to a remote PostgreSQL database that we have hosted on one of the Cherry Servers machines.
psql -h <server-ip-address> -p 5432 -d <database-name> -U postgres