DEV Community

Cover image for PostgreSQL Fast deploy and Easy Remote Admin
cGIfl300
cGIfl300

Posted on

PostgreSQL Fast deploy and Easy Remote Admin

Image description

PostgreSQL managed servers are expansive, why not just using your own VPS to add one?

This is a really short memo, the purpose is to have a PostgreSQL server running on a single computer as fast as possible to start dev things.

PostgreSQL

Installation

To allow connexions from any host on the internet

Edit the postgresql.conf file

sudo pico /etc/postgresql/13/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode
# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
Enter fullscreen mode Exit fullscreen mode

Add users authentification

sudo pico /etc/postgresql/13/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode
host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5
Enter fullscreen mode Exit fullscreen mode

Create your first remotely allowed superuser

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode
postgres=# CREATE USER username WITH PASSWORD 'password' CREATEDB SUPERUSER CREATEROLE INHERIT LOGIN REPLICATION BYPASSRLS;
CREATE ROLE
postgres=# \q
Enter fullscreen mode Exit fullscreen mode
sudo service postgresql restart
Enter fullscreen mode Exit fullscreen mode

Admin

The best way to admin the database will be to remotely use pgAdmin you
could add users, change passwords and add databases on demand.

Enought reading here, start reading on My Blog

Top comments (0)