Syslog to PostgreSQL via Rsyslog: A Production-Ready Setup

Syslog is the backbone of infrastructure logging, but storing logs as flat files makes querying, retention management, and analysis painful. The industry's knee-jerk reaction is to stand up Elasticsearch. Having run Elasticsearch extensively in production—for both high-write log ingestion/charting and high-read complex catalog searches—I am frankly exhausted by it. I am tired of Java. I'm tired of babysitting the JVM, tweaking heap sizes, fighting garbage collection pauses, and dedicating massive amounts of RAM just to keep the cluster from going red. If you already run PostgreSQL, you might not need to inflict that on yourself. While Elasticsearch demands a dedicated cluster and specialized operational knowledge, this PostgreSQL approach elegantly bolts log storage onto your existing database infrastructure.

In this post, I'll walk through a production-ready setup that pipes syslog directly into PostgreSQL via rsyslog, using features like range partitioning and pg_cron to achieve many of the same benefits as Elasticsearch's time-based indices and Index Lifecycle Management (ILM) — without the Java Virtual Machine (JVM) overhead or the separate cluster.

The full SQL schema, rsyslog configs, and everything discussed here is available for you to use as-is.

Architecture Overview

The setup has two parts:

1. Client side — forwards logs via TCP using rsyslog's omfwd module
2. Server side — receives logs on UDP, TCP, and (optionally) Reliable Event Logging Protocol (RELP) ports and writes them to PostgreSQL using ompgsql

┌──────────────┐
│ Linux Server │ ─┐
└──────────────┘  │   TCP/UDP 514   ┌──────────────────┐
                  ├────────────────▶│   Intermediate   │
┌──────────────┐  │                 │  Rsyslog Server  │
│     NAS      │ ─┘                 └────────┬─────────┘
└──────────────┘                             │
                                             │ RELP 2514
┌──────────────┐                             │
│   OPNsense   │ ─┐                      ┌───▼──────────────┐
└──────────────┘  │   TCP/UDP 514        │   Main Rsyslog   │
                  ├─────────────────────▶│      Server      │
┌──────────────┐  │                      └────────┬─────────┘
│Managed Switch│ ─┘                               │
└──────────────┘                           ompgsql│
                                                  ▼
                                         ┌──────────────────┐
                                         │   PostgreSQL     │
                                         │   (syslog DB)    │
                                         └──────────────────┘

(Note: The Intermediate Rsyslog Server shown above is entirely optional. It is included here to illustrate how you can meet strict security requirements—such as aggregating logs from a DMZ or isolated network segment before securely forwarding them via RELP to your main logging infrastructure.)

Client Configuration

On each log-producing host, the forwarding config is minimal:

action(
    type="omfwd"
    target="192.0.2.1"
    port="514"
    protocol="tcp"
    template="RSYSLOG_SyslogProtocol23Format"
)

TCP is used instead of UDP for reliable delivery — no dropped log lines on a busy network. The RSYSLOG_SyslogProtocol23Format template ensures RFC 5424 compliance, giving us structured fields like msgid and programname on the receiving end.

Server Configuration

The server receives logs on three protocols and writes them to PostgreSQL:

module(load="imudp")
module(load="imtcp")
module(load="imrelp")

input(
    type="imudp"
    port="514"
    rateLimit.interval="60"
    rateLimit.burst="2000"
)

input(
    type="imtcp"
    port="514"
)

input(
    type="imrelp"
    port="2514"
    maxDataSize="10k"
)

Three protocols for three use cases:

• UDP/514 — universal compatibility, with rate limiting (burst="2000" per 60 seconds) to prevent a noisy host from overwhelming the server
• TCP/514 — reliable delivery for clients that support it
• RELP/2514 (Optional) — transaction-based syslog protocol with guaranteed delivery and replay on failure. It's a nice-to-have for rsyslog-to-rsyslog forwarding on unstable links, but standard TCP is sufficient for most deployments.

The PostgreSQL Output Template

The heart of the pipeline is the ompgsql module with a list-type template:

template(
    name="LogToPgSQL" type="list" option.stdsql="on" ) {
    constant(value="INSERT INTO system_events (...) VALUES ('")
    property(name="timereported" dateFormat="pgsql" date.inUTC="on")
    constant(value="', '")
    property(name="msgid")
    constant(value="', 'syslog', '")
    property(name="programname")
    -- ... more fields ...
    property(name="msg" escape="sql")
    -- ... more fields ...
}

Two details worth highlighting:

1. dateFormat="pgsql" and date.inUTC="on" — Syslog timestamps are notoriously inconsistent across different devices. This combination forces rsyslog to standardize everything to UTC at the ingestion point and format it exactly as PostgreSQL's TIMESTAMP WITH TIME ZONE expects (YYYY-MM-DD HH:MM:SS.mmm+TZ). No string parsing on the database side, and no timezone nightmares when querying later.
2. escape="sql" — built-in SQL escaping on msg and rawmsg prevents injection from malicious or malformed log content.

(For a complete list of variables you can extract from syslog messages, check out the official Rsyslog properties documentation.)

Queue Configuration for Resilience

action(
    type="ompgsql" server="localhost"
    user="rsyslog" pass="..."
    db="syslog" template="LogToPgSQL"

    queue.type="linkedList"
    queue.size="20000"
    queue.workerThreads="2"
)

The linked-list queue with 20,000 message capacity and 2 worker threads provides a basic buffer during database hiccups. If PostgreSQL briefly stalls, rsyslog holds messages in memory rather than dropping them. Two worker threads keep the pipeline flowing even when one thread is blocked on a slow write.

A Note for Production Deployments:
If you are implementing this in a production environment, Rsyslog's queuing subsystem is something you should absolutely dig deeper into. It is a killer feature for log reliability. Beyond simple in-memory linked lists, Rsyslog supports Disk-Assisted Queues.

If PostgreSQL goes down for an extended maintenance window, or if a broadcast storm triggers a massive log spike that exceeds your database write speed, a disk-assisted queue will seamlessly spill the buffered logs onto the local disk. Once the database recovers, Rsyslog drains the disk queue into PostgreSQL. This guarantees zero log loss during database outages or extreme traffic spikes—a level of resilience that usually requires deploying a dedicated message broker like Kafka in other logging stacks.

