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();

upgrade postgresql version (15)

sudo systemctl stop postgresql

sudo apt-get --purge remove postgresql postgresql-*

sudo apt-get autoremove

sudo apt-get autoclean

sudo rm -rf /etc/postgresql/

sudo rm -rf /var/lib/postgresql/

sudo rm -rf /var/log/postgresql/

# Add PostgreSQL APT repository (if not added before)

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

===================

# Update and install PostgreSQL 15

sudo apt-get update

sudo apt-get install postgresql-15

=====================

psql --version

sudo systemctl start postgresql

sudo systemctl enable postgresql

sudo -u postgres psql