DEV Community: alexey.zh

A Long Story about how I dug into the PostgreSQL source code to write my own WAL receiver, and what came out of it

alexey.zh — Sat, 18 Apr 2026 03:36:23 +0000

Some thoughts are unpredictable.

For example:
"I wonder how pg_receivewal works internally?"

From the outside, it sounds almost innocent. Really, what could possibly be wrong with that? Just ordinary engineering curiosity. I will take a quick look,
understand the general structure, satisfy my curiosity, and then go on living peacefully.

But then, for some reason, this happens:
you are already building PostgreSQL from source, digging into receivelog.c, comparing the behavior of your little creation with the original step by
step, arguing with fsync, looking at .partial files like old friends, and suddenly discovering that you are writing
your own WAL receiver.

In short, everything started quite normally and with absolutely no signs of anything serious.

Why PostgreSQL in the First Place

I have been using PostgreSQL as the main DBMS in almost all of my projects for a long time - both personal and work-related. And the longer you
work with it, the more clearly you understand: this is not just a "good database". This is a system designed by people with a very
serious engineering culture.

When you read notes, discussions, and articles from PostgreSQL developers, you quickly notice how deeply they think through
changes, trade-offs, new features, and behavior in complex scenarios. After such materials, I usually
had a mixed feeling:

admiration
respect
and a slight feeling that I had once again looked at work of a level unreachable for me

PostgreSQL gives you everything you need out of the box for backups and continuous WAL archiving. Including
pg_receivewal - the utility that eventually set everything in motion for me.

Why Exactly `pg_receivewal`

Because it is a very good utility. And good utilities are especially dangerous: they make you want to understand exactly how they
are built.

pg_receivewal continuously receives WAL segments, can work in synchronous and asynchronous replication modes, and in general
looks fairly straightforward. From a distance.

Up close, it turns out that there are quite a few subtle things there:

how the main loop starts
how connection drops are survived
how restart is performed
at what point .partial becomes a complete WAL file
how timeline switching is handled
where and when important fsync calls must happen
what to do so that it is reliable, not slow, and not embarrassing

So, as usual: a simple utility with a decent amount of engineering accuracy hidden around it.

A Few Words About Other Good Solutions I Looked at With Respect and Envy

Before writing something of my own, of course, I spent a lot of time looking at already existing solutions.

I use two of them at work for continuous archiving of the most critical and main databases.

`pgBackRest`

pgBackRest is, without exaggeration, an engineering tank. Everything in its source code is impressive:

logging
testing
architectural discipline
incremental and differential backups
support for large installations
attention to edge cases

And, of course, validation by the community and by time.

When you read the code of this tool, you catch yourself thinking: yes, this is what a product
written by people who know what they are doing looks like.
And then you open your own repository and immediately become humble.

`Barman`

I like Barman for a different reason.
It does not try to magically solve everything in the world.
It is, essentially, a very understandable orchestrator around standard PostgreSQL tools: pg_receivewal and pg_basebackup.

It has a quality that I value a lot: a simple and reliable model.
Not "everything at once", but careful automation around already existing, proven tools.

This also strongly influenced how I started thinking about my own tool.

Why Go, If I Had to Look at So Much C

I decided to write my tool in Go.

The reasons are fairly ordinary:

recently, I have really enjoyed writing in this concise language
simplicity and a UNIX background
it is convenient for writing network and system-level things
concurrency is handled well in it
it fits cloud-native scenarios very naturally
and, importantly, it is still a little harder to accidentally shoot yourself in the foot with a grenade launcher

But there is an important nuance: to understand PostgreSQL, I had to seriously dig into C code.

And here I want to separately say something I formulated for myself a long time ago:
C is, in my opinion, both the most difficult and the most brilliant language at the same time.

I have not spent as much time on any other language trying to understand its semantics.
Syntax is nothing - semantics are everything. Pointers alone are a simple concept, but
hide a whole chain of icebergs underneath. There was even a time when I was making a compiler for C, with a preprocessor,
assembler, and PE32 output (*.exe). I played with that for a long time; it was a very interesting experience and time spent happily.

The C language is so direct, so honest, and so close to the metal that it becomes scary. It feels like
it is very easy to make six sextillion mistakes in it just while opening a file and taking a breath. One pointer going the wrong way -
and that is it, hello, a new form of humiliation. Segmentation Fault becomes a kind of spell that must not be said out loud, lest you
summon it.

With all that said, I cannot say that I know C.
Honestly, I probably know about three percent of it. And even that only on a good day.

But even those three percent were extremely useful to me.
Without them, I would not have been able to read PostgreSQL properly: to separate real logic from my own delusions,
follow the control flow, and at least roughly understand why everything here is arranged this way and not another.

So formally I wrote the tool in Go, but in practice this project also became my way of touching C a little more deeply

and gaining even more respect for the people who have been writing such systems in it for years.

The Beginning: Compiling PostgreSQL, Debugging, and the First Signs of Recklessness

To understand the implementation details at all, I had to go into the PostgreSQL source code.

I had to learn how to:

build PostgreSQL from source
run it in debug mode
attach a debugger
watch how calls flow
understand what happens inside the replication loop
establish the relationship between components and functions

And here I got a surprise: all of this turned out to be less scary than I had imagined. PostgreSQL built, pg_receivewal
started, the debugger attached to the process, and this immediately gave me the dangerous confidence that "well,
now I will definitely figure this out quickly".

Of course, I did not figure it out.

The first thing I did was, like a true amateur, add the most aggressive tracing possible. I logged everything:

function entries
exits
variable values
branches
important calls
and sometimes, it seemed, the mere fact that the universe existed

At first, it seems very clever. Then you have gigantic logs, you no longer understand whether you are reading the system or whether it is slowly
breaking your mind, and the realization comes: many logs do not mean much understanding.

But at this stage, the overall picture started to emerge. I began to understand how entities are connected, where the WAL receiving
loop starts, how errors are survived, what happens to .partial, and at which moments decisions are made about completing a segment.
I discovered libraries, very well-written and years-polished file handling functions, and many more insanely cool things for
the piggy bank of my mind.

And at some point I could not resist: enough watching, time to write.

The First Prototype: "I Will Just Reproduce `pg_receivewal`"

I had a very naive idea: not to invent anything new, but simply to reproduce the behavior of
pg_receivewal as closely as possible.

In theory, it sounds wonderful.
In practice, it means that you voluntarily sign up for weeks of studying:

exactly how the streaming loop starts
how it reacts to connection drops with the database
what a correct restart should look like, from which file and from which offset inside it
when a .partial file can be considered complete
how timeline changes are handled
where you misunderstood something
and where you no longer understand anything at all, but continue out of stubbornness

My first more-or-less stable prototype appeared after a couple of weeks. And those were very fun weeks. At times I
felt like a researcher and a super-cool mega-hacker, at other times - like a person who crawled into an aircraft engine without a license to repair it
using someone else's notes.

But there is one thing I really want to point out: PostgreSQL code is surprisingly pleasant to read. Good comments, competent
decomposition, respect for the reader and colleagues. Even if you yourself understand about twenty percent, it is still clear that in front of you is very
strong engineering work.

When You Realize That Simply Receiving WAL Is Only the Beginning

When the prototype finally worked, the joy did not last long.

Because I already understood: receiving WAL is only half the job. And then the usual engineering carnival begins:

compression
encryption
uploading to S3
uploading to SFTP
cleaning up old files
monitoring
external scripts
cron
more scripts
and then scripts that fix the previous scripts

And I have never liked this universe of external glue. Because it almost always looks like it was written
at night under the threat of a production incident, and then everyone was afraid to touch it. And all of it smells bad and looks disgusting.

Scripts around WAL archiving are often fragile, non-obvious, poorly tested, and live on faith that "it somehow
works". And in critical things, I wanted exactly the opposite.

I wanted the main program itself to manage the archive:

to know what can already be compressed
to know what still cannot be deleted
to understand when a file can be sent to remote storage
and not to try to make such decisions through a layer of suspicious bash magic

So management components began to appear around the WAL receiver:

one receives the log
another archives and encrypts
a third sends files to S3 or SFTP
a fourth handles retention and automatic cleanup
a fifth collects metrics and monitors process state

And at that point, the project stopped being "just a utility". It started turning into a small system where coordination,
order, and the absence of internal fights between components mattered.

About Base Backup: I Did Not Want To, but Curiosity Won

Initially, I had no intention of implementing base backup at all.

The reason is simple: the replication protocol is single-threaded. For small databases, that is fine. For large ones - not so rosy anymore.
If a backup takes ten hours every ten hours, that is, to put it mildly, not always convenient.

Multi-threaded approaches usually require the tool to live next to the database itself. And I wanted exactly the opposite: to remotely
collect WAL and make backups from databases located anywhere - in the cloud, on virtual machines, in Kubernetes - and at the same time not
require sidecar containers or any special infrastructure changes from them.

But then the thing that happens to many technical projects happened:
I did not plan this functionality, and then it simply became interesting.

In the end, I did implement streaming base backup. It does not claim to be a universal solution for huge
installations, but for databases around 200 GiB it turned out to be quite practical. A couple of hours for a nightly job is already a reasonable
scenario.

So it turned out not to be a "superweapon", but an honest working tool in a clear niche.

Why I Did Not Go Deeper Into Incremental Backups

Of course, I also looked at incremental / differential backups.

But there you quickly understand an unpleasant thing: taking an incremental backup is not victory yet. You then have to
assemble it back correctly. And that means a completely different level of complexity begins:

either write your own analogue of pg_combinebackup
or very carefully depend on an external tool
or drown in the number of edge cases and incompatibilities

At that point I honestly looked at the task and decided that I already had enough problems without it.

pgBackRest does such things in a truly well-thought-out way. But reproducing that level is not "built over a couple of
weekends on enthusiasm". It is large, heavy engineering work for years. So I consciously stopped at a simpler
model: reliable base backup for small and medium production environments.

Without claims to world domination. Just a working, predictable thing.

Architecture: The Moment When You Are No Longer Writing a Utility but Coordinating Chaos

As soon as you have several background processes, it immediately becomes clear that the main difficulty is no longer WAL as
such, but making sure this whole household does not fight with itself.

You need to be able to:

not start a backup if another one has not finished yet
not start archiving if it is already running
not delete something that may still be needed
handle errors correctly
carefully stop background processes
keep the system in a predictable state

Here I had to seriously think about patterns:

job queue
worker pool
supervisor
pipes
task lifecycle management
safe shutdown
goroutine coordination

At some point I realized that I was no longer "writing a WAL receiver". I was assembling a gearbox. And if even one gear
shifts a little, all of this will either start screaming or silently break. And silently breaking software is the worst kind of software.
At the same time, the main task was to make sure the main WAL receiving process was not affected by "noisy neighbors".

Streaming Large Files: Another Source of Creativity

