POSTGRESQL SERVER ON UBUNTU SERVER 20.04

PostgreSQL or Postgres is an open-source general-purpose object-relational database management system with many advanced features that allows you to build fault-tolerant environments or complex applications. https://linuxize.com/post/how-to-install-postgresql-on-ubuntu-20-04/

[email protected][~]# sudo apt install postgresql postgresql-contrib postgresql-client -y

[email protected][~]# systemctl status postgresql.service

SETTING PASSWORD ON POSTGRES USER

[email protected][~]# sudo -u postgres psql postgres

[email protected][~]# postgres=# \password postgres

Enter password

[email protected][~]# postgres=# \q

CREATING OF NEW USER AND DATABASE

Be sure to change stephen to your preferred username and password to a strong password of your choosing as well as database name snet:

[email protected][~]# sudo -u postgres psql postgres

[email protected][~]# CREATE USER stephen WITH PASSWORD 'password';

[email protected][~]# CREATE DATABASE snet;

[email protected][~]# GRANT ALL PRIVILEGES ON DATABASE snet TO stephen;

[email protected][~]# postgres=# \q

ALLOWING LOCAL CONNECTION

[email protected][~]# sudo nano /etc/postgresql/12/main/pg_hba.conf

Change from local all postgres peer to local all postgres md5

Change from local all all peer to local all all md5

Change from host all all 127.0.0.1/32 md5 to host all all 0.0.0.0/0 md5

[email protected][~]# sudo systemctl restart postgresql

ENABLING REMOTE ACCESS IN POSTGRESQL

[email protected][~]# sudo nano /etc/postgresql/12/main/postgresql.conf

Uncomment the line and change localhost to ‘*’.

[email protected][~]# sudo systemctl restart postgresql.service

WHAT TO ON THE CLIENT MACHINE

The PostgreSQL Client can be used to connect to an external PostgreSQL database. Use this option if you already have a database server up and running, but need to be able to remotely access the database from one or more client systems. To get started, install the postgresql-client package.

[email protected][~]# sudo apt install postgresql-client

[email protected][~]# psql -h 192.168.1.2 (postgre-server) -U stephen (postgre-user)