Installing PostgreSQL on VPS (Ubuntu 20.04 LTS)

Notes for how to install a specific version of PostgreSQL.

Installation

ref: https://www.postgresql.org/download/linux/ubuntu/

• To use the PostgreSQL Apt Repository

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

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

• Update package index:
  sudo apt update

• Install postgres 13:
  sudo apt install postgresql-13 postgresql-client-13

For installing other versions, simply changing the 13 to other version numbers like 12.

• Check status:
  service postgresql status or systemctl status postgresql.service

• Switch to superadmin of PostgreSQL:
  sudo su - postgres

• Set a password for postgres superadmin:
  psql -c "alter user postgres with password 'some_password_here'"

Basic things finished~

Configuration

• Enable remote access

Open one config file:
sudo nano /etc/postgresql/13/main/postgresql.conf

# Listen on all interfaces
listen_addresses = '*'

or

# Listen on specified IP address - for example, another PC in the same private network
listen_addresses = '192.168.1.123'

Open another config file:
sudo nano /etc/postgresql/13/main/pg_hba.conf

# accept connections from everywhere, using password authentication
# auth_method can also be md5, but scram-sha-256's more secure

# connection_type    database    user    address    auth_method
  host               all         all     0.0.0.0/0  scram-sha-256

or

# accept connections as 'postgres' from a trusted subnet, without password
# connection_type    database    user       address         auth_method
  host               all         postgres   192.168.1.0/24  trust

Remember to set your firewall to allow your PostgreSQL's port.

Finally, restart PostgreSQL.