There is another pleasant task as well: transferring large backup files to remote storage.

When a database weighs, for example, 300 GiB, you quickly understand:

you do not want to save everything locally, and often it is not convenient
you cannot pull it all into memory
you also do not want to write a crooked intermediate scheme, because you will have to maintain it yourself later

So you need a proper streaming pipeline: read the data, transform it on the way, and immediately send it further - without
intermediate garbage, without extra storage, without special effects.

Here Go was useful again. It has good primitives for streaming processing. Although the presence of primitives, of course, does not
stop you from making design mistakes for a very long time.

`fsync`: The Most Subtle Part and My Own Little Nervous Breakdown

If I had to choose what drained the most blood from me, the winner is obvious: fsync.

This is the place where you first think: "well, this part is simple". And then you discover that you have been staring at
the receivelog.c source code for several hours with the expression of a person who has voluntarily entered a very strange stage of life.

The problem here is that it is easy to be wrong in both directions:

call fsync too often - everything slows down
call it too rarely - later you may look at the result very sadly

So it is either slow or shameful. Quite a rich choice, to put it mildly.

I had to literally compare the behavior of my implementation with pg_receivewal step by step:

where exactly synchronization happens
at what moment
why exactly there
which scenarios must force fsync
and how to do neither too much nor too little

In the end, the key points turned out to be:

fsync after finishing writing a segment
fsync when renaming .partial to the final WAL file
fsync on keepalive if the server requests a reply
fsync on errors in the receiving loop

Then the truly fun part began: integration checks. I ran two receivers simultaneously (pg_receivewal, pgrwl), generated
WAL, compared timings, then compared the resulting files byte by byte, measured timing differences in milliseconds, and tried to remove
everything unnecessary.

I even got to logging: in places like this, you begin to understand that it can be either a helper or a quiet
saboteur. For example, you do not need to parse attributes if the logging level does not require it; extra CPU cycles
can be spent on more useful things.

In the end, I managed to achieve very similar behavior and complete matching of the resulting WAL files over the same interval. And
the small timing difference remained only where it is normal: two daemons cannot be started in the exact same
physical microsecond, no matter how hard you try.

In the fight against slowness, I even quickly wrote a small utility that injects
a defer into EVERY function, where the runtime of that function is measured. Not the best check,
but, as practice showed, it helps quickly identify especially hot functions, and then point
the profiler, debugger, and so on at them. My tracing looks something like this:

FUNCTION                            CALLS  TOTAL_NS     TOTAL_SEC
--------                            -----  --------     ---------
storecrypt.Put                      70     23061361400  23.06
receivesuperv.uploadOneFile         35     11606918000  11.61
fsync.Fsync                         106    8813968000   8.81
xlog.processOneMsg                  4481   6818721600   6.82
xlog.processXLogDataMsg             4481   6814495400   6.81
xlog.CloseWalFile                   35     6561511500   6.56
xlog.closeAndRename                 35     6559979000   6.56
fsync.FsyncFname                    70     6525596900   6.53

.....500 more lines

Metrics: Because I Wanted to See Whether It Was Still Alive or Already Dead

Over time, I also added metrics:

number of files
archive size
number of errors
transferred bytes
state of background tasks
deleted files
general runtime statistics

I even made a Grafana dashboard. Not the most beautiful one in the world, but useful enough to quickly understand: everything is still
alive or it is already time to get nervous.

It was important to me to make metrics free if they are disabled. So wherever possible, I used the
noop approach: if observability is not needed, the system should not pay for it.

Logging: Where I Also Realized I Still Have a Long Way to Go

Logging had its own coming-of-age story.

At first, I logged everything. Because, as everyone knows, any person who has deeply entered a complex system for the first time
starts with the phrase: "I will just add more logs and understand everything".

No.

Many logs are not understanding. They are just many logs.

Good logging is when, at the moment of a problem, logs really help you understand what is going on, and do not turn into
an additional source of noise and despair.

I have not yet managed to make this part as good as I would like. The current result is normal, but
not exemplary. And in this sense, pgBackRest still remains for me an example of a very smart and thoughtful approach: you can see
how much discipline and engineering care went specifically into diagnostics.

Integration Tests: The Hardest and Most Important Part

One of the most difficult and at the same time most necessary parts of the whole project is integration testing.

Because a daemon that depends on another daemon is already not the easiest object to test. And if you
also want to:

start PostgreSQL
generate WAL
stop processes
make a backup
restore the database
compare the state before and after
run failure scenarios
check compatibility and correctness

then life starts playing in especially bright colors

I settled on this approach: simple shell scripts that start the test environment in a container,
populate the database, perform actions, then restore everything and check the result.
I also really did not want to drag a ton of dependencies like testcontainers into the project.

In the end, it turned out like this:

shell scripts
docker compose
matrix in GitHub Actions
isolated scenarios
without unnecessary heavy magic where understandable mechanics are enough

That is how I got tests for:

comparison with pg_receivewal
backup/restore
uploading to S3 and SFTP
correctness of WAL files
stopping and restarting
different failure scenarios

And honestly, integration tests are what give me the main confidence in releases. Not one hundred percent, of course. One hundred
percent in such things is promised either by madmen or by marketers. But good, engineering-honest confidence - yes.

Unit tests, of course, also exist. But for me, integration checks are the main criterion
that all of this is not only nicely written (not nicely everywhere), but actually works.

What Came Out of It

Over time, from the fairly harmless desire to "just see how pg_receivewal works", a tool grew that now has:

streaming WAL receiver
archiving
compression
encryption (streaming AES-256-GCM)
uploading to S3 (streaming, +multipart)
uploading to SFTP
retention and automatic cleanup
metrics
logging (mostly zero-cost)
base backup
configuration through a file and environment variables
controlled shutdown
unit and integration tests
behavior comparison with pg_receivewal
documentation with diagrams and examples
as many usage examples as possible (standalone/docker-compose/k8s)
helm-chart (quite simple and working)
website (in progress, but at least now it is clear how this is done and that it is possible)
a set of patterns and libraries for further reuse in Go projects

So, as usually happens, the project long ago stopped being what it seemed to be at the beginning.

What Is Planned

improve metrics, remove what is unnecessary, add what is needed, build a truly useful and beautiful dashboard
improve logging quality, make it consistent, think through levels more carefully, preserve zero-cost semantics
add new capabilities for base backup - around fine-tuning retention periods
a huge amount of space for refactoring and documentation
add even more integration tests, I am planning a V2 version
add every "breaking" scenario to the tests that my imagination can produce
make the website properly, right now it is just a copy of the documentation
create a user guide (because it is simply interesting)
and much more

What I Took Away From This

Perhaps the main result is not that I wrote yet another tool.

The main result is something else:

I understood PostgreSQL much more deeply
I gained even more respect for C, although I know about a miserable three percent of it
I saw how difficult it is to reproduce even a small part of the behavior of a well-made system utility
and once again I became convinced that high-quality code written by others is the best way to quickly cure yourself of excessive self-confidence

Because one thing is to look at architecture from the outside and admire it.
And it is a completely different thing to try to reproduce at least part of that logic yourself and not fall apart along the way.

And yes. If it ever seems to you that the thought

"maybe I should also write some utility for PostgreSQL?"
sounds like a good idea for a couple of quiet weekends -

I have two pieces of news for you.

The first: the idea really is interesting.
The second: you most likely will not have quiet weekends anymore.

Links

Repository: https://github.com/pgrwl/pgrwl

Thanks for reading!

SQL-First PostgreSQL Migrations Without the Magic

alexey.zh — Sun, 12 Apr 2026 14:29:11 +0000

If you work with PostgreSQL long enough, you start noticing a pattern: migration tools often become more complicated than the schema changes they are supposed to manage.

Some tools invent their own DSL.
Some hide behavior in config files.
Some couple migrations to an ORM.
Some force a directory layout that looks neat in a demo but awkward in a real project.

And then there is the simpler question:

Why can’t PostgreSQL migrations just stay plain SQL?

That is the idea behind gopgmigrate.

It is a SQL-first migration tool for PostgreSQL that keeps the core workflow boring in the best possible way:

write normal .sql files
organize them however you want
run them in order
track what was applied
support rollbacks
support repeatable migrations
make non-transactional migrations explicit

No YAML. No hidden DSL. No ORM lock-in. No magic comments.

Just SQL files and a clear naming convention.

Why this approach matters

A migration file should be easy to:

read in a code review
open in your editor
run directly with psql
troubleshoot at 2 AM
keep using even if you stop using the tool

That last point matters more than many teams realize.

A good migration format should outlive the tool that executes it. Your schema history is long-term infrastructure. It should not depend on a framework-specific abstraction that becomes painful to migrate away from later.

With gopgmigrate, the migration files remain usable as ordinary SQL. The tool adds safety and structure on top, but it does not take ownership of your database change process.

What gopgmigrate does

At a high level, the workflow is simple:

Scan a directory tree recursively for SQL migration files
Sort them globally by revision
Compare them with the migration history stored in PostgreSQL
Apply only what is pending
Record hashes and metadata for auditability
Support rolling back the last applied migrations
Re-run repeatable scripts only when their content changes

That gives you a clean PostgreSQL migration workflow with a small mental model.

The naming convention is the API

One of the nicest design choices in gopgmigrate is that the file name itself declares the migration behavior.

Example:

0000001-create-users-table.up.sql
0000001-create-users-table.down.sql
0000003-fn-get-users.r.up.sql
0000004-vacuum-users.notx.up.sql
0000005-refresh-stats.rnotx.up.sql

This is refreshingly explicit.

Versioned migrations

These run once in order:

0000002-add-roles-table.up.sql

Rollbacks

Rollback files are separate and predictable:

0000002-add-roles-table.down.sql

Repeatable migrations

Useful for functions, views, triggers, or other SQL objects you may want to refresh when the file changes:

0000003-fn-get-users.r.up.sql

Non-transactional migrations

Some PostgreSQL operations cannot run inside a transaction, for example:

VACUUM
CREATE INDEX CONCURRENTLY
DROP INDEX CONCURRENTLY
some forms of REINDEX
ALTER SYSTEM

Those are made explicit in the file name:

0000004-vacuum-users.notx.up.sql

And if a migration is both repeatable and non-transactional:

0000005-refresh-stats.rnotx.up.sql

This is a small detail, but it solves a real operational problem: the migration behavior is visible before you open the file.

Real projects are not flat folders

A lot of migration tools quietly assume every team wants the same directory structure.

Reality is messier.

Some teams want to split:

schema
data
functions
maintenance
environment-specific files
release-based groups

That is why I like that gopgmigrate does not force a rigid directory layout.

You can organize migrations by concern:

migrations/
  schema/
  data/
  functions/
  no-transaction/
  down/

Or by release:

migrations/
  v1.0.0/
  v1.1.0/
  down/

Or however your team naturally thinks about database changes.

The only rule is that version ordering remains global.