The PostgreSQL Schema

Now for the centerpiece of this setup: the database schema. This is where PostgreSQL starts to look a lot like a log aggregation platform.

1. Range Partitioning — PostgreSQL's Answer to Time-Based Indices

Elasticsearch handles log retention by creating a new index per time period — logs-2025.01.01, logs-2025.01.02, and so on. When it's time to delete old data, it drops the entire index. Instant, no compaction, no garbage collection.

PostgreSQL range partitioning is the same concept, expressed in SQL:

CREATE TABLE system_events (
    ID SERIAL NOT NULL,
    ReceivedAt TIMESTAMP WITH TIME ZONE NOT NULL,
    DeviceReportedTime TIMESTAMP WITH TIME ZONE NULL,
    EventID VARCHAR(60) NULL,
    EventLogType VARCHAR(60),
    EventSource VARCHAR(60),
    Facility SMALLINT NULL,
    FromHost VARCHAR(255) NULL,
    FromPort INT NULL,
    FromIpAddress INET NULL,
    HostName VARCHAR(255) NULL,
    Message TEXT,
    Priority SMALLINT NULL,
    RawMessage TEXT,
    RFC5424AppName VARCHAR(255) NULL,
    RFC5424MsgID VARCHAR(255) NULL,
    RFC5424ProcID VARCHAR(255) NULL,
    RFC5424ProtocolVersion SMALLINT NULL,
    RFC5424StructuredData TEXT NULL,
    SysLogTag VARCHAR(60),
    message_tsv tsvector
        GENERATED ALWAYS AS (to_tsvector('english', Message)) STORED,
    PRIMARY KEY (ID, ReceivedAt)
) PARTITION BY RANGE (ReceivedAt);

The ReceivedAt column is the partition key — every row is routed to the correct monthly partition automatically. The primary key includes ReceivedAt because PostgreSQL requires the partition key to be part of any unique constraint on a partitioned table.

The message_tsv column is a generated column that automatically maintains a full-text search vector from the Message column. It's always in sync — no application logic, no triggers, no stale data.

Monthly partitions are created like this:

CREATE TABLE system_events_2026_03 PARTITION OF system_events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

CREATE TABLE system_events_2026_04 PARTITION OF system_events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

Partition Pruning in Action

Here's where partitioning pays off. When you query a date range, PostgreSQL's planner knows exactly which partitions to scan — and skips the rest entirely. This is called partition pruning, and it's the same mechanism Elasticsearch uses to skip irrelevant shards.

Watch what happens when we query a date range that has no matching partition:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM system_events
WHERE ReceivedAt >= '2025-03-01' AND ReceivedAt < '2025-04-01';

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0) (actual time=0.001..0.001 rows=0 loops=1)
   One-Time Filter: false
 Planning:
   Buffers: shared hit=6
 Planning Time: 0.053 ms
 Execution Time: 0.012 ms

0.012 milliseconds. The planner determined at plan time that no partition covers March 2025, so it didn't scan anything at all. One-Time Filter: false means the entire query was short-circuited. This is partition pruning at its most effective.

On a real query that hits a partition, PostgreSQL scans only the matching partition — not January, not February, not April. The other partitions are invisible to the query.

Dropping Partitions vs. DELETE

This is the killer feature of partitioning for log retention. When it's time to delete old data, you don't run:

DELETE FROM system_events WHERE ReceivedAt < '2026-01-01';

A DELETE on millions of rows means:

• Scanning millions of rows to find the ones to delete
• Generating massive WAL (write-ahead log)
• Leaving dead tuples behind that require VACUUM
• VACUUM competing with your ingestion workload
• Table bloat until autovacuum catches up

Instead, you drop the partition:

DROP TABLE system_events_2025_12;

This is instant. No scanning, no WAL, no VACUUM, no bloat. The entire partition file is removed from disk in a single filesystem operation. It's the same advantage Elasticsearch gets from dropping an index.

Real-World Partition Stats

In this deployment, the partitioned table holds 2.12 million rows across seven monthly partitions:

Partition	Rows	Table Size	Index Size	Total
2026_01	—	248 MB	70 MB	331 MB
2026_02	—	269 MB	51 MB	334 MB
2026_03	43,310	493 MB	95 MB	608 MB
2026_04	134,439	70 MB	14 MB	86 MB
2026_05+	0	0 bytes	160 kB	168 kB

Note that pg_total_relation_size('system_events') on the parent table returns 0 bytes — this is expected. The parent table in a partitioned setup is just a logical container. All the data lives in the child partitions.

Index overhead sits at 19-28% of table size, which is very reasonable. The primary key index is the largest single index component — a consequence of including ReceivedAt in the composite key for partitioning compatibility.

2. Index Strategy — Designed for How Logs Are Actually Queried

The schema includes a deliberately chosen set of indexes, each serving a specific query pattern:

BRIN Indexes on Time Columns

CREATE INDEX idx_system_events_receivedat_brin
    ON system_events USING brin (ReceivedAt DESC);
CREATE INDEX idx_system_events_devicereportedtime_brin
    ON system_events USING brin (DeviceReportedTime DESC);

BRIN (Block Range Index) stores the minimum and maximum values for each block range in the table. For append-only, time-ordered data like syslog, this is near-perfect. A BRIN index on ReceivedAt is typically ~200 KB — compared to ~23 MB for an equivalent B-tree index. That's a 99% reduction in index size with nearly identical performance for range scans.

BRIN works best for naturally ordered data like timestamps. If your log ingestion has significant out-of-order delivery (hours/days delayed), B-tree might be preferable despite the size difference.

B-tree Indexes on Common Filters

CREATE INDEX idx_system_events_fromhost ON system_events (FromHost);
CREATE INDEX idx_system_events_eventsource ON system_events (EventSource);
CREATE INDEX idx_system_events_facility ON system_events (Facility);
CREATE INDEX idx_system_events_priority ON system_events (Priority);

These cover the most common queries: "show me all logs from this host," "show me all sshd logs," "show me all auth facility messages." Simple, effective, and small enough to stay in memory.

Partial Index for Errors

