Forem: Rostislav Dugin

I vibecoded an extension for VS Code so I wouldn't have to vibecode with Claude Code (and an example of how to develop with AI)

Rostislav Dugin — Mon, 09 Mar 2026 11:37:50 +0000

For the past year and a half, I have been using Cursor IDE as my primary development environment. At first, I used it as an IDE with comfortable autocompletion (relative to GitHub Copilot). Then the AI became smarter and more convenient, a planning mode appeared, and Claude became a basic attribute of my workday.

A week ago, my open source project Databasus (a tool for backing up PostgreSQL, ~6k stars ⭐️ and ~275k Docker pulls) received support from Anthropic as part of their OSS program: and now I have free Claude Code Max for the next six months.

So, I switched to it... and realized that I was very used to the UX in Cursor IDE 😐. The smartest unlimited models are, of course, great. But convenience and control over changes are my priority.

So I took Opus and vibecoded an extension for VS Code that brings interaction with CLI agents closer to the Cursor IDE experience: when you do see changes and can correct them precisely. A couple of hours spent made my work for the next six months significantly more comfortable.

What I did and how I did it is described below.

Disclaimers

There is a big difference between "vibecoding" and "using AI as a tool":

Vibecoding is creating a program through prompts, without looking at what exactly the AI has changed in the code and without evaluating the adequacy of the solution "under the hood". And you do this in large chunks. In other words, the person is not actually responsible for the quality of the code.

Using AI as a tool is when AI helps to plan, write routine code, double-check decisions, and perform actions that a person tells it to. In other words, the person controls every change in the code and is responsible for it.

I use the second approach in my work and open source. This is because even the most intelligent AI cannot fight with the task of "understanding requirements" and "assessing consequences" (for example, that the code will become unsupported in a couple of days). Although they radically increase the speed and efficiency of work.

When you need to assemble a PoC, a simple script, or something for testing, you can use vibe coding. Like, for example, with the extension mentioned in the article.

By the way, at the same time, I still don't know and don't understand how you can "orchestrate agents" by letting them work for hours. My brain is only capable of keeping one task (i.e., chat) in context at a time 🥲.

And for reference:

1) I have been developing for ~11 years and, in my opinion, I am good at writing code by hands.

I have managed to:

develop an eye for ~~while was creating millions of lines of bullshit code~~;
ask stupid questions on StackOverflow (it was mainstream back then, he-he);
spend months reading references and boring books, and I also know how to access JDK documentation from the IDE and still don't mind digging through library source code;
learn how to evaluate the trade-offs of solutions, because I've seen the consequences of bad decisions many times;
install printer drivers in Debian when ChatGPT didn't exist yet (now I'm even afraid to remember that);
spend many, many years writing tests by hand and understanding what needs to be tested and what doesn't.

In general, I have enough expertise to understand the strengths and weaknesses of AI. For me, AI is a valuable tool that complements my own expertise, rather than compensating its absence.

2) Databasus has a disclaimer right in README.md about how AI is used in the project and how AI is NOT used in the project. You can read it here. It talks about the code's quality gates, requirements (for humans and AI), etc. There is a separate point that vibecoders are not welcome there.

How do I work with AI?

First, a little context on what my work looks like when I write code with AI (and let me clarify right away that I only use the latest paid Thinking models from Claude and OpenAI):

1) I decide what problem I need to solve and how to solve it. Sometimes I don't know how to solve the problem, or I have doubts that my idea is the most rational one. Then I go to Claude or ChatGPT and simply communicate with them in the format "I want to do X in way Y, but I think Z is possible. What do you think?" or "How does X work, or how is Y usually done? Is there any useful documentation or examples?".

After that, I form an idea of which direction to move in.

2) I take the task I need (let's say, feature X) and break it down into logically separate subtasks. These can be done and tested separately. For example, "add model X to the code, repository, service, and controller, write migration, write tests Y and Z."

3) I ask the AI to prepare a plan for solving the task (with input from me, if necessary). I add to the context of the plan which files relate to the task and how I see the solution, so that the AI understands what to read, what to change, and what to interact with and how.

4) I refine and detail the plan until I am confident that nothing important has been overlooked. By the way, this is the most important point in working with AI (which takes up ~40% of the time).

Usually, those who complain that "AI generates stupid code for me" do not write a plan or do not bring it to an adequate level of detail.

In the process, I ask AI to double-check its own plan.

If I see that the plan is too big and too many files are changing, I go back to the task decomposition step. Not too much should change at once, otherwise I will lose context and won't be able to understand what the AI has done.

5) I send the AI agent to write code according to the plan. Here, it changes the code, runs tests, etc.

6) I review and correct the changes made by the agent. Here, I go through each line of code and make sure that the code does exactly what I need it to do.

If I see that the naming is wrong, there is code duplication, patterns are missing, or something can be done better, I refactor it manually. If I see that the result is not what I need or I realize that the solution is flawed, I cancel the changes and return to the stage of preparing the plan or even decomposing the task.

This step also takes about 40% of the time, roughly the same as planning.

Here, it is important to reject what you have if it is not quite what you need. Or if you feel that the solution seems to work, but will create maintainability issues in the future.

A significant part of development is subjective, depending on subjective experience, insight, and understanding of the project context (from a product perspective, not code) by the developers themselves. And this is where AI falls short.

7) If the task is related to security or reliability, I ask AI to double-check that everything is reliable, nothing has been overlooked, nothing needs to be improved, and no test scenarios have been missed.

All these steps are convenient to go through in Cursror IDE. There are tools for all of this, and they are really intuitive and convenient.

What did I find lacking in Claude Code?

Claude Code has a CLI interface. It can plan and write code. It even has extensions for VS Code and JetBrains IDE (I initially attempted to return to GoLand, but did not because it is convenient to open both the front and back ends in one place).

However, I missed two features:

1) The ability to drag files directly into the AI chat (point 3, “planning,” above) to set the context during planning, suggest where I need changes, and what to take into account. Neither the terminal nor the extension has a drag-and-drop area.

Manually writing the path to the files each time is time-consuming and inconvenient (when you are used to the convenient Cursor). In addition, in Go, it is customary to name files with 1-2 words, and even in my small projects, there are 30+ model.go, service.go, controller.go files with identical names. This means that without the full path to the package, the desired file is rarely highlighted.

2) Highlighting Diff changes directly in the file and the ability to accept/reject a piece of changes after changes from Claude Code (item 6 “review changes” above). That is, there is no such highlighting directly in the file:

Yes, Claude Code shows changes in the chat like this:

But it's hard for me to understand the context of the code when I can't see the whole file and there are many files. I can't:

navigate through references;
see variable usage;
see syntax highlighting;
see the surrounding code (more precisely, above and below).

This means that I cannot properly review changes from AI (more precisely, it takes much more time). This is equivalent to me working inefficiently.

P.S. It is also inconvenient to use Git diff because I usually have more than one iteration of changes from AI within a commit.

How did I make the extension?

There is a nuance in tracking changes through the extension: I cannot directly access Claude Code from the outside and understand what exactly it is changing. Therefore, I need to take a snapshot of the code base and compare it with what has changed. This tracking finds edits made by AI, but also highlights my own changes. So I decided to make an "on/off" button.

I took Opus 4.6 Thinking and started writing requirements.

I needed:

a "start tracking changes" and "stop tracking changes" button so that changes from AI would be highlighted, but changes from me would not;
highlight changes directly in the editor (so that the entire file is visible and the changed section within the file is highlighted);
make it so that I could apply/reject changes in chunks;
I need arrows to navigate through the changed files and a counter (to make sure I've checked all the files);
I need buttons to accept/reject all changes in the file at once;
when I select multiple files using Ctrl, I need an option in the dropdown menu called "Copy as @ references."

After formalizing the requirements, Opus went to write the code. He created the structure, wrote the files, gave me instructions on how to install the extension, and I went to test it.

Problems arose:

1) Even after three attempts, it was not possible to create attractive highlighting for the modified (rather than supplemented) code: it is still displayed as added + red comment, as it was “before” with lines connected via join:

It's moderately convenient, but much better than when the code jumped around the screen, was re-rendered, or changes weren't displayed at all. I don't know exactly how to fix it yet. When I have time, I'll open the CodeLens documentation and figure out how code display works in VS Code. Here, AI needs to tell me what to do.

2) If there are a lot of binaries in the project (which is specific to Databasus), the extension took a long time to load them into RAM. I added a restriction that files up to 1 MB in size can be tracked and those in .gitignore can be skipped.

3) The extension itself installed after ~5 attempts and changes to package.json. I didn't understand why this happened either. I just went through the options from Opus by trial and error — the costs of vibe coding.

What was the result?

Now the extension can copy links to selected files so that I can paste them into a chat with Claude Code using @. It's not drag-and-drop yet, but at least I don't have to type in the paths manually.

The extension tracks changes, highlights modified code snippets in the file, and allows you to accept or reject changes:

So, before launching Claude Code into the code base, I click "Start tracking" and VS Code starts showing me everything that the agent has changed. Now I can review the changes relatively comfortably, move through the changed files, and see the context around the changed sections of code (albeit not as conveniently as in Cursor).

Conclusion

A couple of hours of vibecoding helped me bring the Claude Code experience closer to that of Cursor IDE. Now that I can see what exactly AI is changing, I can work productively with Claude Code. If this option weren't available, I wouldn't be able to review changes effectively and would return to Cursor for ~$200 a month, trading unlimited Opus for a good UX with Sonnet.

This is yet another reminder for me that small tools can now be made very quickly. Two years ago, the same extension would have taken me probably a week. And now, a working (albeit rough) solution is available in just ~3-4 hours.

The extension is available on GitHub — https://github.com/RostislavDugin/diffus

And don't forget: use AI wisely, it's just a tool like an IDE, not an opportunity to slack off and think less. It's just that now you can think with higher efficiency per unit of time 🙂.

If you like the article, don't forget to look, try or, at least, put a star on my GitHub repo for PostgreSQL backup tool - https://github.com/databasus/databasus

Postgresus 2.0: new version of open source PostgreSQL backup tool

Rostislav Dugin — Wed, 10 Dec 2025 17:23:44 +0000

It's been 6 months since the first release of Postgresus. During this time, the project received 247 commits, new features, as well as ~2.8k stars on GitHub and ~42k downloads from Docker Hub. The project community has also grown, there are now 13 contributors and 90 people in the Telegram group.

In this article I'll cover:

what changed in the project over six months;
what new features appeared
what plans are next.

Contents

What is Postgresus?
New features appeared in version 2.0
- 1) Database health check
- 2) New sources for storing backups and sending notifications
- 3) Workspace and user management
- 4) Encryption and protection
- 5) Compression by default, the most optimal one
- 6) Kubernetes Helm support
- 7) Dark theme
- 8) Get rid of incremental backups and PITR
- 9) Many small improvements
What didn't work out?
- 1) Full database resource monitoring
- 2) SQL console
Conclusion

What is Postgresus?

For those hearing about the project for the first time: Postgresus is an open source PostgreSQL backup tool with UI. It runs scheduled backups of multiple databases, saves them locally or to external storages, and notifies you when backups complete or fail.

The project deploys with a single command in Docker. It can be installed via shell script, Docker command, docker-compose.yml and now via Helm for Kubernetes. More about installation methods.

Besides the main feature "make backups", the project can:

Store backups locally, in S3, CloudFlare R2, Google Drive, Azure Blob Storage and NAS. More details here.
Send status notifications to Slack, Discord, Telegram, MS Teams, via email and to a configurable webhook. More details here.
Separate databases by workspaces, grant access to other users and save audit logs. More details here.
Encrypt backups and credentials. More details here.
Work with both self hosted databases and cloud managed databases.

Website - https://postgresus.com/

GitHub - https://github.com/RostislavDugin/postgresus (would appreciate a star ⭐️)

The project interface looks like this:

Overview video:

For those who want to participate in development, there's a separate page in the documentation. If someone wants to help the project but doesn't want to code — just tell your colleagues and friends about the project! This is also a big help and contribution to the project.

I know how to develop, but promoting even an open source project is quite difficult. The project gains recognition thanks to those who make video reviews and talk about the project in social media. Thank you!

New features appeared in version 2.0

A lot has changed over these six months. The project has been improved in four directions:

expanded basic functionality;
improved UX;
appeared new capabilities for teams (DBAs, DevOps, teams, enterprises);
improved protection and encryption to meet enterprise requirements.

Let's go through each one.

1) Database health check

Besides backups, Postgresus now monitors database health: it shows uptime and alerts you if a database was unavailable.

Health check can be disabled and configured.

If the database is unavailable — the system will send a notification about it.

2) New sources for storing backups and sending notifications

Initially Postgresus could only store backups locally and in S3. Google Drive, CloudFlare R2, Azure Blob Storage and NAS were added. Plans include adding FTP and possibly SFTP and NFS.

For notifications, initially the project had Telegram and email (SMTP). Now Slack, Discord, MS Teams and webhooks are also supported. Moreover, webhooks are now flexibly configured to connect to different platforms:

3) Workspace and user management

Previously the system had only one user (administrator), and databases were global for the entire system. Now Postgresus supports creating workspaces to separate databases and adding users to workspaces. With role separation:

view only (can view backup history, download backup files);
edit (can add\delete\modify databases in addition to reading).

Consequently, you can now separate databases:

client X databases;
startup Y databases;
team Z databases;

DBA teams and large outsourcing companies started using Postgresus, so this became an important feature. It makes the project useful not only to individual developers, DevOps or DBAs, but to entire teams and enterprises.

Audit logs also appeared:

If someone decided to delete all databases or for some reason downloaded all backups of all databases — this will be visible 🙃

4) Encryption and protection

In the first version, honestly, I didn't have time for security. I stored all backup files locally, no one had access to them, and my projects weren't exactly top secret.

But when Postgresus went open source, I quickly learned that teams often save backups to shared S3 buckets and don't want others reading them. Database passwords shouldn't be stored in Postgresus's own DB either, since many people have access to its servers. ~~And there's some distrust of each other.~~ Simply not exposing secrets via API is no longer enough.

So, encryption and security of the entire project became the main priority for Postgresus. Protection now works at three levels, and there's a dedicated documentation page for this.

1) Encryption of all passwords and secrets

All database passwords, Slack tokens and S3 credentials are stored encrypted in Postgresus's database. They're decrypted only when needed. The secret key is stored in a separate file from the DB, so even if someone hacked Postgresus's DB (which isn't exposed externally anyway) — they still couldn't read anything. Encryption uses AES-256-GCM.

2) Encryption of backup files

Backup files are now also encrypted (optionally, but encryption is enabled by default). If you lost a file or saved it in public S3 — it's not so scary anymore.

Encryption uses both salt and a unique initialization vector. This prevents attackers from finding patterns to guess the encryption key, even if they steal all your backup files.

Encryption is done in streaming mode, AES-256-GCM is also used here.

3) Using read only user for backups

Despite the first two points, there's no 100% protection method. There's still a tiny chance that:

your server will be completely hacked, they'll get the secret key and legitimate IP address to access the database;
a hacker will somehow decrypt passwords in Postgresus's internal DB and find out your database password;
or, worse, it won't be a hacker but someone from inside;
and they'll corrupt your database, having previously deleted backups.

So Postgresus should help users minimize damage. The probability of such a hack may be near zero, but that's cold comfort if you're the one it happens to.

Now when you add a database user with write permissions to Postgresus, the system offers to automatically create a read only user and run backups through it. People are much more likely to create a read-only role when it takes one click instead of manually setting it up in the database.

Here's how Postgresus offers:

Very persistently offers:

With this approach, even if your Postgresus server gets hacked, everything gets decrypted and attackers gain access to your DB — they at least won't be able to corrupt the database. Knowing that not everything is lost is a pretty good consolation.

5) Compression by default, the most optimal one

The first version of Postgresus offered all compression algorithms that PostgreSQL supports: gzip, lz4 and zstd, with compression levels from 1 to 9. Honestly, I didn't really understand why anyone would need all these options. I just picked gzip with level 5 as what seemed like a reasonable middle ground.

But once the project went open source, I had to actually research this. So I ran 21 backups in all possible combinations and found the best trade-off between speed and size.

There's an article about this on Dev.to: "PostgreSQL backups: comparing pg_dump speed in different formats and with different compression levels".

So now by default for all backups zstd with compression level 5 is set, if PostgreSQL version is 16 and higher. If lower — still gzip (by the way, thanks again to contributors for PG 12 support). Here's zstd 5 compared to gzip 5 (it's at the bottom):

On average, backups are compressed ~8 times relative to the actual database size.

6) Kubernetes Helm support

This one is quick — we added native k8s support with Helm installation. Teams running k8s in the cloud can now deploy Postgresus more easily. Three contributors helped with this feature.

7) Dark theme

I'm not really a fan of dark themes. But there were many requests, so I added one (~~thanks Claude for the help and designer's eye~~). Surprisingly, it turned out better than the light theme and became the default. I even redesigned the website from light to dark — it looked that good.

Before:

After:

8) Get rid of incremental backups and PITR (Point In Time Recovery)

First, some context:

Logical backup — is when PostgreSQL itself exports its data (to a file or group of files).
Physical backup — is when we connect to PostgreSQL's hard drive and make a copy of its files.
Incremental backup with PITR support — is a physical backup + change log (WAL), copied from disk or via replication protocol.

I believed Postgresus should eventually support incremental backups. After all, that's what serious tools do! Even ChatGPT says serious tools can recover with precision down to the second and transaction.

So I started working on it. But then reality hit:

It's very hard to make it convenient in terms of UX and DevEx. Because you need to either physically connect to the disk with the DB, or set up replication.

For recovery — there's no option not to connect to the disk with the database. To recover from an incremental backup, the backup tool simply restores the pgdata folder (more precisely, part of it).

If the database is really big, for example, several TB and more — fine-tuning in configs is needed. Here UI is more of a hindrance than help.

Most clouds (AWS, Google, Selectel) won't work with external incremental backups, if they require disk access. In theory, with some workarounds, via replication — they will. But recovering from such a backup to a managed cloud still won't work anyway.
All clouds provide incremental backups out of the box. In general, this is one of the reasons why they're paid. And even they usually don't allow recovery second by second or to a specific transaction moment. And if you're not in the cloud — even more unlikely that your project is critical enough to require such precision.

Therefore, if Postgresus is making a backup of a managed DB — it's enough to do it roughly once a week. Just in case of unforeseen emergency or if the cloud doesn't allow storing backups long enough. Otherwise, rely on the cloud's own incremental backups.

For most projects, incremental backups aren't really that necessary. For small databases, granularity between backups of one hour is enough, if needed frequently. For large ones — at least once a day. This is enough for 99% of projects to find lost data or recover completely. These needs are fully covered by logical backups.

But if you're a bank or a managed DB developer, you really need the finest backup configuration for your tens and hundreds of terabytes of data. Here Postgresus will never outperform physical backups from WAL-G or pgBackRest in terms of console convenience and efficiency for DBs with volume in TB and more. But, in my opinion, these are already specialized tools for such tasks, made by geniuses and maintainers of PostgreSQL itself.

In my opinion, incremental backups are really needed in two cases:

In the past, when there wasn't such a choice of cloud managed databases and large projects required hosting everything yourself. Now the same banks, marketplaces and telecom have internal clouds with PITR out of the box.
You are a managed cloud. But here the task is radically more complex than just making backups and recovering from them.

Considering all this, I made a deliberate decision to drop incremental backup development. Instead, I'm focusing on making logical backups as convenient, reliable and practical for daily use by developers, DevOps, DBAs and companies.

9) Many small improvements

The points above are far from everything. 80% of the work usually isn't visible. Off the top of my head, here's a short list:

Buffering and RAM optimization. Postgresus no longer tries to buffer everything pg_dump sends while waiting for S3 to catch up (downloading from database is usually faster than uploading to cloud). RAM usage is now limited to 32 MB per parallel backup.
Various stability improvements and minor bug fixes.
Adding SMTP without username and without password. I didn't even know that happens...
Adding topics for sending notifications to Telegram groups.
Documentation appeared!
PostgreSQL 12 support.

And much more.

What didn't work out?

Of course, not everything works out and some things have to be dropped. I'm building Postgresus in my spare time, which barely exists. So I can't spread myself too thin or complicate UX with unnecessary features. Too many features is also bad.

1) Full database resource monitoring

I wanted to make Postgresus a PostgreSQL monitoring tool as well. Including system resources of the server running PostgreSQL:

CPU
RAM
ROM
IO speed and load

I even made the base for collecting metrics (any) and a template for graphs:

Turns out PostgreSQL only exposes RAM usage and IO metrics out of the box.

Monitoring other resources requires extensions. But not every database can install the extensions I need, so I could only collect partial metrics. Then I discovered that cloud databases often don't allow installing extensions at all.

Then I realized metrics should be stored in VictoriaMetrics or at least TimescaleDB, not in Postgresus's own PostgreSQL, which would complicate the image build.

In the end, this non-essential feature would add:

significant code complexity, meaning harder maintenance;
more image build requirements (additional components);
complicated UX (as I said, too many features is bad);
~~unclear positioning: are we a backup tool, a monitoring tool, or trying to do everything?~~

So I dropped resource monitoring and focused on making Postgresus the best backup tool it can be.

2) SQL console

I also wanted to add an SQL console. Since Postgresus is already connected to the DB, why not run queries directly from it? It would be convenient — no need to open PgAdmin, DataGrip or DBeaver every time.

I never found time to work on it, only planned. One contributor started on the feature and made a PR. But as expected with complex features, many requirements and edge cases came up:

need to add SQL syntax support;
add autocomplete and hints;
make lazy loading so that even 100MB of rows don't come to the browser;
add at least several exports to CSV and XLSX.

