Container - PostgreSQL

Container - PostgreSQL

Summary

PostgreSQL is a powerful, open-source relational database system known for reliability, feature robustness, and performance. Running PostgreSQL as a Container on RouterOS enables local data storage for applications, providing a complete SQL database engine without requiring dedicated hardware.

PostgreSQL supports advanced features including ACID transactions, stored procedures, triggers, views, foreign keys, and JSON support. When deployed as a Container on MikroTik routers, it serves as a lightweight database solution for edge computing applications, IoT data collection, and small-scale deployments.

PostgreSQL Containers require significant storage I/O performance. Always use external storage (USB, SATA, NVMe) with good sequential read/write speeds. Running PostgreSQL on router SPI flash storage will result in severe performance degradation and potential data corruption.

Use Cases

Application Data Storage

Store application data locally without relying on cloud services. This is ideal for:

• IoT data collection and logging
• Local application state persistence
• Configuration storage for containerized services

Development and Testing

Create a local database environment for development and testing without dedicated hardware:

• Prototype applications before production deployment
• Test database-backed applications
• Experiment with SQL queries and PostgreSQL features

Edge Computing

Deploy databases at the network edge for reduced latency and offline capability:

• Local caching of frequently accessed data
• Branch office database requirements
• Standalone network operations

Prerequisites

Container Network

Before deploying PostgreSQL, ensure you have a Container network configured with a virtual ethernet (veth) interface:

/interface/veth/print
Flags: X - disabled, A - active, * - default
 #   NAME                                MTU   ADDRESS                                         GATEWAY
 0   veth1                              1500  172.17.0.1/24                                    172.17.0.1

If you need to create a new Container network, refer to the Container networking documentation for detailed instructions.

Storage Requirements

PostgreSQL requires adequate storage and I/O performance:

Resource	Minimum	Recommended
Storage	500MB	2GB+
I/O Performance	100 IOPS	1000+ IOPS
RAM	256MB	512MB+

Never run PostgreSQL on SPI flash storage. Use only external USB, SATA, or NVMe storage with sufficient performance.

Environment Variables

PostgreSQL Container uses environment variables for initial configuration:

Variable	Description	Default
POSTGRES_PASSWORD	Password for postgres superuser	required
POSTGRES_USER	Create additional superuser	postgres
POSTGRES_DB	Default database name	postgres

Basic Configuration

Step 1: Prepare Storage Directories

Create directories on your external storage for PostgreSQL data and configuration:

/file add type=directory name=disk1/postgres/data
/file add type=directory name=disk1/postgres/config

Step 2: Create Mount Points

Create mount points to persist PostgreSQL data and configuration:

/container/mounts/add name=postgres_data src=disk1/postgres/data dst=/var/lib/postgresql/data
/container/mounts/add name=postgres_config src=disk1/postgres/config dst=/etc/postgresql

The data mount ensures database files persist across Container restarts. The config mount allows custom PostgreSQL configuration.

Step 3: Create the PostgreSQL Container

Add the PostgreSQL Container using the official PostgreSQL image:

/container/add remote-image=postgres:16-alpine interface=veth1 root-dir=disk1/images/postgres mounts=postgres_data,postgres_config envs=POSTGRES_PASSWORD=YourSecurePassword123 name=postgres start-on-boot=yes

This command:

• Uses the alpine-based PostgreSQL 16 image (smaller footprint)
• Connects the Container to the specified virtual interface
• Sets the root directory for Container data
• Mounts data and configuration directories
• Sets the postgres password environment variable
• Enables auto-start on router boot

Step 4: Start the Container

/container start [find where name=postgres]

Verify the Container is running:

/container/print
Flags: X - disabled, R - running
 0 R name="postgres" id="abc123..." interface=veth1 ...

Step 5: Verify Operation

Check the logs for PostgreSQL initialization:

/log print follow

You should see messages indicating PostgreSQL is ready to accept connections:

2024-01-15 10:30:00.123 UTC [1] LOG:  database system is ready to accept connections

Connecting to PostgreSQL

From RouterOS

RouterOS does not include a native PostgreSQL client. Use a Container with psql installed or connect from a remote client machine.

From Remote Hosts

Allow remote connections by configuring PostgreSQL authentication and RouterOS firewall:

Configure PostgreSQL Authentication

Create a custom PostgreSQL configuration to allow remote connections:

1. Mount a custom configuration directory
2. Edit pg_hba.conf to allow password authentication:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0              md5
host    all             all             ::/0                   md5

3. Edit postgresql.conf to listen on all addresses:

listen_addresses = '*'

Configure RouterOS Firewall

Allow PostgreSQL port (5432) through the firewall:

/ip/firewall/filter/add chain=input protocol=tcp dst-port=5432 in-interface=veth1 action=accept

Caution

Opening PostgreSQL to the internet is not recommended. Use VPN tunnels, firewall rules limiting source addresses, or SSH tunneling for remote access.

Connecting Applications

Application connection string examples:

