Lab 5.8 - Install Wiki.js on Ubuntu 24.04 LTS with PostgreSQL

Objectives

By the end of this lab, you will be able to:

• Install Git and Node.js on Ubuntu 24.04
• Install and configure PostgreSQL
• Download and configure Wiki.js with PostgreSQL
• Create a systemd service for Wiki.js
• Manage Node.js and systemd services
• Explore PostgreSQL via the terminal
• Troubleshoot common issues

---

Prerequisites

• Ubuntu 24.04 LTS installed
• Sudo access
• Internet connection
• At least 2 GB of available RAM
• At least 5 GB of free disk space

---

Part 1: Installing Git

sudo apt update
sudo apt install git -y
git --version

---

Part 2: Installing Node.js (LTS)

curl -fsSL https://deb.nodesource.com/setup_lts.x | sudo -E bash -
sudo apt install -y nodejs
node -v && npm -v

---

Part 3: Installing and Configuring PostgreSQL

Installing PostgreSQL

sudo apt install postgresql postgresql-contrib -y
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

Creating the Database and User

sudo -u postgres psql

In the PostgreSQL prompt:

CREATE DATABASE wikidb;
CREATE USER wikidb_user WITH PASSWORD 'StrongPassword123!';
GRANT ALL PRIVILEGES ON DATABASE wikidb TO wikidb_user;
\q

Note: Replace StrongPassword123! with a secure password of your choice.

---

Part 4: Creating a System User for Wiki.js

# Create a system user
sudo adduser --system --group wikijs

# Create the installation directory
sudo mkdir -p /var/www/wikijs

# Verify creation
ls -l /var/www

# Set ownership
sudo chown -R wikijs:wikijs /var/www/wikijs

# Verify permissions
ls -l /var/www

# Allow login with bash
sudo usermod -s /bin/bash wikijs

---

Part 5: Installing Wiki.js

Add wikijs to sudoers and set a password

# Set a password for wikijs
sudo passwd wikijs
# Enter the password (e.g., wikijs)

# Add wikijs to the sudo group
sudo usermod -aG sudo wikijs

# Verify
groups wikijs
# Should display: wikijs : wikijs sudo

Download and install Wiki.js

# Log in as wikijs
sudo su - wikijs

# Go to the installation directory
cd /var/www/wikijs

# Download Wiki.js
wget https://github.com/Requarks/wiki/releases/latest/download/wiki-js.tar.gz

# Extract the archive
tar xzf wiki-js.tar.gz

# Remove the archive
rm wiki-js.tar.gz

# Copy the sample configuration file
cp config.sample.yml config.yml

# Exit the wikijs session
exit

---

Part 6: Configuring Wiki.js for PostgreSQL

Edit the configuration file

sudo nano /var/www/wikijs/config.yml

Replace the db: section with:

port: 3000
bindIP: 0.0.0.0
db:
  type: postgres
  host: localhost
  port: 5432
  user: wikidb_user
  pass: StrongPassword123!
  db: wikidb
  ssl: false

Important: Replace StrongPassword123! with the password you set for wikidb_user.

Comment out unused sections

Ensure the following sections are commented out or removed if unused:

#  schema: public      # COMMENT if not used
#  storage: path/to/database.sqlite  # COMMENT if not used
#  sslOptions:         # COMMENT if unused
#    auto: true
#    rejectUnauthorized: false

Configuration for external access

To make Wiki.js accessible from outside:

bindIP: 0.0.0.0
port: 3000

Save and exit with Ctrl + X, then Y, then Enter.

---

Part 7: Creating the systemd Service

Add to sudoers and set a password for wikijs

# If not already done
sudo passwd wikijs
sudo usermod -aG sudo wikijs

Create the service

sudo nano /etc/systemd/system/wikijs.service

File content:

[Unit]
Description=Wiki.js
After=network.target

[Service]
Type=simple
ExecStart=/usr/bin/node server
Restart=always
User=wikijs
Environment=NODE_ENV=production
WorkingDirectory=/var/www/wikijs

[Install]
WantedBy=multi-user.target

Save and exit.

---

Part 8: Starting and Verifying

# Reload systemd configuration
sudo systemctl daemon-reload

# Start the service
sudo systemctl start wikijs

# Enable the service at startup
sudo systemctl enable wikijs

# Check the status
sudo systemctl status wikijs

---

Part 9: Common Troubleshooting

If you see errors like:

• bad indentation of a mapping entry
• Unable to read configuration file!

