Today I needed to migrate a ~200 GB PostgreSQL database from one server to another. The source was PostgreSQL 17.10 running in a Docker container, and the destination was PostgreSQL 18.4, also in Docker.
There are several ways to move a PostgreSQL database between servers. I chose the simplest approach:
create a dump with pg_dump and restore it with pg_restore.
I ran: docker exec -it postgres pg_dump -U postgres -Fc mydb > mydb.dump. This produced a 56 GB
dump file. After transferring it to the destination server, I tried to restore it with: pg_restore -U postgres -d postgres -C mydb.dump. Instead of restoring the database, pg_restore crashed with a
segmentation fault.
I investigated several possible causes:
- Corrupted dump. The dump looked fine: SHA-256 checksums matched on both servers, and
xxd mydb.dump | head -3showed the expected PGDMP header. - Broken pg_restore binary. I was using the official Docker image, making this unlikely. I also couldn’t find any reports of a similar bug.
- PostgreSQL version incompatibility. PostgreSQL dumps created with version 17 are normally restorable with version 18. To verify, I also tried restoring with PostgreSQL 17.10 and got the same error.
After some debugging and a discussion with Claude, I discovered that the dump contained \r\n
sequences. The problem was the -it option in docker exec, which had corrupted the binary output.
Allocating a TTY (-t) can:
- Convert LF (
\n) to CRLF (\r\n) on output. - Corrupt binary data passing through it.
- Leave the dump header intact (
PGDMP) while damaging the actual content.
You can detect this issue with: cat -v mydb.dump | head -20 | grep '\^M'.
The fix was simple: use -i instead of -it:
docker exec -i postgres pg_dump -U postgres -Fc mydb > mydb.dump
The new dump restored successfully.
Now I know the difference between docker exec -it and docker exec -i — and why TTY allocation
and binary output don’t mix.