CREATE INDEX idx_system_events_errors ON system_events
    (FromHost, EventSource, ReceivedAt DESC)
    WHERE Priority <= 3;

This is a partial composite index — it only indexes rows where Priority <= 3 (emerg, alert, crit, err), which is about 9% of all log messages. The three-column structure covers the equality filters (FromHost, EventSource) and the sort (ReceivedAt DESC) in a single index. Error dashboards hit this index and never touch the other 91% of rows.

GIN Index for Full-Text Search

CREATE INDEX idx_system_events_message_fts
    ON system_events USING gin (message_tsv);

This Generalized Inverted Index (GIN) is what closes the biggest gap vs. Elasticsearch. It enables fast keyword search across all log messages:

SELECT ReceivedAt, FromHost, EventSource,
       ts_headline('english', Message, q) AS context
FROM system_events, to_tsquery('english', 'authentication & failure') q
WHERE message_tsv @@ q
ORDER BY ReceivedAt DESC LIMIT 20;

Tradeoff: the message_tsv column adds ~20-30% to storage per row. If you don't need keyword search, omit the column and its index.

In testing, searching for 'authentication failure' across 2M rows took ~15ms with the GIN index vs. several seconds with a sequential scan.

3. pg_cron — PostgreSQL's Answer to Index Lifecycle Management

Elasticsearch has Index Lifecycle Management (ILM) — a built-in system that automatically creates new indices, rolls over old ones, and deletes expired data. PostgreSQL doesn't have ILM baked in, but it has pg_cron, and with a few stored procedures, you get the same result.

pg_cron runs scheduled SQL jobs directly inside PostgreSQL — no external cron daemon, no shell scripts, no connection management. It's cron, but it speaks SQL.

The Stored Procedures

Three procedures handle the full partition lifecycle:

create_monthly_partition — creates a single partition for a given year and month, idempotent:

CREATE OR REPLACE PROCEDURE public.create_monthly_partition(
    p_table_name TEXT, year INT, month INT)
LANGUAGE plpgsql AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := make_date(year, month, 1);
    end_date := (start_date + INTERVAL '1 month')::DATE;
    partition_name := format('%s_%s_%s', p_table_name, year, lpad(month::TEXT, 2, '0'));

    IF NOT EXISTS (
        SELECT FROM pg_tables
        WHERE tablename = partition_name AND schemaname = 'public'
    ) THEN
        EXECUTE format(
            'CREATE TABLE public.%I PARTITION OF public.%I FOR VALUES FROM (%L) TO (%L)',
            partition_name, p_table_name, start_date, end_date
        );
        RAISE NOTICE 'Created partition public.%', partition_name;
    ELSE
        RAISE NOTICE 'Partition public.% already exists, skipping', partition_name;
    END IF;
END;
$$;

create_future_partitions — loops N months ahead and creates them all:

CREATE OR REPLACE PROCEDURE public.create_future_partitions(
    p_table_name TEXT, months_ahead INT)
LANGUAGE plpgsql AS $$
DECLARE
    current_date DATE := CURRENT_DATE;
    target_date DATE;
    i INT;
BEGIN
    FOR i IN 0..months_ahead LOOP
        target_date := current_date + (i * INTERVAL '1 month');
        CALL public.create_monthly_partition(
            p_table_name,
            EXTRACT(YEAR FROM target_date),
            EXTRACT(MONTH FROM target_date)
        );
    END LOOP;
END;
$$;

drop_old_partitions — drops partitions older than a retention interval:

CREATE OR REPLACE PROCEDURE public.drop_old_partitions(
    p_table_name TEXT, retention_interval INTERVAL)
LANGUAGE plpgsql AS $$
DECLARE
    partition_name TEXT;
BEGIN
    FOR partition_name IN (
        SELECT tables.table_name
        FROM information_schema.tables
        WHERE tables.table_name LIKE p_table_name || '_%'
        AND tables.table_name < p_table_name || '_'
            || to_char(CURRENT_DATE - retention_interval, 'YYYY_MM')
        AND tables.table_schema = 'public'
    )
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(partition_name);
        RAISE NOTICE 'Dropped partition public.%', partition_name;
    END LOOP;
END;
$$;

The Cron Jobs

Two scheduled jobs keep the system self-managing:

SELECT cron.schedule_in_database(
    'drop_old_system_events_partitions',
    '0 14 1 * *',
    $$CALL public.drop_old_partitions('system_events', INTERVAL '3 months');$$,
    'syslog'
);

SELECT cron.schedule_in_database(
    'create_future_system_events_partitions',
    '5 14 1 * *',
    $$CALL public.create_future_partitions('system_events', 3);$$,
    'syslog'
);

On the first of every month at 14:00:

1. Old partitions are dropped — anything older than 3 months vanishes instantly
2. Future partitions are created — the next 3 months are pre-provisioned

Five minutes between jobs ensures the drop completes before new partitions are created. No external scripts, no SSH keys, no cron entry management. The entire lifecycle runs inside PostgreSQL, observable through standard system tables:

SELECT jobid, jobname, schedule, command FROM cron.job;
SELECT jobid, status, start_time, end_time
FROM cron.job_run_details ORDER BY start_time DESC;

In this deployment, the job history tells a nice story: the cron jobs were scheduled before the syslog database existed. They failed every month from December 2025 through February 2026 with "connection failed." Once the database came online in March, they just worked — no manual intervention needed. This self-healing behavior means you can safely deploy the cron jobs before the database is ready - they'll automatically start working when the database becomes available.

This is the log retention equivalent of Elasticsearch's ILM — automatic rollover, automatic deletion — but implemented in pure SQL with a scheduling extension.

4. The INET Data Type

FromIpAddress INET NULL,

PostgreSQL's native INET type stores IPv4 and IPv6 addresses with built-in validation and operators. You can query by subnet directly:

SELECT * FROM system_events WHERE FromIpAddress <<= '192.0.2.0/24';

No string parsing, no LIKE '192.0.2.%' hacks. The <<= operator means "is contained in subnet" — it's indexable and fast.

5. Reference Tables with Foreign Keys

