How do I convert a plain SQL file to a compressed pgdump file?

Question

I have a plain SQL file that is quite big (>70GB) and need to send it over to a server via a slow connection. Is there any way to convert it into a binary pgdump file without restoring the whole DB locally?

It's basically this question reversed: How do I convert a binary pgdump (compressed) to a plain SQL file?

It is not possible, but PostgreSQL uses Zlib to compress data when compression is available, which means that you can achieve similar results compressing your SQL file with gzip, or even better results with stronger formats like 7z. — Vinícius Gobbo A. de Oliveira, Feb 17 '18 at 19:50

score 3 · Answer 1 · answered Feb 18 '18 at 11:23

3

You should compress it for transmission but you don't need to use postgres compression. You might use rsync for this, e.g.:

rsync -avz dump.sql user@server:/path/dump.sql

Will transfer the dump with compression. To reduce the size you might use:

rsync -avz --compress-level=9 dump.sql user@server:/path/dump.sql

You might also compress the dump with a compression program (e.g. gzip, bzip), transfer it, and read it without decompression.

gunzip dump.sql.gz | pg_restore -Upostgres

answered Feb 18 '18 at 11:23

clemens

16,716
11
50
65

Upvote for the way of thinking. Sadly, I have to work on a windows client without administrative rights, so rsync is out of the question. – Mohl Feb 18 '18 at 13:01
@Mohl: I'm sorry to hear that. But you could use an explicit compression without `rsync`. – clemens Feb 18 '18 at 13:55

score 1 · Accepted Answer · answered Apr 16 '18 at 09:29

1

As Vinícius Gobbo A. de Oliveira pointed out in the comments, this is not possible without importing and re-exporting the dump.

answered Apr 16 '18 at 09:29

Mohl

405
2
16

How do I convert a plain SQL file to a compressed pgdump file?

2 Answers2