That's basically a full project on its own, not just one tab.

We decided to drop the feature and save the effort. This turned out to be the right call, since we added read only roles that don't allow INSERT, UPDATE and DELETE anyway.

Conclusion

That's the journey Postgresus has made in six months. It grew from a niche project to an enterprise-level tool that helps both individual developers and entire DBA teams (I was surprised to learn this just ~2 months after the first release, by the way). I'm genuinely glad that thousands of projects and users rely on Postgresus.

The project isn't stopping here. My goal now is to make Postgresus the most convenient PostgreSQL backup tool in the world. There's a large backlog of features and improvements coming gradually.

My main priorities remain:

project security — this is critical, without it everything else doesn't make sense;
convenient UX in terms of using the project itself and no excess of features (we do one task, but very well);
convenient UX in terms of deployment: so that everything deploys with one command and nothing needs to be configured out of the box;
project openness — completely open source and free for any person or company.

If you like the project or find it useful — I'd appreciate a star on GitHub or sharing it with friends ❤️

How to Create PostgreSQL Backups: A Complete Guide

Rostislav Dugin — Mon, 17 Nov 2025 14:23:41 +0000

Backing up your PostgreSQL database is one of the most critical tasks for any database administrator or development team. Without proper backups, you risk losing valuable data due to hardware failures, human errors, or security breaches. In this guide, we'll show you how to create automated, secure PostgreSQL backups using Postgresus - an enterprise-grade solution that's surprisingly easy to use.

What is Postgresus?

Postgresus is the most popular open-source solution for automated PostgreSQL backups, trusted by individual DBAs and enterprises alike. It's a free, self-hosted backup management system that combines enterprise-grade features with simplicity and ease of use.

Key highlights:

Enterprise-Grade Security: AES-256-GCM encryption protects your backup files
Multi-Cloud Support: Store backups on local storage, S3, Cloudflare R2, Google Drive, NAS, Dropbox, and more
Flexible Scheduling: Hourly, daily, weekly, or monthly backups at specific times
Smart Notifications: Real-time alerts via Email, Telegram, Slack, Discord, or webhooks
Team-Friendly: Built-in workspaces, role-based access control, and audit logs
Universal Compatibility: Supports PostgreSQL versions 12, 13, 14, 15, 16, 17, and 18
Open Source: Apache 2.0 licensed, fully transparent and inspectable

Whether you're managing a single database or orchestrating backups for an entire enterprise, Postgresus scales to meet your needs while maintaining simplicity.

Prerequisites

Before we begin, ensure you have:

A PostgreSQL database (version 12 or higher)
Docker installed on your system (or Linux for script-based installation)
Database credentials with appropriate backup permissions
A storage destination (local disk, cloud storage account, etc.)

Step 1: Install Postgresus

Postgresus offers multiple installation methods. Choose the one that best fits your environment.

Option A: Automated Script Installation (Linux Only, Recommended)

If you're on Linux, the easiest way to get started is with the automated installation script. This script will install Docker (if needed), set up Postgresus, and configure it to start automatically on system reboot.

sudo apt-get install -y curl && \
sudo curl -sSL https://raw.githubusercontent.com/RostislavDugin/postgresus/refs/heads/main/install-postgresus.sh \
| sudo bash

The script handles everything automatically:

✅ Installs Docker with Docker Compose (if not already installed)
✅ Sets up Postgresus
✅ Configures automatic startup on system reboot

Option B: Docker Run (Cross-Platform)

For a quick start on any platform (Linux, macOS, or Windows), use this single Docker command:

docker run -d \
  --name postgresus \
  -p 4005:4005 \
  -v ./postgresus-data:/postgresus-data \
  --restart unless-stopped \
  rostislavdugin/postgresus:latest

This command will:

Start Postgresus in a Docker container
Map port 4005 for web access
Store all data in the ./postgresus-data directory
Automatically restart the container on system reboot

Option C: Docker Compose

For more control over your deployment, create a docker-compose.yml file:

services:
  postgresus:
    container_name: postgresus
    image: rostislavdugin/postgresus:latest
    ports:
      - "4005:4005"
    volumes:
      - ./postgresus-data:/postgresus-data
    restart: unless-stopped

Then start the service:

docker compose up -d

Once installed, access the Postgresus dashboard at http://localhost:4005 in your web browser.

Step 2: Add Your Database and Configure Credentials

Now that Postgresus is running, let's add your first database for backup.

Open the Dashboard: Navigate to http://localhost:4005 in your browser
Create admin user: If this is your first time, create an admin account
Add New Database: Click the "New Database" button
Enter Connection Details:
- Host: Your PostgreSQL server address (e.g., localhost, 192.168.1.100, or db.example.com)
- Port: PostgreSQL port (default is 5432)
- Database Name: The name of the database you want to back up
- Username: PostgreSQL user with backup permissions
- Password: The user's password
- SSL Mode: Enable if your database requires SSL connections
Test Connection: Postgresus will automatically test the connection to ensure credentials are correct

Step 3: Enable Backup Encryption (Recommended)

One of Postgresus's standout features is built-in AES-256-GCM encryption. This is particularly important if you're storing backups in shared cloud storage or want zero-trust security.

To enable encryption:

Navigate to Encryption Settings: In the database configuration screen, find the "Encryption" section
Enable Encryption: Toggle the encryption option to "Enabled"
Confirm Settings: Save the encryption configuration

Why Encrypt?

Zero-Trust Storage: Encrypted backups are useless to anyone without the password, even if stored in shared cloud storage
Compliance: Meet data protection regulations (GDPR, HIPAA, etc.)
Peace of Mind: Your sensitive data remains protected even if storage is compromised

Note: You can still download unencrypted backups via the "Download" button in the dashboard for use with pg_restore or other tools.

Step 4: Select Storage Destination

Postgresus supports multiple storage options, giving you flexibility to choose the best fit for your infrastructure.

Available Storage Options:

Local Storage: Keep backups on your server or VPS
Amazon S3: Industry-standard cloud object storage
Cloudflare R2: S3-compatible with zero egress fees
Google Drive: Easy cloud storage integration
NAS: Network-attached storage devices
Dropbox: Popular cloud storage option
And more: Check supported storages

Configuring Your Storage:

Open Storage Settings: In the database configuration, navigate to "Storage Destination"
Select Storage Type: Choose from the available storage options
Enter Credentials:
- For Local Storage: Specify the backup directory path
- For S3-Compatible (S3, R2): Enter access key, secret key, bucket name, and region
- For Google Drive: Authenticate with OAuth
- For NAS: Provide network path and credentials
Set Retention Policy (Optional): Define how long to keep backups (e.g., keep last 30 days)
Test Storage: Postgresus will verify it can write to your chosen storage
Save Configuration: Confirm your storage settings

Step 5: Configure Backup Schedule

With your database and storage configured, it's time to set up the backup schedule.

Choose Frequency: Select how often backups should run
- Hourly: For high-frequency changes (every 1, 2, 4, 6, or 12 hours)
- Daily: Most common option, runs once per day
- Weekly: For less critical or infrequently changing databases
- Monthly: For archival purposes
Set Execution Time: Choose when backups should run
- Best Practice: Schedule during low-traffic periods (e.g., 3:00 AM)
- Reduces impact on database performance
- Ensures faster backup completion
Enable Compression: Postgresus uses smart compression to save 4-8x space with only ~20% overhead
- Automatically enabled by default
- Balances compression ratio with performance
Save and Activate: Enable the backup schedule to start automatic backups

Step 6: Set Up Notifications (Optional but Recommended)

Stay informed about your backup status with real-time notifications.

Navigate to Notifications: In the dashboard, go to "Notifiers"
Add Notification Channel: Click "Add Notifier"
Choose Channel:
- Email: Send notifications to your inbox
- Telegram: Instant messages to your phone
- Slack: Integrate with team channels
- Discord: For Discord-based teams
- Webhooks: Custom integrations with your systems
Configure Success/Failure Alerts:
- Get notified when backups complete successfully
- Receive immediate alerts if backups fail
- Include detailed error information for troubleshooting
Test Notification: Send a test message to verify configuration
Link to Database: Associate the notifier with your database backup configuration

Step 7: Verify and Monitor

After configuration, Postgresus will begin backing up your database according to the schedule.

Monitoring Your Backups:

Dashboard Overview: View all backup jobs, success rates, and storage usage
Backup History: See a complete log of all backup operations
Health Checks: Postgresus monitors backup job health and alerts on issues
Audit Logs: Track all system activities and configuration changes

Testing Backup Restoration:

It's crucial to periodically test that your backups can actually be restored:

Select a Backup: In the dashboard, choose a backup to restore
Download or Restore:
- Download the backup file for manual restoration with pg_restore
- Use one-click restore to a test database
Verify Data Integrity: Check that all data is intact and accessible

Remember: A backup you haven't tested is just hope, not a recovery plan!

Advanced Features for Teams and Enterprises

Workspaces

Group databases, notifiers, and storages for different projects or teams. Perfect for agencies managing multiple clients or enterprises with separate departments.

Role-Based Access Control

Assign different permission levels:

Viewer: Read-only access to view backup status
Member: Can manage backups within assigned workspaces
Admin: Full management capabilities

Audit Logging

Track every action taken in the system:

Who created or modified backup configurations
When backups were run or restored
Changes to access permissions
System configuration updates

Best Practices for PostgreSQL Backups

Test Regularly: Schedule quarterly restoration tests to ensure backups are viable
Monitor Actively: Set up notifications and regularly review backup logs
Secure Credentials: Use encryption and store passwords in secure vaults
Document Recovery Procedures: Create runbooks for emergency restoration scenarios
Review Retention Policies: Balance storage costs with recovery time objectives (RTO)

Conclusion

Creating reliable PostgreSQL backups doesn't have to be complicated. With Postgresus, you get an enterprise-grade backup solution that's as easy to use as it is powerful. In just a few steps, you've set up automated, encrypted backups with your choice of storage destinations and real-time notifications.

Whether you're a solo developer protecting a side project or an enterprise DBA managing critical production databases, Postgresus provides the tools you need without the complexity you don't want.

Key Takeaways:

✅ Installation takes minutes with Docker or automated scripts
✅ Enterprise-grade AES-256-GCM encryption protects your data
✅ Flexible storage options work with your existing infrastructure
✅ Automated scheduling ensures consistent backup coverage
✅ Real-time notifications keep you informed
✅ Open source and self-hosted means full control and transparency

Ready to secure your PostgreSQL databases? Get started with Postgresus today at postgresus.com.

Additional Resources:

I built Log Bull — the simple alternative to ELK, Loki and Graylog to collect logs from code (Python, Java, Go, JS, PHP, etc.)

Rostislav Dugin — Thu, 16 Oct 2025 06:15:29 +0000

Over the past ~5 years, I have often faced the task of collecting logs, usually from small or medium-sized codebases. Sending logs from code is not a problem: Java and Go have libraries for this practically out of the box. But deploying something to collect them is a headache. I understand that it's a solvable task (even before ChatGPT, and now even more so), but still. All logging systems are primarily geared toward the large-large enterprise world and its requirements, ~~rather than small teams or single developers with a few sticks, glue and a “yesterday” deadline~~.

Launching ELK is a challenge for me every time: a bunch of settings, a non-trivial deployment, and when I enter the UI, my eyes run wild from the tabs. With Loki and Graylog, it's a little easier, but there are still way more features than I need. At the same time, separating logs between projects and adding other users to the system so that they don't see anything they shouldn't — is not the most obvious process either.

So about a year ago, I decided to make my own log collection system: as easy to use and launch as possible. It would be deployed on the server with a single command, without any configuration or unnecessary tabs in the interface. That's how Log Bull came about, and now it's open source: a log collection system for developers with middle-sized projects.

Table of content:

About the project
How to deploy Log Bull?
How to send logs?
How to view logs?
Conclusion

About the project

Log Bull is a log collection system with an emphasis on ease of use (minimal configuration, minimal features, zero-config at startup). The project is completely open source under the Apache 2.0 license. My main priority was to create a solution that would allow a junior developer to easily figure out how to start the system, how to send logs to it, and how to view them in about 15 minutes.

Key features of the project:

Deployed with a single command via a .sh script or a Docker command.
You can create multiple isolated projects for collecting logs (and add users to them).
Extremely simple interface with minimal configuration, and no configuration required at all when starting (zero-config).
Libraries for Python, Java, Go, JavaScript (TS \ NodeJS), PHP, C#. Rust and Ruby are planned.
Free, open source and self-hosted.
No need to know LogQL, Kibana DSL or other query languages to search logs.

The project is developed in Go and built on Open Search.

Project website - https://logbull.com
Project GitHub - https://github.com/logbull/logbull

P.S. If you find the project useful and have a GitHub account, please give it a star ⭐️. The first stars are hard to collect. I would be extremely grateful for your support!

How to deploy Log Bull?

There are three ways to deploy a project: via a .sh script (which I recommend), via Docker and via Docker Compose.

Method 1: Installation via script

The script will install Docker, place the project in the /opt/logbull folder, and configure autostart when the system is restarted. Installation command:

sudo apt-get install -y curl && \
sudo curl -sSL https://raw.githubusercontent.com/logbull/logbull/main/install-logbull.sh \
| sudo bash

The system will be available on port 4005.

Method 2: Launch via Docker Compose

Create file docker-compose.yml with the following content:

services:
  logbull:
    container_name: logbull
    image: logbull/logbull:latest
    ports:
      - "4005:4005"
    volumes:
      - ./logbull-data:/logbull-data
    restart: unless-stopped
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:4005/api/v1/system/health"]
      interval: 5s
      timeout: 5s
      retries: 30

And run the command docker compose up -d. The system will start on port 4005.

Method 3: Launch via Docker command

Run the following command in the terminal (the system will also start on port 4005):

docker run -d \
  --name logbull \
  -p 4005:4005 \
  -v ./logbull-data:/logbull-data \
  --restart unless-stopped \
  --health-cmd="curl -f http://localhost:4005/api/v1/system/health || exit 1" \
  --health-interval=5s \
  --health-retries=30 \
  logbull/logbull:latest

How to send logs?

I designed the project with convenience in mind, primarily for developers. That's why I created libraries for most popular development languages. I did this with the idea that Log Bull can be connected to any popular library as a processor without changing the current code base.

I highly recommend checking out the examples on the website, because there is an interactive panel for selecting a language:

Let's take Python as an example. First, you need to install the library (although you can also send it via HTTP; there are examples for cURL):

pip install logbull

Then send from code:

import time
from logbull import LogBullLogger

# Initialize logger
logger = LogBullLogger(
    host="http://LOGBULL_HOST",
    project_id="LOGBULL_PROJECT_ID",
)

# Log messages (printed to console AND sent to LogBull)
logger.info("User logged in successfully", fields={
    "user_id": "12345",
    "username": "john_doe",
    "ip": "192.168.1.100"
})

# With context
session_logger = logger.with_context({
    "session_id": "sess_abc123",
    "user_id": "user_456"
})

session_logger.info("Processing request", fields={
    "action": "purchase"
})

# Ensure all logs are sent before exiting
logger.flush()
time.sleep(5)

How to view logs?

All logs are displayed immediately on the main screen. You can:

Reduce the size of messages (by cutting the line to ~50-100 characters).
Expand the list of sent fields (user_id, order_id, etc.).
Click on a field and add it to the filter.

Logs search with conditions:

Regular logs view:

Viewing messages text only (you can cut extra fields):

You can also collect groups of conditions (for example, the message includes certain text, but exclude a specific server IP address).

Conclusion

I hope my log collection system will be useful to those developers who do not want or cannot (due to limited project resources) implement “heavyweight” solutions such as ELK. I am already using Log Bull in production projects, and everything is going well. I welcome feedback, suggestions for improvement, and issues on GitHub.

PostgreSQL backups: comparing pg_dump speed in different formats and with different compression levels

Rostislav Dugin — Thu, 31 Jul 2025 12:31:03 +0000

I made a backup using pg_dump and restored it 21 times. I made backups in 4 different formats using 1 to 7 compression levels for each format. I recorded the results and compared the different types to understand which methods are more effective for my use case.

Details and measurements are below.

Table of content:

Why do I need this measurement?
Backup formats and compression types in pg_dump
PostgreSQL configuration
Data preparation
Measurement results
Conclusions based on measurements
Conclusion

Why do I need this measurement?

I had a very specific task: to find the best backup format using the standard pg_dump. “Best” means the optimal ratio of backup creation speed, recovery speed and final file size.

I used this information in my open source project for PostgreSQL backups called Postgresus. There is an overview article about it on dev.to:

Rostislav Dugin

Jul 22 '25

How I got into open source in 2025 (with a PostgreSQL backup tool) after almost losing a ~$1,500/mo project in 2023

#webdev #opensource #postgres #programming

Comments 2

6 min read

There were additional requirements:

the backup must be compressed before being sent to my server to minimize network usage;
the backup file itself must be a single file (rather than, for example, a directory) so it can be streamed to a disk, S3 or the cloud;
the method of creating a backup should not require any database configuration in advance (therefore, PgBackRest, WAL-G, and pg_basebackup were ruled out) in order to be easy to integrate into an open source project and work with any database (installed locally, in Docker, in DBaaS, with a read replica, etc.).

Backup formats and compression types in pg_dump

pg_dump supports 4 formats:

Format	Compression	Single file?	Parallel backup	Parallel restore
Plain (SQL)	❌	✅	❌	❌
Custom (-Fc)	✅	✅	❌	✅
Directory (-Fd)	✅	❌	✅	✅
Tar (-Ft)	✅	✅	❌	❌

I was most interested in the custom format and directory format. They support parallel processing of backups. The custom format cannot create a backup in parallel (only restore), but writes it to a single file. The directory format can both back up and restore in parallel, but writes everything to a directory.

For these formats the following compression types are supported:

Name	Feature (in theory)	Compression / Decompression Speed (in theory)	Compression Ratio (in theory)
gzip	Standard compression algorithm	Medium / High	2–3×
lz4	Compression algorithm optimized for higher speed than gzip	Very High / Very High	1.5–2×
zstd	Relatively new (2016) algorithm developed at Facebook: balance of speed and compression	High / High	3–5×

The compression characteristics described are based on perfectly prepared data. In the case of a database, compression does not take up 100% of the time with 100% CPU utilization. There are many database-specific operations that are likely to slow down compression (especially “on the fly”).

Before the test, I assumed that a custom format with gzip compression would be the best option for me (as a happy medium) and had cautious hopes for zstd (as a more modern format). By the way, zstd only began to be supported in PostgreSQL 15.

PostgreSQL configuration

I launched two PostgreSQL instances in Docker Compose: one for creating backups (with data) and one for restoring from them. I didn’t use the standard ports because they are already in use by my local versions of PostgreSQL.

docker-compose.yml

version: "3.8"

services:
  db:
    image: postgres:17
    container_name: db
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: testpassword
    ports:
      - "7000:7000"
    command: -p 7000
    volumes:
      - ./pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d testdb -p 7000"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: unless-stopped

  db-restore:
    image: postgres:17
    container_name: db-restore
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: testpassword
    ports:
      - "7001:7001"
    command: -p 7001
    volumes:
      - ./pgdata-restore:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d testdb -p 7001"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: unless-stopped
    depends_on:
      - db

Then I updated postgresql.conf a little to use more computer resources. I have an AMD Ryzen 9 7950X (16 cores, 32 threads), 64 GB of RAM and a 1 TB NVMe drive. I configured the database to use 4 threads and 16 GB of memory via PgTune.

postgresql.conf

# DB Version: 17
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 16 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd

max_connections = 100
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 40329kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

listen_addresses = '*'

Data preparation

To begin with, I created a database with 3 tables and 9 indexes with a total size of ~11 GB. The data is as diverse as possible. I am more than sure that pg_dump works better with some types of data and worse with others. But my project is aimed at a wide audience, so it is important to measure the “average across the board.”

Below is the tables structure.

Tables:

Table name	Purpose	Columns
large_test_table	Employees and users with various data types	18 columns including name, email, address, salary, etc.
orders	Order data and history of changes	13 columns including user_id, order_number, amounts, etc.
activity_logs	User activity logs with large text fields	13 columns including user_id, action, details, timestamps, etc.

Indexes:

Table	Index Name	Column	Purpose
large_test_table	idx_large_test_name	name	Search by name
large_test_table	idx_large_test_email	email	Search by email
large_test_table	idx_large_test_created_at	created_at	Search by time range
large_test_table	idx_large_test_department	department	Filter by department
orders	idx_orders_user_id	user_id	Search user’s orders
orders	idx_orders_order_date	order_date	Search by date/time
orders	idx_orders_status	status	Search by status
activity_logs	idx_activity_user_id	user_id	Search by ID
activity_logs	idx_activity_timestamp	timestamp	Search by date
activity_logs	idx_activity_action	action	Search by action

The data is generated and inserted into the database using a Python script. The algorithm is as follows:

25,000 rows of data are generated;
100,000 rows are inserted into each table in turn using COPY;
when the database reaches 10 GB in size, indexes are created.

Script to fill database with data

#!/usr/bin/env python3
"""
Script to populate PostgreSQL database with ~10GB of test data for backup performance testing.
Inserts 1 million rows into each of 3 tables until reaching 10GB target.
"""

import psycopg2
import random
import string
import time
import io
from datetime import datetime, timedelta
import sys


# Database connection parameters
DB_CONFIG = {
    "host": "localhost",
    "port": 7000,
    "database": "testdb",
    "user": "postgres",
    "password": "testpassword",
}

# Target database size in bytes (10GB)
TARGET_SIZE_GB = 10
TARGET_SIZE_BYTES = TARGET_SIZE_GB * 1024 * 1024 * 1024

# Rows per table per round
ROWS_PER_TABLE = 100000