CREATE TABLE facilities (facility_id SMALLINT PRIMARY KEY, facility_name VARCHAR(100));
CREATE TABLE priorities (priority_id SMALLINT PRIMARY KEY, priority_name VARCHAR(100));

ALTER TABLE system_events
    ADD CONSTRAINT fk_facility FOREIGN KEY (Facility) REFERENCES facilities (facility_id);
ALTER TABLE system_events
    ADD CONSTRAINT fk_priority FOREIGN KEY (Priority) REFERENCES priorities (priority_id);

Syslog facility and priority are integers — facility=3, priority=6. Without context, those numbers are meaningless. Lookup tables enforce data integrity and make queries self-documenting.

In this deployment, the facility breakdown tells a clear story about the environment:

Facility	Count	Percentage
daemon	622,291	29.3%
local7	493,636	23.3%
cron	325,109	15.3%
auth	284,703	13.4%
user	154,686	7.3%

The dominance of daemon and local7 (commonly used by firewalls) suggests this is a network-heavy environment, which I suppose it is—I configured just about everything I could find in my home lab to forward syslog, including several managed switches, OpenWrt WiFi access points, and an OPNsense firewall. The fact that auth makes up 13.4% of the traffic is an immediate action item for me to look into; that represents hundreds of thousands of SSH sessions, sudo commands, and login attempts across the network.

The priority distribution looks like this:

Priority	Count	Percentage
info	1,240,125	58.5%
notice	405,596	19.1%
err	191,932	9.0%
debug	179,518	8.5%
warning	103,583	4.9%

While only 9.0% of the 2.12 million messages are errors, that's still nearly 200,000 discrete error events I need to investigate. Additionally, the 8.5% debug volume indicates that some of these lab devices are running in highly verbose modes. Tracking down and dialing back those debug logs is another thing I'll need to look into to keep the ingestion volume lean.

6. A Convenience View

CREATE VIEW v_system_events AS
    SELECT se.*, f.facility_name, p.priority_name
    FROM system_events se
    LEFT JOIN facilities f ON se.Facility = f.facility_id
    LEFT JOIN priorities p ON se.Priority = p.priority_id;

A view that joins the lookup tables means analysts never need to remember that facility_id = 3 means "daemon" — it's right there in the result set. Query the view, not the table.

7. Least-Privilege Database User

CREATE ROLE rsyslog WITH LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS
    ENCRYPTED PASSWORD 'SCRAM-SHA-256$...';

GRANT USAGE ON SCHEMA public TO rsyslog;
GRANT USAGE, SELECT ON SEQUENCE public.system_events_id_seq TO rsyslog;
GRANT INSERT ON TABLE public.system_events TO rsyslog;

The rsyslog database user gets exactly what it needs: INSERT on the events table and SELECT on the sequence for the serial ID. Nothing more. SCRAM-SHA-256 authentication ensures passwords aren't stored as the legacy MD5 format.

8. SQL_ASCII Encoding for Heterogeneous Log Sources

CREATE DATABASE "syslog" WITH ENCODING 'SQL_ASCII' TEMPLATE template0;

Syslog messages come from dozens of different hosts, each with their own locale and encoding settings. A UTF-8 database would reject any byte sequence that isn't valid UTF-8 — and syslog is full of them. SQL_ASCII tells PostgreSQL to pass bytes through without validation. No encoding errors from a misbehaving device, no dropped logs.

The tradeoff is that you need to handle encoding in your application layer. For a log ingestion system that's primarily queried by humans who can tolerate the occasional garbled character, that's a reasonable trade.

What the Logs Look Like

After running this setup, the data tells a story about the infrastructure. The top log sources:

Source	Count
dhcpd	370,683
bbstored	286,399
sshd	182,323
radvd	131,274
dhclient	102,213
configd.py	92,668
filterlog	62,534

Since we already know this is an OPNsense-heavy home lab, seeing configd.py, filterlog, and radvd bubble to the top makes perfect sense. But the data provides a great reminder of exactly which services are the chattiest. The heavy dhcpd and dhclient traffic, for instance, highlights just how many DHCP leases are constantly being renewed by various lab and IoT devices across the network.

The ingestion rate shows the system ramping up as more hosts were configured:

Date	Rows
2026-03-29	16,375
2026-03-30	11,998
2026-03-31	31,312
2026-04-01	38,699
2026-04-02	39,349
2026-04-03	37,899
2026-04-04	18,489

From 16,375 rows in the earlier days to nearly 40,000 per day once all hosts were forwarding. At that rate, each monthly partition accumulates roughly 1 million rows — well within PostgreSQL's comfort zone, especially with partitioning keeping individual table sizes manageable.

Useful Queries

Here are practical queries for working with your log data:

Error Rate by Host (Last 24 Hours)

SELECT FromHost, count(*) AS errors
FROM system_events
WHERE Priority <= 3 AND ReceivedAt > now() - INTERVAL '24 hours'
GROUP BY 1 ORDER BY 2 DESC;

Full-Text Search with Highlighted Context

SELECT ReceivedAt, FromHost, EventSource,
        ts_headline('english', Message, q) AS context
FROM system_events, to_tsquery('english', 'authentication & failure') q
WHERE message_tsv @@ q
ORDER BY ReceivedAt DESC LIMIT 20;

Subnet Scan Using INET Type

SELECT FromHost, count(*) AS log_count
FROM system_events
WHERE FromIpAddress <<= '192.0.2.0/24'
GROUP BY 1 ORDER BY 2 DESC;

Error Trend Over Time

SELECT date_trunc('day', ReceivedAt) AS day, count(*) AS errors
FROM system_events
WHERE Priority <= 3
GROUP BY 1 ORDER BY 1;

Top Error Sources This Week

SELECT EventSource, FromHost, count(*) AS cnt
FROM system_events
WHERE Priority <= 3 AND ReceivedAt > now() - INTERVAL '7 days'
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 20;

Facility Breakdown with Percentages

SELECT facility_name, count(*) AS cnt,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM v_system_events
GROUP BY 1 ORDER BY 2 DESC;

Recent Errors from Specific Subnet (Optimized by Partial Error Index)

SELECT ReceivedAt, FromHost, Message
FROM system_events
WHERE Priority <= 3 
  AND FromIpAddress <<= '192.0.2.0/24'
  AND ReceivedAt > now() - INTERVAL '1 hour'
