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

PostGIS Extension for PostgreSQL

sudo apt install postgis

sudo -u postgres psql

Check Availability

=# SELECT * FROM pg_available_extensions;

Load the Extension

=# CREATE EXTENSION postgis ;

Check Installation

=# SELECT * FROM pg_extension ;

=# SELECT postgis_version();