def generate_random_string(length):
    """Generate a random string of specified length."""
    return "".join(random.choices(string.ascii_letters + string.digits + " ", k=length))


def generate_random_date():
    """Generate a random date within the last 5 years."""
    start_date = datetime.now() - timedelta(days=5 * 365)
    random_days = random.randint(0, 5 * 365)
    return start_date + timedelta(days=random_days)


def create_tables(cursor):
    """Create the 3 test tables with various data types."""
    print("Creating 3 tables...")

    # Table 1: Large table with mixed data types
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS large_test_table (
            id BIGSERIAL PRIMARY KEY,
            name VARCHAR(100),
            description TEXT,
            email VARCHAR(255),
            phone VARCHAR(20),
            address TEXT,
            city VARCHAR(100),
            country VARCHAR(100),
            postal_code VARCHAR(20),
            birth_date DATE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            salary DECIMAL(10,2),
            is_active BOOLEAN DEFAULT TRUE,
            rating FLOAT,
            notes TEXT,
            department VARCHAR(100),
            employee_id VARCHAR(50)
        )
    """)

    # Table 2: Orders table for transactional data
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id BIGSERIAL PRIMARY KEY,
            user_id BIGINT,
            order_number VARCHAR(50) UNIQUE,
            total_amount DECIMAL(12,2),
            order_date TIMESTAMP,
            status VARCHAR(20),
            shipping_address TEXT,
            notes TEXT,
            payment_method VARCHAR(50),
            shipping_method VARCHAR(50),
            discount_amount DECIMAL(10,2),
            product_list TEXT,
            customer_notes TEXT
        )
    """)

    # Table 3: Activity logs with lots of text data
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS activity_logs (
            id BIGSERIAL PRIMARY KEY,
            user_id BIGINT,
            action VARCHAR(100),
            details TEXT,
            ip_address INET,
            user_agent TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            session_id VARCHAR(100),
            browser VARCHAR(50),
            operating_system VARCHAR(50),
            referrer TEXT,
            response_time INTEGER,
            error_message TEXT
        )
    """)

    print("3 tables created successfully!")


def create_indexes(cursor):
    """Create indexes for all 3 tables."""
    print("Creating indexes...")

    # Indexes for large_test_table
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_name ON large_test_table(name)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_email ON large_test_table(email)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_created_at ON large_test_table(created_at)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_department ON large_test_table(department)"
    )

    # Indexes for orders
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id)")
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_orders_order_date ON orders(order_date)"
    )
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status)")

    # Indexes for activity_logs
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_activity_user_id ON activity_logs(user_id)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_activity_timestamp ON activity_logs(timestamp)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_activity_action ON activity_logs(action)"
    )

    print("Indexes created successfully!")


def drop_indexes(cursor):
    """Drop indexes for faster bulk loading."""
    print("Dropping indexes for bulk loading...")

    indexes_to_drop = [
        "idx_large_test_name",
        "idx_large_test_email",
        "idx_large_test_created_at",
        "idx_large_test_department",
        "idx_orders_user_id",
        "idx_orders_order_date",
        "idx_orders_status",
        "idx_activity_user_id",
        "idx_activity_timestamp",
        "idx_activity_action",
    ]

    for index in indexes_to_drop:
        try:
            cursor.execute(f"DROP INDEX IF EXISTS {index}")
        except:
            pass

    print("Indexes dropped!")


def print_progress(inserted, total, start_time, operation_name):
    """Print detailed progress information."""
    current_time = time.time()
    elapsed = current_time - start_time
    rate = inserted / elapsed if elapsed > 0 else 0
    percentage = (inserted / total) * 100

    if rate > 0:
        eta_seconds = (total - inserted) / rate
        eta_minutes = eta_seconds / 60
        eta_str = f"ETA: {eta_minutes:.1f}m"
    else:
        eta_str = "ETA: calculating..."

    print(
        f"{operation_name}: {inserted:,} / {total:,} ({percentage:.1f}%) | "
        f"Rate: {rate:,.0f} rows/sec | Elapsed: {elapsed / 60:.1f}m | {eta_str}"
    )


def populate_large_table_batch(cursor, count=ROWS_PER_TABLE):
    """Populate large_test_table with specified number of rows."""
    print(f"Inserting {count:,} rows into large_test_table...")

    batch_size = 25000
    inserted = 0
    start_time = time.time()

    while inserted < count:
        current_batch_size = min(batch_size, count - inserted)
        data_buffer = io.StringIO()

        for i in range(current_batch_size):
            name = (
                generate_random_string(random.randint(20, 100))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            description = (
                generate_random_string(random.randint(100, 500))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            email = f"{generate_random_string(10)}@{generate_random_string(10)}.com"
            phone = f"+1-{random.randint(100, 999)}-{random.randint(100, 999)}-{random.randint(1000, 9999)}"
            address = (
                generate_random_string(random.randint(50, 200))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            city = (
                generate_random_string(random.randint(10, 50))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            country = (
                generate_random_string(random.randint(5, 30))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            postal_code = f"{random.randint(10000, 99999)}"
            birth_date = generate_random_date().date()
            salary = f"{random.randint(30000, 150000) + random.random():.2f}"
            is_active = "t" if random.choice([True, False]) else "f"
            rating = f"{random.uniform(1.0, 5.0):.2f}"
            notes = (
                generate_random_string(random.randint(50, 300))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            department = random.choice(
                ["HR", "Engineering", "Sales", "Marketing", "Finance", "Operations"]
            )
            employee_id = (
                f"EMP-{random.randint(1000, 9999)}-{generate_random_string(4)}"
            )

            data_buffer.write(
                f"{name}\t{description}\t{email}\t{phone}\t{address}\t{city}\t{country}\t{postal_code}\t{birth_date}\t{salary}\t{is_active}\t{rating}\t{notes}\t{department}\t{employee_id}\n"
            )

        data_buffer.seek(0)
        cursor.copy_from(
            data_buffer,
            "large_test_table",
            columns=(
                "name",
                "description",
                "email",
                "phone",
                "address",
                "city",
                "country",
                "postal_code",
                "birth_date",
                "salary",
                "is_active",
                "rating",
                "notes",
                "department",
                "employee_id",
            ),
            sep="\t",
        )

        inserted += current_batch_size
        if inserted % 100000 == 0:
            print_progress(inserted, count, start_time, "Large table")

    elapsed = time.time() - start_time
    print(
        f"Completed large_test_table: {inserted:,} rows in {elapsed:.2f}s ({inserted / elapsed:,.0f} rows/sec)"
    )


def populate_orders_batch(cursor, count=ROWS_PER_TABLE):
    """Populate orders table with specified number of rows."""
    print(f"Inserting {count:,} rows into orders...")

    batch_size = 25000
    inserted = 0
    start_time = time.time()

    while inserted < count:
        current_batch_size = min(batch_size, count - inserted)
        data_buffer = io.StringIO()

        for i in range(current_batch_size):
            user_id = random.randint(1, 10000000)
            order_number = f"ORD-{time.time_ns()}-{i:06d}"
            total_amount = f"{random.uniform(10.0, 5000.0):.2f}"
            order_date = generate_random_date()
            status = random.choice(
                ["pending", "processing", "shipped", "delivered", "cancelled"]
            )
            shipping_address = (
                generate_random_string(random.randint(100, 300))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            notes = (
                generate_random_string(random.randint(50, 200))
                .replace("\t", " ")
                .replace("\n", " ")
                if random.random() > 0.5
                else ""
            )
            payment_method = random.choice(["credit_card", "paypal", "bank_transfer"])
            shipping_method = random.choice(["standard", "express", "overnight"])
            discount_amount = (
                f"{random.uniform(0, 50) if random.random() > 0.7 else 0:.2f}"
            )
            product_list = (
                generate_random_string(random.randint(200, 800))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            customer_notes = (
                generate_random_string(random.randint(100, 400))
                .replace("\t", " ")
                .replace("\n", " ")
            )

            data_buffer.write(
                f"{user_id}\t{order_number}\t{total_amount}\t{order_date}\t{status}\t{shipping_address}\t{notes}\t{payment_method}\t{shipping_method}\t{discount_amount}\t{product_list}\t{customer_notes}\n"
            )

        data_buffer.seek(0)
        cursor.copy_from(
            data_buffer,
            "orders",
            columns=(
                "user_id",
                "order_number",
                "total_amount",
                "order_date",
                "status",
                "shipping_address",
                "notes",
                "payment_method",
                "shipping_method",
                "discount_amount",
                "product_list",
                "customer_notes",
            ),
            sep="\t",
        )

        inserted += current_batch_size
        if inserted % 100000 == 0:
            print_progress(inserted, count, start_time, "Orders")

    elapsed = time.time() - start_time
    print(
        f"Completed orders: {inserted:,} rows in {elapsed:.2f}s ({inserted / elapsed:,.0f} rows/sec)"
    )


def populate_activity_logs_batch(cursor, count=ROWS_PER_TABLE):
    """Populate activity_logs table with specified number of rows."""
    print(f"Inserting {count:,} rows into activity_logs...")

    batch_size = 25000
    inserted = 0
    start_time = time.time()
    actions = [
        "login",
        "logout",
        "view_product",
        "add_to_cart",
        "checkout",
        "update_profile",
        "search",
        "download",
    ]

    while inserted < count:
        current_batch_size = min(batch_size, count - inserted)
        data_buffer = io.StringIO()

        for i in range(current_batch_size):
            user_id = random.randint(1, 10000000)
            action = random.choice(actions)
            details = (
                generate_random_string(random.randint(100, 500))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            ip_address = f"{random.randint(1, 255)}.{random.randint(1, 255)}.{random.randint(1, 255)}.{random.randint(1, 255)}"
            user_agent = f"Mozilla/5.0 ({generate_random_string(50)}) {generate_random_string(30)}".replace(
                "\t", " "
            ).replace("\n", " ")
            timestamp = generate_random_date()
            session_id = generate_random_string(32)
            browser = random.choice(["Chrome", "Firefox", "Safari", "Edge"])
            operating_system = random.choice(
                ["Windows", "macOS", "Linux", "iOS", "Android"]
            )
            referrer = (
                f"https://{generate_random_string(10)}.com"
                if random.random() > 0.3
                else ""
            )
            response_time = random.randint(50, 5000)
            error_message = (
                generate_random_string(random.randint(100, 300))
                .replace("\t", " ")
                .replace("\n", " ")
                if random.random() > 0.8
                else ""
            )

            data_buffer.write(
                f"{user_id}\t{action}\t{details}\t{ip_address}\t{user_agent}\t{timestamp}\t{session_id}\t{browser}\t{operating_system}\t{referrer}\t{response_time}\t{error_message}\n"
            )

        data_buffer.seek(0)
        cursor.copy_from(
            data_buffer,
            "activity_logs",
            columns=(
                "user_id",
                "action",
                "details",
                "ip_address",
                "user_agent",
                "timestamp",
                "session_id",
                "browser",
                "operating_system",
                "referrer",
                "response_time",
                "error_message",
            ),
            sep="\t",
        )

        inserted += current_batch_size
        if inserted % 100000 == 0:
            print_progress(inserted, count, start_time, "Activity logs")

    elapsed = time.time() - start_time
    print(
        f"Completed activity_logs: {inserted:,} rows in {elapsed:.2f}s ({inserted / elapsed:,.0f} rows/sec)"
    )


def get_database_size_bytes(cursor):
    """Get the current database size in bytes."""
    cursor.execute("SELECT pg_database_size('testdb')")
    return cursor.fetchone()[0]


def get_database_size_mb(cursor):
    """Get the current database size in MB."""
    size_bytes = get_database_size_bytes(cursor)
    return size_bytes / (1024 * 1024)


def format_size_mb(size_bytes):
    """Format size in bytes to MB string."""
    return f"{size_bytes / (1024 * 1024):.1f}MB"


def main():
    """Main function to populate the database until reaching 10GB."""
    print("Starting database population for backup performance testing...")
    print(f"Target: {TARGET_SIZE_GB * 1024:.0f}MB of data")
    print(
        f"Strategy: Insert {ROWS_PER_TABLE:,} rows into each of 3 tables per round until target reached"
    )
    print("-" * 80)

    try:
        # Connect to database
        print("Connecting to database...")
        conn = psycopg2.connect(**DB_CONFIG)
        conn.autocommit = False
        cursor = conn.cursor()

        initial_size_mb = get_database_size_mb(cursor)
        print(f"Initial database size: {initial_size_mb:.1f}MB")
        overall_start_time = time.time()

        # Create tables
        create_tables(cursor)
        conn.commit()

        # Drop indexes for faster loading
        drop_indexes(cursor)
        conn.commit()

        round_number = 1
        current_size_bytes = get_database_size_bytes(cursor)

        # Keep adding rounds of 1M rows per table until we reach 10GB
        while current_size_bytes < TARGET_SIZE_BYTES:
            print(f"\n{'=' * 20} ROUND {round_number} {'=' * 20}")
            round_start_time = time.time()

            # Populate all 3 tables with 1M rows each
            populate_large_table_batch(cursor)
            conn.commit()

            populate_orders_batch(cursor)
            conn.commit()

            populate_activity_logs_batch(cursor)
            conn.commit()

            # Check current size
            current_size_bytes = get_database_size_bytes(cursor)
            current_size_mb = format_size_mb(current_size_bytes)
            round_elapsed = time.time() - round_start_time

            print(f"\nRound {round_number} completed in {round_elapsed:.2f}s")
            print(f"Current database size: {current_size_mb}")
            print(
                f"Progress: {(current_size_bytes / TARGET_SIZE_BYTES) * 100:.1f}% of target ({TARGET_SIZE_GB * 1024:.0f}MB)"
            )

            if current_size_bytes >= TARGET_SIZE_BYTES:
                print(f"✅ Target size reached!")
                break

            round_number += 1

        # Recreate indexes
        print(f"\n{'=' * 20} CREATING INDEXES {'=' * 20}")
        index_start_time = time.time()
        create_indexes(cursor)
        conn.commit()
        index_elapsed = time.time() - index_start_time
        print(f"Indexes created in {index_elapsed:.2f}s")

        # Final statistics
        overall_elapsed = time.time() - overall_start_time
        final_size_mb = get_database_size_mb(cursor)

        print(f"\n{'=' * 60}")
        print("DATABASE POPULATION COMPLETED!")
        print(f"Final database size: {final_size_mb:.1f}MB")
        print(f"Total rounds: {round_number}")
        print(
            f"Total time: {overall_elapsed:.2f} seconds ({overall_elapsed / 60:.2f} minutes)"
        )

        # Show table statistics
        cursor.execute("""
            SELECT 
                relname,
                n_tup_ins as "Rows",
                round(pg_total_relation_size('public.'||relname)::numeric / (1024*1024), 1) as "Size_MB"
            FROM pg_stat_user_tables 
            ORDER BY pg_total_relation_size('public.'||relname) DESC
        """)

        print(f"\nTable Statistics:")
        print(f"{'Table':<20} | {'Rows':<12} | {'Size':<10}")
        print("-" * 50)
        for row in cursor.fetchall():
            print(f"{row[0]:<20} | {row[1]:>12,} | {row[2]:.1f}MB")

        cursor.close()
        conn.close()

    except psycopg2.Error as e:
        print(f"Database error: {e}")
        sys.exit(1)
    except Exception as e:
        print(f"Error: {e}")
        sys.exit(1)


if __name__ == "__main__":
    main()

Measurement results

After 21 creations and restores, I obtained the following table with data that includes:

backup creation speed;
restore speed from backup;
total time;
backup size relative to the original database size.

The table with raw CSV data:

tool,format,compression_method,compression_level,backup_duration_seconds,restore_duration_seconds,total_duration_seconds,backup_size_bytes,database_size_bytes,restored_db_size_bytes,compression_ratio,backup_success,restore_success,backup_error,restore_error,timestamp
pg_dump,plain,none,0,100.39210295677185,735.2188968658447,835.6109998226166,9792231003,11946069139,11922173075,0.8197031918249641,True,True,,,2025-07-29T09:56:20.611844
pg_dump,custom,none,0,264.56927490234375,406.6467957496643,671.216070652008,6862699613,11946069139,11943709843,0.5744734550878778,True,True,,,2025-07-29T10:07:37.226681
pg_dump,custom,gzip,1,214.07211470603943,383.0168492794037,597.0889639854431,7074031563,11946069139,11943611539,0.5921639562511493,True,True,,,2025-07-29T10:17:39.801883
pg_dump,custom,gzip,5,260.6179132461548,393.76623010635376,654.3841433525085,6866440205,11946069139,11943718035,0.5747865783384196,True,True,,,2025-07-29T10:28:40.167485
pg_dump,custom,gzip,9,272.3802499771118,385.1409020423889,657.5211520195007,6856264586,11946069139,11943619731,0.5739347819121977,True,True,,,2025-07-29T10:39:42.912960
pg_dump,custom,lz4,1,84.0079517364502,379.6986663341522,463.7066180706024,9146843234,11946069139,11943685267,0.765678075990583,True,True,,,2025-07-29T10:47:32.131593
pg_dump,custom,lz4,5,150.24981474876404,393.44346714019775,543.6932818889618,8926348325,11946069139,11943718035,0.7472205477078983,True,True,,,2025-07-29T10:56:41.333595
pg_dump,custom,lz4,12,220.93980932235718,418.26913809776306,639.2089474201202,8923243046,11946069139,11943767187,0.7469606062188722,True,True,,,2025-07-29T11:07:26.574678
pg_dump,custom,zstd,1,87.83108067512512,419.07846903800964,506.90954971313477,6835388679,11946069139,11943767187,0.5721872692570225,True,True,,,2025-07-29T11:15:59.917828
pg_dump,custom,zstd,5,102.42366409301758,413.64263129234314,516.0662953853607,6774137561,11946069139,11944357011,0.567059966100871,True,True,,,2025-07-29T11:24:42.075008
pg_dump,custom,zstd,15,844.7868592739105,388.23959374427795,1233.0264530181885,6726189591,11946069139,11943636115,0.5630462633973209,True,True,,,2025-07-29T11:45:17.885163
pg_dump,custom,zstd,22,5545.566084384918,404.1370210647583,5949.7031054496765,6798947241,11946069139,11943750803,0.5691367731000038,True,True,,,2025-07-29T13:24:30.014902
pg_dump,directory,none,0,114.9900906085968,395.2716040611267,510.2616946697235,6854332396,11946069139,11943693459,0.5737730391684116,True,True,,,2025-07-29T13:33:05.944191
pg_dump,directory,lz4,1,53.48561334609985,384.92091369628906,438.4065270423889,9146095976,11946069139,11943668883,0.7656155233641663,True,True,,,2025-07-29T13:40:30.590719
pg_dump,directory,lz4,5,83.44352841377258,410.42058181762695,493.86411023139954,8925601067,11946069139,11943718035,0.7471579950814815,True,True,,,2025-07-29T13:48:50.201990
pg_dump,directory,lz4,12,114.15110802650452,400.04946303367615,514.2005710601807,8922495788,11946069139,11943758995,0.7468980535924554,True,True,,,2025-07-29T13:57:30.419171
pg_dump,directory,zstd,1,57.22735643386841,414.4600088596344,471.6873652935028,6835014976,11946069139,11943750803,0.5721559867493079,True,True,,,2025-07-29T14:05:28.529630
pg_dump,directory,zstd,5,60.121564865112305,398.27933716773987,458.4009020328522,6773763858,11946069139,11943709843,0.5670286835931563,True,True,,,2025-07-29T14:13:13.472761
pg_dump,directory,zstd,15,372.43965554237366,382.9877893924713,755.427444934845,6725815888,11946069139,11943644307,0.5630149808896062,True,True,,,2025-07-29T14:25:54.580924
pg_dump,directory,zstd,22,2637.47145485878,394.4939453601837,3031.9654002189636,6798573538,11946069139,11943660691,0.5691054905922891,True,True,,,2025-07-29T15:16:29.450828
pg_dump,tar,none,0,126.3212628364563,664.1294028759003,790.4506657123566,9792246784,11946069139,11942759571,0.8197045128452776,True,True,,,2025-07-29T15:29:45.280592

Script for measurement


python
#!/usr/bin/env python3
"""
Comprehensive PostgreSQL backup performance testing script.
Tests pg_dump with all possible formats and compression levels.
"""

import subprocess
import time
import os
import shutil
import json
import csv
from datetime import datetime
from pathlib import Path
import psycopg2
import argparse


def log_with_timestamp(message):
    """Print a message with timestamp."""
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(f"[{timestamp}] {message}")


# PostgreSQL binary path
PG_BIN_PATH = Path("./postgresql-17/bin")

# Database connection parameters
DB_CONFIG = {
    "host": "localhost",
    "port": 7000,
    "database": "testdb",
    "user": "postgres",
    "password": "testpassword",
}

# Restore database connection parameters
RESTORE_DB_CONFIG = {
    "host": "localhost",
    "port": 7001,
    "database": "testdb",
    "user": "postgres",
    "password": "testpassword",
}

# Test configurations
PG_DUMP_FORMATS = [
    ("plain", "sql"),
    ("custom", "dump"),
    ("directory", "dir"),
    ("tar", "tar"),
]

COMPRESSION_LEVELS = {
    "gzip": [1, 5, 9],
    "lz4": [1, 5, 12],
    "zstd": [1, 5, 15, 22],
}

# Available compression methods (depends on PostgreSQL version)
COMPRESSION_METHODS = ["none", "gzip", "lz4", "zstd"]

# Results storage
results: list[dict] = []


def ensure_backup_directory():
    """Ensure backup directory exists and is clean."""
    backup_dir = Path("./backups")
    if backup_dir.exists():
        shutil.rmtree(backup_dir)
    backup_dir.mkdir(exist_ok=True)
    return backup_dir


def get_database_size():
    """Get current database size in bytes."""
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SELECT pg_database_size('testdb')")
        size = cursor.fetchone()[0]
        cursor.close()
        conn.close()
        return size
    except Exception as e:
        log_with_timestamp(f"Error getting database size: {e}")
        return 0


def get_restore_database_size():
    """Get restore database size in bytes."""
    try:
        conn = psycopg2.connect(**RESTORE_DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SELECT pg_database_size('testdb')")
        size = cursor.fetchone()[0]
        cursor.close()
        conn.close()
        return size
    except Exception as e:
        log_with_timestamp(f"Error getting restore database size: {e}")
        return 0


def clean_restore_database():
    """Clean the restore database by dropping and recreating it."""
    import time

    try:
        # Connect to postgres database to drop/create testdb
        restore_config = RESTORE_DB_CONFIG.copy()
        restore_config["database"] = "postgres"

        conn = psycopg2.connect(**restore_config)
        conn.autocommit = True
        cursor = conn.cursor()

        # Terminate any existing connections to testdb with retry logic
        max_attempts = 5
        for attempt in range(max_attempts):
            try:
                # Get count of active connections first
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM pg_stat_activity
                    WHERE datname = 'testdb' AND pid <> pg_backend_pid()
                """)
                active_connections = cursor.fetchone()[0]

                if active_connections == 0:
                    break

                log_with_timestamp(
                    f"Found {active_connections} active connections to testdb, terminating..."
                )

                # Terminate connections
                cursor.execute("""
                    SELECT pg_terminate_backend(pid)
                    FROM pg_stat_activity
                    WHERE datname = 'testdb' AND pid <> pg_backend_pid()
                """)

                # Fetch all results to ensure the query completes
                terminated_pids = cursor.fetchall()
                log_with_timestamp(f"Terminated {len(terminated_pids)} connections")

                # Wait a bit for connections to actually close
                time.sleep(2)

            except Exception as term_error:
                log_with_timestamp(
                    f"Warning: Error terminating connections (attempt {attempt + 1}): {term_error}"
                )
                if attempt < max_attempts - 1:
                    time.sleep(1)
                else:
                    # Continue anyway, maybe we can still drop the database
                    break

        # Try to drop database with retry logic
        drop_attempts = 3
        for attempt in range(drop_attempts):
            try:
                cursor.execute("DROP DATABASE IF EXISTS testdb")
                log_with_timestamp("Database testdb dropped successfully")
                break
            except Exception as drop_error:
                if (
                    "is being accessed by other users" in str(drop_error)
                    and attempt < drop_attempts - 1
                ):
                    log_with_timestamp(
                        f"Database still in use, waiting and retrying... (attempt {attempt + 1})"
                    )
                    time.sleep(3)
                else:
                    raise drop_error

        # Create fresh database
        cursor.execute("CREATE DATABASE testdb")

        cursor.close()
        conn.close()

        log_with_timestamp("✓ Restore database cleaned and recreated")
        return True

    except Exception as e:
        log_with_timestamp(f"✗ Error cleaning restore database: {e}")
        return False