That is a practical compromise: freedom in layout, predictability in execution.

Why SQL-first migrations are still the best default

There is a reason SQL-first tools keep appealing to engineers who work close to PostgreSQL.

PostgreSQL already has a powerful language for schema and data changes. It is called SQL.

When a tool stays out of the way, you get a few concrete advantages:

Better reviewability

A migration diff is just SQL. Reviewers do not have to mentally decode a framework abstraction.

Better portability

You can run the file with psql, a database IDE, automation scripts, or CI jobs.

Better debugging

When something fails, you are looking at the actual statement PostgreSQL rejected.

Better longevity

Your migration history remains useful years later, even if your application stack changes.

That makes SQL-first migration tooling especially attractive for:

platform teams
backend teams with multiple services
teams that avoid ORM-heavy workflows
projects with long-lived PostgreSQL databases
teams that want plain operational ownership

Safety features that matter in practice

Simple does not mean naive.

For a migration tool to be usable in production, it needs a few guardrails. gopgmigrate includes some of the right ones:

Advisory locking

This helps prevent concurrent migration runs from stepping on each other.

Transactional safety by default

Most PostgreSQL DDL can run inside a transaction, and that is the safe default.

Explicit non-transactional mode

Instead of hiding exceptions, the tool makes them obvious in the filename.

Hash-based change detection

This is particularly useful for repeatable migrations. If the content changes, the tool knows it should re-apply the script.

History tracking

Applied migrations are recorded in a history table, along with metadata such as hash and timing-related details.

That is the kind of boring reliability you want from migration tooling.

Example CLI workflow

The CLI is intentionally straightforward.

Apply pending migrations:

gopgmigrate migrate \
  --dirname ./migrations \
  --connstr postgres://user:pass@localhost:5432/mydb

Preview without applying:

gopgmigrate migrate \
  --dirname ./migrations \
  --connstr postgres://user:pass@localhost:5432/mydb \
  --dry-run

Rollback the last migration count:

gopgmigrate rollback-count 2 \
  --dirname ./migrations \
  --connstr postgres://user:pass@localhost:5432/mydb

Use environment variables in CI:

export PGMIGRATE_DIRNAME=./migrations
export PGMIGRATE_CONNSTR=postgres://user:pass@localhost:5432/mydb

gopgmigrate migrate

That is the kind of interface that works well in local development, CI pipelines, containerized jobs, and release automation.

Where this fits especially well

I think gopgmigrate is especially appealing in a few scenarios.

1. PostgreSQL-first teams

If your team understands PostgreSQL and prefers direct SQL over framework migration layers, this fits naturally.

2. Teams with mixed migration types

Schema changes, data fixes, repeatable view/function refreshes, and non-transactional maintenance are all first-class cases here.

3. Repos with real structure

If your migration directory stopped being a cute flat demo folder a long time ago, recursive scanning and flexible layouts are genuinely useful.

4. CI/CD and automation

The CLI is simple enough to drop into pipelines without teaching your delivery system a new configuration language.

5. Engineers who dislike lock-in

Your migration files stay plain SQL. That is a strong long-term property.

What I like most about this design

The best tools often win not because they do more, but because they make fewer damaging decisions for you.

gopgmigrate seems built around a healthy principle:

the tool should manage execution, not redefine how SQL migrations ought to exist.

That means:

your files remain readable
your shell workflows still work
your database knowledge stays relevant
your migration history does not become framework glue

In database tooling, that is a strong design choice.

Final thoughts

There are plenty of PostgreSQL migration tools out there. Many are good. But a lot of them drift toward abstraction for its own sake.

If what you want is:

PostgreSQL migrations
plain SQL files
explicit rollbacks
repeatable migrations
non-transaction support
advisory locking
transactional safety
hash-based change detection
flexible directory layouts
clean CLI usage
minimal ceremony

then gopgmigrate is worth a look.

It takes a very practical path: keep migrations human-readable, keep behavior explicit, and keep the tool small enough that you can trust what it is doing.

That is a solid direction for database change management.

If you find gopgmigrate useful, consider giving the repo a star on GitHub. It helps more people discover the project.

Repository: https://github.com/hashmap-kz/gopgmigrate

Finding Hidden Bottlenecks in Go Apps: A Lazy, Hacky, and Bruteforce Method

alexey.zh — Thu, 02 Apr 2026 07:23:37 +0000

When developing pgrwl - a PostgreSQL WAL receiver - performance is a critical concern.

Every part of the program must be predictable. There should be no hidden bottlenecks.

But what about:

typos that silently degrade performance?
missing tests that fail to catch inefficiencies?
slow logic introduced "just for now"?
accidental O(n^2) behavior?

These issues are often hard to detect.

The Problem

For instance, you may concatenate a huge template in a loop, but that may be done once outside of the loop. And this will work fine, until the heavy load is reveal that.

Of course you should profile CPU/RAM, and there are a lot of great tools, but sometimes it's not enough.

The Bruteforce Idea

A lazy decision for measuring the whole picture is to to trace each function execution time, and the total number that function being called.

Yeah, that cannot help you to inspect all the loops, nasty conditions, memory leaks, etc... But may help a LOT to find as fast as possible really heavily loaded functions, and start profiling them deeply with more advanced profiling tools.

The Solution

I wrote a KISS library called gotrackfunc that injects timing into each single function in the whole project at one CLI command.

How it works

gotrackfunc injects timing code into all functions
Run your program
Apply load
Stop execution
Analyze the report

It's rough and primitive, but it works!!!

You MUST have a version control system of course, so you can drop
all these changes.

Usage And Example Output

# execute in directory of your project
gotrackfunc ./...

# run your app (a gotrackfunc.log will produced)
go run main.go

# make a report (turn gotrackfunc.log into readable form)
gotrackfunc summarize

In this example I've found that my Put() function is the slowest
part of the whole things. So I can inspect it, refactor, optimize,
write more unit-tests, write integration-tests and measure again.

FUNCTION                            CALLS  TOTAL_NS     TOTAL_SEC
--------                            -----  --------     ---------
storecrypt.Put                      70     23061361400  23.06
receivesuperv.uploadOneFile         35     11606918000  11.61
fsync.Fsync                         106    8813968000   8.81
xlog.processOneMsg                  4481   6818721600   6.82
xlog.processXLogDataMsg             4481   6814495400   6.81
xlog.CloseWalFile                   35     6561511500   6.56
xlog.closeAndRename                 35     6559979000   6.56
fsync.FsyncFname                    70     6525596900   6.53
receivesuperv.performUploads        2      3036884600   3.04
receivesuperv.uploadFiles           1      3034237400   3.03
fsync.FsyncFnameAndDir              35     435023800    0.44
xlog.WriteAtWalFile                 4481   208340500    0.21
cmd.mustInitPgrw                    1      201671300    0.20
xlog.NewPgReceiver                  1      201671300    0.20
xlog.SyncWalFile                    1      48771200     0.05
codec.Flush                         35     42261100     0.04
xlog.OpenWalFile                    36     16938600     0.02
xlog.createFileAndTruncate          36     11396900     0.01
storecrypt.ListInfo                 4      9813800      0.01
receivesuperv.performRetention      2      4906900      0.00
conv.ToUint64                       4482   2664000      0.00
receivesuperv.filterFilesToUpload   2      2647200      0.00
fsx.FileExists                      35     2628000      0.00
xlog.XLogSegmentOffset              8963   2083300      0.00
conv.Uint64ToInt64                  4517   1656300      0.00
cmd.loadConfig                      1      1563600      0.00
config.MustLoad                     1      1563600      0.00
config.mustLoadCfg                  1      1563600      0.00
pipe.CompressAndEncryptOptional     35     1278200      0.00
receivemetrics.AddWALBytesReceived  4481   1121600      0.00
codec.Close                         35     1001200      0.00
xlog.sendFeedback                   3      690700       0.00
xlog.findStreamingStart             1      608600       0.00
receivemode.Init                    1      608600       0.00
storecrypt.NewLocal                 1      522500       0.00
xlog.GetSlotInformation             2      522500       0.00
shared.SetupStorage                 1      522500       0.00
xlog.parseReadReplicationSlot       2      522500       0.00
cmd.mustInitStorageIfRequired       1      522500       0.00
codec.NewWriter                     35     504900       0.00
storecrypt.fullPath                 37     504000       0.00
xlog.XLogFileName                   36     42900        0.00
shared.InitOptionalHandlers         1      0            0.00
config.IsLocalStor                  3      0            0.00
jobq.Start                          1      0            0.00
receivemetrics.IncJobsExecuted      4      0            0.00
receivemetrics.IncWALFilesReceived  35     0            0.00
xlog.IsPartialXLogFileName          2      0            0.00
receivemetrics.ObserveJobDuration   4      0            0.00
xlog.ScanWalSegSize                 1      0            0.00
cmd.needSupervisorLoop              1      0            0.00
shared.getWriteExt                  1      0            0.00
storecrypt.transformsFromName       35     0            0.00
config.checkBackupConfig            1      0            0.00
receivemode.NewReceiveModeService   1      0            0.00
conv.ParseUint32                    2      0            0.00
storecrypt.isSupportedWriteExt      1      0            0.00
receivemode.NewReceiveController    1      0            0.00
receivemetrics.IncJobsSubmitted     4      0            0.00
config.checkMode                    1      0            0.00
storecrypt.encodePath               35     0            0.00
config.expandEnvsWithPrefix         1      0            0.00
config.checkLogConfig               1      0            0.00
xlog.IsPowerOf2                     1      0            0.00
aesgcm.NewChunkedGCMCrypter         1      0            0.00
shared.NewHTTPSrv                   1      0            0.00
xlog.XLogSegmentsPerXLogId          72     0            0.00
xlog.IsValidWalSegSize              1      0            0.00
config.checkMainConfig              1      0            0.00
config.checkStorageConfig           1      0            0.00
logger.Init                         1      0            0.00
receivesuperv.NewArchiveSupervisor  1      0            0.00
middleware.Middleware               6      0            0.00
xlog.existsTimeLineHistoryFile      1      0            0.00
xlog.IsXLogFileName                 2      0            0.00
config.IsExternalStor               1      0            0.00
receivesuperv.log                   83     0            0.00
cmd.App                             1      0            0.00
xlog.parseShowParameter             2      0            0.00
config.expand                       1      0            0.00
jobq.log                            8      0            0.00
xlog.updateLastFlushPosition        37     0            0.00
receivemetrics.IncWALFilesUploaded  35     0            0.00
strx.HeredocTrim                    1      0            0.00
cmd.checkPgEnvsAreSet               1      0            0.00
storecrypt.NewVariadicStorage       1      0            0.00
middleware.Chain                    1      0            0.00
xlog.SetStream                      1      0            0.00
jobq.Submit                         4      0            0.00
config.String                       1      0            0.00
config.validate                     1      0            0.00
jobq.NewJobQueue                    1      0            0.00
config.checkReceiverConfig          1      0            0.00
xlog.calculateCopyStreamSleepTime   3      0            0.00
middleware.SafeHandlerMiddleware    3      0            0.00
xlog.NewStream                      1      0            0.00
conv.Uint32ToInt32                  1      0            0.00
xlog.XLByteToSeg                    36     0            0.00
cmd.initMetrics                     1      0            0.00
xlog.GetShowParameter               2      0            0.00
shared.log                          1      0            0.00
xlog.log                            107    0            0.00
config.Cfg                          3      0            0.00
receivesuperv.filterOlderThan       2      0            0.00
storecrypt.decodePath               70     0            0.00
storecrypt.supportedExts            70     0            0.00
xlog.CurrentOpenWALFileName         37     0            0.00
config.checkStorageModifiersConfig  1      0            0.00
xlog.GetStartupInfo                 1      0            0.00

