When a team asks me to look at their MySQL to PostgreSQL migration plan, the first thing I check is not the data pipeline. It is the output of a routines inventory, because stored procedures, functions, and triggers are the one part of the migration that no tool automates. pgloader will move your tables and data beautifully and will not touch a single procedure. Every routine is a manual port, a rewrite, or a deliberate deletion, and the count of them is the honest size of this phase.
The good news, having ported a fair amount of MySQL procedural code, is that PL/pgSQL is the more capable language, and most routines get shorter and clearer in translation. The bad news is that the translation is never mechanical: the two languages differ in structure, error handling, and trigger architecture in ways that reward understanding over regex. Here is the map, accurate for MySQL 8.x and PostgreSQL 16/17.
First, take inventory
Start by finding out what you actually have. On MySQL, information_schema.ROUTINES lists procedures and functions, and information_schema.TRIGGERS lists triggers; SHOW CREATE PROCEDURE gets you each body. Sort the inventory into three piles: routines the application actually calls (check, some are dead), routines that are data-maintenance jobs, and triggers. In my experience the inventory is always a surprise, half-forgotten routines from previous eras, and pruning the dead ones before porting is free scope reduction.
The delimiter dance versus dollar quoting
Anyone who has written a MySQL procedure knows the DELIMITER incantation. Because the mysql client splits statements on semicolons, and a procedure body is full of semicolons, you temporarily retrain the client. This is a client-side hack, not part of SQL, and it confuses every tool that does not implement it.
PostgreSQL solves the same problem in the language itself: a function body is a string, and dollar quoting lets you write that string without escaping anything. The whole body sits between $$ markers, semicolons and all, and any client that can send one statement can create a function. There is no delimiter concept to port; you simply stop needing it. One structural note before the example: MySQL has PROCEDUREs (called with CALL) and FUNCTIONs. PostgreSQL has both too, since version 11, and the distinction that matters is transaction control, which we will get to. Most MySQL procedures that just read and write data port naturally to either; a routine that returns a value should become a function.
-- MySQL
DELIMITER $$
CREATE PROCEDURE archive_stale_sessions(IN cutoff_days INT)
BEGIN
DECLARE done INT DEFAULT 0;
DELETE FROM sessions
WHERE last_seen_at < NOW() - INTERVAL cutoff_days DAY;
SET done = ROW_COUNT();
INSERT INTO maintenance_log (task, rows_affected, ran_at)
VALUES ('archive_stale_sessions', done, NOW());
END$$
DELIMITER ;-- PostgreSQL
CREATE OR REPLACE PROCEDURE archive_stale_sessions(cutoff_days int)
LANGUAGE plpgsql
AS $$
DECLARE
done bigint;
BEGIN
DELETE FROM sessions
WHERE last_seen_at < now() - make_interval(days => cutoff_days);
GET DIAGNOSTICS done = ROW_COUNT;
INSERT INTO maintenance_log (task, rows_affected, ran_at)
VALUES ('archive_stale_sessions', done, now());
END;
$$;Small translations to notice: ROW_COUNT() becomes GET DIAGNOSTICS, interval arithmetic changes shape, and variable declarations move into a dedicated DECLARE section. None of it is hard; all of it resists find-and-replace.
Error handling: handlers versus exception blocks
This is the deepest structural difference. MySQL error handling is declarative: you DECLARE a CONTINUE or EXIT HANDLER for a condition, SQLEXCEPTION, NOT FOUND, a specific SQLSTATE, at the top of a block, and it fires wherever that condition occurs later. PL/pgSQL error handling is structural, like try/catch: a BEGIN block gets an EXCEPTION section naming conditions such as unique_violation, and control jumps there when a statement in the block raises.
Two consequences matter in practice. CONTINUE handlers have no direct equivalent: a MySQL handler that logs and resumes at the next statement must become, in PL/pgSQL, a tight BEGIN/EXCEPTION block around just the statement that may fail, so execution continues after it. Blanket EXIT handlers translate more directly to an EXCEPTION section at the routine's end. And there is a performance subtlety worth knowing: a PL/pgSQL block with an EXCEPTION clause creates a subtransaction under the hood, which has real cost in hot loops. Wrap the statement that needs protection, not the whole loop body.
The signaling direction also renames: MySQL's SIGNAL SQLSTATE with MESSAGE_TEXT becomes RAISE EXCEPTION with an optional ERRCODE. And MySQL's NOT FOUND handler, which exists mostly to terminate cursor loops, usually disappears entirely, because the idiomatic PL/pgSQL replacement for a cursor loop is FOR rec IN SELECT ... LOOP, which ends on its own. A ported routine losing its cursor, its done flag, and its NOT FOUND handler in one stroke is the single most satisfying transformation in this whole exercise; the FOUND variable covers the remaining did-that-statement-match-anything checks.
Triggers: a different architecture, not just different syntax
MySQL triggers are inline: the body lives in the CREATE TRIGGER statement. PostgreSQL splits this in two, a trigger function (returning the pseudo-type trigger) and a CREATE TRIGGER that binds it to a table and event. The split feels like ceremony until you have five tables needing the same audit behavior, and then one shared trigger function replaces five copied bodies.
Capability differences to plan around: PostgreSQL row triggers can modify or suppress the row by returning a changed NEW or NULL, and PostgreSQL adds statement-level triggers with transition tables (REFERENCING NEW TABLE), which turn per-row audit writes into one set-based insert. One behavioral difference deserves a hunt through your assumptions: MySQL triggers do not fire for rows changed by foreign key cascades, while PostgreSQL triggers do. Any MySQL schema combining ON DELETE CASCADE with audit triggers has been silently missing the cascaded rows; after the port, those rows appear. That is a correctness improvement, but only if the audit consumers expect it.
Transaction control: the rule that surprises everyone
MySQL procedures can freely START TRANSACTION and COMMIT. In PostgreSQL the rule is sharp: functions always run inside the caller's transaction and can never commit; procedures (PostgreSQL 11+) may COMMIT and ROLLBACK, but only when invoked via CALL outside an enclosing transaction. A ported MySQL routine that commits mid-flight, the classic pattern is batch maintenance committing every N thousand rows, must become a PostgreSQL procedure, and its call sites must not wrap it in a transaction. Alternatively, move the batching loop to the application and keep the per-batch work in a plain function. Find these routines early; they constrain design rather than syntax.
The real question: should this logic stay in the database?
A migration is a rare chance to relitigate decisions, and every routine deserves the question: port it, or move it to the application? My working split, having seen both extremes fail: logic that is about the data belongs in the database, set-based maintenance, integrity enforcement that must hold across all callers, audit trails, anything where dragging millions of rows to the app to make a decision is absurd. Logic that is about the business, pricing rules, workflow state machines, anything product managers iterate on, belongs in the application, where it gets code review, tests, deployment pipelines, and observability as a matter of course.
The anti-pattern is porting everything reflexively because it exists. If a 400-line procedure encodes order fulfillment workflow, the migration is the cheapest moment you will ever get to move it into a service. If a 10-line trigger maintains a denormalized counter, port it and move on. Whatever you decide per routine, write the decision down; the migration review checklist has a slot for exactly this inventory, and PostgreSQL will happily support either answer.
After the port: MonPG baselines the routines you kept
The routines you port become part of the PostgreSQL workload, and they need the same day-one baseline as the rest of it. A trigger that was cheap in MySQL can behave differently under PostgreSQL's MVCC, a ported maintenance procedure can interact badly with autovacuum, and none of that is visible without query-level history on the new engine.
MonPG monitors PostgreSQL only, and that is the point: stand it up at cutover and the ported routines show up in pg_stat_statements history alongside everything else, with wait events, lock chains, and autovacuum context to explain what they cost. When the nightly procedure and autovacuum collide at 2 a.m., you want the evidence trail, not a guess. The PostgreSQL monitoring guide covers the baseline signals worth collecting from the first hour your PL/pgSQL runs in production.