postgresql://postgres:YourSecurePassword123@172.17.0.2:5432/mydb

Replace 172.17.0.2 with your PostgreSQL Container’s IP address (check via /interface/veth/print).

Configuration Reference

PostgreSQL Settings

Key postgresql.conf settings for Container deployments:

Setting	Description	Recommended Value
shared_buffers	Memory for caching data	128MB (adjust based on available RAM)
max_connections	Maximum client connections	100
work_mem	Memory for sorting operations	4MB
maintenance_work_mem	Memory for maintenance operations	64MB
fsync	Force writes to disk	on (never disable)
wal_level	Write-ahead log level	replica

Creating Custom Configuration

Create a custom PostgreSQL configuration file:

/container/mounts/add name=postgres_config src=disk1/postgres/config dst=/etc/postgresql/conf.d

Create postgresql.conf in the mounted config directory:

# Memory settings
shared_buffers = 128MB
work_mem = 4MB
maintenance_work_mem = 64MB

# Connection settings
max_connections = 100
listen_addresses = '*'

# Write-ahead log
wal_level = replica
max_wal_senders = 3

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'

Database Management

Creating Databases

Connect to PostgreSQL and create databases:

psql -h 172.17.0.2 -U postgres -c "CREATE DATABASE myapp;"
psql -h 172.17.0.2 -U postgres -c "CREATE USER myuser WITH PASSWORD 'userpassword';"
psql -h 172.17.0.2 -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;"

Creating Tables

CREATE TABLE devices (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    ip_address INET,
    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO devices (name, ip_address) VALUES ('router1', '192.168.1.1');
SELECT * FROM devices;

Backup and Restore

Backup Database

pg_dump -h 172.17.0.2 -U postgres mydb > backup.sql

Restore Database

psql -h 172.17.0.2 -U postgres mydb < backup.sql

Advanced: PostgreSQL with pgAdmin

pgAdmin is a web-based administration tool for PostgreSQL. Running pgAdmin alongside PostgreSQL provides a graphical interface for database management.

Step 1: Create pgAdmin Container

/container/mounts/add name=pgadmin_data src=disk1/pgadmin/data dst=/var/lib/pgadmin
/container/mounts/add name=pgadmin_config src=disk1/pgadmin/config dst=/var/lib/pgadmin/storage
/container/mounts/add name=pgadmin_logs src=disk1/pgadmin/logs dst=/var/log/pgadmin

/container/add remote-image=dpage/pgadmin4:latest interface=veth1 root-dir=disk1/images/pgadmin mounts=pgadmin_data,pgadmin_config,pgadmin_logs envs[email protected],PGADMIN_DEFAULT_PASSWORD=AdminPassword123 name=pgadmin start-on-boot=yes

Step 2: Access pgAdmin

pgAdmin runs on port 80 inside the Container. Access it through the router:

1. Determine pgAdmin Container IP: /interface/veth/print
2. Open browser to http://<router-ip>/pgadmin4/
3. Login with credentials set in environment variables

Step 3: Register PostgreSQL Server

In pgAdmin:

1. Right-click “Servers” → “Create” → “Server”
2. Name: PostgreSQL
3. Connection tab:
   • Host name/address: 172.17.0.2 (PostgreSQL Container IP)
   • Port: 5432
   • Username: postgres
   • Password: YourSecurePassword123

Troubleshooting

Container Will Not Start

1. Verify sufficient storage space: /system/resource/print
2. Check storage I/O performance
3. Verify mount point directories exist
4. Check logs for specific errors: /log print

/container/print detail

Cannot Connect to PostgreSQL

1. Verify Container is running: /container/print
2. Check Container IP address: /interface/veth/print
3. Test connectivity: /tool ping 172.17.0.2
4. Verify firewall allows connections

/ip/firewall/filter/print
/interface/veth/print detail

Poor Performance

1. Check storage performance (avoid SPI flash)
2. Review PostgreSQL memory settings
3. Monitor system resources: /system resource/print
4. Consider dedicated storage device for database

/system/resource/print

Data Corruption

1. Always use external storage with good I/O
2. Never stop Container without graceful shutdown
3. Regular backups are essential
4. Check filesystem integrity

Danger

Never use power loss to stop the router while PostgreSQL Container is running. This can result in database corruption. Always stop Container gracefully first: /container stop postgres

Password Issues

If you forget the postgres password:

1. Stop the Container
2. Edit the Container to remove envs
3. Start Container with new password environment variable
4. Connect and reset password:

ALTER USER postgres WITH PASSWORD 'newpassword';

Security Considerations

Network Security

• Use firewall rules to limit PostgreSQL access
• Consider VPN for remote database access
• Never expose PostgreSQL directly to the internet

Authentication

• Use strong passwords
• Consider certificate-based authentication for production
• Limit superuser access

Data Protection

• Regular backups to external location
• Encrypt sensitive data at application layer
• Monitor access logs

See Also

• Container Overview
• Container Networking
• Container - HAProxy
• PostgreSQL Documentation