pgreplay(1)

📦
version 1.5.0 · 2 versions
, in ubuntu resolute, ubuntu stonking

💖 pgreplay 💖

okay so bestie, welcome to pgreplay, straight out of the PostgreSQL Utilities squad ✨ it's the PostgreSQL log file replayer for performance tests, and honestly? it's giving time machine. periodt.

✨ NAME ✨

pgreplay — a PostgreSQL log file replayer for performance tests 🎀

🦋 SYNOPSIS 🦋

pgreplay [parse options] [replay options] [-d level] [infile]
pgreplay -f [parse options] [-o outfile] [-d level] [infile]
pgreplay -r [replay options] [-d level] [infile]

💅 DESCRIPTION 💅

So here's the vibe. pgreplay reads a PostgreSQL log file — and NOT a WAL file, okay, we're not doing that — pulls out the SQL statements, and runs them against a PostgreSQL database cluster in the same order and with the same relative timing as the original. Then it hands you a final report with a genuinely useful statistical analysis of your workload and how it executed. obsessed. 💖

First form up there? That's the classic: the log file infile gets replayed at the time it is read, live, as it goes. 🫶

Then there's -f. With -f, pgreplay is in its no-thoughts-just-notes era — it does not execute anything, it just writes the statements to a 'replay file' called outfile, which you can replay later with the third form. 💅

And with -r? Now we cook. -r makes pgreplay execute the statements sitting in the replay file infile that was made by the second form. 🍓

If the execution of statements gets behind schedule, warning messages get issued, letting you know the server cannot handle the load in a timely fashion. The whole idea is to replay a real-world database workload as exactly as possible. 💀

🎀 how to make a log file pgreplay can actually parse 🎀

You gotta set these parameters in postgresql.conf, bestie, no cap:

log_min_messages=error (or more)
log_min_error_statement=log (or more)
log_connections=on
log_disconnections=on
log_line_prefix='%m|%u|%d|%c|' (if you don't use CSV logging)
log_statement='all'
lc_messages must be set to English (encoding does not matter)
bytea_output=escape (from version 9.0 on, only if you want to
replay the log on 8.4 or earlier)

Also important, and I mean important: the database cluster you replay against has to be a clone of the database cluster that generated the logs, from the moment immediately before those logs were generated. Like, a full-on twin. No shortcuts. 😭

🌸 when is this whole thing useful 🌸

pgreplay is a performance-testing icon, especially in these situations:

  • You want to compare the performance of your PostgreSQL application on different hardware or different operating systems. 👑
  • You want to upgrade your database and want to make sure the new database version does not suffer from performance regressions that affect you. 💜

And bonus — pgreplay can give you some feeling for how your application might scale, because it lets you try replaying the workload at a higher speed. ✨

⚠️ be warned though: 500 users working at double speed is not really the same as 1000 users working at normal speed. fr fr. 🙈

🎀 OPTIONS 🎀

🌸 Parse options:

  • -c — tells pgreplay the log file is in 'csvlog' format (this is highly recommended bestie, take the hint) and not in 'stderr' format.
  • -b timestamp — only log entries greater or equal to that timestamp will be parsed. The format is YYYY-MM-DD HH:MM:SS.FFF, just like in the log file. Any optional time zone part gets ignored. 🕐
  • -e timestamp — only log entries less or equal to that timestamp will be parsed. Same format, YYYY-MM-DD HH:MM:SS.FFF, just like in the log file, and again any optional time zone part is ignored. 🕐
  • -q — says that a backslash in a simple string literal escapes the following single quote. This depends on configuration options like standard_conforming_strings, and it's the default for server version 9.0 and less.
  • -D database — only log entries related to the specified database will be parsed. (You can pass this option multiple times if you've got more than one database. 💅)
  • -U username — only log entries related to the specified username will be parsed. (Also repeatable, for more than one user. 🫶)

🍓 Replay options:

  • -h hostname — the host name where the target database cluster is running (or the directory where the UNIX socket can be found). Defaults to local connections. This works just like the -h option of psql. 💖
  • -p port — the TCP port where the target database cluster can be reached.
  • -W password — by default, pgreplay assumes the target database cluster is configured for trust authentication. With -W you can specify a password that will be used for all users in the cluster. 🔑
  • -s factor — the speed factor for replay, 1 by default. This can be any valid positive floating point number. A factor less than 1 replays the workload in 'slow motion', while a factor greater than 1 means 'fast forward'. ⏩
  • -E encoding — the encoding of the log file, which gets used as the client encoding during replay. If you leave it out, your default client encoding is used.
  • -j — if all connections are idle, jump ahead to the next request instead of sleeping. This speeds up replay. Execution delays will still be reported correctly, but heads up: replay statistics will not contain the idle time. 😴➡️🏃
  • -n — dry run mode. No connections to the server are made. Super useful for checking if the replay file is corrupt, or to get statistics about the replay file (number of statements, original duration, ...). 🙈
  • -X options — extra connection options for replay connections. These must be libpq connection options specified in the format 'option=value [...]'.

⭐ Output options:

  • -o outfile — specifies the replay file where the statements will be written, for later replay. 💾

🧚 Debug options:

  • -d level — specifies the trace level (between 1 and 3). Increasing levels produce more detailed information about what pgreplay is doing. 🔍
  • -v — prints the program version and exits. bye bestie 👋

💜 ENVIRONMENT 💜

  • PGHOST — specifies the default value for the -h option.
  • PGPORT — specifies the default value for the -p option.
  • PGCLIENTENCODING — specifies the default value for the -E option.

😭 LIMITATIONS 😭

okay so pgreplay can only replay what PostgreSQL actually logs, and that leads to some limitations. not her fault, it's just the vibes:

  • COPY statements will not be replayed, because the copy data are not logged. 💀
  • Fast-path API function calls are not logged and will not be replayed. Unfortunately, this includes the Large Object API. 😭
  • Since the log file is always in the server encoding (which you can specify with the -E switch of pgreplay), all SET client_encoding statements will be ignored.
  • Since the preparation time of prepared statements is not logged (unless log_min_messages is debug2 or more), these statements will be prepared immediately before they are first executed during replay.
  • Because the log file contains only text, query parameters and return values will always be in text and never in binary format. If you use binary mode to, say, transfer large binary data, pgreplay can cause significantly more network traffic than the original run. 🚦
  • Sometimes, if a connection takes longer to complete, the session ID unexpectedly changes in the PostgreSQL log file. This causes pgreplay to treat the session as two different ones, resulting in an additional connection. This is arguably a bug in PostgreSQL. 🙃

👑 AUTHOR 👑

Written by Laurenz Albe <laurenz.albe@cybertec.at>. 💖

and that's a wrap, bestie 🫶 this whole page is from the pgreplay(1) manual in the PostgreSQL Utilities set, dated Jun 2011. slay, stay hydrated, and always replay responsibly 💅✨

📦 where this page comes from