Recheck that you have commented out or removed the following blocks in config.yml:

#  sslOptions:
#    auto: true

#  schema: public

#  storage: path/to/database.sqlite

Then restart:

sudo systemctl restart wikijs

Check the logs

# View service logs
sudo journalctl -u wikijs.service --no-pager --lines=50

# Follow logs in real-time
sudo journalctl -u wikijs.service -f

---

Part 10: Accessing the Wiki.js Interface

In your browser: http://<your_server_IP>:3000

Follow the instructions to create an admin account and complete the setup.

---

Part 11: Switching to HTTPS (Optional)

# Install Certbot and the Nginx plugin
sudo apt update
sudo apt install certbot python3-certbot-nginx -y

# Allow Nginx (HTTP and HTTPS)
sudo ufw allow 'Nginx Full'

# Obtain an SSL certificate
sudo certbot --nginx

# Test automatic renewal
sudo certbot renew --dry-run

---

Appendix 1: Exploring PostgreSQL

1. Connecting to PostgreSQL

# Connect as superuser
sudo -u postgres psql

# Or connect directly to a specific database with a user
psql -U wikidb_user -d wikidb -h localhost

2. Navigation Commands in psql

Command	Description
\l	List all databases
\c dbname	Connect to a database
\dt	List tables
\d tablename	Show table structure
\du	List users
\dn	List schemas
\df	List functions
\x	Toggle extended display mode
\q	Quit psql

3. Useful Query Examples

Tables, columns, and indexes:

-- List all tables in a schema
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- View columns of a table
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'users';

-- View indexes
SELECT * FROM pg_indexes WHERE tablename = 'users';

Spaces and sizes:

-- Database size
SELECT pg_size_pretty(pg_database_size('wikidb'));

-- Table size
SELECT pg_size_pretty(pg_total_relation_size('users'));

Activity and sessions:

-- View active connections
SELECT * FROM pg_stat_activity;

-- View current locks
SELECT * FROM pg_locks;

Current schema:

-- View the default schema of the connected user
SHOW search_path;

4. Export and Dump

# Full database dump (binary format)
pg_dump -U wikidb_user -d wikidb -F c -f wikidb.dump

# Export in readable SQL
pg_dump -U wikidb_user -d wikidb -F p -f wikidb.sql

5. Cleaning and Verification

-- Find empty tables
SELECT schemaname, tablename
FROM pg_tables
WHERE schemaname = 'public'
AND NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = tablename);

-- Verify constraints
SELECT * FROM information_schema.table_constraints WHERE table_schema = 'public';

---

Appendix 2: Exercise - Mastering PostgreSQL Basics

Objective

Create a complete database named coursdb, with a dedicated user, two tables (etudiants, programmes), relationships, insertions, SQL queries, and rights management.

Step 1 – Create the Database and User

As PostgreSQL superuser:

sudo -u postgres psql

In psql:

-- Create a database
CREATE DATABASE coursdb;

-- Create a user
CREATE USER cours_user WITH PASSWORD 'cours123';

-- Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE coursdb TO cours_user;

-- Quit
\q

Step 2 – Connect to the Database

psql -U cours_user -d coursdb -h localhost

Step 3 – Create the Tables

-- Create the programmes table
CREATE TABLE programmes (
    id SERIAL PRIMARY KEY,
    nom TEXT NOT NULL,
    niveau TEXT NOT NULL
);

-- Create the etudiants table with a relation to programmes
CREATE TABLE etudiants (
    id SERIAL PRIMARY KEY,
    nom TEXT NOT NULL,
    age INTEGER CHECK (age > 0),
    programme_id INTEGER REFERENCES programmes(id) ON DELETE CASCADE
);

Step 4 – Insert Data

-- Insert programmes
INSERT INTO programmes (nom, niveau) VALUES
('Informatique', 'DEC'),
('Administration', 'AEC');

-- Insert students
INSERT INTO etudiants (nom, age, programme_id) VALUES
('Alice', 21, 1),
('Bob', 24, 1),
('Caroline', 20, 2);

Step 5 – Perform Queries

-- All students
SELECT * FROM etudiants;

-- Student names with their programme
SELECT e.nom AS etudiant, p.nom AS programme
FROM etudiants e
JOIN programmes p ON e.programme_id = p.id;

-- Count students by programme
SELECT p.nom, COUNT(e.id) AS total
FROM programmes p
LEFT JOIN etudiants e ON p.id = e.programme_id
GROUP BY p.nom;