ORDER BY ReceivedAt DESC;

Top Generalized Error Messages (De-duplicating PIDs/IPs)

Syslog messages often contain unique numbers (like PIDs, ports, or IP addresses) that make identical errors look like distinct strings. This query uses regexp_replace to mask numbers, allowing you to group and count the true underlying error patterns.

SELECT regexp_replace(Message, '[0-9]+', 'X', 'g') AS generalized_message, 
       count(*) AS error_count
FROM system_events
WHERE Priority <= 3 
  AND ReceivedAt >= now() - INTERVAL '7 days'
GROUP BY generalized_message
ORDER BY error_count DESC
LIMIT 10;

PostgreSQL Tuning for Log Ingestion

It's worth noting the hardware profile of the environment described in this post: it is running in a small LXC container backed by unremarkable SSDs, with just 4 CPU cores and 8GB of RAM. Despite processing millions of logs and maintaining continuous GIN indexes, the system hovers at around 12% utilization. You do not need massive hardware to make this work.

To put some real numbers behind that efficiency, here is the actual performance data from this 8GB container:

• Index Hit Rate: 99.87% (357,517 index scans vs. just 476 sequential scans). This proves the indexing strategy perfectly matches the query patterns. The database planner almost never resorts to scanning full tables.
• Cache Hit Ratio: 84.3%. For a write-heavy log database, keeping 84% of accessed blocks in memory is a healthy balance. The SSDs handle the continuous sequential writes, while RAM serves the repetitive index updates and active lookups.
• Zero-Overhead Historical Data. PostgreSQL's internal stats show the active month's partition (2026_04) has been autovacuumed 15 times to keep up with the insert churn, while all historic partitions show exactly 0 runs. Maintenance work is isolated entirely to the active partition, meaning your retained data costs zero CPU cycles to keep around.

That said, the default PostgreSQL configuration isn't optimized for write-heavy log workloads. Here is the real postgresql.conf block used to hit those numbers on a 4-core, 8GB machine:

# Memory
shared_buffers = 2GB                     # 25% of 8GB RAM
maintenance_work_mem = 1GB               # faster index creation and partition operations
work_mem = 32MB                          # per-operation memory for sorts and joins

# Disk & Asynchronous Behavior
effective_io_concurrency = 256           # highly parallel I/O for SSDs
max_worker_processes = 23                # enough workers for background jobs
max_parallel_workers = 4                 # matches the 4 CPU cores
default_toast_compression = lz4          # faster text compression on Postgres 14+

# WAL and Checkpoints
max_wal_size = 2GB
min_wal_size = 512MB
checkpoint_completion_target = 0.9       # spread checkpoint I/O out smoothly

# Autovacuum
autovacuum_max_workers = 4               # keep up with partition churn

The Magic of LZ4 Compression

One setting in that config deserves a special shoutout: default_toast_compression = lz4.

PostgreSQL automatically compresses large text columns behind the scenes (a mechanism called TOAST, or The Oversized-Attribute Storage Technique). By default, it uses an older, slower algorithm called PGLZ. If you are running PostgreSQL 14 or newer, switching this default to LZ4 provides drastically faster compression during log ingestion and faster decompression during queries. For text-heavy workloads like syslog Message and RawMessage blobs, this is one of the easiest ways to drop your CPU utilization almost instantly.

Properly Sizing work_mem

Another critical adjustment is work_mem = 32MB. In a log database, your queries almost exclusively involve massive GROUP BY and ORDER BY operations (like aggregating errors or sorting chronologically).

By default, PostgreSQL's work_mem is extremely conservative (4MB). If an analytical query requires more memory than that to execute a sort, PostgreSQL will spill the workspace over to your SSD as a temporary file, causing a massive latency spike. Giving those analytical queries 32MB of dedicated RAM ensures that complex groupings (like the regex deduplication query above) execute entirely in memory. In my 8GB container, that regex query groups over 10,000 recent errors in just ~106 milliseconds without ever touching the disk.

Backup and Restore

Partitioned tables behave mostly like regular tables for backup purposes, with a few quirks worth knowing:

• pg_dump dumps the parent table definition and all partitions. Restoring recreates the full partitioned structure.
• pg_basebackup works normally — partitions are just tables on disk.
• Restoring a single partition — you can pg_dump a specific partition table and restore it independently, useful for recovering from a data archive.
• pg_restore --jobs — parallel restore works across partitions, speeding up large restores.

For compliance archival, you can pg_dump specific partitions: pg_dump -t system_events_2026_03 > march2026.sql

One gotcha: if you restore into a database that already has partitions, you'll get conflicts. Always restore into a fresh database or drop existing partitions first.

Troubleshooting

Queue Full

If rsyslog's queue fills up (20,000 messages), new messages are dropped. Check with:

# Check rsyslog queue status
journalctl -u rsyslog | grep -i "queue"

Mitigate by increasing queue.size or adding disk-backed queuing:

queue.type="disk"
queue.spoolDirectory="/var/spool/rsyslog"
queue.maxDiskSpace="1g"

pg_cron Job Failed

Check the job run history:

SELECT jobid, runid, status, return_message, start_time, end_time
FROM cron.job_run_details
ORDER BY start_time DESC LIMIT 10;

Common failures:

• "connection failed" — the target database wasn't reachable. Check that the database exists and the role has permissions.
• "no partition of relation found" — a partition is missing. Run CALL public.create_future_partitions('system_events', 3); manually.
• Permission denied — ensure the rsyslog role has the necessary grants on new partitions.

Partition Missing on Insert

If rsyslog logs an error like "no partition of relation 'system_events' found for row," the partition for that date range doesn't exist yet. This happens if pg_cron hasn't run or failed. Fix it manually:

CALL public.create_monthly_partition('system_events', 2026, 5);

Slow Queries

Use EXPLAIN (ANALYZE, BUFFERS) to check if partition pruning is working:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM system_events
WHERE ReceivedAt >= '2026-03-01' AND ReceivedAt < '2026-04-01';

Look for the partitions being scanned in the plan. If all partitions appear, your query isn't filtering on the partition key.

Check for unused indexes:

SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname LIKE 'system_events_%'
ORDER BY idx_scan;

If idx_scan is 0 after weeks of operation, consider dropping that index.

High CPU During Partition Creation

Creating many partitions at once can be CPU-intensive. The create_future_partitions procedure creates them sequentially to spread the load. If you need to create many partitions manually, consider adding a small delay between calls or running during off-peak hours.

Comparison: PostgreSQL vs. Elasticsearch

Feature	PostgreSQL	Elasticsearch
Time-based data split	Range partitions	Time-based indices
Pruning	Partition pruning at plan time	Shard routing
Retention	Instant DROP TABLE	Delete index
Automation	pg_cron + stored procedures	Index Lifecycle Management (ILM)
IP queries	Native INET type with subnet operators	IP field type
Self-documenting queries	Lookup tables + foreign keys + views	Index templates + Kibana
Ingestion resilience	rsyslog queue buffering	Logstash queue
Data integrity	ACID, foreign keys	Eventual consistency
Query language	SQL	Query DSL
Full-text search	tsvector + GIN index	Native, highly optimized
Aggregations at scale	Good up to ~100M rows	Excellent at petabyte scale
Operational Complexity	Low (uses existing infrastructure, standard SQL)	High (separate cluster, specialized knowledge needed)

PostgreSQL wins at structured log analysis with relational context, ACID guarantees, and operational simplicity. Elasticsearch wins at full-text search and aggregations at massive scale.

Where to Go From Here

This setup is production-ready as-is, but there are natural enhancements you can add to suit your needs:

Materialized Views for Dashboards

A materialized view is a pre-computed query result stored on disk — like a cached query. Unlike a regular view that re-runs every time, a materialized view stores the results and you refresh it on a schedule.

CREATE MATERIALIZED VIEW mv_daily_error_counts AS
SELECT
    date_trunc('day', ReceivedAt) AS day,
    FromHost,
    EventSource,
    Priority,
    count(*) AS error_count
FROM system_events
WHERE Priority <= 3
GROUP BY 1, 2, 3, 4;

CREATE UNIQUE INDEX ON mv_daily_error_counts (day, FromHost, EventSource, Priority);

Refresh it on a schedule:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_error_counts;

The CONCURRENTLY keyword means the view stays queryable during refresh — no downtime for dashboards. Schedule it via pg_cron every hour and you have a near-real-time error dashboard backed by a tiny table instead of scanning millions of rows.

Composite Indexes for Specific Query Patterns

A composite index covers multiple columns in a single structure. The column order matters because of the leftmost prefix rule — an index on (A, B, C) supports queries on A, A+B, and A+B+C, but not on B alone or C alone.

CREATE INDEX idx_system_events_host_time ON system_events
    (FromHost, ReceivedAt DESC);

CREATE INDEX idx_system_events_source_time ON system_events
    (EventSource, ReceivedAt DESC);

These support your most common queries efficiently:

-- "Show me all logs from this host, newest first"
SELECT * FROM system_events WHERE FromHost = 'firewall-primary' ORDER BY ReceivedAt DESC;

-- "Show me all sshd logs from today"
SELECT * FROM system_events
WHERE EventSource = 'sshd' AND ReceivedAt > now() - INTERVAL '1 day';

Horizontal Scale-Out

If your ingestion volume ever outgrows a single node, this architecture scales out cleanly at both layers:

• Rsyslog Scale-Out: You can run multiple instances of Rsyslog behind a network load balancer (like HAProxy or F5) to distribute the incoming TCP/UDP traffic. Because the Rsyslog instances are completely stateless log routers, you can scale them horizontally to handle massive broadcast storms, with all nodes writing concurrently to the central database.
• PostgreSQL Scale-Out: To handle high dashboard concurrency or complex analytical queries, you can easily spin up Read Replicas to offload read pressure from your primary database. If your write volume eventually exceeds a single node's capacity, you can shard your time-partitioned tables across a cluster of worker nodes using distributed SQL or time-series extensions, unlocking petabyte-scale retention.

Other Enhancements

• JSONB structured parsing — Parse common log formats (nginx, auth, systemd) into structured JSONB columns for targeted queries instead of LIKE '%pattern%'.
• Row-Level Security — Restrict log access by team, host group, or customer using PostgreSQL RLS policies.
• Data Archiving — COPY TO old partitions to an external file before dropping them, for compliance retention without bloating the database.
• Grafana dashboards — Connect PostgreSQL directly as a datasource for real-time log dashboards, or use Metabase for self-service exploration by non-SQL users.
• TLS Encryption — Standard syslog over TCP/UDP is unencrypted plaintext. If you are forwarding logs across untrusted networks and your client devices support it, configure rsyslog to use the TLS module to encrypt your log traffic in transit.

The beauty of this approach is that each enhancement uses PostgreSQL features you're already running. No new infrastructure, no new operational burden — just SQL.

Running It

Prerequisites: By default, Linux distributions do not ship with the PostgreSQL or RELP modules. For Debian/Ubuntu-based systems, install them first (omit rsyslog-relp if you are sticking with TCP/UDP):

sudo apt install rsyslog-pgsql rsyslog-relp

1. Create the database and schema with the provided syslog.sql
2. Drop the rsyslog server config into /etc/rsyslog.conf (or /etc/rsyslog.d/)
3. Configure clients with the forwarding config
4. Restart rsyslog: systemctl restart rsyslog
5. Verify: SELECT count(*) FROM v_system_events;

The whole pipeline is self-maintaining: partitions roll forward and backward automatically, old data vanishes without DELETE overhead, the queue absorbs transient failures, and full-text search is always in sync. For a log ingestion system, that's exactly what you want.

---

Appendix

The Problem with Flat File Logs

Flat file syslog works fine until you need to answer questions like:

• "Show me all authentication failures from the last 48 hours across all hosts"
• "How many error-level messages did our firewall generate this week?"
• "What's the trend in disk warnings over the past three months?"

With flat files, these require grep, awk, and a lot of patience. With a database, they're a single SQL query.

Why Not Elasticsearch?

Elasticsearch is purpose-built for log aggregation, and it excels at full-text search and aggregations at massive scale. But it comes with real costs:

