log_lock_waits is the cheapest lock-contention detector PostgreSQL ships, and through version 18 it is off by default. Turn it on. PostgreSQL 19 will do it for you. The mechanism is borrowed rather than built. When a backend has to sleep on a heavyweight lock (a row, a relation, a transaction ID,…

[...]

If you run PostgreSQL on EC2, you've had two real choices until now: build from source, or fall back to whatever version Amazon Linux itself carries in its base repos. Neither matches what the rest of the PostgreSQL community gets from yum.postgresql.org — the full extension ecosystem, day-one minor releases, and a consistent layout across distributions.



That gap is closed. Amazon Linux 2023 is now a first-class target of the PGDG YUM repository, with its own build root and its own package tree, right next to Enterprise Linux, Fedora, and SUSE.

Summary first:

 

 

 

 

 

 

Continue reading "PostgreSQL RPM repo comes to Amazon Linux 2023!"

The PostgreSQL documentation on numeric contains two statements that don't sit well together:"especially recommended for storing monetary amounts and other quantities where exactness is required" — and right away: "calculations on numeric values are very slow compared to the integer types, or to the floating-point types". So the type is recommended for storing monetary amounts, and in the same breath admitted to be rather expensive.For me, as a DBMS developer, that reads as a call to action. If operations on a type are noticeably slower than on bigint, a temptation arises: couldn't we store monetary amounts as an integer number of cents and round by the standard rule? That would save a fair amount of computing resources on our database servers, wouldn't it? And what if we went all the way and used double precision?But before optimizing the type or swapping it for an integer, it's worth understanding what is actually demanded of it: by law, by data interchange formats, by application platforms. Is the exact decimal type really the standard for financial applications, if only a de-facto one? Or is it engineering folklore that can safely be worked around?Rather than rely on survey literature, let's dig into the primary sources. This task has never been a simple one, but AI agents have made it much easier. So let's roll up our sleeves and get started. If the text feels overly dry or boring — well, that's because it is. Which is why there's a table of contents, so you can quickly jump to whatever you need.

Table of contents

• What the SQL standard says

• What law and regulators require

• Financial data interchange formats

• Payment systems: scale as an attribute of the currency

• What the TPC benchmarks require

• What vendors, authorities, and practitioners say

• What's going on in ERP systems

• Where the exact decimal type is heading

• Conclusions

1. What the SQL standard says

ISO SQL has no MONEY type. And it's not just the type that's missing — the SQL standar[...]

PostgreSQL 19 Beta 3 shipped on August 13, 2026, and the release notes have been filled in as of 2026-07-18 — still marked subject to change, and the GA date isn’t announced yet, but following the project’s usual September/October cadence general availability should land within the next few weeks. That makes now the right time to read through what’s changing, the same way I did for PostgreSQL 11 through 18 a few weeks ago.

This is not a changelog dump. It’s the subset of PG 19 I think is worth knowing about before you upgrade: a handful of compatibility breaks that will bite people who don’t read release notes, and the SQL-level additions I found genuinely useful once I started poking at them. Every query below ran against a real PostgreSQL 19 Beta 3 instance — no hand-waving about syntax that might work.

log_replication_commands is log_statement = all for walsenders, with the one difference that you should turn it on. A replication connection (replication=1 for physical, replication=database for logical) speaks a small language of its own: IDENTIFY_SYSTEM, CREATE_REPLICATION_SLOT, START_REPLICATI…

Adding a 5-row lookup table to a 9-table billing query flips the plan from 0.3ms nested loops to 467ms hash joins. The cause is join_collapse_limit = 8, a default Postgres has shipped since 2005. SQL Server and MySQL don't have this cliff. Measured across three engines using ExoBench.

When dividing by zero, Postgres fails and declares you ran an illegal operation. Cast 'abc' to an integer, and get an error. Divide by NULL and the query still runs. NULL isn't even a value. It is a marker for “unknown.” When using NULL, the concept of being unknown propagates through comparisons, arithmetic, concatenation, aggregates, window functions, and WHERE clauses. The result is well-defined, but it may not be the result you had in mind.

The unofficial subtitle of this post could be: why NOT NULL constraints are serious business. One way to dodge the complications below is to never store NULL in the first place. If a column should always have a value, say so in the schema.

Let's start with a quiz: what does this return?

SELECT (NULL = NULL) = (NULL != NULL);

If you said NULL, you are right. Both NULL = NULL and NULL != NULL are unknown, so the outer = is comparing unknown to unknown, which is also unknown. Comparison operators (=, <>, and the rest) return NULL when either side is unknown. That is why SQL has IS NULL instead of = NULL: you cannot know whether two unknowns are equal, but you can test whether a value is unknown. (There is a legacy caveat! It is at the bottom.)

Keep that in mind as we walk through the rest. NULL is not a value. It is unknown.

Three-Valued Logic with OR and AND

Boolean expressions in SQL are not limited to TRUE and FALSE. Every predicate can also be NULL, meaning unknown. WHERE and HAVING keep only rows where the expression is true. Unknown is discarded the same way false is.

SELECT
  NULL = NULL              AS null_eq_null,    -- NULL
  TRUE  OR  NULL           AS true_or_null,    -- t
  FALSE OR  NULL           AS false_or_null,   -- NULL
  TRUE  AND NULL           AS true_and_null,   -- NULL
  FALSE AND NULL           AS false_and_null,  -- f
  NOT NULL::boolean        AS not_null,        -- NULL
  NULL::boolean IS UNKNOWN AS is_unknown;      -- t

OR can still be true if the other side is true. AND can still be false if the other side is fal

[...]

 Note: I am presenting a tutorial for those who want to learn SQL at this year's Texas Linuxfest (https://pretalx.com/txlf2026/talk/DQ3XW7/) on November 6th.  This is a great opportunity at a fantastic community event and I encourage you to attend.

Demo databases are good way to develop skills with Structured Query Language. Some are associate with one data store more than another, like Sakila and World with MySQL, or DVD with PostgreSQL. The Northwind database originated in the Microsoft sphere of influence but it is easy to obtain for PostgreSQL.

Step 1

Go to https://github.com/pthom/northwind_psql and download https://github.com/pthom/northwind_psql/blob/master/northwind.sql

This file has all you need.

Step 2

I am using DBeaver Enterprise Edition 26.1.0 and open the northwind.sql file. DBeaver is an amazing data tool and makes this type of project simple. 

Opening the northwind.sql file

Step 3

This is that northwind.sql file in all its glory. If you are new to SQL, take a moment and scroll through the file. This is a well structed example that you could use to model your future work (hint, hint).

The contents of the northwind.sql file

Step 4

Now we can execute the northwind.sql file to load the structure and data. 

Use Alt + X to execute the northwind.sql file

How did it go? Did it load properly. If it did, you should see something like the following report on the script's execution. 

Step 6

Try a sample query! What, you're new to SQL and don't have a sample handy? Try this:

[...]

Back on Friday, July 17th, I joined Courtney from Manning for another LinkedIn Live, this time on what to do when you inherit a bad database. The recording and the slides are now up, with a Q&A at the end.

The session was based on Chapter 11 of my book, PostgreSQL Mistakes and How to Avoid Them (Manning). This one was more of a fireside chat than a technical presentation, and if you know me, I tend to give the latter. It's a situation most people who work with data run into sooner or later, and the first thing worth saying about it is that you are not alone.

These databases come about for what we politely call historical reasons: no DBA on the team when the thing was built, rushed deadlines, organic growth, and various other reasons. There's also a thing I call architect disease, which is architectural arrogance: a data or software architect joins the team and says forget the best practices everyone keeps talking about, I know the perfect way to do this. What they build might work fine for the use case at the moment it rolls out, but people usually have trouble maintaining such designs afterwards. The symptoms are recognizable: improper database encodings, tables with a hundred columns because they were once spreadsheets, missing indexes, no constraints so the data is inconsistent, etc.

The part that matters is that assigning blame is not a strategy. What we covered instead:

• Don't panic. These are solvable problems, and never let a good crisis go to waste: you have effectively been given permission to fix things.
• Ask the humans what hurts. People are keen to complain once they find an outlet, so let them. Beware the XY problem while you listen: when someone asks for partitioning or a Kafka pipeline, find out what they are actually trying to solve before you build it.
• Examine everything: the schema with pg_dump, pgAdmin or DBeaver, and the data with exploratory queries. The configuration, and the behavior, through logs, pg_stat_activity and pg_stat_statements.

[...]

Aggregate query statistics hide the forest of cheap queries.

While writing about the monitoring improvements in PostgreSQL 19 and preparing my new talk on Postgres observability for PostgreSQL Conference Europe in October, I noticed the system views got their own section in this release. The last time they were similarly highlighted was in PG13 and PG14. So I decided the system views need a blog of their own to go through what has changed. PostgreSQL 19 adds four new views, pg_stat_lock, pg_stat_recovery, pg_stat_autovacuum_scores, and pg_dsm_registry_allocations, each deserving more than the one-line mention they got in my monitoring blog, so here is the tour.

*Disclaimer: PostgreSQL 19 is still in beta as I write this and this area has already seen columns renamed mid-cycle; things can still change or get reverted before GA. The release notes will be the final word.*

pg_stat_lock {#pg_stat_lock}

Locks are a special interest of mine 😀 Last year, I spoke at 16 conferences with a talk called "Anatomy of Table-Level Locks in PostgreSQL". If you're interested, some of those talks were recorded and are available on YouTube. So, you can imagine how excited I was to see a new locks view in PostgreSQL 19\.

Your repository contains two version control systems.

One is Git. The other is your migration directory — a timestamped, append-only sequence of schema changes with its own applied-state tracking in the database. Two systems, two timelines, living in the same repo. And they do not synchronize.

Because migrations are files, it’s natural to assume they inherit the protections Git gives files: reviewable diffs, merge conflicts where work collides, meaningful revert, meaningful checkout. This post walks through those protections one by one and shows that, for migrations, each is quietly absent — not because Git is deficient, but because being stored in Git is not the same as being managed by Git. I think the pattern is underdiagnosed: a lot of “weird database problems” in day-to-day development are really this one mismatch wearing different costumes.

The control group: declarative schema files

To see what’s missing, look at a kind of file Git genuinely manages. A declarative schema file — schema.prisma, models.py, schema.rb, an Ent or sqlc definition — cooperates with Git almost perfectly:

• Diffs are readable. Open the PR and you see which columns changed, in place, in context.
• Conflicts happen when they should. Two people touch the same table, they touch the same lines, Git flags it. A human gets involved.
• Revert means something. Revert the commit and the declared state is restored.
• Checkout means something. Switch branches and you’re looking at that branch’s schema.

This is no accident. Git is built to manage stateful files evolving over time. A declarative schema is exactly that.

A migration directory is not that. It’s an event log wearing a file system costume. The costume is convincing — text files, in a directory, in the repo — and every Git operation degrades the moment it touches what’s underneath.

Editing is forbidden

Git’s core verb is “change this file.” Applied migrations must never change — edit one and every environment that alr

[...]

Why transactional evidence matters as AI moves from generating answers to changing business state

Imagine an AI agent handling a customer refund.

It receives the request, retrieves the order, evaluates the relevant information, decides the refund is allowed, and invokes the appropriate service. From the AI system’s perspective, the workflow succeeded because the trace shows that a decision was made and a tool was called.

The business system tells a different story. The payment operation failed. The transaction rolled back. No refund was actually committed.

So, did the AI successfully issue the refund?

That question points to a larger challenge enterprises will face as AI systems gain the authority to act.

For most of the generative AI era, we have focused on what models produce. We measure accuracy, latency, token consumption, and trace quality. That is the right focus when an interaction ends with an answer. It is not enough when the interaction ends with a business action.

The fact that an AI agent called a tool and the fact that a business transaction committed are two different facts.

As AI moves from answering questions to changing enterprise systems, we need architectures that preserve that distinction.

Observability tells you what the AI did. It does not prove what changed.

AI observability — prompts, tool calls, latency, evaluation traces — is necessary infrastructure, and enterprises are right to invest in it. An execution trace can establish that an agent invoked approve_claim() at 10:42:17. Only the authoritative claims system can establish whether claim 84721 actually moved from pending to approved.

The first describes execution. The second describes business state. A reliable AI architecture has to connect them, yet those two records often live in different systems with different identifiers, retention policies, and notions of success — which is exactly where things go wrong. A timeout might cause an agent to retry an action that actually succeeded

[...]

That was an experimental meetup: we never, ever scheduled meetups in August, especially during the last week of August! Still, when Keanya Phelps came up with the idea to have a Postgres meetup during the Djangocon.US conference, I couldn’t say no!

Until the last week before the meetup, I was unsure how many people would register and, more importantly, how many would come, but we had a full house (and yes, I didn’t take enough pictures because we had a Zoom setup crisis, so you have to trust me!)

Once again, I can’t even describe how proud I am of the Prairie Postgres community we built during these less than two years of our existence! I am so thankful to everyone who comes, listens, asks questions, and participates in discussions. I have to remind the attendees multiple times that we are about to close the house because people keep talking :). And if you were there and you can’t believe it was ever different, trust me, it was!

Nothing feels as rewarding as seeing genuine interest from listeners and hearing them thank you for organizing the event. That’s when I feel that I am doing something good

Here is the event recording:

If you’ve never been to our meetups, please consider coming! We love our new venue, and we have the same Giordano’s pizza! And we are family-friendly: we have room for kids just by our meeting room, and if you notify me in advance, we will provide childcare!

Our next meetup is on September 22! Register here!

Control what bind parameters PostgreSQL logs alongside statements and errors.

Your API accepts the change. It returns 201 Created or 200 OK, the system has saved the user's change, and the moment the user clicks, the change disappears from the app, only to resurface seconds later. That's if you are lucky. There's no error. The change simply ceased to exist for a while.

In the era of server-side-rendered applications this was a non-issue: either the state was managed as part of a single request, or the user was too slow to outrun the system. The modern application changed that. It fires off the mutation, invalidates the cache, and wakes up the state management, all in parallel and milliseconds after the write. The snappier your frontend feels, the more reliably it outruns your replica.

Throw a collaborative product into the mix and it gets worse, because every change sent over a websocket invalidates state on every teammate's open tab and device, and all of them go back to the same endpoints on the same replicas.

Some of the common workarounds are:

• pin reads to the primary (which you really want to avoid)
• add sleep delays
• set flags in Redis

Your application has to act like a traffic conductor. It guesses the replication lag using timeouts and Redis flags. The replica already knows exactly where it stands; your code just has no way to ask.

PostgreSQL 19 adds a way to ask. On the standby:

WAIT FOR LSN '0/554D1B78';

The standby blocks until it has replayed that position, then returns and lets the next statement run. That is all a reader needs to know to follow the numbers below.

There is a great deal more to it, and my friend Gülçin Yıldırım Jelínek wrote it up last week: why it has to be a top-level command rather than a function, the self-deadlock that rule prevents, and the 2016 proposal it grew out of. Read hers for that; it is the account I kept failing to write, and it saved me a lot of work. Everything below is what happened when I put the statement in front of traffic and measured it.

What makes a replica lag

Replica lag is a comb

[...]

When dividing by zero, Postgres fails and declares you ran an illegal operation. Cast 'abc' to an integer, and get an error. Divide by NULL and the query still runs. NULL isn't even a value. It is a marker for “unknown.” When using NULL, the concept of being unknown propagates through comparisons, arithmetic, concatenation, aggregates, window functions, and WHERE clauses. The result is well-defined, but it may not be the result you had in mind.

The unofficial subtitle of this post could be: why NOT NULL constraints are serious business. One way to dodge the complications below is to never store NULL in the first place. If a column should always have a value, say so in the schema.

Let's start with a quiz: what does this return?

SELECT (NULL = NULL) = (NULL != NULL);

If you said NULL, you are right. Both NULL = NULL and NULL != NULL are unknown, so the outer = is comparing unknown to unknown, which is also unknown. Comparison operators (=, <>, and the rest) return NULL when either side is unknown. That is why SQL has IS NULL instead of = NULL: you cannot know whether two unknowns are equal, but you can test whether a value is unknown. (There is a legacy caveat! It is at the bottom.)

Keep that in mind as we walk through the rest. NULL is not a value. It is unknown.

Three-Valued Logic with OR and AND

Boolean expressions in SQL are not limited to TRUE and FALSE. Every predicate can also be NULL, meaning unknown. WHERE and HAVING keep only rows where the expression is true. Unknown is discarded the same way false is.

SELECT
  NULL = NULL              AS null_eq_null,    -- NULL
  TRUE  OR  NULL           AS true_or_null,    -- t
  FALSE OR  NULL           AS false_or_null,   -- NULL
  TRUE  AND NULL           AS true_and_null,   -- NULL
  FALSE AND NULL           AS false_and_null,  -- f
  NOT NULL::boolean        AS not_null,        -- NULL
  NULL::boolean IS UNKNOWN AS is_unknown;      -- t

OR can still be true if the other side is true. AND can still be false if the other side is fal

[...]

Did you know: PG19 is on track to contain the most changes to pg_catalog in history

Introduction

pg_catalog is one of the most important interfaces PostgreSQL gives us: it exposes the metadata that describes database structure – tables, columns, indexes, constraints, types, and dependencies – alongside views into what the server is doing right now. When diagnosing replication lag, long-running or blocked queries, idle transactions, lock contention, vacuum activity, and other real-time performance problems, pg_catalog is often where I’m spending my time poking around.

Every so often, working with those catalogs leads me to a question that sounds like it should have a quick answer:

I wonder when that changed.

When was leader_pid added to pg_stat_activity? Has pg_locks always had waitstart? Which catalogs and views will be new in PostgreSQL 19?

Those answers are available in the PostgreSQL documentation, tracking changes over time isn’t very trivial. Opening the documentation for several releases, comparing tables, and then checking release notes to understand what happened – this can get tedious very quickly.

I wanted something simpler, and pg-catalog-almanac, a browsable representation of the PostgreSQL documentation for pg_catalog hopefully accomplishes that.

---

Inspiration

postgresqlco.nf is my favorite PostgreSQL reference outside of the official documentation. It makes the history of configuration parameters easy to explore: choose a setting, see the versions, and quickly understand how it evolved. And then there are useful links to articles as well.

I wanted that same experience for PostgreSQL’s system catalogs, system views, and statistics views. pg-catalog-almanac may not be as feature-rich as postgresqlco.nf but I hope it gets close – it currently covers all 143 documented relations across PostgreSQL 9.6 through the upcoming PostgreSQL 19.

---

Some Interesting Discoveries

Once I got the versions placed next to each other, I found these observations

[...]

Three parameters control what reaches your server log: a severity floor, whether to attach the triggering SQL, and how many fields each message prints.

A log line has two parts: the message, which PostgreSQL decides, and everything in front of it, which you do. log_line_prefix is the join key. Every fact that lets you connect a line to a session, a transaction, a client, or a moment in another system’s logs has to be in the prefix, because the m…

I finished patch that integrates Lua language to psql client https://github.com/okbob/lua-psql I wrote two examples. Second is a wrapping VACUUM command. Target is a more readable output in verbose mode. One bonus is printing progress - any second the pg_stat_progress_vacuum is selected and result is printed.

function vacuum(args)
  local options = {}
  local tables = {}

  if args == "help" then
    print "\\lua vacuum {verbose=true}"
    return
  end

  if args then
    local transfx = load("return " .. args)
    options = transfx()
  end

  local query = 
[[SELECT quote_ident(n.nspname) AS nspname,
         quote_ident(c.relname) AS relname
  FROM pg_class c, pg_namespace n
 WHERE n.oid = c.relnamespace
  AND n.nspname 

 'information_schema'
  AND n.nspname !~ '^pg_toast'
  AND c.relkind IN ('r','s','n')]]

  local rs, err = psql.exec(query)
  local data = rs:fetch()
  while data do
    table.insert(tables, { nspname = data.nspname, relname = data.relname } )
    data = rs:fetch()
  end
  rs:clear()

  local n = 0

  local mycon = psql.connect():clone()
  rs, err = mycon:exec("select pg_backend_pid()");
  local data = rs:fetch()
  local pid = data.pg_backend_pid

  for _, tbl in ipairs(tables) do
    if options.verbose then
      print(string.format("\27[7mVACUUM %s.%s \27[27m", tbl.nspname, tbl.relname))
      mycon:sendquery("vacuum verbose " .. tbl.nspname .. "." .. tbl.relname)
      mycon:sendquery("select pg_sleep(20)")
    else
      print(tbl.nspname .. "." .. tbl.relname)
      mycon:sendquery("vacuum " .. tbl.nspname .. "." .. tbl.relname)
    end

    mycon:consumeinput()
    isbusy = mycon:isbusy()
    while isbusy do
      n = n + 1
      if mycon:resultwait(1) == 0 then
        rs, err = psql.connect():exec(string.format("SELECT * FROM pg_stat_progress_vacuum WHERE pid = %d", pid))
        if err then
          error(err)
        end
        data = rs:fetch()
        if data then
          io.write(string.format("\rphase: %s, blks total: %d, blks scanned: %d, indexes total: %d, indexes processed

[...]

PostgreSQL's log rotation has three parameters that only make sense together: one rotates at clock boundaries, one at file sizes, and one decides whether to…

[...]

Hello fellow Postgres Enjoyer. I'd like to do something a little different this week and talk a bit about how I got ensnared by the Postgres ecosystem, and what I've done with it over the years. Maybe you have something better to do with your Friday than listen to some old guy reminiscing about Postgres, but I promise you it'll be worth the read.There's a reason I've been a dedicated Postgres zealot for over 20 years now, and it definitely isn't because of the name.

Postgres Sucks!

One of the blessings and curses of the internet is that anything you say within its confines lives in perpetuity. Or perhaps in my case it's better to describe it as infamy. Now, before I start incriminating myself, I actually contributed to Postgres in a tiny way as early as 2002 with Postgres 7.3 when I submitted a  utility.The actual story behind that utility is something else entirely. It all actually started in this thread where I adamantly defended Oracle (lol) and then moaned constantly about how much  sucks. Here's a fun excerpt:Yes, I've always been sort of obnoxious. Yes, I've mellowed out immensely over the years. And yes, it's hard not to giggle looking back, given that all of this started over 2GB of bloat. But thanks to that thread, I started this one regarding .Essentially what happened is that there was a bug in  way back in the 7.x days that prevented it from cleaning up indexes. So even though a table may have been reduced from 2GB to 100MB through a , the index would continue to grow. To get around this, I took the  script, renamed it to , re-tooled it a bit to rebuild indexes, and ran it on occasion to reindex every table in the database. And by "on occasion", I mean:Yes, this particular system was vastly improved by reindexing every table in the database every two hours. I can't stress enough just how bad Postgres was back then. Up until 7.1, every  required an exclusive table lock, for example. Can you imagine? Not , just regular . An exclusive lock. Ridiculous.Eventually I pushed to remove Postgres from ou[...]

In the first post we covered the headline feature of v6.0.0-beta: Prometheus exporters as a native pgwatch source. This second post covers everything that shipped alongside it: a full dashboard overhaul, a reaper core that's noticeably harder to kill, four new metrics, and a security fix worth knowing about even if you never touch Prometheus.

Dashboards, reimagined

Every bundled dashboard has been rebuilt on Grafana's new v13 schema (dashboard.grafana.app/v2), with a unified row layout and a shared "colophon" footer across the board. That's the boring-sounding part. The visible part is that Prometheus sources — the ones from post one — now get first-class dashboard parity with Postgres, not an afterthought.

New Prometheus-side dashboards in this release include:

• Lock Details mirrors the Postgres v13 "Locks (PG19+)" dashboard: lock waits/sec by type, average wait time per acquisition, fast-path lock pool exceeded counts, and time since the last pg_stat_lock reset.
• Server Log Events displays server log events by database and by whole instance, plus "Top ERROR generating DBs" and "Top FATAL generating instances" panels that link straight into the per-database overview dashboard.
• Postgres Version Overview is a single "Monitored DBs by version" panel driven off the settings metric, for fleets where "which Postgres version is running where" is a recurring question.
• Stored Procedures dashboards, matching the existing Postgres sproc views.

A brand new Patroni Cluster Overview, now with per-cluster health summaries, node role and leader-lock panels, DCS-last-seen age, and WAL write/received/replayed location. All this is fed by the new patroni Prometheus preset from post one.

If you're running a Patroni cluster, that last one is worth a look on its own: "Clusters Without Leader" and "Paused Clusters" panels turn split-brain risk into a single number that should read zero.

A reaper that doesn't choke under load

The other half of this release is entire

[...]

All three of these do nothing unless logging_collector is on; they describe the files the collector writes. Where, what they’re called, and who can read them. The defaults for all three are fine for a laptop and wrong for a server, and the reasons are more mechanical than the documentation lets o…

A pg_dump data-only restore into a fresh, empty copy of the schema is the safest-sounding restore in PostgreSQL, since there is not a single row in the target for the incoming data to collide with. It stops anyway, on a table sitting plainly in \dt.

psql:/tmp/data.sql:56: ERROR:  relation "book_audit" does not exist
LINE 1: INSERT INTO book_audit(book_id, note) VALUES (NEW.id, 'inser...

book_audit exists. The statement that cannot see it never appears in the dump, because it lives inside an AFTER INSERT trigger on books, and the reason a bare table name stops resolving is one line the dump wrote at the top of itself before any data moved.

SELECT pg_catalog.set_config('search_path', '', false);

That pins search_path to the empty string for the rest of the session, and every unqualified table reference inside a trigger body goes down with it. The dump breaks its own restore.

Folklore warns about a different failure here, the one where the trigger fires successfully during the books load and collides with the audit rows the dump is putting back. That failure is real. You meet it second, and only in a session whose triggers can still resolve their own tables.

Reach for one of three levers at this point. --disable-triggers bakes trigger suspension into the file, one transaction with SET CONSTRAINTS ALL DEFERRED fixes load order, and session_replication_role = replica switches enforcement off for the session. Two of the three bill you in a currency nobody mentions until the restore is half done.

Key Takeaways

• The dump's own preamble pins search_path to the empty string, so a trigger function that names its own tables without a schema breaks the restore with relation ... does not exist, even into a target holding zero rows.
• A trigger error rolls back the entire COPY it interrupted, leaving that table at zero rows while every other table in the file loads normally around it.
• The trailing setval calls fire whether or not the matching table loaded, so a sequence can sit at 5 abov

[...]

[...]

PostgreSQL's logging collector is a small pipe-based daemon that prevents message loss and garbling—but turning it on requires a restart, which you'll want on…