Thread

BUG #18926: PostgreSQL 17.5 produces malformed binary COPY output (`GPOCYP` instead of `PGCOPY`) across platform

PG Bug reporting form <noreply@postgresql.org> — 2025-05-14T03:27:14Z

The following bug has been logged on the website:

Bug reference:      18926
Logged by:          Robb Shecter
Email address:      robb@public.law
PostgreSQL version: 17.5
Operating system:   Both Ubuntu and MacOS
Description:        

Thank you — that’s a crucial update. Here's the revised version of the
message for the `pgsql-bugs@postgresql.org` mailing list, now reflecting
that the issue **also occurs on Ubuntu in production**, making it likely a
PostgreSQL 17.5 upstream bug rather than a Homebrew or ARM-specific issue.
Hi all,
I'm encountering a bug in PostgreSQL 17.5 where a `COPY ... TO STDOUT WITH
(FORMAT binary)` operation produces malformed output: the file begins with
`GPOCYP` instead of the expected `PGCOPY`. This behavior occurs consistently
across environments, including:
- macOS 14.4.1 on Apple Silicon (M1), PostgreSQL 17.5 installed via Homebrew
- Ubuntu 22.04 in a production environment, PostgreSQL 17.5 installed via
apt/postgresql.org
This suggests the issue is not specific to platform, CPU architecture, or
package manager.
---
### Steps to Reproduce
1. Run this SQL script:
```sql
-- mini_test.sql
CREATE TEMP TABLE m1_psql_test (id int);
INSERT INTO m1_psql_test VALUES (1);
COPY (SELECT * FROM m1_psql_test) TO STDOUT WITH (FORMAT binary);
````
2. Execute:
```bash
psql your_db -f mini_test.sql > output.bin
hexdump -C output.bin | head -n 3
```
3. Output (from both macOS M1 and Ubuntu):
```
00000000  47 50 4f 43 59 50 ff 0a  0d 0a 00 00 00 00 00 00
|GPOCYP..........|
```
Expected:
```
00000000  50 47 43 4f 50 59 0a ff  0d 0a 00 00 00 00 00 00
|PGCOPY..........|
```
---
### Notes
* This issue appears in both `psql -c "COPY ..."` and `psql -f` execution
styles.
* The symptom — flipped bytes like `PG` → `GP`, `\n\xff` → `\xff\n` — looks
like a 16-bit byte swap, suggesting a possible endianness bug or
serialization bug.
* The output breaks any downstream tooling expecting a valid binary COPY
stream and results in errors like `COPY file signature not recognized`.
---
### Environment Info
**macOS (M1 Homebrew)**:
* `psql (PostgreSQL) 17.5 (Homebrew)`
* `uname -m`: `arm64`
* macOS 14.4.1 (Sonoma)
**Ubuntu (x86\_64 production)**:
* `psql (PostgreSQL) 17.5 (Debian 17.5-1.pgdg22.04+1)`
* `uname -m`: `x86_64`
* Installed via postgresql.org APT repo
---
### Questions
* Could this indicate a regression in PostgreSQL 17’s binary `COPY TO
STDOUT` handling?
* Has anyone else seen this behavior in 17.5?
* Is there a workaround short of downgrading or switching to CSV?
I’m happy to provide test environments, dumps, or help isolate the patch if
needed.
Thanks,
Robb Shecter
[robb@public.law](mailto:robb@public.law)

Re: BUG #18926: PostgreSQL 17.5 produces malformed binary COPY output (`GPOCYP` instead of `PGCOPY`) across platform

Tom Lane <tgl@sss.pgh.pa.us> — 2025-05-14T12:57:16Z

PG Bug reporting form <noreply@postgresql.org> writes:
> I'm encountering a bug in PostgreSQL 17.5 where a `COPY ... TO STDOUT WITH
> (FORMAT binary)` operation produces malformed output: the file begins with
> `GPOCYP` instead of the expected `PGCOPY`. This behavior occurs consistently
> across environments, including:

Works as expected for me, on macOS and RHEL8:

$ psql -q -f mini_test.sql >output.bin
$ hexdump -C output.bin | head
00000000  50 47 43 4f 50 59 0a ff  0d 0a 00 00 00 00 00 00  |PGCOPY..........|
00000010  00 00 00 00 01 00 00 00  04 00 00 00 01 ff ff     |...............|
0000001f

I think your version of hexdump is regarding the input as 2-byte LSB-first
integers.  (Maybe you replaced it with some "helpful" alias?)

You might try double-checking the file with some other tool, eg od:

$ od -c output.bin | head
0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 001  \0  \0  \0 004  \0  \0  \0 001 377 377
0000037

			regards, tom lane