• A separate cluster to provision, monitor, and upgrade
• For small-to-medium deployments, Elasticsearch's operational overhead can still be significant compared to adding tables to an existing PostgreSQL instance
• Index lifecycle management (ILM) to configure and maintain
• No ACID (Atomicity, Consistency, Isolation, Durability) guarantees — log ingestion can lose data during restarts
• Query Domain-Specific Language (DSL) that's powerful but has its own learning curve

PostgreSQL, on the other hand, is probably already running in your stack. It gives you ACID guarantees, relational joins, foreign keys, and a query language your team already knows. With the right schema design, it handles log ingestion and retention surprisingly well.

This isn't to say PostgreSQL replaces Elasticsearch at petabyte scale. But for small-to-medium deployments — a few million rows, dozens of hosts — a well-designed PostgreSQL setup can cover 80% of the use cases with 20% of the operational overhead.

---

Full SQL Schema

Here's the complete schema for reference. Save this as syslog.sql and run it with psql -f syslog.sql.

CREATE DATABASE "syslog" WITH ENCODING 'SQL_ASCII' TEMPLATE template0;

\connect syslog;

CREATE TABLE system_events
(
    ID SERIAL NOT NULL,
    DeviceReportedTime TIMESTAMP WITH TIME ZONE NULL,
    EventID VARCHAR(60) NULL,
    EventLogType VARCHAR(60),
    EventSource VARCHAR(60),
    Facility SMALLINT NULL,
    FromHost VARCHAR(255) NULL,
    FromPort INT NULL,
    FromIpAddress INET NULL,
    HostName VARCHAR(255) NULL,
    Message TEXT,
    Priority SMALLINT NULL,
    RawMessage TEXT,
    ReceivedAt TIMESTAMP WITH TIME ZONE NOT NULL,
    RFC5424AppName VARCHAR(255) NULL,
    RFC5424MsgID VARCHAR(255) NULL,
    RFC5424ProcID VARCHAR(255) NULL,
    RFC5424ProtocolVersion SMALLINT NULL,
    RFC5424StructuredData TEXT NULL,
    SysLogTag VARCHAR(60),
    message_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', Message)) STORED,
    PRIMARY KEY (ID, ReceivedAt)
) PARTITION BY RANGE (ReceivedAt);

-- BRIN indexes for time-ordered columns (much smaller than B-tree for append-only data)
CREATE INDEX idx_system_events_receivedat_brin ON system_events USING brin (ReceivedAt DESC);
CREATE INDEX idx_system_events_devicereportedtime_brin ON system_events USING brin (DeviceReportedTime DESC);
-- B-tree indexes for common query filters
CREATE INDEX idx_system_events_facility ON system_events (Facility);
CREATE INDEX idx_system_events_priority ON system_events (Priority);
CREATE INDEX idx_system_events_fromhost ON system_events (FromHost);
CREATE INDEX idx_system_events_eventsource ON system_events (EventSource);
-- Partial index for error-level logs only (Priority 0-3: emerg, alert, crit, err)
CREATE INDEX idx_system_events_errors ON system_events (FromHost, EventSource, ReceivedAt DESC)
    WHERE Priority <= 3;
-- Full-text search index on Message
CREATE INDEX idx_system_events_message_fts ON system_events USING gin (message_tsv);

-- Create table for Facility mappings
CREATE TABLE facilities (
    facility_id SMALLINT PRIMARY KEY,
    facility_name VARCHAR(100) NOT NULL
);

-- Insert common syslog facility values
INSERT INTO facilities (facility_id, facility_name) VALUES
(0, 'kern'), (1, 'user'), (2, 'mail'), (3, 'daemon'),
(4, 'auth'), (5, 'syslog'), (6, 'lpr'), (7, 'news'),
(8, 'uucp'), (9, 'cron'), (10, 'authpriv'), (11, 'ftp'),
(12, 'ntp'), (13, 'security'), (14, 'console'), (15, 'solaris-cron'),
(16, 'local0'), (17, 'local1'), (18, 'local2'), (19, 'local3'),
(20, 'local4'), (21, 'local5'), (22, 'local6'), (23, 'local7');

-- Create table for Priority mappings
CREATE TABLE priorities (
    priority_id SMALLINT PRIMARY KEY,
    priority_name VARCHAR(100) NOT NULL
);

-- Insert common syslog priority values
INSERT INTO priorities (priority_id, priority_name) VALUES
(0, 'emerg'), (1, 'alert'), (2, 'crit'), (3, 'err'),
(4, 'warning'), (5, 'notice'), (6, 'info'), (7, 'debug');

-- Add foreign key constraints
ALTER TABLE system_events
    ADD CONSTRAINT fk_facility FOREIGN KEY (Facility) REFERENCES facilities (facility_id);
ALTER TABLE system_events
    ADD CONSTRAINT fk_priority FOREIGN KEY (Priority) REFERENCES priorities (priority_id);

-- A convenient view
CREATE VIEW v_system_events AS
    SELECT se.*, f.facility_name, p.priority_name
    FROM system_events se
    LEFT JOIN facilities f ON se.Facility = f.facility_id
    LEFT JOIN priorities p ON se.Priority = p.priority_id;

-- Create initial partitions
CREATE TABLE system_events_2026_03 PARTITION OF system_events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE system_events_2026_04 PARTITION OF system_events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE system_events_2026_05 PARTITION OF system_events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

-- Procedure to create a new monthly partition
CREATE OR REPLACE PROCEDURE public.create_monthly_partition(p_table_name TEXT, year INT, month INT)
LANGUAGE plpgsql AS $$
DECLARE
    partition_name TEXT;
    start_date DATE;
    end_date DATE;
BEGIN
    start_date := make_date(year, month, 1);
    end_date := (start_date + INTERVAL '1 month')::DATE;
    partition_name := format('%s_%s_%s', p_table_name, year, lpad(month::TEXT, 2, '0'));

    IF NOT EXISTS (
        SELECT FROM pg_tables WHERE tablename = partition_name AND schemaname = 'public'
    ) THEN
        EXECUTE format(
            'CREATE TABLE public.%I PARTITION OF public.%I FOR VALUES FROM (%L) TO (%L)',
            partition_name, p_table_name, start_date, end_date
        );
        RAISE NOTICE 'Created partition public.%', partition_name;
    ELSE
        RAISE NOTICE 'Partition public.% already exists, skipping', partition_name;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Error creating partition public.%: %', partition_name, SQLERRM;
        RAISE;
