PostgreSQL Setup Guide for Worldgate Server Connector

This guide explains how to set up PostgreSQL for the Worldgate Server Connector on your private network.

Quick Setup Summary

Common Setup: PostgreSQL on one VM, Luanti servers on different VMs

On PostgreSQL VM: Install PostgreSQL server
On PostgreSQL VM: Configure it to accept remote connections (edit postgresql.conf and pg_hba.conf)
On PostgreSQL VM: Run the database_schema.sql file (creates tables + user)
On PostgreSQL VM: Configure firewall to allow port 5432 from Luanti servers
On Luanti servers: Add database connection info to world.mt (using PostgreSQL VM's IP)
Test: Connect from Luanti server to verify: psql -h <postgres-ip> -U worldgate -d worldgate

Important: Luanti servers need the Lua PostgreSQL library installed to connect to the database.

Why PostgreSQL?

Self-hosted: You control the database on your own infrastructure
Private: Database stays on your private network
Native Support: Minetest/Luanti has built-in PostgreSQL support
No Extra Mods: Unlike MySQL/MariaDB, no additional mods required
Performance: Excellent performance for multi-server setups
Reliability: Battle-tested, ACID-compliant database

Architecture Overview

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│  Server A   │     │  Server B   │     │  Server C   │
│  (Alpha)    │     │  (Beta)     │     │  (Gamma)    │
└──────┬──────┘     └──────┬──────┘     └──────┬──────┘
       │                   │                   │
       │   PostgreSQL Connection (5432)        │
       └───────────────────┼───────────────────┘
                           │
                    ┌──────▼──────┐
                    │ PostgreSQL  │
                    │   Database  │
                    │  (Private   │
                    │   Network)  │
                    └─────────────┘

Installation Steps

1. Install PostgreSQL Server

Ubuntu/Debian:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

CentOS/RHEL:

sudo yum install postgresql-server postgresql-contrib
sudo postgresql-setup initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql

Fedora:

sudo dnf install postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl start postgresql
sudo systemctl enable postgresql

2. Configure PostgreSQL Authentication

Edit the PostgreSQL configuration to allow network connections from your game servers.

sudo nano /etc/postgresql/*/main/pg_hba.conf

Add these lines (adjust IP addresses for your network):

# Allow worldgate user from local network
host    worldgate    worldgate    192.168.1.0/24    md5

Edit the main configuration:

sudo nano /etc/postgresql/*/main/postgresql.conf

Change the listen address:

listen_addresses = '*'  # or specify your private network interface

Restart PostgreSQL:

sudo systemctl restart postgresql

3. Install Lua PostgreSQL Library on Luanti Servers

CRITICAL: Each Luanti server needs the Lua PostgreSQL library to connect to the database.

Ubuntu/Debian:

sudo apt-get install lua-pgsql

Alternative: Install via LuaRocks:

# Install LuaRocks if not installed
sudo apt-get install luarocks

# Install luapgsql
sudo luarocks install luapgsql

Verify Installation:

lua -e "require('pgsql'); print('PostgreSQL library loaded successfully')"

If you see "PostgreSQL library loaded successfully", you're good to go!

Note: The library must be installed on EVERY Luanti server that needs to connect to the database, not just the PostgreSQL server.

4. Create the Worldgate Database

Switch to the postgres user and create the database:

sudo -u postgres psql

In the PostgreSQL prompt:

CREATE DATABASE worldgate;
\q

5. Run the Schema Script

You have several options depending on your setup:

Option A: Run Directly on PostgreSQL Server (Recommended)

Best for: Separate PostgreSQL VM, or when you don't have psql on Luanti servers

Transfer the schema file to your PostgreSQL server:

# From your Luanti server (where the worldgate mod is)
scp /path/to/mods/worldgate/database_schema.sql user@192.168.1.100:/tmp/

# SSH to PostgreSQL server
ssh user@192.168.1.100

# Run the schema (this creates tables AND the worldgate user)
sudo -u postgres psql -d worldgate < /tmp/database_schema.sql

Important: Edit the schema file first to change the default password on line 86:

CREATE USER worldgate WITH PASSWORD 'change_this_password';

Option B: Run Remotely from Luanti Server

Best for: When you have PostgreSQL client installed on Luanti server

First, install PostgreSQL client tools on your Luanti server (just the client, not the server):

# Ubuntu/Debian
sudo apt install postgresql-client

# Fedora/RHEL
sudo dnf install postgresql

# Arch
sudo pacman -S postgresql-libs

Then run the schema remotely:

cd /path/to/mods/worldgate/
psql -h 192.168.1.100 -U postgres -d worldgate < database_schema.sql

Replace 192.168.1.100 with your PostgreSQL server's IP.

Option C: Manual Copy/Paste

Best for: When you can't transfer files or install packages

On your Luanti server, display the schema:

cat /path/to/mods/worldgate/database_schema.sql

Copy all the SQL content (it's about 90 lines)
SSH to your PostgreSQL server and connect:
```
sudo -u postgres psql worldgate
```
Paste the entire SQL content into the PostgreSQL prompt
Press Enter to execute

What the Schema Creates

The database_schema.sql script does 3 things:

✅ Creates the three tables (servers, worldgates, transfer_logs) with indexes
✅ Creates automatic timestamp update triggers
✅ Creates the worldgate database user with proper permissions

CRITICAL: The script includes a default password that you MUST change! See step 5 below.

5. Verify the Setup

Check that the tables were created:

sudo -u postgres psql worldgate -c "\dt"

You should see:

servers
worldgates
transfer_logs

6. Test the Connection

From each game server, test the PostgreSQL connection:

psql -h 192.168.1.100 -U worldgate -d worldgate

Enter the password when prompted. If you can connect, you're all set!

7. Configure Firewall

Allow connections from your game servers only:

UFW (Ubuntu):

sudo ufw allow from 192.168.1.10 to any port 5432
sudo ufw allow from 192.168.1.11 to any port 5432

# Or allow entire private subnet
sudo ufw allow from 192.168.1.0/24 to any port 5432

firewalld (CentOS/RHEL):

sudo firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="5432" accept'
sudo firewall-cmd --reload

Minetest Configuration

Option 1: Using Native PostgreSQL Backend (Recommended)

Configure each Minetest server to use PostgreSQL natively.

Edit world.mt in your world directory:

# Use PostgreSQL as the world database backend
backend = postgresql
pgsql_connection = host=192.168.1.100 port=5432 user=worldgate password=your_password dbname=worldgate

# Server identification (unique per server)
servergate.server_name = My Server Alpha
servergate.server_url = minetest://alpha.example.com:30000

# Database configuration (for mod queries)
servergate.db_host = 192.168.1.100
servergate.db_port = 5432
servergate.db_name = worldgate
servergate.db_user = worldgate
servergate.db_password = your_password

# Recommended settings
servergate.native.link = false

Option 2: Using Mod Storage (Single Server)

If you don't need multi-server support, you can use local storage:

# Just set the server identification
servergate.server_name = My Server
servergate.server_url = minetest://localhost:30000

Security Best Practices

1. Use Strong Passwords

-- Generate a secure password for the database user
-- Use a password manager or: openssl rand -base64 32
ALTER USER worldgate WITH PASSWORD 'Ax9Kp2Mn8Qr5Wt7Yv3Zb6Cd1Ef4Gh0';

2. Restrict Network Access

Edit pg_hba.conf to allow only specific servers:

# Specific servers only
host    worldgate    worldgate    192.168.1.10/32    md5
host    worldgate    worldgate    192.168.1.11/32    md5

3. Enable SSL/TLS (Recommended)

Generate SSL certificates:

sudo -u postgres openssl req -new -x509 -days 365 -nodes -text -out /var/lib/postgresql/*/main/server.crt -keyout /var/lib/postgresql/*/main/server.key
sudo chmod 600 /var/lib/postgresql/*/main/server.key
sudo chown postgres:postgres /var/lib/postgresql/*/main/server.*

Enable SSL in postgresql.conf:

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

Update pg_hba.conf to require SSL:

hostssl    worldgate    worldgate    192.168.1.0/24    md5

4. Regular Backups

Backup your database regularly:

# Daily backup
pg_dump -U worldgate worldgate > worldgate_backup_$(date +%Y%m%d).sql

# Automated with cron
0 2 * * * pg_dump -U worldgate worldgate > /backups/worldgate_$(date +\%Y\%m\%d).sql

5. Monitor Connections

Check who's connected:

SELECT datname, usename, client_addr, state FROM pg_stat_activity WHERE datname = 'worldgate';

Performance Tuning

For Small Networks (2-5 servers)

Default PostgreSQL settings are usually sufficient.

For Larger Networks (10+ servers)

Edit /etc/postgresql/*/main/postgresql.conf:

# Connection settings
max_connections = 100

# Memory settings (adjust based on available RAM)
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 16MB
maintenance_work_mem = 64MB

# Query planning
random_page_cost = 1.1  # For SSD storage
effective_io_concurrency = 200

# Logging
log_min_duration_statement = 1000  # Log slow queries (1 second)

Restart PostgreSQL after changes:

sudo systemctl restart postgresql

Troubleshooting

Can't connect from game server

This is the most common issue when PostgreSQL is on a different machine.

Step 1: Verify PostgreSQL is listening on the network

On the PostgreSQL server:

# Check if PostgreSQL is listening on all interfaces (0.0.0.0)
sudo netstat -tlnp | grep 5432

# Should show something like:
# tcp  0  0.0.0.0:5432  0.0.0.0:*  LISTEN  12345/postgres

If it only shows 127.0.0.1:5432, PostgreSQL is only listening locally. Fix this:

sudo nano /etc/postgresql/*/main/postgresql.conf
# Change: listen_addresses = 'localhost'
# To:     listen_addresses = '*'

sudo systemctl restart postgresql

Step 2: Check pg_hba.conf allows your Luanti server

On the PostgreSQL server:

sudo cat /etc/postgresql/*/main/pg_hba.conf | grep -v "^#"

You should see a line like:

host    worldgate    worldgate    192.168.1.0/24    md5

Where 192.168.1.0/24 includes your Luanti server's IP. If not, add it:

sudo nano /etc/postgresql/*/main/pg_hba.conf
# Add this line (replace with your Luanti server's IP):
host    worldgate    worldgate    192.168.1.50/32    md5

sudo systemctl restart postgresql

Step 3: Test network connectivity

From your Luanti server:

# Test if port 5432 is reachable
telnet 192.168.1.100 5432

# Or use nc (netcat)
nc -zv 192.168.1.100 5432

# Should output: Connection to 192.168.1.100 5432 port [tcp/postgresql] succeeded!

If this fails, check firewall rules (Step 4).

Step 4: Check firewall on PostgreSQL server

On the PostgreSQL server:

# Check firewall status
sudo ufw status
sudo iptables -L -n | grep 5432

# If blocked, allow your Luanti server(s):
sudo ufw allow from 192.168.1.50 to any port 5432
sudo ufw allow from 192.168.1.51 to any port 5432

# Or allow entire subnet:
sudo ufw allow from 192.168.1.0/24 to any port 5432

Step 5: Test PostgreSQL authentication

From your Luanti server:

psql -h 192.168.1.100 -U worldgate -d worldgate -c "SELECT version();"

If this works, your connection is fully configured!

Authentication failed

-- Check user exists
sudo -u postgres psql -c "\du"

-- Recreate user if needed
DROP USER IF EXISTS worldgate;
CREATE USER worldgate WITH PASSWORD 'your_password';
GRANT CONNECT ON DATABASE worldgate TO worldgate;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO worldgate;
GRANT USAGE ON SCHEMA public TO worldgate;

Permission denied errors

Common issue: When tables are created by the postgres user, other database users (like worldgate) don't automatically get access to them.

Symptoms:

ERROR: permission denied for table servers
ERROR: permission denied for table worldgates

Solution:

Option 1: Using pgAdmin (GUI)

Open pgAdmin and connect to your worldgate database
Open the Query Tool (Tools → Query Tool, or right-click database → Query Tool)

Run this SQL:

GRANT SELECT, INSERT, UPDATE, DELETE ON servers, worldgates, transfer_logs TO worldgate;

Click Execute (⚡ lightning bolt icon) or press F5

Option 2: Using psql (Command Line)

# Connect as postgres superuser
sudo -u postgres psql worldgate

# Grant permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON servers, worldgates, transfer_logs TO worldgate;

# Exit
\q

Option 3: Grant on ALL tables (if you create more tables later)

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO worldgate;
GRANT USAGE ON SCHEMA public TO worldgate;

After granting permissions, restart your Minetest/Luanti server.

Slow queries

-- Check for missing indexes
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename IN ('servers', 'worldgates', 'transfer_logs');

-- Analyze tables
ANALYZE servers;
ANALYZE worldgates;
ANALYZE transfer_logs;

Database corruption

# Check database integrity
sudo -u postgres pg_dump worldgate > /dev/null

# Vacuum and analyze
sudo -u postgres psql worldgate -c "VACUUM FULL ANALYZE;"

Maintenance

Weekly Tasks

# Vacuum and analyze
sudo -u postgres psql worldgate -c "VACUUM ANALYZE;"

# Clean old transfer logs (keep 30 days)
sudo -u postgres psql worldgate -c "DELETE FROM transfer_logs WHERE transfer_time < NOW() - INTERVAL '30 days';"

Monthly Tasks

# Full vacuum
sudo -u postgres psql worldgate -c "VACUUM FULL;"

# Full backup
pg_dump -U worldgate worldgate | gzip > worldgate_full_$(date +%Y%m%d).sql.gz

# Check database size
sudo -u postgres psql worldgate -c "SELECT pg_size_pretty(pg_database_size('worldgate'));"

Monitor Database Size

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Migration from MySQL/MariaDB

If you're migrating from MySQL/MariaDB to PostgreSQL:

1. Export Data from MySQL

mysqldump -u worldgate -p worldgate > mysql_export.sql

2. Convert Schema

The SQL syntax differences are minimal. Key changes:

DATETIME → TIMESTAMP
NOW() → CURRENT_TIMESTAMP
BOOLEAN values: 1/0 → TRUE/FALSE
Auto-increment triggers are different

3. Import to PostgreSQL

# Create the schema first
sudo -u postgres psql -d worldgate < database_schema.sql

# Then import data (may need manual conversion)
sudo -u postgres psql -d worldgate < converted_data.sql

4. Update Minetest Configuration

Update world.mt to use PostgreSQL settings as described above.

Comparison: PostgreSQL vs MySQL/MariaDB

Feature	PostgreSQL	MySQL/MariaDB
Minetest Support	Native (built-in)	Requires external mod
Setup Complexity	Moderate	Moderate + mod install
Performance	Excellent	Excellent
Standards Compliance	Excellent	Good
Advanced Features	More extensive	Less extensive
Community Support	Very strong	Very strong

Support

For PostgreSQL-specific issues:

PostgreSQL Documentation: https://www.postgresql.org/docs/
PostgreSQL Community: https://www.postgresql.org/community/

For Worldgate mod issues:

Check server logs in debug.txt
Review database connection settings
Verify PostgreSQL is running and accessible

For Minetest PostgreSQL backend:

Minetest Forum: https://forum.minetest.net/
Minetest Documentation: https://github.com/minetest/minetest/blob/master/doc/world_format.txt

FilesExpand file tree

POSTGRESQL_SETUP.md

Latest commit

History

POSTGRESQL_SETUP.md

File metadata and controls

PostgreSQL Setup Guide for Worldgate Server Connector

Quick Setup Summary

Why PostgreSQL?

Architecture Overview

Installation Steps

1. Install PostgreSQL Server

Ubuntu/Debian:

CentOS/RHEL:

Fedora:

2. Configure PostgreSQL Authentication

3. Install Lua PostgreSQL Library on Luanti Servers

Ubuntu/Debian:

Alternative: Install via LuaRocks:

Verify Installation:

4. Create the Worldgate Database

5. Run the Schema Script

Option A: Run Directly on PostgreSQL Server (Recommended)

Option B: Run Remotely from Luanti Server

Option C: Manual Copy/Paste

What the Schema Creates

5. Verify the Setup

6. Test the Connection

7. Configure Firewall

UFW (Ubuntu):

firewalld (CentOS/RHEL):

Minetest Configuration

Option 1: Using Native PostgreSQL Backend (Recommended)

Option 2: Using Mod Storage (Single Server)

Security Best Practices

1. Use Strong Passwords

2. Restrict Network Access

3. Enable SSL/TLS (Recommended)

4. Regular Backups

5. Monitor Connections

Performance Tuning

For Small Networks (2-5 servers)

For Larger Networks (10+ servers)

Troubleshooting

Can't connect from game server

Step 1: Verify PostgreSQL is listening on the network

Step 2: Check pg_hba.conf allows your Luanti server

Step 3: Test network connectivity

Step 4: Check firewall on PostgreSQL server

Step 5: Test PostgreSQL authentication

Authentication failed

Permission denied errors

Symptoms:

Solution:

Slow queries

Database corruption

Maintenance

Weekly Tasks

Monthly Tasks

Monitor Database Size

Migration from MySQL/MariaDB

1. Export Data from MySQL

2. Convert Schema

3. Import to PostgreSQL

4. Update Minetest Configuration

Comparison: PostgreSQL vs MySQL/MariaDB

Support