def wait_for_restore_db():
    """Wait for restore database to be ready."""
    import time

    max_attempts = 30
    for attempt in range(max_attempts):
        try:
            conn = psycopg2.connect(**RESTORE_DB_CONFIG)
            conn.close()
            return True
        except:
            if attempt < max_attempts - 1:
                time.sleep(1)
            else:
                log_with_timestamp("✗ Restore database not ready after 30 seconds")
                return False


def restore_pg_dump(backup_path, format_name):
    """Restore a pg_dump backup."""
    env = os.environ.copy()
    env["PGPASSWORD"] = RESTORE_DB_CONFIG["password"]

    if format_name == "plain":
        # Use psql for plain format
        command = [
            str(PG_BIN_PATH / "psql.exe"),
            "-h",
            RESTORE_DB_CONFIG["host"],
            "-p",
            str(RESTORE_DB_CONFIG["port"]),
            "-U",
            RESTORE_DB_CONFIG["user"],
            "-d",
            RESTORE_DB_CONFIG["database"],
            "-f",
            str(backup_path),
            "-v",
            "ON_ERROR_STOP=1",
        ]
    else:
        # Use pg_restore for custom, directory, tar formats
        command = [
            str(PG_BIN_PATH / "pg_restore.exe"),
            "-h",
            RESTORE_DB_CONFIG["host"],
            "-p",
            str(RESTORE_DB_CONFIG["port"]),
            "-U",
            RESTORE_DB_CONFIG["user"],
            "-d",
            RESTORE_DB_CONFIG["database"],
            "--verbose",
            str(backup_path),
        ]

        # Add parallel processing for custom and directory formats
        if format_name in ["custom", "directory"]:
            command.extend(["-j", "4"])
        else:
            # Only add --single-transaction for tar format (not parallel)
            command.insert(-1, "--single-transaction")

    return run_command(command, env=env)


def get_file_size(filepath):
    """Get file or directory size in bytes."""
    if os.path.isfile(filepath):
        return os.path.getsize(filepath)
    elif os.path.isdir(filepath):
        total_size = 0
        for dirpath, dirnames, filenames in os.walk(filepath):
            for filename in filenames:
                file_path = os.path.join(dirpath, filename)
                total_size += os.path.getsize(file_path)
        return total_size
    return 0


def format_size(size_bytes):
    """Format size in GB with 1 decimal place."""
    size_gb = size_bytes / (1024.0 * 1024.0 * 1024.0)
    return f"{size_gb:.1f} GB"


def format_minutes(seconds):
    """Format time in minutes with 1 decimal place."""
    minutes = seconds / 60.0
    return f"{minutes:.1f} mins"


def run_command(command, timeout=7200, env=None):
    """Run a command and measure execution time."""
    log_with_timestamp(f"Running: {' '.join(command)}")
    start_time = time.time()

    try:
        # Set environment variables for password
        if env is None:
            env = os.environ.copy()
            env["PGPASSWORD"] = DB_CONFIG["password"]

        result = subprocess.run(
            command, capture_output=True, text=True, timeout=timeout, env=env
        )

        end_time = time.time()
        duration = end_time - start_time

        if result.returncode != 0:
            log_with_timestamp(f"Command failed with return code {result.returncode}")
            log_with_timestamp(f"STDERR: {result.stderr}")
            return None, duration, result.stderr

        return result, duration, None

    except subprocess.TimeoutExpired:
        log_with_timestamp(f"Command timed out after {timeout} seconds")
        return None, timeout, "Command timed out"
    except Exception as e:
        end_time = time.time()
        duration = end_time - start_time
        log_with_timestamp(f"Command failed with exception: {e}")
        return None, duration, str(e)


def test_pg_dump(skip_restore=False):
    """Test pg_dump with all format and compression combinations."""
    log_with_timestamp("\n" + "=" * 60)
    log_with_timestamp("TESTING PG_DUMP")
    log_with_timestamp("=" * 60)

    backup_dir = Path("./backups")

    for format_name, extension in PG_DUMP_FORMATS:
        log_with_timestamp(f"\nTesting pg_dump format: {format_name}")

        # Test without compression
        test_name = f"pg_dump_{format_name}_no_compression"
        output_path = backup_dir / f"{test_name}.{extension}"

        command = [
            str(PG_BIN_PATH / "pg_dump.exe"),
            "-h",
            DB_CONFIG["host"],
            "-p",
            str(DB_CONFIG["port"]),
            "-U",
            DB_CONFIG["user"],
            "-d",
            DB_CONFIG["database"],
            "-f",
            str(output_path),
            "--format",
            format_name,
            "--verbose",
        ]

        if format_name == "directory":
            # For directory format, create the directory first
            output_path.mkdir(exist_ok=True)
            # Replace the path after -f flag with the directory path
            for i, item in enumerate(command):
                if item == "-f" and i + 1 < len(command):
                    command[i + 1] = str(output_path)
                    break
            # Add parallel processing for directory format
            command.extend(["-j", "4"])

        # Perform backup
        result, backup_duration, error = run_command(command)

        if result is not None:
            backup_size = get_file_size(output_path)
            db_size = get_database_size()
            compression_ratio = backup_size / db_size if db_size > 0 else 0

            # Clean restore database and perform restore
            restore_success = False
            restore_duration = 0
            restore_error = None
            restored_db_size = 0

            if not skip_restore:
                if clean_restore_database() and wait_for_restore_db():
                    restore_result, restore_duration, restore_error = restore_pg_dump(
                        output_path, format_name
                    )
                    if restore_result is not None and restore_result.returncode == 0:
                        restore_success = True
                        restored_db_size = get_restore_database_size()
            else:
                restore_success = True  # Mark as successful if skipped
                restore_error = "Skipped"

            results.append(
                {
                    "tool": "pg_dump",
                    "format": format_name,
                    "compression_method": "none",
                    "compression_level": 0,
                    "backup_duration_seconds": backup_duration,
                    "restore_duration_seconds": restore_duration,
                    "total_duration_seconds": backup_duration + restore_duration,
                    "backup_size_bytes": backup_size,
                    "database_size_bytes": db_size,
                    "restored_db_size_bytes": restored_db_size,
                    "compression_ratio": compression_ratio,
                    "backup_success": True,
                    "restore_success": restore_success,
                    "backup_error": None,
                    "restore_error": restore_error,
                    "timestamp": datetime.now().isoformat(),
                }
            )

            if skip_restore:
                log_with_timestamp(
                    f"✓ {test_name}: Backup {format_minutes(backup_duration)}, "
                    f"{format_size(backup_size)}, ratio: {compression_ratio:.3f} (restore skipped)"
                )
            else:
                log_with_timestamp(
                    f"✓ {test_name}: Backup {format_minutes(backup_duration)}, Restore {format_minutes(restore_duration)}, "
                    f"{format_size(backup_size)}, ratio: {compression_ratio:.3f}"
                )
        else:
            results.append(
                {
                    "tool": "pg_dump",
                    "format": format_name,
                    "compression_method": "none",
                    "compression_level": 0,
                    "backup_duration_seconds": backup_duration,
                    "restore_duration_seconds": 0,
                    "total_duration_seconds": backup_duration,
                    "backup_size_bytes": 0,
                    "database_size_bytes": get_database_size(),
                    "restored_db_size_bytes": 0,
                    "compression_ratio": 0,
                    "backup_success": False,
                    "restore_success": False,
                    "backup_error": error,
                    "restore_error": "Backup failed",
                    "timestamp": datetime.now().isoformat(),
                }
            )
            log_with_timestamp(f"✗ {test_name}: BACKUP FAILED - {error}")

        # Test with compression (only for formats that support it)
        if format_name in ["custom", "directory"]:
            for compression_method in COMPRESSION_METHODS[1:]:  # Skip 'none'
                if compression_method == "gzip" and format_name == "directory":
                    continue  # Directory format doesn't support gzip compression directly

                compression_levels = COMPRESSION_LEVELS.get(compression_method, [1])

                for level in compression_levels:
                    test_name = (
                        f"pg_dump_{format_name}_{compression_method}_level_{level}"
                    )
                    output_path = backup_dir / f"{test_name}.{extension}"

                    command = [
                        str(PG_BIN_PATH / "pg_dump.exe"),
                        "-h",
                        DB_CONFIG["host"],
                        "-p",
                        str(DB_CONFIG["port"]),
                        "-U",
                        DB_CONFIG["user"],
                        "-d",
                        DB_CONFIG["database"],
                        "-f",
                        str(output_path),
                        "--format",
                        format_name,
                        "--verbose",
                    ]

                    # Add compression options
                    if format_name == "custom":
                        if compression_method == "gzip":
                            command.extend(["-Z", str(level)])
                        else:
                            command.extend(
                                [
                                    "--compress",
                                    f"{compression_method}:{level}",
                                ]
                            )
                    elif format_name == "directory":
                        output_path.mkdir(exist_ok=True)
                        # For directory format, replace the path after -f
                        for i, item in enumerate(command):
                            if item == "-f" and i + 1 < len(command):
                                command[i + 1] = str(output_path)
                                break
                        if (
                            compression_method != "gzip"
                        ):  # Directory format supports lz4 and zstd
                            command.extend(
                                [
                                    "--compress",
                                    f"{compression_method}:{level}",
                                ]
                            )
                        # Add parallel processing for directory format
                        command.extend(["-j", "4"])

                    # Perform backup
                    result, backup_duration, error = run_command(command)

                    if result is not None:
                        backup_size = get_file_size(output_path)
                        db_size = get_database_size()
                        compression_ratio = backup_size / db_size if db_size > 0 else 0

                        # Clean restore database and perform restore
                        restore_success = False
                        restore_duration = 0
                        restore_error = None
                        restored_db_size = 0

                        if not skip_restore:
                            if clean_restore_database() and wait_for_restore_db():
                                restore_result, restore_duration, restore_error = (
                                    restore_pg_dump(output_path, format_name)
                                )
                                if (
                                    restore_result is not None
                                    and restore_result.returncode == 0
                                ):
                                    restore_success = True
                                    restored_db_size = get_restore_database_size()
                        else:
                            restore_success = True  # Mark as successful if skipped
                            restore_error = "Skipped"

                        results.append(
                            {
                                "tool": "pg_dump",
                                "format": format_name,
                                "compression_method": compression_method,
                                "compression_level": level,
                                "backup_duration_seconds": backup_duration,
                                "restore_duration_seconds": restore_duration,
                                "total_duration_seconds": backup_duration
                                + restore_duration,
                                "backup_size_bytes": backup_size,
                                "database_size_bytes": db_size,
                                "restored_db_size_bytes": restored_db_size,
                                "compression_ratio": compression_ratio,
                                "backup_success": True,
                                "restore_success": restore_success,
                                "backup_error": None,
                                "restore_error": restore_error,
                                "timestamp": datetime.now().isoformat(),
                            }
                        )

                        if skip_restore:
                            log_with_timestamp(
                                f"✓ {test_name}: Backup {format_minutes(backup_duration)}, "
                                f"{format_size(backup_size)}, ratio: {compression_ratio:.3f} (restore skipped)"
                            )
                        else:
                            log_with_timestamp(
                                f"✓ {test_name}: Backup {format_minutes(backup_duration)}, Restore {format_minutes(restore_duration)}, "
                                f"{format_size(backup_size)}, ratio: {compression_ratio:.3f}"
                            )
                    else:
                        results.append(
                            {
                                "tool": "pg_dump",
                                "format": format_name,
                                "compression_method": compression_method,
                                "compression_level": level,
                                "backup_duration_seconds": backup_duration,
                                "restore_duration_seconds": 0,
                                "total_duration_seconds": backup_duration,
                                "backup_size_bytes": 0,
                                "database_size_bytes": get_database_size(),
                                "restored_db_size_bytes": 0,
                                "compression_ratio": 0,
                                "backup_success": False,
                                "restore_success": False,
                                "backup_error": error,
                                "restore_error": "Backup failed",
                                "timestamp": datetime.now().isoformat(),
                            }
                        )
                        log_with_timestamp(f"✗ {test_name}: BACKUP FAILED - {error}")


def save_tabular_results():
    """Save tabular test results to a CSV file."""
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

    # Save as tabular CSV
    csv_file = f"backup_performance_tabular_{timestamp}.csv"

    with open(csv_file, "w", newline="") as f:
        writer = csv.writer(f)

        # Write header
        writer.writerow(
            [
                "format",
                "backup duration mins",
                "restore duration mins",
                "total duration mins",
                "backup size GB",
                "db size GB",
                "restored DB size GB",
                "db size % from original",
            ]
        )

        # Write data rows
        for r in sorted(
            results,
            key=lambda x: (
                x["format"],
                x["compression_method"],
                x["compression_level"],
            ),
        ):
            if not r["backup_success"]:
                continue

            format_str = f"{r['format']} {r['compression_method']}"
            if r["compression_method"] != "none":
                format_str += f" {r['compression_level']}"

            backup_mins = round(r["backup_duration_seconds"] / 60.0, 1)
            restore_mins = round(r["restore_duration_seconds"] / 60.0, 1)
            total_mins = round(backup_mins + restore_mins, 1)

            backup_gb = round(r["backup_size_bytes"] / (1024.0 * 1024.0 * 1024.0), 1)
            db_gb = round(r["database_size_bytes"] / (1024.0 * 1024.0 * 1024.0), 1)
            restored_db_gb = round(
                r["restored_db_size_bytes"] / (1024.0 * 1024.0 * 1024.0), 1
            )

            db_size_percent = round((backup_gb / db_gb) * 100) if db_gb > 0 else 0

            writer.writerow(
                [
                    format_str,
                    backup_mins,
                    restore_mins,
                    total_mins,
                    backup_gb,
                    db_gb,
                    restored_db_gb,
                    db_size_percent,
                ]
            )

    print(f"Tabular results saved to {csv_file}")


def save_results():
    """Save test results to JSON and CSV files."""
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

    # Save as JSON
    json_file = f"backup_performance_results_{timestamp}.json"
    with open(json_file, "w") as f:
        json.dump(results, f, indent=2)
    print(f"\nResults saved to {json_file}")

    # Save as CSV
    csv_file = f"backup_performance_results_{timestamp}.csv"
    if results:
        fieldnames = results[0].keys()
        with open(csv_file, "w", newline="") as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames)
            writer.writeheader()
            writer.writerows(results)
        print(f"Results saved to {csv_file}")

    # Save tabular results
    save_tabular_results()


def print_summary():
    """Print a summary of test results."""
    print("\n" + "=" * 80)
    print("PERFORMANCE TEST SUMMARY")
    print("=" * 80)

    if not results:
        print("No results to display.")
        return

    # Check if any tests had restore attempts
    restore_attempted = any(
        r["restore_error"] != "Skipped" for r in results if r["backup_success"]
    )

    if restore_attempted:
        successful_tests = [
            r for r in results if r["backup_success"] and r["restore_success"]
        ]
        backup_only_success = [
            r
            for r in results
            if r["backup_success"]
            and not r["restore_success"]
            and r["restore_error"] != "Skipped"
        ]
        failed_tests = [r for r in results if not r["backup_success"]]

        print(f"Total tests: {len(results)}")
        print(f"Complete success (backup + restore): {len(successful_tests)}")
        print(f"Backup only success: {len(backup_only_success)}")
        print(f"Complete failures: {len(failed_tests)}")

        if successful_tests:
            print(f"\nBest compression ratios (complete success):")
            best_compression = sorted(
                successful_tests, key=lambda x: x["compression_ratio"]
            )[:5]
            for test in best_compression:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Ratio: {test['compression_ratio']:.3f}, Backup: {format_minutes(test['backup_duration_seconds'])}, "
                    f"Restore: {format_minutes(test['restore_duration_seconds'])}, Total: {format_minutes(test['total_duration_seconds'])}"
                )

            print(f"\nFastest total time (backup + restore):")
            fastest_total = sorted(
                successful_tests, key=lambda x: x["total_duration_seconds"]
            )[:5]
            for test in fastest_total:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Total: {format_minutes(test['total_duration_seconds'])}, Ratio: {test['compression_ratio']:.3f}"
                )

            print(f"\nFastest backup times:")
            fastest_backup = sorted(
                successful_tests, key=lambda x: x["backup_duration_seconds"]
            )[:5]
            for test in fastest_backup:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup: {format_minutes(test['backup_duration_seconds'])}, Restore: {format_minutes(test['restore_duration_seconds'])}"
                )

            print(f"\nFastest restore times:")
            fastest_restore = sorted(
                successful_tests, key=lambda x: x["restore_duration_seconds"]
            )[:5]
            for test in fastest_restore:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Restore: {format_minutes(test['restore_duration_seconds'])}, Backup: {format_minutes(test['backup_duration_seconds'])}"
                )

        if backup_only_success:
            print(f"\nBackup-only successes (restore failed):")
            for test in backup_only_success:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup: {format_minutes(test['backup_duration_seconds'])}, Restore Error: {test['restore_error']}"
                )

        if failed_tests:
            print(f"\nComplete failures:")
            for test in failed_tests:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup Error: {test['backup_error']}"
                )
    else:
        # Restore was skipped for all tests
        successful_tests = [r for r in results if r["backup_success"]]
        failed_tests = [r for r in results if not r["backup_success"]]

        print(f"Total tests: {len(results)}")
        print(f"Successful backups: {len(successful_tests)}")
        print(f"Failed backups: {len(failed_tests)}")
        print("Note: Restore tests were skipped")

        if successful_tests:
            print(f"\nBest compression ratios:")
            best_compression = sorted(
                successful_tests, key=lambda x: x["compression_ratio"]
            )[:5]
            for test in best_compression:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Ratio: {test['compression_ratio']:.3f}, Backup: {format_minutes(test['backup_duration_seconds'])}"
                )

            print(f"\nFastest backup times:")
            fastest_backup = sorted(
                successful_tests, key=lambda x: x["backup_duration_seconds"]
            )[:5]
            for test in fastest_backup:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup: {format_minutes(test['backup_duration_seconds'])}, Ratio: {test['compression_ratio']:.3f}"
                )

        if failed_tests:
            print(f"\nFailed backups:")
            for test in failed_tests:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup Error: {test['backup_error']}"
                )


def print_tabular_report():
    """Print a tabular report of results similar to the requested format."""
    if not results:
        print("No results to display.")
        return

    print("\n" + "=" * 120)
    print("TABULAR PERFORMANCE REPORT")
    print("=" * 120)

    # Print header
    print(
        f"{'format':<20}{'backup duration mins':<20}{'restore duration mins':<25}{'total duration mins':<20}"
        f"{'backup size GB':<15}{'db size GB':<15}{'restored DB size GB':<20}{'db size % from original':<25}"
    )
    print("-" * 120)

    # Group results by format and compression method+level
    for r in sorted(
        results,
        key=lambda x: (x["format"], x["compression_method"], x["compression_level"]),
    ):
        if not r["backup_success"]:
            continue

        format_str = f"{r['format']} {r['compression_method']}"
        if r["compression_method"] != "none":
            format_str += f" {r['compression_level']}"

        backup_mins = r["backup_duration_seconds"] / 60.0
        restore_mins = r["restore_duration_seconds"] / 60.0
        total_mins = backup_mins + restore_mins

        backup_gb = r["backup_size_bytes"] / (1024.0 * 1024.0 * 1024.0)
        db_gb = r["database_size_bytes"] / (1024.0 * 1024.0 * 1024.0)
        restored_db_gb = r["restored_db_size_bytes"] / (1024.0 * 1024.0 * 1024.0)

        db_size_percent = (backup_gb / db_gb) * 100 if db_gb > 0 else 0

        print(
            f"{format_str:<20}{backup_mins:.1f}{'':<14}{restore_mins:.1f}{'':<19}{total_mins:.1f}{'':<14}"
            f"{backup_gb:.1f}{'':<9}{db_gb:.1f}{'':<9}{restored_db_gb:.1f}{'':<14}{db_size_percent:.0f}"
        )


def main():
    """Main function to run pg_dump backup performance tests."""
    parser = argparse.ArgumentParser(
        description="PostgreSQL pg_dump backup performance testing"
    )
    parser.add_argument(
        "--skip-gzip", action="store_true", help="Skip gzip compression tests"
    )
    parser.add_argument(
        "--skip-lz4", action="store_true", help="Skip lz4 compression tests"
    )
    parser.add_argument(
        "--skip-zstd", action="store_true", help="Skip zstd compression tests"
    )
    parser.add_argument(
        "--skip-restore", action="store_true", help="Skip restore tests"
    )

    args = parser.parse_args()

    # Filter compression methods based on skip flags
    global COMPRESSION_METHODS
    filtered_compression_methods = ["none"]  # Always include 'none'

    if not args.skip_gzip:
        filtered_compression_methods.append("gzip")
    if not args.skip_lz4:
        filtered_compression_methods.append("lz4")
    if not args.skip_zstd:
        filtered_compression_methods.append("zstd")

    COMPRESSION_METHODS = filtered_compression_methods

    log_with_timestamp("PostgreSQL pg_dump Backup Performance Testing")
    log_with_timestamp("=" * 60)
    log_with_timestamp(
        f"Source Database: {DB_CONFIG['database']} on {DB_CONFIG['host']}:{DB_CONFIG['port']}"
    )
    log_with_timestamp(
        f"Restore Database: {RESTORE_DB_CONFIG['database']} on {RESTORE_DB_CONFIG['host']}:{RESTORE_DB_CONFIG['port']}"
    )
    log_with_timestamp(f"Database size: {format_size(get_database_size())}")
    log_with_timestamp(f"Compression methods: {', '.join(COMPRESSION_METHODS)}")
    log_with_timestamp(
        f"Restore tests: {'Skipped' if args.skip_restore else 'Enabled'}"
    )
    log_with_timestamp(f"Test started: {datetime.now()}")

    # Ensure backup directory is ready
    backup_dir = ensure_backup_directory()
    log_with_timestamp(f"Backup directory: {backup_dir.absolute()}")

    try:
        # Run pg_dump tests only
        test_pg_dump(skip_restore=args.skip_restore)

        # Save and display results
        save_results()
        print_summary()
        print_tabular_report()

    except KeyboardInterrupt:
        log_with_timestamp("\nTest interrupted by user")
        if results:
            save_results()
            print_summary()
            print_tabular_report()
    except Exception as e:
        log_with_timestamp(f"\nTest failed with error: {e}")
        if results:
            save_results()
            print_summary()
            print_tabular_report()

    log_with_timestamp(f"\nTest completed: {datetime.now()}")


if __name__ == "__main__":
    main()

I had to remove the results for zstd with compression level 15 and zstd with compression level 22 from the charts. They breaks the charts significantly due to the long compression time, while not providing any noticeable increase in compression.

So, measurements.

Backup speed in seconds (lower is better):

Restore speed from backup in seconds (lower is better):

Total time for creation and restoration from backup in seconds (lower is better):

Total backup size as a percentage of the original database size (smaller is better):

Conclusions based on measurements

Before talking about conclusions, I would like to make an important disclaimer: the test was performed on synthetic data, and the results with “real-world” data will differ significantly. The trend will be the same, but the time difference will be greater.

The measurements show that there is no radical difference in speed between the plain format, custom format and directory format on synthetic data. Despite the fact that the custom format is restored in parallel mode relative to plain, and the directory format also creates the copy itself in parallel.

The difference in speed between the custom format and plain is ~30%. Between the custom and directory formats, it is only ~20%. I would suppose that the test data lacked a sufficient number of independent tables and objects — otherwise, the gap between the formats would have been multiple times greater.

So, based on the measurements, I can make the following conclusions:

The fastest backup format is directory-based.

The custom format is faster than plain and tar in terms of total time under any circumstances. The directory format is faster than custom under any circumstances. If parallel mode enabled, of course.

Moreover, in terms of backup creation speed, the directory format is more than twice as fast as the custom format. This is very important, considering that <u>we make backups more often than we restore from them</u>.

The most useful in terms of the “speed and compression level” ratio was zstd with a compression level of 5.

In terms of backup creation speed, it is only surpassed by uncompressed formats. In terms of recovery speed, it is on average ~4% slower than other formats. At the same time, it has maximum compression comparable to gzip with compression level 9, but outperforms it in speed by ~18%. Taking into account the error margin on synthetic data.

zstd 15 and zstd 22 turned out to be useless in this particular test. They gave compression levels roughly the same as gzip 9, but took 2-8x times longer to produce the result.

I think that with a database of at least 1 TB without synthetic data and cold storage, they will show completely different results and may turn out to be very cost-effective (especially if you need to make hundreds of backups and store them for a long time).

Conclusion

The measurement showed that the most optimal format for my task was a custom format with zstd compression and a compression level of 5. I get the maximum total speed with almost maximum compression and a single backup file.

After implementing zstd 5 instead of gzip 6 in the project, the backup size was reduced by almost half with a slightly shorter backup time. At the same time, unlike synthetic data, a 4.7 GB database was compressed to 276 MB (17 times smaller!):

I hope that my test will be useful to those who develop backup tools or regularly dump databases using pg_dump scripts. Perhaps in the future, I will conduct the same test, but with a more diverse data set.

And once again, if you need to create regular backups, I have an open source project for this task. I would be extremely grateful for a star on GitHub ❤️, as the first stars are hard to come by.

Also may be interesting:

10 unusual moments in Go for Java developer

PostgreSQL backups: comparing pg_dump speed in different formats and with different compression levels

Rostislav Dugin — Thu, 31 Jul 2025 12:31:01 +0000

Details and measurements are below.

Table of content:

Why do I need this measurement?
Backup formats and compression types in pg_dump
PostgreSQL configuration
Data preparation
Measurement results
Conclusions based on measurements
Conclusion

Why do I need this measurement?

I had a very specific task: to find the best backup format using the standard pg_dump. “Best” means the optimal ratio of backup creation speed, recovery speed and final file size.

I used this information in my open source project for PostgreSQL backups called Postgresus. There is an overview article about it on dev.to:

Rostislav Dugin

Jul 22 '25

How I got into open source in 2025 (with a PostgreSQL backup tool) after almost losing a ~$1,500/mo project in 2023

#webdev #opensource #postgres #programming

Comments 2

6 min read

There were additional requirements:

the backup must be compressed before being sent to my server to minimize network usage;
the backup file itself must be a single file (rather than, for example, a directory) so it can be streamed to a disk, S3 or the cloud;
the method of creating a backup should not require any database configuration in advance (therefore, PgBackRest, WAL-G, and pg_basebackup were ruled out) in order to be easy to integrate into an open source project and work with any database (installed locally, in Docker, in DBaaS, with a read replica, etc.).

Backup formats and compression types in pg_dump

pg_dump supports 4 formats:

Format	Compression	Single file?	Parallel backup	Parallel restore
Plain (SQL)	❌	✅	❌	❌
Custom (-Fc)	✅	✅	❌	✅
Directory (-Fd)	✅	❌	✅	✅
Tar (-Ft)	✅	✅	❌	❌

For these formats the following compression types are supported:

Name	Feature (in theory)	Compression / Decompression Speed (in theory)	Compression Ratio (in theory)
gzip	Standard compression algorithm	Medium / High	2–3×
lz4	Compression algorithm optimized for higher speed than gzip	Very High / Very High	1.5–2×
zstd	Relatively new (2016) algorithm developed at Facebook: balance of speed and compression	High / High	3–5×

PostgreSQL configuration

docker-compose.yml

version: "3.8"

services:
  db:
    image: postgres:17
    container_name: db
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: testpassword
    ports:
      - "7000:7000"
    command: -p 7000
    volumes:
      - ./pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d testdb -p 7000"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: unless-stopped

  db-restore:
    image: postgres:17
    container_name: db-restore
    environment:
      POSTGRES_DB: testdb
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: testpassword
    ports:
      - "7001:7001"
    command: -p 7001
    volumes:
      - ./pgdata-restore:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d testdb -p 7001"]
      interval: 10s
      timeout: 5s
      retries: 5
    restart: unless-stopped
    depends_on:
      - db

postgresql.conf

# DB Version: 17
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 16 GB
# CPUs num: 4
# Connections num: 100
# Data Storage: ssd

max_connections = 100
shared_buffers = 4GB
effective_cache_size = 12GB
maintenance_work_mem = 1GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 40329kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2

listen_addresses = '*'

Data preparation

Below is the tables structure.

Tables:

Table name	Purpose	Columns
large_test_table	Employees and users with various data types	18 columns including name, email, address, salary, etc.
orders	Order data and history of changes	13 columns including user_id, order_number, amounts, etc.
activity_logs	User activity logs with large text fields	13 columns including user_id, action, details, timestamps, etc.

Indexes:

Table	Index Name	Column	Purpose
large_test_table	idx_large_test_name	name	Search by name
large_test_table	idx_large_test_email	email	Search by email
large_test_table	idx_large_test_created_at	created_at	Search by time range
large_test_table	idx_large_test_department	department	Filter by department
orders	idx_orders_user_id	user_id	Search user’s orders
orders	idx_orders_order_date	order_date	Search by date/time
orders	idx_orders_status	status	Search by status
activity_logs	idx_activity_user_id	user_id	Search by ID
activity_logs	idx_activity_timestamp	timestamp	Search by date
activity_logs	idx_activity_action	action	Search by action

The data is generated and inserted into the database using a Python script. The algorithm is as follows:

25,000 rows of data are generated;
100,000 rows are inserted into each table in turn using COPY;
when the database reaches 10 GB in size, indexes are created.

Script to fill database with data

#!/usr/bin/env python3
"""
Script to populate PostgreSQL database with ~10GB of test data for backup performance testing.
Inserts 1 million rows into each of 3 tables until reaching 10GB target.
"""

import psycopg2
import random
import string
import time
import io
from datetime import datetime, timedelta
import sys


# Database connection parameters
DB_CONFIG = {
    "host": "localhost",
    "port": 7000,
    "database": "testdb",
    "user": "postgres",
    "password": "testpassword",
}

# Target database size in bytes (10GB)
TARGET_SIZE_GB = 10
TARGET_SIZE_BYTES = TARGET_SIZE_GB * 1024 * 1024 * 1024

# Rows per table per round
ROWS_PER_TABLE = 100000


def generate_random_string(length):
    """Generate a random string of specified length."""
    return "".join(random.choices(string.ascii_letters + string.digits + " ", k=length))


def generate_random_date():
    """Generate a random date within the last 5 years."""
    start_date = datetime.now() - timedelta(days=5 * 365)
    random_days = random.randint(0, 5 * 365)
    return start_date + timedelta(days=random_days)