END;
$$;

-- Procedure to create partitions for the next N months
CREATE OR REPLACE PROCEDURE public.create_future_partitions(p_table_name TEXT, months_ahead INT)
LANGUAGE plpgsql AS $$
DECLARE
    current_date DATE := CURRENT_DATE;
    target_date DATE;
    target_year INT;
    target_month INT;
    i INT;
BEGIN
    FOR i IN 0..months_ahead LOOP
        target_date := current_date + (i * INTERVAL '1 month');
        target_year := EXTRACT(YEAR FROM target_date);
        target_month := EXTRACT(MONTH FROM target_date);
        CALL public.create_monthly_partition(p_table_name, target_year, target_month);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Error in create_future_partitions for %: %', p_table_name, SQLERRM;
        RAISE;
END;
$$;

-- Procedure to drop partitions older than a specified interval
CREATE OR REPLACE PROCEDURE public.drop_old_partitions(p_table_name TEXT, retention_interval INTERVAL)
LANGUAGE plpgsql AS $$
DECLARE
    partition_name TEXT;
    dropped_count INT := 0;
BEGIN
    FOR partition_name IN (
        SELECT tables.table_name
        FROM information_schema.tables
        WHERE tables.table_name LIKE p_table_name || '_%'
        AND tables.table_name < p_table_name || '_' || to_char(CURRENT_DATE - retention_interval, 'YYYY_MM')
        AND tables.table_schema = 'public'
    )
    LOOP
        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(partition_name);
        RAISE NOTICE 'Dropped partition public.%', partition_name;
        dropped_count := dropped_count + 1;
    END LOOP;
    IF dropped_count = 0 THEN
        RAISE NOTICE 'No partitions dropped for % older than %', p_table_name, retention_interval;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Error dropping partitions for %: %', p_table_name, SQLERRM;
        RAISE;
END;
$$;

CALL public.create_future_partitions('system_events', 4);
CALL public.drop_old_partitions('system_events', INTERVAL '3 months');

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON TABLE public.system_events TO postgres;
GRANT EXECUTE ON PROCEDURE public.create_monthly_partition(TEXT, INT, INT) TO postgres;
GRANT EXECUTE ON PROCEDURE public.create_future_partitions(TEXT, INT) TO postgres;
GRANT EXECUTE ON PROCEDURE public.drop_old_partitions(TEXT, INTERVAL) TO postgres;

GRANT USAGE ON SCHEMA public TO rsyslog;
GRANT USAGE, SELECT ON SEQUENCE public.system_events_id_seq TO rsyslog;
GRANT INSERT ON TABLE public.system_events TO rsyslog;

-- Run this in the postgres database where pg_cron is installed
\connect postgres

CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA public;

-- pg_cron job to delete partitions older than 3 months
SELECT cron.unschedule('drop_old_system_events_partitions');
SELECT cron.schedule_in_database(
    'drop_old_system_events_partitions',
    '0 14 1 * *',
    $$CALL public.drop_old_partitions('system_events', INTERVAL '3 months');$$,
    'syslog'
);

-- pg_cron job to create partitions for the next 3 months
SELECT cron.unschedule('create_future_system_events_partitions');
SELECT cron.schedule_in_database(
    'create_future_system_events_partitions',
    '5 14 1 * *',
    $$CALL public.create_future_partitions('system_events', 3);$$,
    'syslog'
);

---

Full Rsyslog Server Configuration

Save this to /etc/rsyslog.conf (or inside /etc/rsyslog.d/ depending on your OS) on the server receiving logs.

# Load required modules
module(load="imudp") # UDP syslog reception
module(load="imtcp") # TCP syslog reception
module(load="imrelp") # RELP syslog reception

# Define inputs
input(
    type="imudp"
    port="514"
    rateLimit.interval="60"
    rateLimit.burst="2000"
)

input(
    type="imtcp"
    port="514"
)

input(
    type="imrelp"
    port="2514"
    maxDataSize="10k"
)

# Load PostgreSQL output module
module(load="ompgsql")

# Define the SQL template
template(
    name="LogToPgSQL" type="list" option.stdsql="on" ) {
    constant(value="INSERT INTO system_events (DeviceReportedTime, EventID, EventLogType, EventSource, Facility, FromHost, FromIpAddress, HostName, Message, Priority, RawMessage, ReceivedAt, SysLogTag) VALUES ('")
    property(name="timereported" dateFormat="pgsql" date.inUTC="on")
    constant(value="', '")
    property(name="msgid")
    constant(value="', 'syslog', '")
    property(name="programname")
    constant(value="', '")
    property(name="syslogfacility")
    constant(value="', '")
    property(name="fromhost")
    constant(value="', '")
    property(name="fromhost-ip")
    constant(value="', '")
    property(name="hostname")
    constant(value="', '")
    property(name="msg" escape="sql")
    constant(value="', ")
    property(name="syslogpriority")
    constant(value=", '")
    property(name="rawmsg" escape="sql")
    constant(value="', '")
    property(name="timegenerated" dateFormat="pgsql" date.inUTC="on")
    constant(value="', '")
    property(name="syslogtag")
    constant(value="')")
}

# Action to write to PostgreSQL
action(
    type="ompgsql" server="localhost"
    user="rsyslog" pass="YOUR_STRONG_PASSWORD_HERE"
    db="syslog" template="LogToPgSQL"

    queue.type="linkedList"
    queue.size="20000"
    queue.workerThreads="2"
)

---

Full Rsyslog Client Configuration

Save this to /etc/rsyslog.d/99-forward.conf on any Linux client you want to forward logs from. Be sure to replace 192.0.2.1 with the actual IP of your Rsyslog server.

# Forward all logs via TCP to the central server
*.* action(
    type="omfwd"
    target="192.0.2.1"
    port="514"
    protocol="tcp"
    template="RSYSLOG_SyslogProtocol23Format" # RFC 5424 format
)