How To: PostgreSQL Install

Install PostgreSQL

sudo apt install postgresql postgresql-contrib #Ubuntu

sudo yum install postgresql postgresql-contrib #Centos

Start PostgreSQL Service

sudo systemctl start postgresql.service

Switch to the postgres account & access the PostgreSQL

sudo -i -u postgres
psql

sudo -u postgres psql

Exit PostgreSQL prompt

\q

Create a new role

createuser --interactive

sudo -u postgres createuser --interactive

Output
Enter name of role to add: megatron
Shall the new role be a superuser? (y/n) y

Create a new database

createdb megatron

sudo -u postgres createdb megatron

Open a Postgres Prompt with the New Role

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user)

sudo adduser megatron

sudo -u megatron psql

To connect to a different database

psql -d postgres

To check current connection information

\conninfo

Output
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Open Firewall Port

sudo ufw allow 5432

Edit postgresql.conf file

sudo nano /etc/postgresql/14/main/postgresql.conf

Uncommented the following line & change the value to '*'

listen_addresses = '*'

Edit pg_hba.conf file

Add an entry for the host: specify the host, the database, the authentication method, and the database user.

Note: PostgreSQL server uses SCRAM (Salted Challenge Response Authentication Mechanism) authentication

sudo nano /etc/postgresql/14/main/pg_hba.conf

TYPE    DATABASE  USER         ADDRESS             METHOD
host    all       megatron     10.0.0.167/32       scram-sha-256

Restart postgreSQL service

sudo service postgresql restart

Download PostgreSQL Repository RPM

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Install PosgreSQL on CentOS 7

sudo yum install -y postgresql15-server

Initialize the Database

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

Start the Database

sudo systemctl start postgresql-15

Enable PostgreSQL Launch on Reboot

sudo systemctl enable postgresql-15

Modify the postgres user password

sudo passwd postgres

Open Firewall Port

sudo firewall-cmd --zone=public--add-port=5432/tcp --permanent
sudo firewall-cmd --reload

Edit postgres.conf file

sudo su - postgres

nano /var/lib/pgsql/15/data/postgresql.conf

Uncommented the following line & change the value to '*'

listen_addresses = '*'

Edit pg_hba.conf file

Add an entry for the host: specify the host, the database, the authentication method, and the database user.

Note: PostgreSQL server uses SCRAM (Salted Challenge Response Authentication Mechanism) authentication

nano /var/lib/pgsql/15/data/pg_hba.conf

TYPE    DATABASE  USER         ADDRESS             METHOD
host    all       all          10.0.0.167/32       scram-sha-256

Restart postgreSQL service

find the service name and then reload the service

systemctl list-units|grep postgresql

service postgresql-15.service reload

----------------------------------------------------------------------------------------------------

Connect to PostgreSQL with DBeaver

----------------------------------------------------------------------------------------------------

Change user password

ALTER USER username WITH PASSWORD 'new_password';

References

How To Install PostgreSQL on Ubuntu 20.04 [Quickstart]. https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart