Installing PostgreSQL on a VPS

On a VPS running Ubuntu 22.04 LTS, as root, you install PostgreSQL with apt install postgresql postgresql-contrib. The service starts by itself and, by default, listens only on the server itself, which is what you want. This article is for people with a VPS or a dedicated server. On shared hosting PostgreSQL is already installed; see PostgreSQL in cPanel.

On an unmanaged VPS, installation and maintenance are yours. That covers updates, backups and the security of PostgreSQL. See managed or unmanaged VPS and the Support Policy.

Step by step

1 Log in over SSH as root. If it is your first time, see first steps with your VPS.
2 Refresh the package list and install:apt update
apt install postgresql postgresql-contrib
3 Check that the service is active: systemctl status postgresql. To have it start with the server, systemctl enable postgresql.
4 Check the installed version: psql --version. It is the version Ubuntu 22.04 ships by default.
5 Enter PostgreSQL as the system user postgres: sudo -u postgres psql. The postgres=# prompt appears. Leave with \q.
6 Go on to the next step: creating a user and a database. See creating a user and a database.

What gets installed, and where

What Where
Main configuration /etc/postgresql/<version>/main/postgresql.conf
Who may connect, and how /etc/postgresql/<version>/main/pg_hba.conf
The service systemctl status postgresql, systemctl restart postgresql
The port 5432, on 127.0.0.1 only by default
Do not open port 5432 to the internet just because the install went well. An exposed database is found by bots within hours. To connect from outside, use an SSH tunnel or follow remote connections, safely.

The first adjustments after installing

Check it only listens locally: ss -ltn | grep 5432 should show 127.0.0.1:5432. The data lives in /var/lib/postgresql, so watch the disk space there. Keep the system up to date with apt update and apt upgrade: PostgreSQL security fixes arrive that way. Moving to a major version of PostgreSQL is a different operation, not something that happens by accident in an apt upgrade, and it needs a backup and a migration. Finally, make sure the VPS firewall lets through only what you use (SSH and, if you host a site, the web). See keeping your VPS secure. And note down somewhere of your own the version installed and the date: a year from now, when you need to update, you will be glad not to have to guess.

Set up your backup routine now, before there is data you would hate to lose. See backing up with pg_dump and backing up a VPS.

Need to pick the plan or the operating system before installing? Have a look at the VPS servers and tell us what you will run.

See the VPS servers

SEE ALSO

First steps with your VPS

Which operating system to choose for your VPS or server

VPS servers

RECOMMENDED PRODUCT

VPS server with root access

Resources of your own, the OS you choose, reinstall whenever you like. from 456,30 MT/mo (3-year plan, with coupon)

See plans
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Creating databases and using phpMyAdmin Almost every site that isn't just static pages needs a database. In cPanel you create one in about a minute - and there...
What time the server is on, and why PHP and MySQL can disagree A form that records the wrong time, an order dated yesterday, a scheduled job firing two hours early - it is nearly alwa...
Backing up the database of a Drupal site Drupal files are the skeleton; the database is everything else. Pages, users, menus, permissions, site configuration and...
Connecting to your MySQL database with MySQL Workbench MySQL Workbench is a program you install on your own computer to work on a database the way you would work in a spreadsh...
When the site says it cannot connect to the database It is one of the more frightening messages a site can show, because there is no page at all behind it: just a line of wh...
Creating a PostgreSQL user and database, and connecting with psql On a VPS with PostgreSQL already installed, enter as the system user postgres with sudo -u postgres psql, then create th...