def create_tables(cursor):
    """Create the 3 test tables with various data types."""
    print("Creating 3 tables...")

    # Table 1: Large table with mixed data types
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS large_test_table (
            id BIGSERIAL PRIMARY KEY,
            name VARCHAR(100),
            description TEXT,
            email VARCHAR(255),
            phone VARCHAR(20),
            address TEXT,
            city VARCHAR(100),
            country VARCHAR(100),
            postal_code VARCHAR(20),
            birth_date DATE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            salary DECIMAL(10,2),
            is_active BOOLEAN DEFAULT TRUE,
            rating FLOAT,
            notes TEXT,
            department VARCHAR(100),
            employee_id VARCHAR(50)
        )
    """)

    # Table 2: Orders table for transactional data
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS orders (
            id BIGSERIAL PRIMARY KEY,
            user_id BIGINT,
            order_number VARCHAR(50) UNIQUE,
            total_amount DECIMAL(12,2),
            order_date TIMESTAMP,
            status VARCHAR(20),
            shipping_address TEXT,
            notes TEXT,
            payment_method VARCHAR(50),
            shipping_method VARCHAR(50),
            discount_amount DECIMAL(10,2),
            product_list TEXT,
            customer_notes TEXT
        )
    """)

    # Table 3: Activity logs with lots of text data
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS activity_logs (
            id BIGSERIAL PRIMARY KEY,
            user_id BIGINT,
            action VARCHAR(100),
            details TEXT,
            ip_address INET,
            user_agent TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            session_id VARCHAR(100),
            browser VARCHAR(50),
            operating_system VARCHAR(50),
            referrer TEXT,
            response_time INTEGER,
            error_message TEXT
        )
    """)

    print("3 tables created successfully!")


def create_indexes(cursor):
    """Create indexes for all 3 tables."""
    print("Creating indexes...")

    # Indexes for large_test_table
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_name ON large_test_table(name)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_email ON large_test_table(email)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_created_at ON large_test_table(created_at)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_large_test_department ON large_test_table(department)"
    )

    # Indexes for orders
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id)")
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_orders_order_date ON orders(order_date)"
    )
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status)")

    # Indexes for activity_logs
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_activity_user_id ON activity_logs(user_id)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_activity_timestamp ON activity_logs(timestamp)"
    )
    cursor.execute(
        "CREATE INDEX IF NOT EXISTS idx_activity_action ON activity_logs(action)"
    )

    print("Indexes created successfully!")


def drop_indexes(cursor):
    """Drop indexes for faster bulk loading."""
    print("Dropping indexes for bulk loading...")

    indexes_to_drop = [
        "idx_large_test_name",
        "idx_large_test_email",
        "idx_large_test_created_at",
        "idx_large_test_department",
        "idx_orders_user_id",
        "idx_orders_order_date",
        "idx_orders_status",
        "idx_activity_user_id",
        "idx_activity_timestamp",
        "idx_activity_action",
    ]

    for index in indexes_to_drop:
        try:
            cursor.execute(f"DROP INDEX IF EXISTS {index}")
        except:
            pass

    print("Indexes dropped!")


def print_progress(inserted, total, start_time, operation_name):
    """Print detailed progress information."""
    current_time = time.time()
    elapsed = current_time - start_time
    rate = inserted / elapsed if elapsed > 0 else 0
    percentage = (inserted / total) * 100

    if rate > 0:
        eta_seconds = (total - inserted) / rate
        eta_minutes = eta_seconds / 60
        eta_str = f"ETA: {eta_minutes:.1f}m"
    else:
        eta_str = "ETA: calculating..."

    print(
        f"{operation_name}: {inserted:,} / {total:,} ({percentage:.1f}%) | "
        f"Rate: {rate:,.0f} rows/sec | Elapsed: {elapsed / 60:.1f}m | {eta_str}"
    )


def populate_large_table_batch(cursor, count=ROWS_PER_TABLE):
    """Populate large_test_table with specified number of rows."""
    print(f"Inserting {count:,} rows into large_test_table...")

    batch_size = 25000
    inserted = 0
    start_time = time.time()

    while inserted < count:
        current_batch_size = min(batch_size, count - inserted)
        data_buffer = io.StringIO()

        for i in range(current_batch_size):
            name = (
                generate_random_string(random.randint(20, 100))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            description = (
                generate_random_string(random.randint(100, 500))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            email = f"{generate_random_string(10)}@{generate_random_string(10)}.com"
            phone = f"+1-{random.randint(100, 999)}-{random.randint(100, 999)}-{random.randint(1000, 9999)}"
            address = (
                generate_random_string(random.randint(50, 200))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            city = (
                generate_random_string(random.randint(10, 50))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            country = (
                generate_random_string(random.randint(5, 30))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            postal_code = f"{random.randint(10000, 99999)}"
            birth_date = generate_random_date().date()
            salary = f"{random.randint(30000, 150000) + random.random():.2f}"
            is_active = "t" if random.choice([True, False]) else "f"
            rating = f"{random.uniform(1.0, 5.0):.2f}"
            notes = (
                generate_random_string(random.randint(50, 300))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            department = random.choice(
                ["HR", "Engineering", "Sales", "Marketing", "Finance", "Operations"]
            )
            employee_id = (
                f"EMP-{random.randint(1000, 9999)}-{generate_random_string(4)}"
            )

            data_buffer.write(
                f"{name}\t{description}\t{email}\t{phone}\t{address}\t{city}\t{country}\t{postal_code}\t{birth_date}\t{salary}\t{is_active}\t{rating}\t{notes}\t{department}\t{employee_id}\n"
            )

        data_buffer.seek(0)
        cursor.copy_from(
            data_buffer,
            "large_test_table",
            columns=(
                "name",
                "description",
                "email",
                "phone",
                "address",
                "city",
                "country",
                "postal_code",
                "birth_date",
                "salary",
                "is_active",
                "rating",
                "notes",
                "department",
                "employee_id",
            ),
            sep="\t",
        )

        inserted += current_batch_size
        if inserted % 100000 == 0:
            print_progress(inserted, count, start_time, "Large table")

    elapsed = time.time() - start_time
    print(
        f"Completed large_test_table: {inserted:,} rows in {elapsed:.2f}s ({inserted / elapsed:,.0f} rows/sec)"
    )


def populate_orders_batch(cursor, count=ROWS_PER_TABLE):
    """Populate orders table with specified number of rows."""
    print(f"Inserting {count:,} rows into orders...")

    batch_size = 25000
    inserted = 0
    start_time = time.time()

    while inserted < count:
        current_batch_size = min(batch_size, count - inserted)
        data_buffer = io.StringIO()

        for i in range(current_batch_size):
            user_id = random.randint(1, 10000000)
            order_number = f"ORD-{time.time_ns()}-{i:06d}"
            total_amount = f"{random.uniform(10.0, 5000.0):.2f}"
            order_date = generate_random_date()
            status = random.choice(
                ["pending", "processing", "shipped", "delivered", "cancelled"]
            )
            shipping_address = (
                generate_random_string(random.randint(100, 300))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            notes = (
                generate_random_string(random.randint(50, 200))
                .replace("\t", " ")
                .replace("\n", " ")
                if random.random() > 0.5
                else ""
            )
            payment_method = random.choice(["credit_card", "paypal", "bank_transfer"])
            shipping_method = random.choice(["standard", "express", "overnight"])
            discount_amount = (
                f"{random.uniform(0, 50) if random.random() > 0.7 else 0:.2f}"
            )
            product_list = (
                generate_random_string(random.randint(200, 800))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            customer_notes = (
                generate_random_string(random.randint(100, 400))
                .replace("\t", " ")
                .replace("\n", " ")
            )

            data_buffer.write(
                f"{user_id}\t{order_number}\t{total_amount}\t{order_date}\t{status}\t{shipping_address}\t{notes}\t{payment_method}\t{shipping_method}\t{discount_amount}\t{product_list}\t{customer_notes}\n"
            )

        data_buffer.seek(0)
        cursor.copy_from(
            data_buffer,
            "orders",
            columns=(
                "user_id",
                "order_number",
                "total_amount",
                "order_date",
                "status",
                "shipping_address",
                "notes",
                "payment_method",
                "shipping_method",
                "discount_amount",
                "product_list",
                "customer_notes",
            ),
            sep="\t",
        )

        inserted += current_batch_size
        if inserted % 100000 == 0:
            print_progress(inserted, count, start_time, "Orders")

    elapsed = time.time() - start_time
    print(
        f"Completed orders: {inserted:,} rows in {elapsed:.2f}s ({inserted / elapsed:,.0f} rows/sec)"
    )


def populate_activity_logs_batch(cursor, count=ROWS_PER_TABLE):
    """Populate activity_logs table with specified number of rows."""
    print(f"Inserting {count:,} rows into activity_logs...")

    batch_size = 25000
    inserted = 0
    start_time = time.time()
    actions = [
        "login",
        "logout",
        "view_product",
        "add_to_cart",
        "checkout",
        "update_profile",
        "search",
        "download",
    ]

    while inserted < count:
        current_batch_size = min(batch_size, count - inserted)
        data_buffer = io.StringIO()

        for i in range(current_batch_size):
            user_id = random.randint(1, 10000000)
            action = random.choice(actions)
            details = (
                generate_random_string(random.randint(100, 500))
                .replace("\t", " ")
                .replace("\n", " ")
            )
            ip_address = f"{random.randint(1, 255)}.{random.randint(1, 255)}.{random.randint(1, 255)}.{random.randint(1, 255)}"
            user_agent = f"Mozilla/5.0 ({generate_random_string(50)}) {generate_random_string(30)}".replace(
                "\t", " "
            ).replace("\n", " ")
            timestamp = generate_random_date()
            session_id = generate_random_string(32)
            browser = random.choice(["Chrome", "Firefox", "Safari", "Edge"])
            operating_system = random.choice(
                ["Windows", "macOS", "Linux", "iOS", "Android"]
            )
            referrer = (
                f"https://{generate_random_string(10)}.com"
                if random.random() > 0.3
                else ""
            )
            response_time = random.randint(50, 5000)
            error_message = (
                generate_random_string(random.randint(100, 300))
                .replace("\t", " ")
                .replace("\n", " ")
                if random.random() > 0.8
                else ""
            )

            data_buffer.write(
                f"{user_id}\t{action}\t{details}\t{ip_address}\t{user_agent}\t{timestamp}\t{session_id}\t{browser}\t{operating_system}\t{referrer}\t{response_time}\t{error_message}\n"
            )

        data_buffer.seek(0)
        cursor.copy_from(
            data_buffer,
            "activity_logs",
            columns=(
                "user_id",
                "action",
                "details",
                "ip_address",
                "user_agent",
                "timestamp",
                "session_id",
                "browser",
                "operating_system",
                "referrer",
                "response_time",
                "error_message",
            ),
            sep="\t",
        )

        inserted += current_batch_size
        if inserted % 100000 == 0:
            print_progress(inserted, count, start_time, "Activity logs")

    elapsed = time.time() - start_time
    print(
        f"Completed activity_logs: {inserted:,} rows in {elapsed:.2f}s ({inserted / elapsed:,.0f} rows/sec)"
    )


def get_database_size_bytes(cursor):
    """Get the current database size in bytes."""
    cursor.execute("SELECT pg_database_size('testdb')")
    return cursor.fetchone()[0]


def get_database_size_mb(cursor):
    """Get the current database size in MB."""
    size_bytes = get_database_size_bytes(cursor)
    return size_bytes / (1024 * 1024)


def format_size_mb(size_bytes):
    """Format size in bytes to MB string."""
    return f"{size_bytes / (1024 * 1024):.1f}MB"


def main():
    """Main function to populate the database until reaching 10GB."""
    print("Starting database population for backup performance testing...")
    print(f"Target: {TARGET_SIZE_GB * 1024:.0f}MB of data")
    print(
        f"Strategy: Insert {ROWS_PER_TABLE:,} rows into each of 3 tables per round until target reached"
    )
    print("-" * 80)

    try:
        # Connect to database
        print("Connecting to database...")
        conn = psycopg2.connect(**DB_CONFIG)
        conn.autocommit = False
        cursor = conn.cursor()

        initial_size_mb = get_database_size_mb(cursor)
        print(f"Initial database size: {initial_size_mb:.1f}MB")
        overall_start_time = time.time()

        # Create tables
        create_tables(cursor)
        conn.commit()

        # Drop indexes for faster loading
        drop_indexes(cursor)
        conn.commit()

        round_number = 1
        current_size_bytes = get_database_size_bytes(cursor)

        # Keep adding rounds of 1M rows per table until we reach 10GB
        while current_size_bytes < TARGET_SIZE_BYTES:
            print(f"\n{'=' * 20} ROUND {round_number} {'=' * 20}")
            round_start_time = time.time()

            # Populate all 3 tables with 1M rows each
            populate_large_table_batch(cursor)
            conn.commit()

            populate_orders_batch(cursor)
            conn.commit()

            populate_activity_logs_batch(cursor)
            conn.commit()

            # Check current size
            current_size_bytes = get_database_size_bytes(cursor)
            current_size_mb = format_size_mb(current_size_bytes)
            round_elapsed = time.time() - round_start_time

            print(f"\nRound {round_number} completed in {round_elapsed:.2f}s")
            print(f"Current database size: {current_size_mb}")
            print(
                f"Progress: {(current_size_bytes / TARGET_SIZE_BYTES) * 100:.1f}% of target ({TARGET_SIZE_GB * 1024:.0f}MB)"
            )

            if current_size_bytes >= TARGET_SIZE_BYTES:
                print(f"✅ Target size reached!")
                break

            round_number += 1

        # Recreate indexes
        print(f"\n{'=' * 20} CREATING INDEXES {'=' * 20}")
        index_start_time = time.time()
        create_indexes(cursor)
        conn.commit()
        index_elapsed = time.time() - index_start_time
        print(f"Indexes created in {index_elapsed:.2f}s")

        # Final statistics
        overall_elapsed = time.time() - overall_start_time
        final_size_mb = get_database_size_mb(cursor)

        print(f"\n{'=' * 60}")
        print("DATABASE POPULATION COMPLETED!")
        print(f"Final database size: {final_size_mb:.1f}MB")
        print(f"Total rounds: {round_number}")
        print(
            f"Total time: {overall_elapsed:.2f} seconds ({overall_elapsed / 60:.2f} minutes)"
        )

        # Show table statistics
        cursor.execute("""
            SELECT 
                relname,
                n_tup_ins as "Rows",
                round(pg_total_relation_size('public.'||relname)::numeric / (1024*1024), 1) as "Size_MB"
            FROM pg_stat_user_tables 
            ORDER BY pg_total_relation_size('public.'||relname) DESC
        """)

        print(f"\nTable Statistics:")
        print(f"{'Table':<20} | {'Rows':<12} | {'Size':<10}")
        print("-" * 50)
        for row in cursor.fetchall():
            print(f"{row[0]:<20} | {row[1]:>12,} | {row[2]:.1f}MB")

        cursor.close()
        conn.close()

    except psycopg2.Error as e:
        print(f"Database error: {e}")
        sys.exit(1)
    except Exception as e:
        print(f"Error: {e}")
        sys.exit(1)


if __name__ == "__main__":
    main()

Measurement results

After 21 creations and restores, I obtained the following table with data that includes:

backup creation speed;
restore speed from backup;
total time;
backup size relative to the original database size.

The table with raw CSV data:

tool,format,compression_method,compression_level,backup_duration_seconds,restore_duration_seconds,total_duration_seconds,backup_size_bytes,database_size_bytes,restored_db_size_bytes,compression_ratio,backup_success,restore_success,backup_error,restore_error,timestamp
pg_dump,plain,none,0,100.39210295677185,735.2188968658447,835.6109998226166,9792231003,11946069139,11922173075,0.8197031918249641,True,True,,,2025-07-29T09:56:20.611844
pg_dump,custom,none,0,264.56927490234375,406.6467957496643,671.216070652008,6862699613,11946069139,11943709843,0.5744734550878778,True,True,,,2025-07-29T10:07:37.226681
pg_dump,custom,gzip,1,214.07211470603943,383.0168492794037,597.0889639854431,7074031563,11946069139,11943611539,0.5921639562511493,True,True,,,2025-07-29T10:17:39.801883
pg_dump,custom,gzip,5,260.6179132461548,393.76623010635376,654.3841433525085,6866440205,11946069139,11943718035,0.5747865783384196,True,True,,,2025-07-29T10:28:40.167485
pg_dump,custom,gzip,9,272.3802499771118,385.1409020423889,657.5211520195007,6856264586,11946069139,11943619731,0.5739347819121977,True,True,,,2025-07-29T10:39:42.912960
pg_dump,custom,lz4,1,84.0079517364502,379.6986663341522,463.7066180706024,9146843234,11946069139,11943685267,0.765678075990583,True,True,,,2025-07-29T10:47:32.131593
pg_dump,custom,lz4,5,150.24981474876404,393.44346714019775,543.6932818889618,8926348325,11946069139,11943718035,0.7472205477078983,True,True,,,2025-07-29T10:56:41.333595
pg_dump,custom,lz4,12,220.93980932235718,418.26913809776306,639.2089474201202,8923243046,11946069139,11943767187,0.7469606062188722,True,True,,,2025-07-29T11:07:26.574678
pg_dump,custom,zstd,1,87.83108067512512,419.07846903800964,506.90954971313477,6835388679,11946069139,11943767187,0.5721872692570225,True,True,,,2025-07-29T11:15:59.917828
pg_dump,custom,zstd,5,102.42366409301758,413.64263129234314,516.0662953853607,6774137561,11946069139,11944357011,0.567059966100871,True,True,,,2025-07-29T11:24:42.075008
pg_dump,custom,zstd,15,844.7868592739105,388.23959374427795,1233.0264530181885,6726189591,11946069139,11943636115,0.5630462633973209,True,True,,,2025-07-29T11:45:17.885163
pg_dump,custom,zstd,22,5545.566084384918,404.1370210647583,5949.7031054496765,6798947241,11946069139,11943750803,0.5691367731000038,True,True,,,2025-07-29T13:24:30.014902
pg_dump,directory,none,0,114.9900906085968,395.2716040611267,510.2616946697235,6854332396,11946069139,11943693459,0.5737730391684116,True,True,,,2025-07-29T13:33:05.944191
pg_dump,directory,lz4,1,53.48561334609985,384.92091369628906,438.4065270423889,9146095976,11946069139,11943668883,0.7656155233641663,True,True,,,2025-07-29T13:40:30.590719
pg_dump,directory,lz4,5,83.44352841377258,410.42058181762695,493.86411023139954,8925601067,11946069139,11943718035,0.7471579950814815,True,True,,,2025-07-29T13:48:50.201990
pg_dump,directory,lz4,12,114.15110802650452,400.04946303367615,514.2005710601807,8922495788,11946069139,11943758995,0.7468980535924554,True,True,,,2025-07-29T13:57:30.419171
pg_dump,directory,zstd,1,57.22735643386841,414.4600088596344,471.6873652935028,6835014976,11946069139,11943750803,0.5721559867493079,True,True,,,2025-07-29T14:05:28.529630
pg_dump,directory,zstd,5,60.121564865112305,398.27933716773987,458.4009020328522,6773763858,11946069139,11943709843,0.5670286835931563,True,True,,,2025-07-29T14:13:13.472761
pg_dump,directory,zstd,15,372.43965554237366,382.9877893924713,755.427444934845,6725815888,11946069139,11943644307,0.5630149808896062,True,True,,,2025-07-29T14:25:54.580924
pg_dump,directory,zstd,22,2637.47145485878,394.4939453601837,3031.9654002189636,6798573538,11946069139,11943660691,0.5691054905922891,True,True,,,2025-07-29T15:16:29.450828
pg_dump,tar,none,0,126.3212628364563,664.1294028759003,790.4506657123566,9792246784,11946069139,11942759571,0.8197045128452776,True,True,,,2025-07-29T15:29:45.280592

Script for measurement


python
#!/usr/bin/env python3
"""
Comprehensive PostgreSQL backup performance testing script.
Tests pg_dump with all possible formats and compression levels.
"""

import subprocess
import time
import os
import shutil
import json
import csv
from datetime import datetime
from pathlib import Path
import psycopg2
import argparse


def log_with_timestamp(message):
    """Print a message with timestamp."""
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(f"[{timestamp}] {message}")


# PostgreSQL binary path
PG_BIN_PATH = Path("./postgresql-17/bin")

# Database connection parameters
DB_CONFIG = {
    "host": "localhost",
    "port": 7000,
    "database": "testdb",
    "user": "postgres",
    "password": "testpassword",
}

# Restore database connection parameters
RESTORE_DB_CONFIG = {
    "host": "localhost",
    "port": 7001,
    "database": "testdb",
    "user": "postgres",
    "password": "testpassword",
}

# Test configurations
PG_DUMP_FORMATS = [
    ("plain", "sql"),
    ("custom", "dump"),
    ("directory", "dir"),
    ("tar", "tar"),
]

COMPRESSION_LEVELS = {
    "gzip": [1, 5, 9],
    "lz4": [1, 5, 12],
    "zstd": [1, 5, 15, 22],
}

# Available compression methods (depends on PostgreSQL version)
COMPRESSION_METHODS = ["none", "gzip", "lz4", "zstd"]

# Results storage
results: list[dict] = []


def ensure_backup_directory():
    """Ensure backup directory exists and is clean."""
    backup_dir = Path("./backups")
    if backup_dir.exists():
        shutil.rmtree(backup_dir)
    backup_dir.mkdir(exist_ok=True)
    return backup_dir


def get_database_size():
    """Get current database size in bytes."""
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SELECT pg_database_size('testdb')")
        size = cursor.fetchone()[0]
        cursor.close()
        conn.close()
        return size
    except Exception as e:
        log_with_timestamp(f"Error getting database size: {e}")
        return 0


def get_restore_database_size():
    """Get restore database size in bytes."""
    try:
        conn = psycopg2.connect(**RESTORE_DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SELECT pg_database_size('testdb')")
        size = cursor.fetchone()[0]
        cursor.close()
        conn.close()
        return size
    except Exception as e:
        log_with_timestamp(f"Error getting restore database size: {e}")
        return 0


def clean_restore_database():
    """Clean the restore database by dropping and recreating it."""
    import time

    try:
        # Connect to postgres database to drop/create testdb
        restore_config = RESTORE_DB_CONFIG.copy()
        restore_config["database"] = "postgres"

        conn = psycopg2.connect(**restore_config)
        conn.autocommit = True
        cursor = conn.cursor()

        # Terminate any existing connections to testdb with retry logic
        max_attempts = 5
        for attempt in range(max_attempts):
            try:
                # Get count of active connections first
                cursor.execute("""
                    SELECT COUNT(*)
                    FROM pg_stat_activity
                    WHERE datname = 'testdb' AND pid <> pg_backend_pid()
                """)
                active_connections = cursor.fetchone()[0]

                if active_connections == 0:
                    break

                log_with_timestamp(
                    f"Found {active_connections} active connections to testdb, terminating..."
                )

                # Terminate connections
                cursor.execute("""
                    SELECT pg_terminate_backend(pid)
                    FROM pg_stat_activity
                    WHERE datname = 'testdb' AND pid <> pg_backend_pid()
                """)

                # Fetch all results to ensure the query completes
                terminated_pids = cursor.fetchall()
                log_with_timestamp(f"Terminated {len(terminated_pids)} connections")

                # Wait a bit for connections to actually close
                time.sleep(2)

            except Exception as term_error:
                log_with_timestamp(
                    f"Warning: Error terminating connections (attempt {attempt + 1}): {term_error}"
                )
                if attempt < max_attempts - 1:
                    time.sleep(1)
                else:
                    # Continue anyway, maybe we can still drop the database
                    break

        # Try to drop database with retry logic
        drop_attempts = 3
        for attempt in range(drop_attempts):
            try:
                cursor.execute("DROP DATABASE IF EXISTS testdb")
                log_with_timestamp("Database testdb dropped successfully")
                break
            except Exception as drop_error:
                if (
                    "is being accessed by other users" in str(drop_error)
                    and attempt < drop_attempts - 1
                ):
                    log_with_timestamp(
                        f"Database still in use, waiting and retrying... (attempt {attempt + 1})"
                    )
                    time.sleep(3)
                else:
                    raise drop_error

        # Create fresh database
        cursor.execute("CREATE DATABASE testdb")

        cursor.close()
        conn.close()

        log_with_timestamp("✓ Restore database cleaned and recreated")
        return True

    except Exception as e:
        log_with_timestamp(f"✗ Error cleaning restore database: {e}")
        return False


def wait_for_restore_db():
    """Wait for restore database to be ready."""
    import time

    max_attempts = 30
    for attempt in range(max_attempts):
        try:
            conn = psycopg2.connect(**RESTORE_DB_CONFIG)
            conn.close()
            return True
        except:
            if attempt < max_attempts - 1:
                time.sleep(1)
            else:
                log_with_timestamp("✗ Restore database not ready after 30 seconds")
                return False


def restore_pg_dump(backup_path, format_name):
    """Restore a pg_dump backup."""
    env = os.environ.copy()
    env["PGPASSWORD"] = RESTORE_DB_CONFIG["password"]

    if format_name == "plain":
        # Use psql for plain format
        command = [
            str(PG_BIN_PATH / "psql.exe"),
            "-h",
            RESTORE_DB_CONFIG["host"],
            "-p",
            str(RESTORE_DB_CONFIG["port"]),
            "-U",
            RESTORE_DB_CONFIG["user"],
            "-d",
            RESTORE_DB_CONFIG["database"],
            "-f",
            str(backup_path),
            "-v",
            "ON_ERROR_STOP=1",
        ]
    else:
        # Use pg_restore for custom, directory, tar formats
        command = [
            str(PG_BIN_PATH / "pg_restore.exe"),
            "-h",
            RESTORE_DB_CONFIG["host"],
            "-p",
            str(RESTORE_DB_CONFIG["port"]),
            "-U",
            RESTORE_DB_CONFIG["user"],
            "-d",
            RESTORE_DB_CONFIG["database"],
            "--verbose",
            str(backup_path),
        ]

        # Add parallel processing for custom and directory formats
        if format_name in ["custom", "directory"]:
            command.extend(["-j", "4"])
        else:
            # Only add --single-transaction for tar format (not parallel)
            command.insert(-1, "--single-transaction")

    return run_command(command, env=env)


def get_file_size(filepath):
    """Get file or directory size in bytes."""
    if os.path.isfile(filepath):
        return os.path.getsize(filepath)
    elif os.path.isdir(filepath):
        total_size = 0
        for dirpath, dirnames, filenames in os.walk(filepath):
            for filename in filenames:
                file_path = os.path.join(dirpath, filename)
                total_size += os.path.getsize(file_path)
        return total_size
    return 0


def format_size(size_bytes):
    """Format size in GB with 1 decimal place."""
    size_gb = size_bytes / (1024.0 * 1024.0 * 1024.0)
    return f"{size_gb:.1f} GB"


def format_minutes(seconds):
    """Format time in minutes with 1 decimal place."""
    minutes = seconds / 60.0
    return f"{minutes:.1f} mins"


def run_command(command, timeout=7200, env=None):
    """Run a command and measure execution time."""
    log_with_timestamp(f"Running: {' '.join(command)}")
    start_time = time.time()

    try:
        # Set environment variables for password
        if env is None:
            env = os.environ.copy()
            env["PGPASSWORD"] = DB_CONFIG["password"]

        result = subprocess.run(
            command, capture_output=True, text=True, timeout=timeout, env=env
        )

        end_time = time.time()
        duration = end_time - start_time

        if result.returncode != 0:
            log_with_timestamp(f"Command failed with return code {result.returncode}")
            log_with_timestamp(f"STDERR: {result.stderr}")
            return None, duration, result.stderr

        return result, duration, None

    except subprocess.TimeoutExpired:
        log_with_timestamp(f"Command timed out after {timeout} seconds")
        return None, timeout, "Command timed out"
    except Exception as e:
        end_time = time.time()
        duration = end_time - start_time
        log_with_timestamp(f"Command failed with exception: {e}")
        return None, duration, str(e)


def test_pg_dump(skip_restore=False):
    """Test pg_dump with all format and compression combinations."""
    log_with_timestamp("\n" + "=" * 60)
    log_with_timestamp("TESTING PG_DUMP")
    log_with_timestamp("=" * 60)

    backup_dir = Path("./backups")

    for format_name, extension in PG_DUMP_FORMATS:
        log_with_timestamp(f"\nTesting pg_dump format: {format_name}")

        # Test without compression
        test_name = f"pg_dump_{format_name}_no_compression"
        output_path = backup_dir / f"{test_name}.{extension}"

        command = [
            str(PG_BIN_PATH / "pg_dump.exe"),
            "-h",
            DB_CONFIG["host"],
            "-p",
            str(DB_CONFIG["port"]),
            "-U",
            DB_CONFIG["user"],
            "-d",
            DB_CONFIG["database"],
            "-f",
            str(output_path),
            "--format",
            format_name,
            "--verbose",
        ]

        if format_name == "directory":
            # For directory format, create the directory first
            output_path.mkdir(exist_ok=True)
            # Replace the path after -f flag with the directory path
            for i, item in enumerate(command):
                if item == "-f" and i + 1 < len(command):
                    command[i + 1] = str(output_path)
                    break
            # Add parallel processing for directory format
            command.extend(["-j", "4"])

        # Perform backup
        result, backup_duration, error = run_command(command)

        if result is not None:
            backup_size = get_file_size(output_path)
            db_size = get_database_size()
            compression_ratio = backup_size / db_size if db_size > 0 else 0

            # Clean restore database and perform restore
            restore_success = False
            restore_duration = 0
            restore_error = None
            restored_db_size = 0

            if not skip_restore:
                if clean_restore_database() and wait_for_restore_db():
                    restore_result, restore_duration, restore_error = restore_pg_dump(
                        output_path, format_name
                    )
                    if restore_result is not None and restore_result.returncode == 0:
                        restore_success = True
                        restored_db_size = get_restore_database_size()
            else:
                restore_success = True  # Mark as successful if skipped
                restore_error = "Skipped"

            results.append(
                {
                    "tool": "pg_dump",
                    "format": format_name,
                    "compression_method": "none",
                    "compression_level": 0,
                    "backup_duration_seconds": backup_duration,
                    "restore_duration_seconds": restore_duration,
                    "total_duration_seconds": backup_duration + restore_duration,
                    "backup_size_bytes": backup_size,
                    "database_size_bytes": db_size,
                    "restored_db_size_bytes": restored_db_size,
                    "compression_ratio": compression_ratio,
                    "backup_success": True,
                    "restore_success": restore_success,
                    "backup_error": None,
                    "restore_error": restore_error,
                    "timestamp": datetime.now().isoformat(),
                }
            )

            if skip_restore:
                log_with_timestamp(
                    f"✓ {test_name}: Backup {format_minutes(backup_duration)}, "
                    f"{format_size(backup_size)}, ratio: {compression_ratio:.3f} (restore skipped)"
                )
            else:
                log_with_timestamp(
                    f"✓ {test_name}: Backup {format_minutes(backup_duration)}, Restore {format_minutes(restore_duration)}, "
                    f"{format_size(backup_size)}, ratio: {compression_ratio:.3f}"
                )
        else:
            results.append(
                {
                    "tool": "pg_dump",
                    "format": format_name,
                    "compression_method": "none",
                    "compression_level": 0,
                    "backup_duration_seconds": backup_duration,
                    "restore_duration_seconds": 0,
                    "total_duration_seconds": backup_duration,
                    "backup_size_bytes": 0,
                    "database_size_bytes": get_database_size(),
                    "restored_db_size_bytes": 0,
                    "compression_ratio": 0,
                    "backup_success": False,
                    "restore_success": False,
                    "backup_error": error,
                    "restore_error": "Backup failed",
                    "timestamp": datetime.now().isoformat(),
                }
            )
            log_with_timestamp(f"✗ {test_name}: BACKUP FAILED - {error}")

        # Test with compression (only for formats that support it)
        if format_name in ["custom", "directory"]:
            for compression_method in COMPRESSION_METHODS[1:]:  # Skip 'none'
                if compression_method == "gzip" and format_name == "directory":
                    continue  # Directory format doesn't support gzip compression directly

                compression_levels = COMPRESSION_LEVELS.get(compression_method, [1])

                for level in compression_levels:
                    test_name = (
                        f"pg_dump_{format_name}_{compression_method}_level_{level}"
                    )
                    output_path = backup_dir / f"{test_name}.{extension}"

                    command = [
                        str(PG_BIN_PATH / "pg_dump.exe"),
                        "-h",
                        DB_CONFIG["host"],
                        "-p",
                        str(DB_CONFIG["port"]),
                        "-U",
                        DB_CONFIG["user"],
                        "-d",
                        DB_CONFIG["database"],
                        "-f",
                        str(output_path),
                        "--format",
                        format_name,
                        "--verbose",
                    ]

                    # Add compression options
                    if format_name == "custom":
                        if compression_method == "gzip":
                            command.extend(["-Z", str(level)])
                        else:
                            command.extend(
                                [
                                    "--compress",
                                    f"{compression_method}:{level}",
                                ]
                            )
                    elif format_name == "directory":
                        output_path.mkdir(exist_ok=True)
                        # For directory format, replace the path after -f
                        for i, item in enumerate(command):
                            if item == "-f" and i + 1 < len(command):
                                command[i + 1] = str(output_path)
                                break
                        if (
                            compression_method != "gzip"
                        ):  # Directory format supports lz4 and zstd
                            command.extend(
                                [
                                    "--compress",
                                    f"{compression_method}:{level}",
                                ]
                            )
                        # Add parallel processing for directory format
                        command.extend(["-j", "4"])

                    # Perform backup
                    result, backup_duration, error = run_command(command)

                    if result is not None:
                        backup_size = get_file_size(output_path)
                        db_size = get_database_size()
                        compression_ratio = backup_size / db_size if db_size > 0 else 0

                        # Clean restore database and perform restore
                        restore_success = False
                        restore_duration = 0
                        restore_error = None
                        restored_db_size = 0

                        if not skip_restore:
                            if clean_restore_database() and wait_for_restore_db():
                                restore_result, restore_duration, restore_error = (
                                    restore_pg_dump(output_path, format_name)
                                )
                                if (
                                    restore_result is not None
                                    and restore_result.returncode == 0
                                ):
                                    restore_success = True
                                    restored_db_size = get_restore_database_size()
                        else:
                            restore_success = True  # Mark as successful if skipped
                            restore_error = "Skipped"

                        results.append(
                            {
                                "tool": "pg_dump",
                                "format": format_name,
                                "compression_method": compression_method,
                                "compression_level": level,
                                "backup_duration_seconds": backup_duration,
                                "restore_duration_seconds": restore_duration,
                                "total_duration_seconds": backup_duration
                                + restore_duration,
                                "backup_size_bytes": backup_size,
                                "database_size_bytes": db_size,
                                "restored_db_size_bytes": restored_db_size,
                                "compression_ratio": compression_ratio,
                                "backup_success": True,
                                "restore_success": restore_success,
                                "backup_error": None,
                                "restore_error": restore_error,
                                "timestamp": datetime.now().isoformat(),
                            }
                        )

                        if skip_restore:
                            log_with_timestamp(
                                f"✓ {test_name}: Backup {format_minutes(backup_duration)}, "
                                f"{format_size(backup_size)}, ratio: {compression_ratio:.3f} (restore skipped)"
                            )
                        else:
                            log_with_timestamp(
                                f"✓ {test_name}: Backup {format_minutes(backup_duration)}, Restore {format_minutes(restore_duration)}, "
                                f"{format_size(backup_size)}, ratio: {compression_ratio:.3f}"
                            )
                    else:
                        results.append(
                            {
                                "tool": "pg_dump",
                                "format": format_name,
                                "compression_method": compression_method,
                                "compression_level": level,
                                "backup_duration_seconds": backup_duration,
                                "restore_duration_seconds": 0,
                                "total_duration_seconds": backup_duration,
                                "backup_size_bytes": 0,
                                "database_size_bytes": get_database_size(),
                                "restored_db_size_bytes": 0,
                                "compression_ratio": 0,
                                "backup_success": False,
                                "restore_success": False,
                                "backup_error": error,
                                "restore_error": "Backup failed",
                                "timestamp": datetime.now().isoformat(),
                            }
                        )
                        log_with_timestamp(f"✗ {test_name}: BACKUP FAILED - {error}")


def save_tabular_results():
    """Save tabular test results to a CSV file."""
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

    # Save as tabular CSV
    csv_file = f"backup_performance_tabular_{timestamp}.csv"

    with open(csv_file, "w", newline="") as f:
        writer = csv.writer(f)

        # Write header
        writer.writerow(
            [
                "format",
                "backup duration mins",
                "restore duration mins",
                "total duration mins",
                "backup size GB",
                "db size GB",
                "restored DB size GB",
                "db size % from original",
            ]
        )

        # Write data rows
        for r in sorted(
            results,
            key=lambda x: (
                x["format"],
                x["compression_method"],
                x["compression_level"],
            ),
        ):
            if not r["backup_success"]:
                continue

            format_str = f"{r['format']} {r['compression_method']}"
            if r["compression_method"] != "none":
                format_str += f" {r['compression_level']}"

            backup_mins = round(r["backup_duration_seconds"] / 60.0, 1)
            restore_mins = round(r["restore_duration_seconds"] / 60.0, 1)
            total_mins = round(backup_mins + restore_mins, 1)

            backup_gb = round(r["backup_size_bytes"] / (1024.0 * 1024.0 * 1024.0), 1)
            db_gb = round(r["database_size_bytes"] / (1024.0 * 1024.0 * 1024.0), 1)
            restored_db_gb = round(
                r["restored_db_size_bytes"] / (1024.0 * 1024.0 * 1024.0), 1
            )

            db_size_percent = round((backup_gb / db_gb) * 100) if db_gb > 0 else 0

            writer.writerow(
                [
                    format_str,
                    backup_mins,
                    restore_mins,
                    total_mins,
                    backup_gb,
                    db_gb,
                    restored_db_gb,
                    db_size_percent,
                ]
            )

    print(f"Tabular results saved to {csv_file}")


def save_results():
    """Save test results to JSON and CSV files."""
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

    # Save as JSON
    json_file = f"backup_performance_results_{timestamp}.json"
    with open(json_file, "w") as f:
        json.dump(results, f, indent=2)
    print(f"\nResults saved to {json_file}")

    # Save as CSV
    csv_file = f"backup_performance_results_{timestamp}.csv"
    if results:
        fieldnames = results[0].keys()
        with open(csv_file, "w", newline="") as f:
            writer = csv.DictWriter(f, fieldnames=fieldnames)
            writer.writeheader()
            writer.writerows(results)
        print(f"Results saved to {csv_file}")

    # Save tabular results
    save_tabular_results()


def print_summary():
    """Print a summary of test results."""
    print("\n" + "=" * 80)
    print("PERFORMANCE TEST SUMMARY")
    print("=" * 80)

    if not results:
        print("No results to display.")
        return

    # Check if any tests had restore attempts
    restore_attempted = any(
        r["restore_error"] != "Skipped" for r in results if r["backup_success"]
    )

    if restore_attempted:
        successful_tests = [
            r for r in results if r["backup_success"] and r["restore_success"]
        ]
        backup_only_success = [
            r
            for r in results
            if r["backup_success"]
            and not r["restore_success"]
            and r["restore_error"] != "Skipped"
        ]
        failed_tests = [r for r in results if not r["backup_success"]]

        print(f"Total tests: {len(results)}")
        print(f"Complete success (backup + restore): {len(successful_tests)}")
        print(f"Backup only success: {len(backup_only_success)}")
        print(f"Complete failures: {len(failed_tests)}")

        if successful_tests:
            print(f"\nBest compression ratios (complete success):")
            best_compression = sorted(
                successful_tests, key=lambda x: x["compression_ratio"]
            )[:5]
            for test in best_compression:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Ratio: {test['compression_ratio']:.3f}, Backup: {format_minutes(test['backup_duration_seconds'])}, "
                    f"Restore: {format_minutes(test['restore_duration_seconds'])}, Total: {format_minutes(test['total_duration_seconds'])}"
                )

            print(f"\nFastest total time (backup + restore):")
            fastest_total = sorted(
                successful_tests, key=lambda x: x["total_duration_seconds"]
            )[:5]
            for test in fastest_total:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Total: {format_minutes(test['total_duration_seconds'])}, Ratio: {test['compression_ratio']:.3f}"
                )

            print(f"\nFastest backup times:")
            fastest_backup = sorted(
                successful_tests, key=lambda x: x["backup_duration_seconds"]
            )[:5]
            for test in fastest_backup:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup: {format_minutes(test['backup_duration_seconds'])}, Restore: {format_minutes(test['restore_duration_seconds'])}"
                )

            print(f"\nFastest restore times:")
            fastest_restore = sorted(
                successful_tests, key=lambda x: x["restore_duration_seconds"]
            )[:5]
            for test in fastest_restore:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Restore: {format_minutes(test['restore_duration_seconds'])}, Backup: {format_minutes(test['backup_duration_seconds'])}"
                )

        if backup_only_success:
            print(f"\nBackup-only successes (restore failed):")
            for test in backup_only_success:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup: {format_minutes(test['backup_duration_seconds'])}, Restore Error: {test['restore_error']}"
                )

        if failed_tests:
            print(f"\nComplete failures:")
            for test in failed_tests:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup Error: {test['backup_error']}"
                )
    else:
        # Restore was skipped for all tests
        successful_tests = [r for r in results if r["backup_success"]]
        failed_tests = [r for r in results if not r["backup_success"]]

        print(f"Total tests: {len(results)}")
        print(f"Successful backups: {len(successful_tests)}")
        print(f"Failed backups: {len(failed_tests)}")
        print("Note: Restore tests were skipped")

        if successful_tests:
            print(f"\nBest compression ratios:")
            best_compression = sorted(
                successful_tests, key=lambda x: x["compression_ratio"]
            )[:5]
            for test in best_compression:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Ratio: {test['compression_ratio']:.3f}, Backup: {format_minutes(test['backup_duration_seconds'])}"
                )

            print(f"\nFastest backup times:")
            fastest_backup = sorted(
                successful_tests, key=lambda x: x["backup_duration_seconds"]
            )[:5]
            for test in fastest_backup:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup: {format_minutes(test['backup_duration_seconds'])}, Ratio: {test['compression_ratio']:.3f}"
                )

        if failed_tests:
            print(f"\nFailed backups:")
            for test in failed_tests:
                print(
                    f"  {test['tool']} {test['format']} {test['compression_method']}:{test['compression_level']} - "
                    f"Backup Error: {test['backup_error']}"
                )


def print_tabular_report():
    """Print a tabular report of results similar to the requested format."""
    if not results:
        print("No results to display.")
        return

    print("\n" + "=" * 120)
    print("TABULAR PERFORMANCE REPORT")
    print("=" * 120)

    # Print header
    print(
        f"{'format':<20}{'backup duration mins':<20}{'restore duration mins':<25}{'total duration mins':<20}"
        f"{'backup size GB':<15}{'db size GB':<15}{'restored DB size GB':<20}{'db size % from original':<25}"
    )
    print("-" * 120)

    # Group results by format and compression method+level
    for r in sorted(
        results,
        key=lambda x: (x["format"], x["compression_method"], x["compression_level"]),
    ):
        if not r["backup_success"]:
            continue

        format_str = f"{r['format']} {r['compression_method']}"
        if r["compression_method"] != "none":
            format_str += f" {r['compression_level']}"

        backup_mins = r["backup_duration_seconds"] / 60.0
        restore_mins = r["restore_duration_seconds"] / 60.0
        total_mins = backup_mins + restore_mins

        backup_gb = r["backup_size_bytes"] / (1024.0 * 1024.0 * 1024.0)
        db_gb = r["database_size_bytes"] / (1024.0 * 1024.0 * 1024.0)
        restored_db_gb = r["restored_db_size_bytes"] / (1024.0 * 1024.0 * 1024.0)

        db_size_percent = (backup_gb / db_gb) * 100 if db_gb > 0 else 0

        print(
            f"{format_str:<20}{backup_mins:.1f}{'':<14}{restore_mins:.1f}{'':<19}{total_mins:.1f}{'':<14}"
            f"{backup_gb:.1f}{'':<9}{db_gb:.1f}{'':<9}{restored_db_gb:.1f}{'':<14}{db_size_percent:.0f}"
        )


def main():
    """Main function to run pg_dump backup performance tests."""
    parser = argparse.ArgumentParser(
        description="PostgreSQL pg_dump backup performance testing"
    )
    parser.add_argument(
        "--skip-gzip", action="store_true", help="Skip gzip compression tests"
    )
    parser.add_argument(
        "--skip-lz4", action="store_true", help="Skip lz4 compression tests"
    )
    parser.add_argument(
        "--skip-zstd", action="store_true", help="Skip zstd compression tests"
    )
    parser.add_argument(
        "--skip-restore", action="store_true", help="Skip restore tests"
    )

    args = parser.parse_args()

    # Filter compression methods based on skip flags
    global COMPRESSION_METHODS
    filtered_compression_methods = ["none"]  # Always include 'none'

    if not args.skip_gzip:
        filtered_compression_methods.append("gzip")
    if not args.skip_lz4:
        filtered_compression_methods.append("lz4")
    if not args.skip_zstd:
        filtered_compression_methods.append("zstd")

    COMPRESSION_METHODS = filtered_compression_methods

    log_with_timestamp("PostgreSQL pg_dump Backup Performance Testing")
    log_with_timestamp("=" * 60)
    log_with_timestamp(
        f"Source Database: {DB_CONFIG['database']} on {DB_CONFIG['host']}:{DB_CONFIG['port']}"
    )
    log_with_timestamp(
        f"Restore Database: {RESTORE_DB_CONFIG['database']} on {RESTORE_DB_CONFIG['host']}:{RESTORE_DB_CONFIG['port']}"
    )
    log_with_timestamp(f"Database size: {format_size(get_database_size())}")
    log_with_timestamp(f"Compression methods: {', '.join(COMPRESSION_METHODS)}")
    log_with_timestamp(
        f"Restore tests: {'Skipped' if args.skip_restore else 'Enabled'}"
    )
    log_with_timestamp(f"Test started: {datetime.now()}")

    # Ensure backup directory is ready
    backup_dir = ensure_backup_directory()
    log_with_timestamp(f"Backup directory: {backup_dir.absolute()}")

    try:
        # Run pg_dump tests only
        test_pg_dump(skip_restore=args.skip_restore)

        # Save and display results
        save_results()
        print_summary()
        print_tabular_report()

    except KeyboardInterrupt:
        log_with_timestamp("\nTest interrupted by user")
        if results:
            save_results()
            print_summary()
            print_tabular_report()
    except Exception as e:
        log_with_timestamp(f"\nTest failed with error: {e}")
        if results:
            save_results()
            print_summary()
            print_tabular_report()

    log_with_timestamp(f"\nTest completed: {datetime.now()}")


if __name__ == "__main__":
    main()

So, measurements.

Backup speed in seconds (lower is better):

Restore speed from backup in seconds (lower is better):

Total time for creation and restoration from backup in seconds (lower is better):

Total backup size as a percentage of the original database size (smaller is better):

Conclusions based on measurements

So, based on the measurements, I can make the following conclusions:

The fastest backup format is directory-based.

The custom format is faster than plain and tar in terms of total time under any circumstances. The directory format is faster than custom under any circumstances. If parallel mode enabled, of course.

The most useful in terms of the “speed and compression level” ratio was zstd with a compression level of 5.

zstd 15 and zstd 22 turned out to be useless in this particular test. They gave compression levels roughly the same as gzip 9, but took 2-8x times longer to produce the result.

Conclusion

And once again, if you need to create regular backups, I have an open source project for this task. I would be extremely grateful for a star on GitHub ❤️, as the first stars are hard to come by.

Also may be interesting:

10 unusual moments in Go for Java developer

How I got into open source in 2025 (with a PostgreSQL backup tool) after almost losing a ~$1,500/mo project in 2023

Rostislav Dugin — Tue, 22 Jul 2025 10:31:28 +0000

Once I destroyed my production DB with a sleepy UPDATE and had no fresh backups. Lost ~30% of the revenue, a bunch of nerves and way too many hours. That pain pushed me to build and open-source a PostgreSQL backup & monitoring tool that I now use everywhere.

Table of content

About the open-source PostgreSQL backup project
The story of how I broke the DB and couldn't fully recover
How I started building the project
Roadmap & future plans
DB safety rules I now enforce on every project
Wrap-up

About the open source PostgreSQL backup project

I open sourced my own tool for monitoring and backing up PostgreSQL. I've been building and using it for a little over two years. Initially, I've developed it for my day job and a couple of pet projects.

Only recently did it hit me that the project actually looks good enough to show publicly, it already helps my friends and could be useful to the community.

Stack: Go, gin, gorm, React, TypeScript, PostgreSQL - everything wrapped in Docker. The very first version was in Java, but I rewrote it to Go over the time.

In essence, it's a UI wrapper around the standard pg_dump in custom format, with a bunch of extras that make the UX less painful and add integrations with external storage and notification services.

What it can do:

Schedule backups (e.g., every day at 4 AM or every Sunday at midnight) for PostgreSQL 13-17.
Store compressed backups locally on the server, in S3 or Google Drive (NS servers and FTP are on the roadmap).
Send you a message after each backup that everything is fine... or not. Notifications are optional.
Ping you in Discord, email, Telegram and Slack if the DB stops responding. It only alerts after n failed checks (to avoid false positives due to network glitches) and shows an availability graph.

Naturally, the project is free, open source (MIT), self-hosted and comes with a humane web UI.

Project site: https://postgresus.com/

GitHub: https://github.com/RostislavDugin/postgresus

P.S. If the project looks useful and you have a GitHub account, I'd really appreciate a ⭐️. The first stars are really the hardest to get.

The story of how I broke the DB and couldn't fully recover

Back in 2023 I had a pet project that was a wrapper of ChatGPT (3.5). Basically, it was just reselling API access with a pretty UI and shortcuts. The project grew, then started to go down and I finally sold it. The DB was being backed up once a day with a console tool like PgBackRest to another server.

At the moment when the project was bringing in about $1,500 passively and hitting its revenue peak, something bad happened: I broke the data in the DB.

It was a Friday night. I was tired, I was instantly switching from coding to messages answering, totally unfocused. A customer asked me to change the email for his account.

Through SSH and psql I hopped into production VPS and typed something like:

UPDATE users SET email = 'customer@email.com' WHERE email ILIKE '%%';

Then I got distracted to copy the correct email from the chat and... hit Enter on "autopilot". Next thing I saw was something like AFFECTED ROWS: 10 000.

That was the only time in many, many years I literally felt a cold sweat on my back.

Disclaimers: of course, I knew I should have done a SELECT first, maybe set a SAVEPOINT, etc. But like in every horror story, the basic safety rules were ignored, and it all snowballed into disaster.

All user emails were overwritten. And here's the key detail: payment systems have strict rules - if a user can't access a paid service, that's a huge violation. Naturally, nobody could log in anymore and complaints started rolling in.

I ran to the backups - and the cold sweat got even worse. The most recent backup was about a month old 😐. No way to restore from that. From that time new payments came in, subscriptions were canceled (meaning I couldn't just restore everyone - some people had already left), etc.

Somehow, over the rest of the night and morning, I managed to reconstruct about 65% of the DB via scripts using user IDs. For the rest, I had to cancel subscriptions and refund people. It was painful, unpleasant and expensive.

The lesson was learned.

How I started building the project

Decision time: I'm going to build myself a backup tool that will ping me every day that everything is fine! And restore in a couple of clicks! ~~And blackjack and microservices!~~ And I'll add an API health-check endpoint too!

I made the first version of Postgresus in about a month in Java. Started using it. Let a few friends try it. Kept polishing it based on my needs and their feedback.

Turned out: it's useful. A few times those backups saved me (and not just me). The name "Postgresus" only appeared two months ago, before the repo was simply called "pg-web-backup".

Right now Postgresus solves these problems for me:

It's the main backup tool if the project is small or lives on a VPS instead of a cloud DB service.
It's the fallback backup tool if the project is large and lives in a DBaaS with its own cloud backups. It backs up "just in case" (if the cloud dies, gets blocked, the DB is accidentally deleted along with cloud backups due to non-payment, etc.). It's always better to have a duplicate backup than to end up in that unlucky 0.01% when even the cloud disappears and there's no Plan B.

Roadmap & future plans

I'm planning to push the project in these directions:

1) Add more PostgreSQL-specific load monitoring (pg_stat_activity, pg_stat_system, pg_locks) with a friendly UI. Think of it as an alternative to postgres_exporter + Grafana, but bundled out of the box with backups.

2) Observe and alert on slowdown of key queries.

In my work project, there are tables and specific functions that are too early to optimize (if the hypothesis fails, we might drop them), but they could grow and slow down.

For example, if INSERT INTO users (...) VALUES (...) starts taking more than 100 ms while the flow of new users is growing - we'll get a notification and go optimize.

3) Collect query stats by CPU time and execution frequency to see where resources are actually going and what's worth improving.

4) Add more channels for notifications and more storage providers.

DB safety rules I now enforce on every project

Let me remind you of two pieces of folk wisdom:

System admins fall into two categories: those who don't make backups yet and those who already do.

Don't just make backups - regularly test that you can actually restore from them.

Since trashing my project's DB, I've adopted these rules without exception:

Before any UPDATE, always run a SELECT and make sure you're touching exactly 1-2 rows.
If the change is big - set a SAVEPOINT manually.
Conduct "fire alarms" with restoration at least once every 3 months: restore from a cloud copy and from a local one. So when it matters, you don't discover there's no data, backups don't work,or restores take forever.`

In the last two years there have been a few cases where we needed to restore from backups - every time it worked, both in the cloud and via Postgresus. No issues, because the process had already been ironed out during tests. Basic safety rules do work.

Wrap-up

I hope this project will be useful to a broad set of developers, DBAs and DevOps folks. I plan to keep evolving it to make it even more helpful in real-world scenarios. I'm happy to hear any suggestions and feedback for improvement.

Might also be interesting

10 odd things in Go for a Java developer

10 unusual moments in Go for Java developer

Rostislav Dugin — Sun, 22 Jun 2025 12:32:12 +0000

A few years ago, I started adding Go to my arsenal of languages (being a Java developer at the time). It was very unfamiliar to me. Moreover, I did not accept the language at the first attempt. And I had to adopt it more because of circumstances than by choice.

But as time passed, Go became my main language and, I dare to say, my favorite. In the article below I will tell you why the language seemed unfamiliar to me, what paradigms I had to change in my head, and why in many ways it turned out to be more effective.

Let me make it clear: the article is aimed more at those who are planning to switch to Go than for experienced developers.

I was first introduced to Go in 2022. I had heard that Go was a simple and concise language that produced cleaner, more maintainable and faster code. Some imaginary image of performance, stability, and simplicity in one formed in my head. Looking ahead: in the end, it turned out to be true.

I decided to explore it - and... after a few approaches, I gave up. Unfamiliar syntax, slightly perverted model of open and closed by capitalization, no inheritance... Creepy!

After a couple more months, Go turned out to be the most suitable tool for the project (related to cryptocurrency) and the team already had expertise in Go. The language was taken for the project. Consequently, Go had to be learned regardless of preferences. After all, taste is taste, and work is work.

I studied the documentation, went through the official tutorial, got acquainted with a couple of libraries, wrote a few CRUD's and got used to Go. I couldn't love the language yet, but I accepted it and started writing in it regularly.

After a couple tens of thousands of lines of code, I noticed that I had reorganized myself. I wrote 70% of my code in Go, it became habitual for me. All third-party utilities, pet-projects and small proofs of concept became easier for me to write in Go (though I would have taken Java or Python earlier). And now, a few years later, the share of Go in my work has smoothly grown from ~10% to almost 100%.

In this article, I decided to share the moments that I personally found unusual when changing languages. Among other things, I talked to some developers I know who have also changed their stack from Java \ C# to Go in recent years and synchronized our feelings.

Table of content

The main priority of a language is visibility (but almost always at the expense of verbosity)
Implicit implementation of interfaces and point-of-use declaration
During structure definition, errors are not highlighted if some fields are missing
You can call methods on nil structures
Tags instead of annotations
Working with strings: string, rune and []byte
There is a common way of formatting, but not really
Control field visibility by capitalizing the first letter in the field name
No cyclic dependencies between modules (packages)
Short variable names…. too short

Before you read: I open sourced my project for PostgreSQL backups. I would be grateful for star on GitHub :)

1) The main priority of a language is visibility (but almost always at the expense of verbosity)

In Go, everything is done in favor of explicitness and readability. From error handling to data validation in API routes to explicit transactions. DI is not common in the Go community, especially in medium-small projects and microservices (although there is Uber FX). No annotations, minimal syntactic sugar.

In Java it is customary to think about abstractions first (proper interfaces, decompose into classes, think about factories). In Go, it is customary not to think about abstractions beforehand, but to make a minimally adequate structure and just write code. And introduce abstractions when needed. By the way, there is no inheritance either, only composition and interfaces.

Regarding errors: this is something that immediately catches your eye in Go and that brings a significant share of “explicitness”. Go has no “thrown” exceptions (and no try block in general). All errors are returned from functions as one of the parameters. However, there is panic that still can be thrown to stop your app (for business applications it is considered an antipattern in all style guides).

I will show some examples of how the amount of code increases, but the explicitness of error handling increases.

Example 1: config read

Java code with single try \ catch block:

try {
    String raw = Files.readString(Path.of("cfg.json"));

    Config cfg = mapper.readValue(raw, Config.class);
    cache.put("cfg", cfg);

    return cfg;
} catch (IOException | JsonProcessingException ex) {
    throw new IllegalStateException("cannot init config", ex);
}

The same code in Go:

raw, err := os.ReadFile("cfg.json")
if err != nil {
    return nil, fmt.Errorf("read cfg: %w", err)
}

var cfg Config
if err := json.Unmarshal(raw, &cfg); err != nil {
    return nil, fmt.Errorf("parse cfg: %w", err)
}

if err := cache.Set("cfg", cfg); err != nil {
    return nil, fmt.Errorf("cache cfg: %w", err)
}

return &cfg, nil

On the one hand, Java code is more compact. On the other hand, in case of an error in Java code, we will have to look at the stack trace and try to figure out which line generated the error.

In Go, an error will immediately show where exactly and what broke (and we don't have the option not to handle the error). Another subjective plus: it is easier for a reader unfamiliar with the code to understand what and where exactly went wrong.

Example 2: transactions

Java code:

@Transactional
public void processPayment(Payment p) {
    try {
        accountService.debit(p);
        journalRepository.save(p);
        mq.send(new PaymentMessage(p));
    } catch (Exception e) {
        throw new PaymentFailed("processing error", e);
    }
}

Go code:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return fmt.Errorf("begin tx: %w", err)
}

defer tx.Rollback()

if err := debit(tx, p); err != nil {
    return fmt.Errorf("debit: %w", err)
}

if err := journalSave(tx, p); err != nil {
    return fmt.Errorf("journal: %w", err)
}

if err := mq.Send(ctx, newPaymentMsg(p)); err != nil {
    return fmt.Errorf("mq send: %w", err)
}

return tx.Commit()

Again, Java code is shorter and more concise. Go code is longer and very explicit. No annotations. Transactions are called manually. Errors from each method are handled manually.

By the way, the absence of try reduces the code nesting, it becomes a kind of “one-level” code.

2) Implicit implementation of interfaces and point-of-use declaration

Go has interfaces similar to Java. For example:

type PaymentGateway interface {
    Charge(ctx context.Context, req ChargeRequest) (ChargeResponse, error)
}

To implement it, we need to create a structure with methods with the same signature:

type StripeGateway struct {}

func (s *StripeGateway) Charge(ctx context.Context, req ChargeRequest,) (ChargeResponse, error) {
  // implementation
}

As you can see, there are no implements words or anything that indicates that the structure implements the interface. This was very strange to me at first, because there is no explicit connection between interface and implementation. You can't press shift and go to an interface from a structure (at that time I was using VS Code, in Goland from JetBrains you can).

I was also confused by the situation when you change the name of a method or parameters, but the error is thrown somewhere at the other end of the project. Where someone is trying to use the interface. I was expecting an error like “wrong interface implementation, method arguments mismatch” in the place where the structure is defined, but the error was highlighted in the place where the interface is used.

It turned out to be an important idea of the language itself, which is related to the fact that interfaces are usually declared at the point of use, not implementations.

In Java, we stack interfaces (like PaymentGateway) and their implementations (like StripePaymentGateway or PayPalPaymentGateway) side by side in the same package.

In Go, it's common to put the interface in the module (similar to Java's package) that uses it. For example, in the billing or checkout package, where we will call the methods of the interface.

In other words, the interface lies in one part of the project. The implementations are in another. And there is no obvious connection between them, unless you look at the DI graph. It's not obvious. But it gives a bonus: reduced coupling. Easier to put mocks, easier to test, less dependency of modules on each other. It also allows you to avoid cyclic dependencies between modules (about it below is written).

However, it is important not to forget that even though there are fewer links in the code, the logical connectivity does not go anywhere.

3) During structure definition, errors are not highlighted if some fields are missing

Suppose we have the following structure:

type BillingService struct {
    stripeService *stripeService
    receiptService *receiptService
}

If you omit one of the values during the declaration - there will be no error during compilation. For example:

var billingService := BillingService{
   stripeService: getStripeService(),
   // there is no field, but it compiles fine
}

At first, I considered the structure declaration above as an analog of a constructor in Java. You should always pass all the parameters to it, otherwise the code will not compile. But no. In Go, the declaration without passing all the parameters will be valid.

In this case, with high probability, when using receiptService, there will be a panic because of referring to a nil value (similar to NullPointerException). By the way, it may not even occur - that's what the next paragraph is about.

There is a way to check the passing of all parameters at the compilation stage. To do this, you need to pass values not by field name, but by comma:

var billingService := BillingService{
   getStripeService(), // first param
   // error: there should be second param
}

In this case, the compiler will tell us that one more parameter is missing. This is the definition that is considered correct to use when building a DI graph. In order not to forget about new dependencies as they are added.

4) You can call methods on nil structures

Suppose we have a structure with a method (which accesses a pointer to the structure).

type Person struct {}

func (p *Person) Speak() { // attention: there is a pointer *, not value
    fmt.Println("Hey!")
}

We define a nil variable with the type of this structure and call a method on this variable. In Java, such code would cause NullPointerException. And in Go... it depends. If the structure uses a pointer to itself in the * method, there will be no problem:

package main

import "fmt"


func main() {
    var person *Person = nil
    person.Speak()
}

The code above will work and output Hey, even though the person variable is nil. Now let's remove the * from the Speak method:

type Person struct {}

func (p Person) Speak() { // use Person instead of *Person
    fmt.Println("Hey!")
}

And execute the same code:

package main

import "fmt"


func main() {
    var person *Person = nil
    person.Speak()
}

We're getting an error:

panic: runtime error: invalid memory address or nil pointer dereference

Why this is so - I realized after a very long time. After I read the book “Learning Go” and learned about pointer dereferencing. The book is tedious, practically a reference book, but I recommend reading it at least once.

The point is that when calling a method with a value, Go first dereferences the pointer to copy the structure and pass it to the method. But when calling a method with a reference, it just passes the pointer itself (even if it is nil) without trying to dereference it.

5) Tags instead of annotations

Java has annotations that can be attached to fields, methods and classes. They reduce code size a lot and hide a lot of logic underneath. I can't imagine developing in Spring and Hibernate without annotations anymore.

Annotations make magic. But... at some point I started to have a periodic feeling that there is too much magic. It's kind of hard to figure out where which proxy is calling whom and where which reflection is being used. Long canvases of stacktraces are the same way.

There are no annotations in Go.

Instead of annotations, there are “tags” that contain no logic. You can take a structure, write tags for its fields. Then pass the structure to some function that will extract those tags and do something based on their contents.

For example, you could write a structure like this, which we are waiting for in an API method:

type User struct {
    ID    int64  `json:"id"`
    Name  string `json:"name"  binding:"required"`
    Email string `json:"email" binding:"required,email"`
}

Again, the tags themselves don't do anything and nothing happens at compile time.

Then, we can pass this structure to a method that decodes JSON:

if err := c.ShouldBindJSON(&user); err != nil { // decode request body to struct
    c.JSON(http.StatusBadRequest, gin.H{"error": err.Error()})
    return
}

ShouldBindJSON will extract the tags and validate each field based on what the tag requires. ORM and SQL constructors work similarly, which define column names and other parameters via tags.

But, since tags don't have their own logic, Go doesn't have annotations like @ToString, @Builder, @Transactional, @Service and many others. On the one hand, we do everything by hand and there is more code. On the other hand, more explicity and control.

Important: I'm not saying annotations are bad. It is very good. It's just that languages have different approaches for different tasks with their own pros, cons and compromises.

6) Working with strings: `string`, `rune` and `[]byte`

In Java, a string is an immutable sequence of char. In Go, the string type is an immutable slice of bytes (UTF-8 by default). To represent an individual character, we use rune, a 32-bit integer containing the character in Unicode.

Hence, when working with strings, we need to use len and trim strings carefully. Especially when we use non-English language and non-standard characters (for example, smiley faces).

Let's look at an example. Let's take an English word:

s := "hello"                // 5 chars, 5 bytes
fmt.Println(len(s))         // 5 🔢 (bytes)
fmt.Println(len([]rune(s))) // 5 🔣 (chars)
fmt.Println([]rune(s))      // [104 101 108 108 111]

That makes sense. Now we take a Russian word and a couple of smiley faces:

s := "Привет 🌍😊"         // 8 chars, 20 bytes
fmt.Println(len(s))         // 20 🔢 (bytes)
fmt.Println(len([]rune(s))) // 8 🔣 (chars)
fmt.Println([]rune(s))      // [1055 1088 1080 1074 1077 1090 127757 128522]

The number of characters and bytes diverged. If we trimmed or changed the string without converting to rune, we would run into errors and incorrect data.

7) There is a common way of formatting, but not really

In Java, there are several common codestyles and an infinite number of customizations in the IDE (at least in IDEA). Typically, projects take a commonly accepted codestyle (from Oracle or Google) and add local formatting rules in the IDE itself. This config is then passed between team members.

One of the reasons I wanted to try Go is the claimed uniformity. There is a built-in go fmt utility that comes with the language. All code in Go is formatted by it. I was intrigued by this!

Turns out... It's not that simple. Here is the same code manually formatted differently:

func SomeMethod(value1 string, value2 string, value3 string) error {
    return nil
}

func SomeMethod(
    value1 string,
    value2 string,
    value3 string,
) error {
    return nil
}

func SomeMethod(value1 string,
    value2 string,
    value3 string,
) error {
    return nil
}

The standard go fmt considers all these options valid and does not change them.... Also go fmt does not know how to limit line length and hyphenation rules, there are no rules for import list and many other things.

That's why teams still have to agree on some things, for some things third-party formatters are used. The most popular tool is golangci-lint. It is able to accumulate other formatters (and linters) in itself.

8) Control field visibility by capitalizing the first letter in the field name

This is one of the points that repulsed me a lot in Go. My mindset had a hard time adjusting to this logic.

In Java, scope is defined by the modifiers public / private / protected. Common English words, everything is clear. This approach is used in a large number of popular languages.

In Go, the scope is defined by the first character of the name: an uppercase letter makes the field public, a lowercase letter makes it private inside the module.

Here is an example of a public (or exported in Go terms) structure with public and private methods:

type Person struct {}       // public struct

func (Person) Talk() {}     // public method

func (Person) speak() {}    // private method

Here is a private structure with a private method (and you can also make structures with a small letter, not like classes in Java):

type person struct { /*...*/ }  // private struct

func (person) talk() {}         // private method

Here are the public and private functions:

func SomeFunc() { /*...*/ }     // public func

func someFunc() { /*...*/ }     // private func

Whether this approach is good or bad is a matter of habit. It took me a long time to get used to it, but it's a matter of taste. Over time, I got used to it.

9) No cyclic dependencies between modules (packages)

I like this part of Go a lot. It forces you to write cleaner code and think about the correct hierarchy of components and modules.

In Java, we can import objects from different packages to each other. For example:

// src/a/A.java
package a;

import b.B;

public class A {
    private B b;
}

// src/b/B.java
package b;

import a.A;

public class B {
    private A a;
}

In this example, both classes refer to each other via fields, and packages a and b import each other's classes. The Java compiler allows this (even though we need to remember about lazy initialization of values).

You can't do this in Go. For example:

// a.go
package a

import "project/b"  // a → b

// b.go
package b

import "project/a"  // b → a ❌

In this case, we will get an error:

import cycle not allowed

10) Short variable names.... too short

This is one of those practices that I haven't fully come to terms with.

In Go, it's common to use concise or single letter identifiers (ctx, db, err, s, r). Especially in small functions. For example, here's an example of idiomatic code:

func GetUser(c *gin.Context) {
    id := c.Param("id")

    u, err := userRepo.FindByID(id)
    if err != nil {
        c.JSON(404, gin.H{"error": err.Error()})
        return
    }

    c.JSON(200, u)
}

context is shortened to c. user to u. Similarly, a lot of common conventions are ResponseWriter -> w, request -> r, payment -> p, etc. In “infrastructure” or “template” code, this is normal. But in business logic it is not so good, because code readability decreases.

I try to use more clear names. Especially in business logic. For example, the same concepts in gin and quite obvious things can be shortened to one letter (although the same req and res are clearer as for me).

But when it comes to business logic, I still advise you not to write like this:

func (s *BillingService) Pay(payments *[]Payment, b *Bank) (*Receipt, error) {
    r := &Receipt{ID: uuid.New(), Amounts: []int{}}

    for _, p := range *payments {
        r.Amounts = append(r.Amounts, p.Amount)
    }

    if err := b.Debit(p.Amount); err != nil {
        return nil, err
    }

    return r, nil
}

Better give clearer and longer names:

func (s *BillingService) Pay(payments *[]Payment, bank *Bank) (*Receipt, error) {
    receipt := &Receipt{ID: uuid.New(), Amounts: []int{}}

    for _, payment := range *payments {
        receipt.Amounts = append(receipt.Amounts, payment.Amount)
    }

    if err := bank.Debit(p.Amount); err != nil {
        return nil, err
    }

    return receipt, nil
}

This provides the following advantages:

reduces the cognitive load of the reader (no need to run upward with your eyes to figure out what that letter means);
less chance of mistaking the name or confusing something;
if the method is long - allows to understand in any part of the method what a variable is responsible for.

Of course, such conventions depend on the team and the practices adopted. If the team has adopted the standard approach with short names, there is no need to do otherwise (at least not without agreeing on it in advance).

Conclusion

Actually, above I tried to describe the main points that were unfamiliar to me (and not only to me). The languages are quite different. I would say that Go for me is something between the stability of Java and the flexibility of Python.

Time has shown that for me learning Go has been worth it. After all, a language is first and foremost a tool. Go has become an effective tool for me. I like it, it gives me fast and stable results. I really like the simplicity of the language and its clarity, despite the shortage of syntactic sugar and at the expense of a little more code. Also, AI (especially Cursor IDE) is good friends with it.

I hope my article will encourage at least a few developers to learn Go and make some things easier to understand.

If you like the article, you can loook at my project for PostgreSQL backups in open source. I would be very grateful for the star on GitHub. ❤️

Forem: Rostislav Dugin

I vibecoded an extension for VS Code so I wouldn't have to vibecode with Claude Code (and an example of how to develop with AI)

Disclaimers

How do I work with AI?

What did I find lacking in Claude Code?

How did I make the extension?

Problems arose:

What was the result?

Conclusion

Postgresus 2.0: new version of open source PostgreSQL backup tool

What is Postgresus?

New features appeared in version 2.0

1) Database health check

2) New sources for storing backups and sending notifications

3) Workspace and user management

4) Encryption and protection

1) Encryption of all passwords and secrets

2) Encryption of backup files

3) Using read only user for backups

5) Compression by default, the most optimal one

6) Kubernetes Helm support

7) Dark theme

8) Get rid of incremental backups and PITR (Point In Time Recovery)

9) Many small improvements

What didn't work out?

1) Full database resource monitoring

2) SQL console

Conclusion

How to Create PostgreSQL Backups: A Complete Guide

What is Postgresus?

Prerequisites

Step 1: Install Postgresus

Option A: Automated Script Installation (Linux Only, Recommended)

Option B: Docker Run (Cross-Platform)

Option C: Docker Compose

Step 2: Add Your Database and Configure Credentials

Step 3: Enable Backup Encryption (Recommended)

Step 4: Select Storage Destination

Available Storage Options:

Configuring Your Storage:

Step 5: Configure Backup Schedule

Step 6: Set Up Notifications (Optional but Recommended)

Step 7: Verify and Monitor

Monitoring Your Backups:

Testing Backup Restoration:

Advanced Features for Teams and Enterprises

Workspaces

Role-Based Access Control

Audit Logging

Best Practices for PostgreSQL Backups

Conclusion

Top 5 PostgreSQL backup tools in 2025

1. Postgresus

2. pgBackRest

3. Barman

5. PgBackWeb (UFO backup)

Conclusion

I built Log Bull — the simple alternative to ELK, Loki and Graylog to collect logs from code (Python, Java, Go, JS, PHP, etc.)

About the project

How to deploy Log Bull?

How to send logs?

How to view logs?

Conclusion

PostgreSQL backups: comparing pg_dump speed in different formats and with different compression levels

Why do I need this measurement?

How I got into open source in 2025 (with a PostgreSQL backup tool) after almost losing a ~$1,500/mo project in 2023

Backup formats and compression types in pg_dump

Data preparation

Measurement results

Conclusions based on measurements

Conclusion

PostgreSQL backups: comparing pg_dump speed in different formats and with different compression levels

Why do I need this measurement?

How I got into open source in 2025 (with a PostgreSQL backup tool) after almost losing a ~$1,500/mo project in 2023

Backup formats and compression types in pg_dump

Data preparation

Measurement results

Conclusions based on measurements

Conclusion

How I got into open source in 2025 (with a PostgreSQL backup tool) after almost losing a ~$1,500/mo project in 2023

6) Working with strings: `string`, `rune` and `[]byte`