Conclusion

This approach is rough, primitive - and surprisingly effective.

Sometimes, brute force wins.

Backup Is Not Enough: A PostgreSQL Recovery Story

alexey.zh — Tue, 31 Mar 2026 13:57:06 +0000

This experiment is designed to test and validate the pgrwl tool in
real conditions: https://github.com/pgrwl/pgrwl

Instead of synthetic examples, we simulate a real-world failure and
verify that recovery actually works end-to-end.

Let’s do something slightly uncomfortable.

We're going to simulate a database crash and recovery.

Think disk failure. Whole server gone.

And then bring it back - byte for byte - as if nothing happened.

Not "some" data.
Not "close enough".

Everything.

The Myth of "Backups"

Most people think:

"I have a backup, so I'm safe."

That's... half true.

A base backup is just a snapshot - a frozen picture of your
database at one moment.

But databases don't sit still.

Every insert, update, delete - all of that happens after your
backup.

So where does that data live?

-> In WAL (Write-Ahead Log)

The Real Rule

If you remember one thing from this post, let it be this:

Recovery = Base Backup + WAL

Without WAL:

your backup is outdated
your data is incomplete
your recovery is a lie

The Experiment

Warning: this is not intended to be run on production environment

Note: For simplicity, both the database and the backup tool are running on the same machine. In production, you should never store backups on the same host where the database is running.

We'll prove this using a bunch of simple shell commands.

Note: env-vars are omitted for simplicity.

A full working script will be attached at the end of the article.

Step 1 --- Build a Database From Nothing

log "Initializing PostgreSQL cluster..."
initdb -D "$PGDATA" -A trust \
  --auth-local=trust \
  --auth-host=trust >/dev/null

log "Starting PostgreSQL..."
pg_ctl -D "$PGDATA" -l "$WORKDIR/pg.log" start >/dev/null
wait_for_postgres

log "Creating physical replication slot: $REPL_SLOT"
psql -d postgres -v ON_ERROR_STOP=1 \
  -c "select pg_create_physical_replication_slot('$REPL_SLOT');" >/dev/null

log "Creating test database: $DBNAME"
createdb "$DBNAME"

We start from zero.

Step 2 --- Start Capturing WAL

log "Writing pgrwl configuration..."
cat >"$PGRWL_CONFIG" <<EOF
{
  "main": {
    "listen_port": 7070,
    "directory": "$WAL_ARCHIVE_DIR"
  },
  "receiver": {
    "slot": "$REPL_SLOT",
    "no_loop": true
  },
  "log": {
    "level": "debug",
    "format": "text",
    "add_source": false
  }
}
EOF

log "Starting pgrwl receiver..."
pgrwl daemon -m receive -c "$PGRWL_CONFIG" >"$WORKDIR/pgrwl-receive.log" 2>&1 &

Starting pgrwl in a receive mode

Step 3 --- Take a Base Backup

log "Creating base backup..."
pgrwl backup -c "$PGRWL_CONFIG"

This is your snapshot in time.

Backup created by using PostgreSQL replication protocol (i.e. additional tools are not required).

Step 4 --- Populate DB

log "Initializing pgbench data (scale=10 ~ about 1 million rows in pgbench_accounts)..."
pgbench -i -s 10 "$DBNAME"

log "Running pgbench workload..."
pgbench -c 4 -j 2 -t 200 "$DBNAME"

All this data exists ONLY in WAL.

Step 5 --- Save the Truth

log "Dumping cluster state before destruction..."
pg_dumpall --quote-all-identifiers --restrict-key=0 >"$WORKDIR/before.sql"

This dump becomes our ground truth.
After restore + WAL replay, we expect the cluster to match this state.

Step 6 --- Delete Everything

log "Stopping PostgreSQL and pgrwl receiver..."
stop_postgres
stop_pgrwl_receive

log "Removing original PGDATA to simulate data loss..."
rm -rf "$PGDATA"

Database gone.

No tables.
No data.
No second chances.

Only backup and WAL remain.

Step 7 --- Restore the Base Backup

log "Restoring PGDATA from base backup..."
pgrwl restore --dest="$PGDATA" -c "$PGRWL_CONFIG"

chmod 0750 "$PGDATA"
chown -R postgres:postgres "$PGDATA"

# recovery.signal tells PostgreSQL to start in archive recovery mode.
touch "$PGDATA/recovery.signal"

We are back to snapshot state only.

Step 8 --- Replay History

log "Starting pgrwl restore server..."
pgrwl daemon -m serve -c "$PGRWL_CONFIG" >"$WORKDIR/pgrwl-serve.log" 2>&1 &
PGRWL_SERVE_PID=$!

cat >>"$PGDATA/postgresql.conf" <<EOF
restore_command = 'pgrwl restore-command --serve-addr=127.0.0.1:7070 %f %p'
EOF

log "Starting restored PostgreSQL cluster..."
pg_ctl -D "$PGDATA" -l "$WORKDIR/postgres-restored.log" start >/dev/null

wait_for_postgres
wait_until_out_of_recovery

Start pgrwl in serve mode for restore_command, run cluster, PostgreSQL starts replaying WAL.

It is replaying history.

Every insert.
Every update.
Every commit.

Reconstructed from WAL.

Step 9 --- Did It Work?

log "Dumping cluster state after recovery..."
pg_dumpall --quote-all-identifiers --restrict-key=0 >"$WORKDIR/after.sql"

log "Comparing dumps..."
if diff -u "$WORKDIR/before.sql" "$WORKDIR/after.sql" >"$WORKDIR/dump.diff"; then
  log "SUCCESS: restored cluster matches original state"
  echo "before: $WORKDIR/before.sql"
  echo "after : $WORKDIR/after.sql"
  echo "diff  : $WORKDIR/dump.diff (empty)"
else
  echo
  echo "FAIL: restored cluster differs from original state"
  echo "See diff: $WORKDIR/dump.diff"
  exit 1
fi

If there is no diff:

We recovered every single transaction.

Not approximately. Not logically. Exactly.

Mental Model

Think Git:

backup = commit
WAL = commits after
recovery = replay commits

Final Thought

If you don't understand WAL, you don't understand PostgreSQL recovery.

Using docker environment for integration tests

Integration Tests

#!/usr/bin/env bash
set -Eeuo pipefail

# setup docker-compose env
cd /tmp
git clone https://github.com/pgrwl/pgrwl.git
cd pgrwl/test/integration/environ
make restart

# exec into container
docker exec -it pg-primary bash

# run tests
su - postgres
cd scripts/tests
bash 011-basic-flow.sh

Full Script

#!/usr/bin/env bash
set -Eeuo pipefail

###############################################################################
# Simple 'Point In Time Recovery' tutorial with pgrwl
#
# What this script demonstrates:
#
#   1. Start a fresh PostgreSQL cluster
#   2. Start pgrwl in WAL receiver mode
#   3. Take a base backup
#   4. Generate more data AFTER the base backup
#   5. Save a logical dump of the final database state
#   6. Destroy PGDATA (simulate disaster)
#   7. Restore from the base backup
#   8. Replay archived WAL files
#   9. Compare the restored database with the original state
#
# Main idea:
#
#   A base backup is only a snapshot at one point in time.
#   All changes made after that snapshot live in WAL.
#   To recover to the latest committed transaction, we need BOTH:
#
#     - the base backup
#     - the WAL generated after the backup
#
###############################################################################

###############################################################################
# Configuration
###############################################################################

PGDATA="/tmp/pgrwl-basic/pgdata"
WAL_ARCHIVE_DIR="/tmp/pgrwl-basic/wal-archive"
PGRWL_CONFIG="/tmp/pgrwl-basic/pgrwl-config.json"

DBNAME="bench"
REPL_SLOT="pgrwl_v5"

export PGHOST="localhost"
export PGPORT="5432"
export PGUSER="postgres"
export PGPASSWORD="postgres"

PGRWL_RECEIVE_PID=""
PGRWL_SERVE_PID=""

###############################################################################
# Small helper functions
###############################################################################

log() {
  printf '\n[%s] %s\n' "$(date '+%F %T')" "$*"
}

die() {
  echo "ERROR: $*" >&2
  exit 1
}

wait_for_postgres() {
  log "Waiting for PostgreSQL to accept connections..."
  for _ in $(seq 1 120); do
    if pg_isready -h "$PGHOST" -p "$PGPORT" -U "$PGUSER" >/dev/null 2>&1; then
      return 0
    fi
    sleep 1
  done
  die "PostgreSQL did not become ready in time"
}

wait_until_out_of_recovery() {
  log "Waiting for PostgreSQL to finish recovery..."
  for _ in $(seq 1 120); do
    if psql -d postgres -Atqc "select pg_is_in_recovery()" 2>/dev/null | grep -q '^f$'; then
      return 0
    fi
    sleep 1
  done
  die "PostgreSQL did not finish recovery in time"
}

stop_postgres() {
  if [[ -d "$PGDATA" ]]; then
    log "Stopping PostgreSQL..."
    pg_ctl -D "$PGDATA" -m immediate stop >/dev/null 2>&1 || true
  fi
}

stop_pgrwl_receive() {
  if [[ -n "${PGRWL_RECEIVE_PID:-}" ]]; then
    log "Stopping pgrwl receiver..."
    kill "$PGRWL_RECEIVE_PID" >/dev/null 2>&1 || true
    wait "$PGRWL_RECEIVE_PID" >/dev/null 2>&1 || true
    PGRWL_RECEIVE_PID=""
  fi
}

stop_pgrwl_serve() {
  if [[ -n "${PGRWL_SERVE_PID:-}" ]]; then
    log "Stopping pgrwl restore server..."
    kill "$PGRWL_SERVE_PID" >/dev/null 2>&1 || true
    wait "$PGRWL_SERVE_PID" >/dev/null 2>&1 || true
    PGRWL_SERVE_PID=""
  fi
}

cleanup() {
  stop_pgrwl_receive
  stop_pgrwl_serve
}
trap cleanup EXIT