Step 6 – Modify or Delete

-- Modify a student
UPDATE etudiants SET age = 22 WHERE nom = 'Alice';

-- Delete a student
DELETE FROM etudiants WHERE nom = 'Bob';

-- Delete a programme (triggers cascade deletion)
DELETE FROM programmes WHERE nom = 'Administration';

Step 7 – User Management

-- Connected as postgres
sudo -u postgres psql

-- Create a read-only user
CREATE USER lecteur WITH PASSWORD 'readonly123';

-- Connect to the database
\c coursdb

-- Grant read-only access
GRANT CONNECT ON DATABASE coursdb TO lecteur;
GRANT USAGE ON SCHEMA public TO lecteur;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lecteur;

-- Apply automatically to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO lecteur;

\q

Bonus – Verification

-- View users
\du

-- List tables and view their rights
\dt+

---

Appendix 3: Managing Services and Ports

Part 1: View All Active Ports

To view all active ports (open, listened, or being listened to by services, including Node.js) on Ubuntu 24.04, use several commands.

1. With ss (modern replacement for netstat)

sudo ss -tuln

Explanation:

• -t: TCP
• -u: UDP
• -l: listening ports
• -n: no DNS resolution (faster)

2. With lsof to see which process uses which port

sudo lsof -i -P -n

Filter by Node.js only:

sudo lsof -i -P -n | grep node

3. With netstat (if installed)

sudo netstat -tulnp

Otherwise, install it:

sudo apt install net-tools

4. View All Current Node.js Processes

ps aux | grep node

5. View Services Listening on a Specific Port

sudo lsof -i :3000
sudo lsof -i :8080

6. Display Ports with nmap (local scan)

sudo apt install nmap
sudo nmap -sT -O localhost

---

Part 2: Stop a Node.js Service Installed with systemd

Step 1 – Find the Service Name

If you're unsure of the exact service name, list active services related to Node.js:

systemctl | grep node

Or list all active services:

systemctl list-units --type=service

You can also search in the systemd folder:

ls /etc/systemd/system/

Step 2 – Stop the Service

Once the name is found (e.g., wikijs.service or my-node-app.service):

sudo systemctl stop service-name

Example:

sudo systemctl stop wikijs.service

Step 3 – (Optional) Disable the Service at Startup

sudo systemctl disable service-name

Step 4 – (Optional) Completely Remove the Service

sudo rm /etc/systemd/system/service-name.service
sudo systemctl daemon-reload

---

Part 3: Stop a Node.js Process on a Specific Port

Example: Your Node.js application runs on port 3100, and it is launched by the user www-data via a node process.

node      1064738   www-data   18u  TCP *:3100 (LISTEN)

Step 1 — Find and Stop the systemd Service

If it is launched as a service, then its service name might resemble wikijs, nodeapp, app3100, etc.

Try:

systemctl | grep 3100

or more generally:

systemctl | grep node

Step 2 — Stop the Service (if known)

Once you have found the service name (e.g., wikijs.service), do:

sudo systemctl stop wikijs.service

Step 3 — Optionally: Manually Kill the Process

If the service does not stop or if it is not a true systemd service (e.g., launched manually by a script), you can kill the process directly:

sudo kill 1064738

or more safely:

sudo kill -9 1064738

Verification

After stopping:

sudo ss -tuln | grep 3100

or

sudo lsof -i :3100

Nothing should appear.

---

Part 4: Service that Automatically Restarts

If your Node.js application still listens on port 3100, and it is relaunched even after you manually killed it, this indicates it is managed by a service or supervisor that automatically restarts it.

Steps to Identify and Stop It Permanently

1. Find the Real Service Name

Try this command to find all services containing "node" or 3100:

sudo systemctl list-units --type=service | grep -i node

Or more broadly:

ps aux | grep node

Then:

ps -fp <PID>

This will tell us which script or file is executed and by what it was launched.

2. Check if pm2 is Used

which pm2 && pm2 list

If pm2 is installed, it automatically restarts your app. To stop it permanently:

pm2 stop all
pm2 delete all
pm2 unstartup

3. See the Exact Origin of the Process

sudo ls -l /proc/<PID>/exe
sudo cat /proc/<PID>/cmdline | tr '\0' ' '

This will display the real path of the Node.js script, for example:

/usr/bin/node /var/www/wiki/server.js

4. Find the Responsible .service Files

