PostgreSQL Setup and Configuration

What is PostgreSQL?

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

PostgreSQL Setup

sudo apt-get install postgresql postgresql-contrib libpq-dev

sudo -u postgres createuser -s username

sudo -u postgres psql

postgres=# \password username

sudo -u postgres createdb -O username db_name

sudo service postgresql restart

test: psql -h localhost -d db_name -U username -p 5432

Make Remote Database:

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

#listen_addresses = 'localhost'

listen_addresses = '*'

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

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:

host all all 0.0.0.0/0 md5

# IPv6 local connections:

host all all ::1/128 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

host replication all 127.0.0.1/32 md5

host replication all ::1/128 md5

# custom

host all all 0.0.0.0/0 md5

Remote DB config (centos)

Setup: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-7

firewall-cmd --permanent --add-service=postgresql;firewall-cmd --reload

vi /var/lib/pgsql/data/pg_hba.conf

host all all XXX.XXX.XXX.XXX/XX md5

vi /var/lib/pgsql/data/postgresql.conf

listen_addresses = '*'

port = 5432

sudo systemctl start postgresql-11

sudo systemctl enable postgresql-11

sudo systemctl restart postgresql-11

Remote DB Connect

psql -U postgres -h 197.245.200.125 -p 5432

create database:

CREATE DATABASE Db_name;