###############################################################################
# Phase 0. Start from a clean state
###############################################################################

log "Cleaning up old processes and files..."
sudo pkill -9 postgres || true
sudo pkill -9 pgrwl || true
sudo rm -rf "/tmp/pgrwl-basic"

log "Preparing work directory: /tmp/pgrwl-basic"
mkdir -p "/tmp/pgrwl-basic" "$WAL_ARCHIVE_DIR"

###############################################################################
# Phase 1. Create and start a fresh PostgreSQL cluster
###############################################################################

log "Initializing PostgreSQL cluster..."
initdb -D "$PGDATA" -A trust --auth-local=trust --auth-host=trust >/dev/null

cat >>"$PGDATA/postgresql.conf" <<EOF
listen_addresses      = '*'

# Settings required for WAL streaming / archiving style workflows
wal_level                = replica
max_wal_senders          = 10
max_replication_slots    = 10
wal_keep_size            = 64MB

# Durability settings
fsync                    = on
synchronous_commit       = on
full_page_writes         = on

# Basic logging settings
log_directory            = '/tmp/pgrwl-basic'
log_filename             = 'pg.log'
log_lock_waits           = on
log_temp_files           = 0
log_checkpoints          = on
log_connections          = off
log_destination          = 'stderr'
log_error_verbosity      = 'DEFAULT' # TERSE, DEFAULT, VERBOSE
log_hostname             = off
log_min_messages         = 'WARNING' # DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC
log_timezone             = 'Asia/Aqtau'
log_line_prefix          = '%t [%p-%l] %r %q%u@%d '
EOF

log "Starting PostgreSQL..."
pg_ctl -D "$PGDATA" -l "/tmp/pgrwl-basic/pg.log" start >/dev/null
wait_for_postgres

log "Creating physical replication slot: $REPL_SLOT"
psql -d postgres -v ON_ERROR_STOP=1 \
  -c "select pg_create_physical_replication_slot('$REPL_SLOT');" >/dev/null

log "Creating test database: $DBNAME"
createdb "$DBNAME"

###############################################################################
# Phase 2. Configure and start pgrwl in receive mode
###############################################################################

log "Writing pgrwl configuration..."
cat >"$PGRWL_CONFIG" <<EOF
{
  "main": {
    "listen_port": 7070,
    "directory": "$WAL_ARCHIVE_DIR"
  },
  "receiver": {
    "slot": "$REPL_SLOT",
    "no_loop": true
  },
  "log": {
    "level": "debug",
    "format": "text",
    "add_source": false
  }
}
EOF

log "Starting pgrwl receiver..."
pgrwl daemon -m receive -c "$PGRWL_CONFIG" >"/tmp/pgrwl-basic/pgrwl-receive.log" 2>&1 &
PGRWL_RECEIVE_PID=$!

# Give the receiver a moment to connect and begin streaming.
sleep 3

###############################################################################
# Phase 3. Take a base backup
###############################################################################

log "Creating base backup..."
pgrwl backup -c "$PGRWL_CONFIG"

###############################################################################
# Phase 4. Generate data AFTER the base backup
#
# This is the important part.
# If we recover only from the base backup, these changes would be lost.
# They survive only because the WAL receiver captures the WAL stream.
###############################################################################

log "Initializing pgbench data (scale=10 ~ about 1 million rows in pgbench_accounts)..."
pgbench -i -s 10 "$DBNAME"

log "Running pgbench workload..."
pgbench -c 4 -j 2 -t 200 "$DBNAME"

###############################################################################
# Phase 5. Save the final logical state before disaster
#
# This dump becomes our ground truth.
# After restore + WAL replay, we expect the cluster to match this state.
###############################################################################

log "Dumping cluster state before destruction..."
pg_dumpall --quote-all-identifiers --restrict-key=0 >"/tmp/pgrwl-basic/before.sql"

###############################################################################
# Phase 6. Force PostgreSQL to emit final WAL and let receiver catch up
###############################################################################

log "Forcing checkpoint and WAL switch..."
psql -d postgres -v ON_ERROR_STOP=1 -c "checkpoint;" >/dev/null
psql -d postgres -v ON_ERROR_STOP=1 -c "select pg_switch_wal();" >/dev/null

# Give pgrwl time to receive the last WAL segment(s).
sleep 3

###############################################################################
# Phase 7. Simulate disaster
###############################################################################

log "Stopping PostgreSQL and pgrwl receiver..."
stop_postgres
stop_pgrwl_receive

log "Removing original PGDATA to simulate data loss..."
rm -rf "$PGDATA"

###############################################################################
# Phase 8. Restore the base backup
###############################################################################

log "Restoring PGDATA from base backup..."
pgrwl restore --dest="$PGDATA" -c "$PGRWL_CONFIG"

chmod 0750 "$PGDATA"
chown -R postgres:postgres "$PGDATA"

# recovery.signal tells PostgreSQL to start in archive recovery mode.
touch "$PGDATA/recovery.signal"

###############################################################################
# Phase 9. Start pgrwl in serve mode for restore_command
###############################################################################

log "Starting pgrwl restore server..."
pgrwl daemon -m serve -c "$PGRWL_CONFIG" >"/tmp/pgrwl-basic/pgrwl-serve.log" 2>&1 &
PGRWL_SERVE_PID=$!

cat >>"$PGDATA/postgresql.conf" <<EOF
restore_command = 'pgrwl restore-command --serve-addr=127.0.0.1:7070 %f %p'
EOF

###############################################################################
# Phase 10. Start restored PostgreSQL and let it replay WAL
###############################################################################

log "Starting restored PostgreSQL cluster..."
pg_ctl -D "$PGDATA" -l "/tmp/pgrwl-basic/postgres-restored.log" start >/dev/null

wait_for_postgres
wait_until_out_of_recovery

###############################################################################
# Phase 11. Dump restored state and compare
###############################################################################

log "Dumping cluster state after recovery..."
pg_dumpall --quote-all-identifiers --restrict-key=0 >"/tmp/pgrwl-basic/after.sql"

log "Comparing dumps..."
if diff -u "/tmp/pgrwl-basic/before.sql" "/tmp/pgrwl-basic/after.sql" >"/tmp/pgrwl-basic/dump.diff"; then
  log "SUCCESS: restored cluster matches original state"
  echo "before: /tmp/pgrwl-basic/before.sql"
  echo "after : /tmp/pgrwl-basic/after.sql"
  echo "diff  : /tmp/pgrwl-basic/dump.diff (empty)"
else
  echo
  echo "FAIL: restored cluster differs from original state"
  echo "See diff: /tmp/pgrwl-basic/dump.diff"
  exit 1
fi

PostgreSQL Streaming WAL Archiver and a backup tool in Go (pgrwl)

alexey.zh — Sat, 28 Mar 2026 08:13:15 +0000

A production-grade, cloud-native PostgreSQL WAL archiving system designed for:

streaming WAL to S3 with compression, encryption, and retention
Kubernetes-native PostgreSQL backup workflows
zero data loss and reliable Point-in-Time Recovery (PITR)

Project

https://github.com/pgrwl/pgrwl

Features

WAL receiver (replication protocol)
Continuous WAL streaming
Backup to S3 (MinIO, AWS, etc.)
Backup to SFTP (backup server)
WAL compression (gzip, zstd-ready)
WAL encryption (AES-GCM)
WAL retention management
WAL monitoring and observability
Kubernetes & container ready
Helm chart support
YAML / JSON / ENV config
Lightweight single binary
Structured logging
Integration tests (containerized)
Unit tests
Backup automation (streaming basebackup)
Continuous backup for PostgreSQL

Key Capabilities

Streaming WAL

Uses PostgreSQL replication protocol
Supports synchronous WAL streaming
Enables zero data loss setups

Storage Backends

S3-compatible storage
SFTP backup servers

Compression + Encryption

Pipeline based on filename:

000000010000000000000001.gz.aes

Flow:

compress -> encrypt -> upload
download -> decrypt -> decompress

Architecture

PostgreSQL
   | (replication protocol)
WAL Receiver
   |
Local FS (fsync)
   |
Uploader (S3 / SFTP)
   |
Retention manager
   |
HTTP server (restore_command)

Continuous Backup

real-time WAL streaming
safe off-site storage
full PITR support
near-zero RPO

Kubernetes Ready

run as StatefulSet
works with StatefulSets / CNPG / Virtual Machines
deploy via Helm
GitOps-friendly

Configuration Example

main:
  listen_port: 7070
  directory: wals
receiver:
  slot: pgrwl_v5
log:
  level: trace
  format: text
  add_source: true

Testing

integration tests with real PostgreSQL containers
end-to-end WAL validation
unit-tested components

Why pgrwl?

simple deployment (single binary)
production-grade reliability
cloud-native design
built for Kubernetes and containers
secure and efficient WAL handling

Contribute

