> For the complete documentation index, see [llms.txt](https://docs.ititanhosting.ro/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.ititanhosting.ro/docs/english/vps/linux/how-to-install-postgresql-on-ubuntu.md).

# How to install PostgreSQL on Ubuntu

PostgreSQL is a powerful open-source relational database. It is known for reliability, advanced features, and good performance. If you need a database that handles complex queries and large datasets well, PostgreSQL is a solid choice.

### Step 1: Update and install

```bash
sudo apt update
sudo apt install postgresql postgresql-contrib -y
```

The `postgresql-contrib` package adds extra utilities and extensions.

### Step 2: Check that it is running

```bash
sudo systemctl status postgresql
```

You should see "active (exited)" or "active (running)". PostgreSQL starts automatically after installation.

### Step 3: Understand the default user

PostgreSQL creates a system user called `postgres` during installation. To work with the database, you switch to this user:

```bash
sudo -i -u postgres
```

Now you can access the PostgreSQL prompt:

```bash
psql
```

You are now in the PostgreSQL interactive shell. Type `\q` to exit, or `\?` to see all commands.

### Step 4: Create a database and user

While still logged in as the `postgres` user, create a dedicated user and database for your application:

```bash
# From the psql prompt
CREATE USER myapp_user WITH PASSWORD 'strongpassword';

CREATE DATABASE myapp_db OWNER myapp_user;

GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

\q
```

Then exit the postgres system user:

```bash
exit
```

### Step 5: Configure remote access (optional)

By default, PostgreSQL only listens on localhost. If you need to connect from another machine, edit the config file:

```bash
sudo nano /etc/postgresql/16/main/postgresql.conf
```

Find the line that says `#listen_addresses = 'localhost'` and change it to:

```
listen_addresses = '*'
```

Then update the client authentication file:

```bash
sudo nano /etc/postgresql/16/main/pg_hba.conf
```

Add this line at the bottom to allow password-based connections from any IP:

```
host    myapp_db    myapp_user    0.0.0.0/0    md5
```

For better security, replace `0.0.0.0/0` with your specific IP.

### Step 6: Restart PostgreSQL

```bash
sudo systemctl restart postgresql
```

If you use UFW, allow the PostgreSQL port:

```bash
sudo ufw allow 5432/tcp
```

### Step 7: Test the connection

From your local machine, test connecting to the remote database:

```bash
psql -h your-server-ip -U myapp_user -d myapp_db
```

Enter the password when prompted, and you should be in.

### Useful psql commands

| Command        | What it does                    |
| -------------- | ------------------------------- |
| `\l`           | List all databases              |
| `\c dbname`    | Connect to a database           |
| `\dt`          | Show tables in current database |
| `\du`          | List users/roles                |
| `\d tablename` | Describe a table                |
| `\q`           | Exit psql                       |
| `\?`           | List all commands               |

### Backup and restore

Backup a database:

```bash
pg_dump myapp_db > myapp_db_backup.sql
```

Restore a backup:

```bash
psql myapp_db < myapp_db_backup.sql
```

For PostgreSQL 16, the version number in paths may differ. Check your installed version with `psql --version` and adjust paths accordingly.

### Common issues

**"Connection refused"** Check that PostgreSQL is running and listening on the right address. Verify the `listen_addresses` setting in postgresql.conf.

**"Peer authentication failed"** This happens when trying to connect as a different Linux user. Use `sudo -u postgres psql` or switch to the postgres system user.

**"FATAL: password authentication failed"** Reset the password:

```bash
sudo -u postgres psql -c "ALTER USER myapp_user WITH PASSWORD 'newpassword';"
```

### Tuning for small VPS

If your VPS has limited memory, add these to `postgresql.conf`:

```
shared_buffers = 256MB
effective_cache_size = 512MB
work_mem = 16MB
maintenance_work_mem = 64MB
```

Adjust the values based on your available RAM. A good rule is to set `shared_buffers` to about 25% of total RAM.

### Conclusion

PostgreSQL is now installed and ready. Whether you are running a web application, a game server, or an analytics tool, it will handle your data reliably.