Installing MySQL or PostgreSQL

What is it? #

A database on the same VPS as the application is a perfectly reasonable setup for small and medium systems, and it removes network latency between them.

Three things must be right: the database must not be reachable from the internet, the application must connect as a limited user rather than the superuser, and the default memory settings need adjusting.

Default configurations are conservative because they must work on any machine. On a server with 4 GB of memory, the defaults leave most of it unused.

Whichever database you choose, the setup steps are the same shape.

Think of it like this #

A strongroom inside the building rather than across town. Faster to reach, and the door still needs a proper lock and a short list of who may open it.

Simple example #

PostgreSQL on the application server, listening only on localhost, with a dedicated application user owning one database, plus memory settings appropriate for the machine.

Code #

# PostgreSQL
sudo apt install postgresql postgresql-contrib
sudo systemctl status postgresql

# Create a database and a limited application user
sudo -u postgres psql <<'SQL'
CREATE USER appuser WITH PASSWORD 'generate-a-long-random-password';
CREATE DATABASE shop OWNER appuser;
REVOKE ALL ON DATABASE shop FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO appuser;
SQL

# Connect as the application user to verify
psql "postgresql://appuser:PASSWORD@localhost/shop" -c "SELECT version();"

# /etc/postgresql/16/main/postgresql.conf — localhost only
listen_addresses = 'localhost'        # never '*' on a public server

# Memory settings for a 4 GB machine running app + database
shared_buffers = 1GB                  # ~25% of RAM
effective_cache_size = 3GB            # ~75%: a hint to the planner
work_mem = 16MB                       # per sort/hash operation, per connection
maintenance_work_mem = 256MB
max_connections = 100                 # use a pooler rather than raising this

# Logging that is actually useful
log_min_duration_statement = 500      # log queries slower than 500ms
log_checkpoints = on
log_connections = off

# /etc/postgresql/16/main/pg_hba.conf — who may connect, how
# TYPE  DATABASE  USER     ADDRESS        METHOD
local   all       postgres                peer
host    shop      appuser  127.0.0.1/32   scram-sha-256
host    shop      appuser  10.0.1.0/24    scram-sha-256   # private network only
# Do NOT add: host all all 0.0.0.0/0 md5

# MySQL / MariaDB equivalent
sudo apt install mysql-server
sudo mysql_secure_installation       # removes anonymous users and test database

sudo mysql <<'SQL'
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
SQL
# Note: no DROP, no GRANT, no access to other databases.

# Verify it is not publicly reachable
sudo ss -tulpn | grep -E "5432|3306"
# Must show 127.0.0.1 or a private address, never 0.0.0.0

How it works #

listen_addresses = 'localhost' is the strongest control available. A database that does not listen on a public interface cannot be attacked remotely regardless of passwords or firewall rules.

The application user owns one database and has no rights over others. Granting only the statements the application needs limits the damage of an SQL injection vulnerability — an injected DROP TABLE fails if the user cannot drop tables.

pg_hba.conf controls who may connect from where and with which authentication method. The ordering matters: the first matching line wins, and a broad rule near the top overrides the specific ones below it.

shared_buffers is memory PostgreSQL uses for caching data pages. effective_cache_size does not allocate anything; it tells the query planner how much memory it can assume is available for caching, which influences whether it chooses an index scan.

work_mem is per operation, not global. A complex query with several sorts, multiplied by many connections, can use far more than expected — which is why it is set modestly and connection counts are kept low.

log_min_duration_statement logs slow queries, which is the single most useful database logging setting for finding performance problems.

mysql_secure_installation removes the anonymous user and test database that older MySQL installations created, and should always be run.

Real-world use #

Exposed databases with weak credentials remain one of the most common breach causes. Binding to localhost and using a firewall means it simply is not reachable.

Separating the database onto its own server becomes worthwhile when either component needs to scale independently, or when you want the database to survive an application server rebuild.

Managed databases remove most of this work — patching, backups, failover — at a higher price. For teams without operations capacity that is often the right trade.

Connection pooling matters sooner than expected. PostgreSQL connections are relatively expensive, and PgBouncer in front of it is standard once an application runs several worker processes.

Backups are covered separately in this track, and are the one piece that must not be skipped: a well-tuned, well-secured database with no tested restore is still a single disk failure from disaster.

Common mistakes #

• Binding the database to 0.0.0.0 on a public server.
• Using the superuser as the application database user.
• Leaving default memory settings, using a fraction of the available RAM.
• Adding a broad pg_hba rule that overrides the specific ones.
• Raising max_connections instead of introducing a connection pooler.

Practice #

Install a database, create an application user with only the privileges your application needs, and confirm it is listening only on localhost. Tune the memory settings for the machine size, enable slow query logging, and verify from another machine that the port is unreachable.