Star the repo (https://github.com/pgrwl/pgrwl)
Open issues (https://github.com/pgrwl/pgrwl/issues)
Suggest improvements
Submit PRs (https://github.com/pgrwl/pgrwl/blob/master/CONTRIBUTING.md)

Summary

pgrwl is a lightweight, powerful, production-ready WAL archiving solution that brings:

streaming
security
automation
observability

to PostgreSQL backups.

Patch-based, environment-aware Kubernetes deployments using plain YAML and zero templating

alexey.zh — Wed, 25 Jun 2025 14:18:53 +0000

Meet kubepatch — a simple tool for deploying Kubernetes manifests using a patch-based approach.

Unlike tools that embed logic into YAML or require custom template languages, kubepatch keeps your base manifests clean and idiomatic.

Simple: No templates, DSLs, or logic in YAML, zero magic
Predictable: No string substitutions or regex hacks
Safe: Only native Kubernetes YAML manifests - readable, valid, untouched
Layered: Patch logic is externalized and explicit via JSON Patch (RFC 6902)
Declarative: Cross-environment deployment with predictable, understandable changes

🛠 Example

Given a base set of manifests for deploy a basic microservice
see examples

---
apiVersion: v1
kind: Service
metadata:
  name: myapp
  labels:
    app: myapp
spec:
  type: NodePort
  selector:
    app: myapp
  ports:
    - protocol: TCP
      port: 8080
      targetPort: 8080

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: myapp
  labels:
    app: myapp
spec:
  replicas: 2
  selector:
    matchLabels:
      app: myapp
  template:
    metadata:
      labels:
        app: myapp
    spec:
      containers:
        - name: myapp
          image: "localhost:5000/restapiapp:latest"

A patches/prod.yaml might look like:

myapp-prod:
  deployment/myapp:
    - op: replace
      path: /spec/replicas
      value: 2
    - op: replace
      path: /spec/template/spec/containers/0/image
      value: "localhost:5000/restapiapp:1.21"
    - op: add
      path: /spec/template/spec/containers/0/env
      value:
        - name: RESTAPIAPP_VERSION
          value: prod
        - name: LOG_LEVEL
          value: info
    - op: add
      path: /spec/template/spec/containers/0/resources
      value:
        limits:
          cpu: "500m"
          memory: "512Mi"
        requests:
          cpu: "64m"
          memory: "128Mi"
  service/myapp:
    - op: add
      path: /spec/ports/0/nodePort
      value: 30266

A patches/dev.yaml might look like:

myapp-dev:
  deployment/myapp:
    - op: replace
      path: /spec/template/spec/containers/0/image
      value: "localhost:5000/restapiapp:1.22"
    - op: add
      path: /spec/template/spec/containers/0/env
      value:
        - name: RESTAPIAPP_VERSION
          value: dev
        - name: LOG_LEVEL
          value: debug
  service/myapp:
    - op: add
      path: /spec/ports/0/nodePort
      value: 30265

Apply the appropriate patch set based on the target environment.

kubepatch patch -f base/ -p patches/dev.yaml | kubectl apply -f -

Rendered manifest may look like this (note that all labels are set, as well as all patches are applied)

---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: myapp-dev
  name: myapp-dev
spec:
  ports:
    - nodePort: 30265
      port: 8080
      protocol: TCP
      targetPort: 8080
  selector:
    app: myapp-dev
  type: NodePort
---
apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: myapp-dev
  name: myapp-dev
spec:
  replicas: 1
  selector:
    matchLabels:
      app: myapp-dev
  template:
    metadata:
      labels:
        app: myapp-dev
    spec:
      containers:
        - env:
            - name: RESTAPIAPP_VERSION
              value: dev
            - name: LOG_LEVEL
              value: debug
          image: localhost:5000/restapiapp:1.22
          name: myapp

Installation

Manual Installation

Download the latest binary for your platform from the Releases page.
Place the binary in your system's PATH (e.g., /usr/local/bin).

Installation script

(
set -euo pipefail

OS="$(uname | tr '[:upper:]' '[:lower:]')"
ARCH="$(uname -m | sed -e 's/x86_64/amd64/' -e 's/\(arm\)\(64\)\?.*/\1\2/' -e 's/aarch64$/arm64/')"
TAG="$(curl -s https://api.github.com/repos/kubepatch/kubepatch/releases/latest | jq -r .tag_name)"

curl -L "https://github.com/kubepatch/kubepatch/releases/download/${TAG}/kubepatch_${TAG}_${OS}_${ARCH}.tar.gz" |
tar -xzf - -C /usr/local/bin && \
chmod +x /usr/local/bin/kubepatch
)

Package-Based installation (suitable in CI/CD)

Debian

sudo apt update -y && sudo apt install -y curl
curl -LO https://github.com/kubepatch/kubepatch/releases/latest/download/kubepatch_linux_amd64.deb
sudo dpkg -i kubepatch_linux_amd64.deb

Alpine Linux

apk update && apk add --no-cache bash curl
curl -LO https://github.com/kubepatch/kubepatch/releases/latest/download/kubepatch_linux_amd64.apk
apk add kubepatch_linux_amd64.apk --allow-untrusted

✨ Key Features

JSON Patch Only

Patches are applied using JSON Patch:

- op: replace
  path: /spec/replicas
  value: 1

Every patch is minimal, explicit, and easy to understand. No string manipulation or text templating involved.

Plain Kubernetes YAML Manifests

Your base manifests are 100% pure Kubernetes objects - no logic, no annotations, no overrides, no preprocessing. This
ensures:

Easy editing
Compatibility with other tools
Clean Git diffs

Cross-Environment Deploys

Deploy to dev, staging, or prod just by selecting the right set of patches. All logic lives in patch files, not
your base manifests.

Common Labels Support

Inject common labels (like env, team, app), including deep paths like pod templates and selectors.

Env Var Substitution (in Patch Values Only)

You can inject secrets and configuration values directly into patch files:

- op: add
  path: /spec/template/spec/containers/0/env
  value:
    - name: PGPASSWORD
      value: ${IAM_SERVICE_PGPASS}

Strict env-var substitution (prefix-based) is only allowed inside patches - never in base manifests.

Feedback

Have a feature request or issue? Feel free to open an issue or submit a PR!

Apply Kubernetes Manifests Atomically With Rollback

alexey.zh — Sat, 21 Jun 2025 06:57:27 +0000

katomik - Atomic Apply for Kubernetes Manifests with Rollback Support.

Applies multiple Kubernetes manifests with all-or-nothing guarantees. Like kubectl apply -f, but transactional:
if any resource fails to apply or become ready, all previously applied resources are rolled back automatically.

GitHub Repo →

Features

Atomic behavior: Applies multiple manifests as a unit. If anything fails, restores the original state.
Server-Side Apply (SSA): Uses PATCH with SSA to minimize conflicts and preserve intent.
Status tracking: Waits for all resources to become Current (Ready/Available) before succeeding.
Rollback support: Automatically restores previous state if apply or wait fails.
Recursive: Like kubectl, supports directories and -R for recursive traversal.
STDIN support: Use -f - to read from stdin.

Installation

Manual Installation

Download the latest binary for your platform from the Releases page.
Place the binary in your system's PATH (e.g., /usr/local/bin).

Installation script

(
set -euo pipefail

OS="$(uname | tr '[:upper:]' '[:lower:]')"
ARCH="$(uname -m | sed -e 's/x86_64/amd64/' -e 's/\(arm\)\(64\)\?.*/\1\2/' -e 's/aarch64$/arm64/')"
TAG="$(curl -s https://api.github.com/repos/hashmap-kz/katomik/releases/latest | jq -r .tag_name)"

curl -L "https://github.com/hashmap-kz/katomik/releases/download/${TAG}/katomik_${TAG}_${OS}_${ARCH}.tar.gz" |
tar -xzf - -C /usr/local/bin && \
chmod +x /usr/local/bin/katomik
)

Homebrew installation

brew tap hashmap-kz/homebrew-tap
brew install katomik

Usage

# Apply multiple files atomically
katomik apply -f manifests/

# Read from stdin
katomik apply -f - < all.yaml

# Apply recursively
katomik apply -R -f ./deploy/

# Set a custom timeout (default: 5m)
katomik apply --timeout 2m -f ./manifests/

# Process and apply a manifest located on a remote server
katomik apply \
  -f https://raw.githubusercontent.com/user/repo/refs/heads/master/manifests/deployment.yaml

Example Output

# katomik apply -f test/integration/k8s/manifests/

┌───────────────────────────────────────┬──────────────┐
│               RESOURCE                │  NAMESPACE   │
├───────────────────────────────────────┼──────────────┤
│ Namespace/katomik-test                │ (cluster)    │
│ ConfigMap/postgresql-init-script      │ katomik-test │
│ ConfigMap/postgresql-envs             │ katomik-test │
│ ConfigMap/postgresql-conf             │ katomik-test │
│ Service/postgres                      │ katomik-test │
│ PersistentVolumeClaim/postgres-data   │ katomik-test │
│ StatefulSet/postgres                  │ katomik-test │
│ ConfigMap/prometheus-config           │ katomik-test │
│ PersistentVolumeClaim/prometheus-data │ katomik-test │
│ Service/prometheus                    │ katomik-test │
│ StatefulSet/prometheus                │ katomik-test │
│ PersistentVolumeClaim/grafana-data    │ katomik-test │
│ Service/grafana                       │ katomik-test │
│ ConfigMap/grafana-datasources         │ katomik-test │
│ Deployment/grafana                    │ katomik-test │
└───────────────────────────────────────┴──────────────┘

+ watching
| Service/grafana                       katomik-test Unknown
| Deployment/grafana                    katomik-test Unknown
| StatefulSet/postgres                  katomik-test InProgress
| StatefulSet/prometheus                katomik-test InProgress
+ watching

✓ Success

Quick Start

cd test/integration/k8s
bash 00-setup-kind.sh
katomik apply -f manifests/

🔒 Rollback Guarantees

On failure (bad manifest, missing dependency, timeout, etc.):

Existing objects are reverted to their exact pre-apply state.
New objects are deleted.

This guarantees your cluster remains consistent - no partial updates.

Flags

Flag	Description
`-f`	File, directory, or `-` for stdin
`-R`	Recurse into directories
`--timeout`	Timeout to wait for readiness

Feedback

Have a feature request or issue? Feel free to open an issue
or submit a PR!

Streaming PostgreSQL Backups with pgrwl: Now with Time & Count-Based Retention!

alexey.zh — Sun, 15 Jun 2025 14:41:16 +0000

A new release of pgrwl just shipped, a cloud-native WAL receiver and backup agent for PostgreSQL — and it comes with a new feature: streaming basebackups with automated retention.

What's New

`feat(basebackup): time/count based retention`

pgrwl can now:

Run basebackups on a schedule (via built-in cron),
Stream backups directly to remote storage (S3, SFTP, etc),
And automatically enforce retention policies:
- Retain the last N backups (e.g. count=3)
- Or retain all backups for a certain duration (e.g. days=7)

This makes it easier than ever to keep your PostgreSQL cluster safely backed up without relying on external scripts or schedulers.

Streaming Basebackup?

pgrwl performs a streaming basebackup:

Uses replication protocol
Streams backup with optional compression and encryption,
Uploads to remote storage as the backup progresses — no need for temporary local copies!

This design drastically reduces time-to-storage and fits perfectly into Kubernetes-native workflows.

Retention Policies in Action

Here's a typical config for scheduled backups:

backup:
  schedule: "0 3 * * *"  # every day at 3AM
  retention:
    type: count          # count-based retention
    count: 3             # keep last 3 backups

After each successful backup, pgrwl automatically:

Lists available backups from storage,
Sorts them by timestamp,
Deletes old ones beyond the retention threshold — clean and simple.

Example: Kubernetes StatefulSet

A typical setup consists of two StatefulSets:

Receiver — continuously streams and archives WALs.
Backup — schedules full basebackups and handles retention.

On restore, the receiver pod can switch to serve mode, exposing archived WALs over HTTP to your PostgreSQL’s restore_command.

Why This Matters

For teams running PostgreSQL in Kubernetes (or other containerized environments), backup tooling is often a headache:

No clear story for WAL + basebackup orchestration,
Manual scripts with little observability,
Poor retention handling.

pgrwl aims to solve all of that, with:

Streaming basebackups
Streaming WAL receiver
Pluggable storage
Built-in compression/encryption
Clean Kubernetes integration
Minimal dependencies

Try It Out

Get started here 👉 https://github.com/hashmap-kz/pgrwl

If you have feedback, issues, or want to contribute, we’d love to hear from you! 🙌

Introducing relimpact: Fast Release Impact Analyzer for Go Projects

alexey.zh — Sun, 08 Jun 2025 17:02:16 +0000

relimpact

Release Impact Analyzer for Go projects — catch breaking API changes, docs updates & important file diffs — fast.

GitHub Repo →

In modern Go projects, it's too easy for accidental API changes or subtle documentation edits to sneak through pull requests or release processes unnoticed.

relimpact is a lightweight CLI tool that helps you understand what really changed between two Git refs — with clean, structured, human-friendly reports.

Use it in CI pipelines, release PRs, or locally before tagging new versions.

✨ Features

🔍 API Diff — Track breaking public API changes (structs, interfaces, functions, constants, variables).
📝 Docs Diff — Section-aware Markdown diff to highlight meaningful content changes.
🗂️ Other Files Diff — Group file changes by extension (.sh, .sql, .json, etc.) to surface migrations and auxiliary files.
🚀 Designed for Release PR reviews — Quickly see the real impact of changes.
🖋️ Markdown Reports — Ready to paste into GitHub Releases, Slack, or changelogs.
⚙️ Works in GitHub Actions, GitLab CI, or locally — Integrates easily.
🔒 No server required — Pure CLI tool.

🚀 Quickstart

Run on a GitHub PR:

relimpact --old=v1.0.0 --new=HEAD > release-impact.md

Example Output:

Expanded Sections

PR Comment Generated

⚙️ GitHub Action Integration

name: Release Impact on PR

on:
  pull_request:
    branches: [ master ]
    types: [ opened, synchronize, reopened ]

jobs:
  release-impact:
    name: Generate Release Impact Report
    runs-on: ubuntu-latest

    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0

      - name: Determine previous tag
        id: prevtag
        run: |
          git fetch --tags
          TAG_LIST=$(git tag --sort=-version:refname)
          PREV_TAG=$(echo "$TAG_LIST" | head -n2 | tail -n1)
          echo "Previous tag: $PREV_TAG"
          # Fallback to first tag if no previous
          if [ -z "$PREV_TAG" ]; then
            PREV_TAG=$(echo "$TAG_LIST" | head -n1)
            echo "Fallback to first tag: $PREV_TAG"
          fi
          echo "prev_tag=$PREV_TAG" >> $GITHUB_OUTPUT

      - name: Determine new ref
        id: newref
        run: |
          if [ "${{ github.event_name }}" = "pull_request" ]; then
            echo "new_ref=${{ github.event.pull_request.head.sha }}" >> $GITHUB_OUTPUT
          else
            echo "new_ref=HEAD" >> $GITHUB_OUTPUT
          fi

      # Cache restore for old ref
      - name: Cache API snapshot (old ref)
        uses: actions/cache/restore@v4
        id: cache-old
        with:
          path: .cache/relimpact-api-cache
          key: relimpact-api-${{ steps.prevtag.outputs.prev_tag }}
          restore-keys: |
            relimpact-api-

      # Cache restore for new ref
      - name: Cache API snapshot (new ref)
        uses: actions/cache/restore@v4
        id: cache-new
        with:
          path: .cache/relimpact-api-cache
          key: relimpact-api-${{ steps.newref.outputs.new_ref }}
          restore-keys: |
            relimpact-api-

      # Run your relimpact-action (this runs SnapshotAPI and writes cache)
      - uses: hashmap-kz/relimpact-action@main
        with:
          old-ref: ${{ steps.prevtag.outputs.prev_tag }}
          new-ref: ${{ steps.newref.outputs.new_ref }}
          output: release-impact.md
        env:
          RELIMPACT_API_CACHE_DIR: ${{ github.workspace }}/.cache/relimpact-api-cache

      # Cache save for old ref — only if not already restored
      - name: Save API snapshot cache (old ref)
        if: steps.cache-old.outputs.cache-hit != 'true'
        uses: actions/cache/save@v4
        with:
          path: .cache/relimpact-api-cache
          key: relimpact-api-${{ steps.prevtag.outputs.prev_tag }}

      # Cache save for new ref — only if not already restored
      - name: Save API snapshot cache (new ref)
        if: steps.cache-new.outputs.cache-hit != 'true'
        uses: actions/cache/save@v4
        with:
          path: .cache/relimpact-api-cache
          key: relimpact-api-${{ steps.newref.outputs.new_ref }}

      # Upload the release impact report
      - name: Upload Release Impact Report
        uses: actions/upload-artifact@v4
        with:
          name: release-impact-${{ github.run_id }}-${{ github.run_attempt }}
          path: release-impact.md

      # Post release impact to PR comment
      - name: Post Release Impact to PR
        if: github.event_name == 'pull_request'
        uses: marocchino/sticky-pull-request-comment@v2
        with:
          recreate: true
          path: release-impact.md

📦 Installation

Homebrew

brew tap hashmap-kz/homebrew-tap
brew install relimpact

Manual Download

👉 Download latest release

🧠 How It Works

1️⃣ Go Source API Changes

Uses Go type system & AST parsing:
- Detects breaking changes: method signatures, removed fields, new/removed types, etc.
- Ignores formatting & comment noise.
- Based on golang.org/x/tools/go/packages.

2️⃣ Markdown Docs Changes

Section-aware diff of .md files:
- Headings added/removed.
- Links and images changes.
- Section word count diffs.
- Based on goldmark parser.

3️⃣ Other Files Changes

Groups changes by file type:
- .sql, .sh, .json, .yaml, .conf, etc.
- Uses git diff --name-status.

Why Use It?

Most release PRs include:

✅ API changes
✅ Doc updates
✅ Migration scripts
✅ Other important config tweaks

But raw git diff is noisy and hard to review.
relimpact gives you a release-ready summary, focusing on what's important.

📜 License

MIT License. See LICENSE.

👉 Try it today: https://github.com/hashmap-kz/relimpact

If you found this useful, leave a ⭐ on GitHub — it helps others discover the project!

Happy releasing 🚀

High-Speed File Transfer to and from Kubernetes PVCs

alexey.zh — Sat, 07 Jun 2025 11:33:39 +0000

When working with Kubernetes, Persistent Volume Claims (PVCs) are the backbone of many stateful workloads.
However, transferring data to and from a PVC is surprisingly hard to do efficiently.

Sure, there are plenty of tools that look like they can help - tools that mount PVCs locally, migrate data between PVCs,
or provide backup/restore functionality.

But when I needed a fast and reliable way to copy large files into and out of PVCs, especially for PostgreSQL WAL archiving workflows, I ran into a big roadblock:

kubectl cp became painfully slow - I gave up waiting on a 100 GiB copy
kubectl exec requires shell tools inside your container

And CSI-backed PVCs (like Ceph RBD) don't easily expose the underlying storage for direct access.

That’s why I built kubectl-syncpod.

What is kubectl-syncpod?

It’s a CLI tool that enables high-speed file transfers between your local machine and Kubernetes PVCs - even if the workload uses minimal container images (distroless, scratch) or CSI-backed block storage.

Unlike kubectl cp, it leverages:

✅ A temporary helper Pod that mounts the PVC
✅ An ephemeral SSHD server for fast and secure file transfer
✅ A parallel SFTP client that transfers files concurrently
✅ Automatic cleanup (no leftover Pods or Services)

About

kubectl cp and kubectl exec degrade in performance with a large set of files (~100 GiB).
They also require additional tools (tar, sh) and fail with distroless/scratch images.
Most importantly, they do not support concurrent reading/writing.

Features

Upload local files/directories to a pod volume
Download the pod volume files to the local
Safe overwrite protection
Auto-rename remote directories
Concurrent file transfer with worker pool
Preserves directory structure
Optional chown of uploaded files inside the pod
Fully based on SFTP + Kubernetes Exec API

Typical Use Cases

✅ Download backup from PVC for verification/restore
✅ Sync files between PVCs and local
✅ Testing PVC mount behavior
✅ CI/CD pipelines for volume data prep

How It Works

Spins up a helper pod mounting the PVC
Runs SSHD server with ephemeral key
Local SFTP client transfers files
Auto cleans up pod/service

Installation

Homebrew

brew tap hashmap-kz/homebrew-tap
brew install kubectl-syncpod

Manual

Download the latest release and place it in PATH.

Installation script for UNIX-based OS:

(
set -euo pipefail

OS="$(uname | tr '[:upper:]' '[:lower:]')"
ARCH="$(uname -m | sed -e 's/x86_64/amd64/' -e 's/\(arm\)\(64\)\?.*/\1\2/' -e 's/aarch64$/arm64/')"
TAG="$(curl -s https://api.github.com/repos/hashmap-kz/kubectl-syncpod/releases/latest | jq -r .tag_name)"

curl -L "https://github.com/hashmap-kz/kubectl-syncpod/releases/download/${TAG}/kubectl-syncpod_${TAG}_${OS}_${ARCH}.tar.gz" |
tar -xzf - -C /usr/local/bin && \
chmod +x /usr/local/bin/kubectl-syncpod
)

Example Usage

Upload with safe rename and chown

kubectl-syncpod upload \
  --namespace my-db \
  --pvc postgres-data \
  --mount-path=/var/lib/postgresql/data \
  --src=backups \
  --dst=pgdata-new \
  --allow-overwrite \
  --owner="999:999"

Download the remote directory

kubectl-syncpod download \
  --namespace my-db \
  --pvc postgres-data \
  --mount-path=/var/lib/postgresql/data \
  --src=pgdata-new \
  --dst=backups-copy

Comparison Table

Feature	`kubectl cp`	`kubectl exec`	`kubectl-syncpod` (SFTP mode)
Uses sidecar or helper pod	❌	❌	✅
Works with PVCs	⚠️ Only if mounted in container	⚠️ Manual path required	✅ Helper pod mounts PVC
Requires tools in container (`tar`, `sh`)	✅	✅	❌ (uses `sshd` in helper pod)
Supports `readOnlyRootFilesystem` pods	❌	❌	✅
Works on `distroless`/`scratch` images	❌	❌	✅
Affects main application container	✅	✅	❌
Requires container to run as root	Often yes	Often yes	❌ or configurable via helper pod spec
Safe for production workloads	⚠️ Risky	⚠️ Risky	✅ (safe for read)
Auto-cleans after sync	❌	❌	✅
Supports concurrent transfers	❌	❌	✅ (parallel SFTP workers)
Performance on large file trees	🐢 Slow	🐢 Slow	🚀 Fast (streaming + concurrency)

Conclusion

kubectl-syncpod fills a small but important gap in the Kubernetes toolchain:

👉 Efficient and fast file transfer to/from PVCs
👉 No need to modify your main containers
👉 Works great with CSI-backed volumes
👉 Production-safe and automated

The project is open source and evolving:

👉 https://github.com/hashmap-kz/kubectl-syncpod

If you find it useful - contributions, feedback, and GitHub ⭐️ stars are very welcome!

🔬 Internals of PostgreSQL WAL Streaming with pgrwl: A Cloud-Native Receiver Built in Go

alexey.zh — Thu, 05 Jun 2025 15:39:11 +0000

PostgreSQL's Write-Ahead Logging (WAL) underpins everything from crash recovery to PITR and streaming replication. But streaming WALs safely and reliably, especially into cloud or container-native environments, can be fragile.

Enter pgrwl: a modern WAL receiver written in Go that gives you:

📆 WAL streaming to local/S3/SFTP
🔐 On-the-fly encryption + compression
🐳 Kubernetes-native workflows
📊 Prometheus metrics + Grafana dashboards
🧹 Automatic retention + cleanup
⚙️ Safe WAL handling, with proper fsync guarantees

Let’s explore how it works under the hood.

🧪 Note: pgrwl is a research project, inspired by PostgreSQL’s built-in tools like pg_receivewal, and is
based on careful reading of their official source code and streaming behaviors.

pgrwl serves as a starting point to explore more cloud-native, container-friendly, and extensible
implementations of these time-tested concepts.

📆 The Problem:

For a cloud-native WAL streamer, these features are expected:

Cloud uploads (S3, SFTP)
Streaming compression
Encryption
Metrics and observability
Retention and cleanup
Pod/container integration

Most of all, it should be integrated with modern DevOps pipelines.

⚙️ How `pgrwl` Works

The architecture captures the streaming and archival lifecycle:

🔍 Component Breakdown

📅 WAL Receiver

Connects to PostgreSQL replication slot
Receives XLogData messages via pglogrepl
Buffers segment into a .partial file
Once the 16 MiB boundary is reached:
- fsync() the file
- Atomically rename to final segment

🧠 Archive Supervisor

Periodically triggers:
- WAL uploader
- WAL retention sweeper

☁️ WAL Uploader

Scans for complete segments
Compresses + encrypts
Streams directly to remote storage
Deletes local copy on success

🧹 WAL Retainer

Lists remote storage
Applies time-based retention
Deletes expired segments

🧪 Integrity Testing: Byte-for-Byte Fidelity

pgrwl is tested to ensure:

Received WALs match PostgreSQL originals
.partial files are never uploaded
Segment naming and metadata are preserved

🛡️ WAL archiving for disaster recovery (PITR)
🔐 Encrypted WAL pipelines
🐳 Kubernetes integration

🤝 Contribute & Collaborate

You can help by:

Suggest features
File issues
Improving CLI and UX

Start here: Contributing

📂 Links

GitHub: hashmap-kz/pgrwl
Docker: ghcr.io/hashmap-kz/pgrwl
Docs: In-repo README & examples
License: MIT

Stream safely, backup smart. 🌊

Testing PostgreSQL WAL Streamers for Byte-Level Fidelity

alexey.zh — Sat, 31 May 2025 14:36:40 +0000

Verifying that WAL streamers preserve exact database state — bit by bit.

🧭 Context

In the previous post, we explored the motivations behind building pgrwl, a PostgreSQL WAL receiver designed for zero data loss (RPO=0) scenarios in containerized environments. We covered its architecture, features like compression/encryption, and its suitability for Kubernetes-based disaster recovery.

This follow-up post focuses on testing — specifically validating that pgrwl produces WAL archives that are byte-for-byte identical to PostgreSQL’s official tool (pg_receivewal) and that it supports full PITR (Point-in-Time Recovery) after abrupt system crashes.

🚀 Intro

Write-Ahead Logs (WALs) are at the heart of PostgreSQL’s crash recovery and replication capabilities. But what happens when we replace the native WAL receiver (pg_receivewal) with a third-party tool like pgrwl? Can we trust it to preserve data integrity byte-for-byte?

This post dives into a golden test designed to answer that question — by simulating real-world PostgreSQL workloads, abrupt crashes, and full recovery workflows.

Note: All Bash scripts shown here are simplified examples to illustrate the core logic. The full implementation with deep technical details and automation scripts is available in the pgrwl GitHub repository. This post focuses on explaining the primary test goal, rather than every integration nuance.

Integration Test Source Code

✅ Goal

To verify that:

pgrwl can reliably stream WALs during active writes.
The restored database is identical to its pre-crash state.
WAL files produced by pgrwl match those produced by pg_receivewal bit-for-bit.

🛠️ Tools Used

PostgreSQL 16+
pg_receivewal — the official WAL receiver.
pgrwl — WAL receiver with encryption/compression/backends.
pg_dumpall, pgbench
Bash for orchestration

🧪 Test Procedure: Step-by-Step

We simulate a live system, insert tons of data, kill everything mid-flight, and then recover from base backup + WALs.

1. Start PostgreSQL

Initialize a clean cluster:

initdb -D /tmp/pgdata
pg_ctl -D /tmp/pgdata -l logfile start

2. Launch WAL Receivers

Run both in parallel (in background):

pg_receivewal --slot=test_slot -D /tmp/pgwal_pg ...
pgrwl --mode=receive -c config.yml ...

3. Take a Base Backup

pg_basebackup \
  --pgdata="/tmp/base_backup" \
  --wal-method=none \
  --checkpoint=fast \
  --progress \
  --no-password \
  --verbose

4. Simulate Real Workload

Insert timestamps every second:

psql -c 'CREATE TABLE ticks(ts TIMESTAMPTZ DEFAULT now());'
while true; do psql -c 'INSERT INTO ticks DEFAULT VALUES;'; sleep 1; done &

Run pgbench to add load:

pgbench -i -s 10

Create 100 tables in parallel:

for i in $(seq 1 100); do
  psql -c "CREATE TABLE t_$i AS SELECT * FROM generate_series(1, 10000) AS g(id);" &
done
wait

5. Capture Golden Snapshot

pg_dumpall > /tmp/before.sql

Kill the ticks inserter.

6. Simulate Crash

pkill -9 postgres || true
pg_ctl -D /tmp/pgdata -m immediate stop
rm -rf /tmp/pgdata

7. Restore from Base + WALs

cp -r /tmp/base_backup /tmp/pgdata
touch /tmp/pgdata/recovery.signal
echo "restore_command = 'pgrwl restore-command --serve-addr=127.0.0.1:7070 %f %p'" >> /tmp/pgdata/postgresql.conf

💡 Rename all *.partial WALs to their final names before restart.

8. Restart PostgreSQL

pg_ctl -D /tmp/pgdata -l logfile start

Wait for recovery to complete.

9. Validate Database Consistency

pg_dumpall > /tmp/after.sql
diff -u /tmp/before.sql /tmp/after.sql

✅ Expect: No differences.

Also verify ticks table for the latest inserted row — confirming no data loss.

10. Compare WAL Files

diff -r /tmp/pgwal_pg /tmp/pgwal_pgrwl

✅ Expect: Identical content and filenames.

📉 Post-Crash: Retest on New Timeline

Restart both WAL streamers on a new timeline (due to crash + recovery) and verify they pick up correctly.

Then rerun the diff again.

🧠 What This Test Proves

WALs received by pgrwl are valid and byte-identical to official ones.
PostgreSQL can recover from pgrwl's archived WALs to the latest committed transaction.

🔬 Bonus: Add Compression and Encryption

Add this to the config:

compression:
  algo: gzip
encryption:
  algo: aesgcm
  pass: "${PGRWL_ENCRYPT_PASS}"

💡 WALs will no longer match byte-for-byte (they’re transformed), but recovery should still work identically.

✅ Conclusion

Testing WAL archiving isn’t just about receiving files — it’s about trust.
This golden test validates pgrwl as a reliable WAL receiver with byte-level fidelity and advanced features
like encryption and compression.

📦 Check out the code: github.com/hashmap-kz/pgrwl

🙌 Get Involved

pgrwl is an open-source project built for the PostgreSQL community — and your feedback matters!

🐞 Found a bug? Open an issue
💡 Have an idea or feature request? We'd love to hear it.
🧪 Want to improve WAL testing coverage? Run the integration tests or add your own cases.
🔧 Found a rough edge or an unclear doc? Contributions are always welcome.

Start by starring ⭐ the repo, trying it out in your own cluster, and sharing what you learn.

Let’s build better PostgreSQL backup tooling — together.

DEV Community: alexey.zh

A Long Story about how I dug into the PostgreSQL source code to write my own WAL receiver, and what came out of it

Why PostgreSQL in the First Place

Why Exactly pg_receivewal

A Few Words About Other Good Solutions I Looked at With Respect and Envy

pgBackRest

Barman

Why Go, If I Had to Look at So Much C

The Beginning: Compiling PostgreSQL, Debugging, and the First Signs of Recklessness

The First Prototype: "I Will Just Reproduce pg_receivewal"

When You Realize That Simply Receiving WAL Is Only the Beginning

About Base Backup: I Did Not Want To, but Curiosity Won

Why I Did Not Go Deeper Into Incremental Backups

Architecture: The Moment When You Are No Longer Writing a Utility but Coordinating Chaos

Streaming Large Files: Another Source of Creativity

fsync: The Most Subtle Part and My Own Little Nervous Breakdown

Metrics: Because I Wanted to See Whether It Was Still Alive or Already Dead

Logging: Where I Also Realized I Still Have a Long Way to Go

Integration Tests: The Hardest and Most Important Part

What Came Out of It

What Is Planned

What I Took Away From This

Links

SQL-First PostgreSQL Migrations Without the Magic

Why this approach matters

What gopgmigrate does

The naming convention is the API

Versioned migrations

Rollbacks

Repeatable migrations

Non-transactional migrations

Real projects are not flat folders

Why SQL-first migrations are still the best default

Better reviewability

Better portability

Better debugging

Better longevity

Safety features that matter in practice

Advisory locking

Transactional safety by default

Explicit non-transactional mode

Hash-based change detection

History tracking

Example CLI workflow

Where this fits especially well

1. PostgreSQL-first teams

2. Teams with mixed migration types

3. Repos with real structure

4. CI/CD and automation

5. Engineers who dislike lock-in

What I like most about this design

Final thoughts

Finding Hidden Bottlenecks in Go Apps: A Lazy, Hacky, and Bruteforce Method

The Problem

The Bruteforce Idea

The Solution

How it works

Usage And Example Output

Conclusion

Links

Backup Is Not Enough: A PostgreSQL Recovery Story

The Myth of "Backups"

The Real Rule

The Experiment

Step 1 --- Build a Database From Nothing

Step 2 --- Start Capturing WAL

Step 3 --- Take a Base Backup

Step 4 --- Populate DB

Step 5 --- Save the Truth

Step 6 --- Delete Everything

Step 7 --- Restore the Base Backup

Step 8 --- Replay History

Step 9 --- Did It Work?

Mental Model

Final Thought

Using docker environment for integration tests

Full Script

PostgreSQL Streaming WAL Archiver and a backup tool in Go (pgrwl)

Project

Features

Why Exactly `pg_receivewal`

`pgBackRest`

`Barman`

The First Prototype: "I Will Just Reproduce `pg_receivewal`"

`fsync`: The Most Subtle Part and My Own Little Nervous Breakdown

`feat(basebackup): time/count based retention`