List files in /etc/systemd/system/ and /lib/systemd/system/:

ls /etc/systemd/system/ | grep -i wiki
ls /lib/systemd/system/ | grep -i wiki

Or more broadly:

grep -r 3100 /etc/systemd/system/

Final Step: Disable and Completely Stop

Once you have the real service name, do:

sudo systemctl stop name.service
sudo systemctl disable name.service

Then reload:

sudo systemctl daemon-reload
sudo systemctl reset-failed

---

Part 5: Complete Removal of a systemd Service

To permanently remove a Node.js service (or any other systemd service), proceed in three steps:

Step 1 — Find the Exact Service Name

First, list all custom systemd services:

ls /etc/systemd/system/

Look for a file that might resemble:

• wikijs.service
• nodeapp.service
• app3100.service
• etc.

You can also filter:

ls /etc/systemd/system/ | grep -i wiki

Step 2 — Stop and Disable the Service

Suppose the file name is wikijs.service, execute:

sudo systemctl stop wikijs.service
sudo systemctl disable wikijs.service

Step 3 — Remove the Service Definition File

sudo rm /etc/systemd/system/wikijs.service

Then reload systemd to apply the changes:

sudo systemctl daemon-reload
sudo systemctl reset-failed

(Optional) Verify It No Longer Exists

systemctl list-units --type=service | grep wiki

or

systemctl status wikijs.service

You should get a message like:

Unit wikijs.service could not be found.

Useful Commands

# List all custom services
ls -l /etc/systemd/system/

# Search for a specific service
grep -r ExecStart /etc/systemd/system/

# Analyze the exact process based on PID
ps -fp <PID>
sudo cat /proc/<PID>/cmdline | tr '\0' ' '

---

Appendix 4: What is pm2?

In the context of Node.js, pm2 refers to Process Manager 2, a widely used tool for:

• Running Node.js applications in the background
• Keeping them alive even after a crash
• Automatically restarting them at system startup
• Easily managing multiple Node.js processes

Why pm2 Might Be the Cause

Even if you stop a Node.js service manually (kill, systemctl, etc.), pm2 can automatically:

• Restart the application
• Reset processes at machine startup (if pm2 startup was executed)

That's why it's important to check if pm2 is active.

Commands to Know to Completely Stop pm2

1. Check if pm2 is installed:

which pm2 && pm2 list

2. Stop all processes managed by pm2:

pm2 stop all

3. Delete registered processes:

pm2 delete all

4. Disable automatic restart at startup:

pm2 unstartup

5. Optional: Uninstall pm2 if no longer needed:

npm uninstall -g pm2

---

Summary of Important Commands

Command	Description
sudo ss -tuln	View all listening ports
sudo lsof -i -P -n	View processes using ports
sudo systemctl status wikijs	Check the status of a service
sudo systemctl stop wikijs	Stop a service
sudo systemctl disable wikijs	Disable a service at startup
sudo journalctl -u wikijs.service -f	Follow logs in real-time
ps aux | grep node	View all Node.js processes
pm2 list	View processes managed by pm2

---

Final Verification

Ensure you have successfully:

• Installed Git and Node.js
• Installed and configured PostgreSQL
• Created the database and user for Wiki.js
• Downloaded and extracted Wiki.js
• Configured the config.yml file for PostgreSQL
• Created and enabled the systemd service
• Accessed Wiki.js via the browser
• Explored PostgreSQL with psql commands
• Understood systemd service management
• Learned to stop and remove a service

---

Reflection Questions

1. What is the difference between PostgreSQL and MariaDB?

   • In what cases would you use one over the other?

2. Why is it important to comment out unused sections in config.yml?

   • Explain YAML errors and how to avoid them

3. What is the difference between systemctl stop and systemctl disable?

   • When would you use each of these commands?

4. What is pm2 and why can it cause issues when stopping a service?

   • How to check if pm2 is used?

5. Explain the difference between bindIP: 127.0.0.1 and bindIP: 0.0.0.0

   • In what cases would you use each of these configurations?

---

Next Steps

• Lab 5.7: Wiki.js Installation with MariaDB
• Module 8: Services and Daemons (to deepen systemd)
• TP 2: NGINX Reverse Proxy (to expose Wiki.js via a reverse proxy)

---

Additional Resources

• Wiki.js Documentation
• PostgreSQL Documentation
• Node.js Documentation
• systemd Documentation
• PM2 Documentation

---

Lab created on: December 2024