I have a 15 GB compressed PostgreSQL dump (as a pgbackup), that when extracted is closer to 60 GB. I have a cloud server with only 69 GB of free space (54 GB after the pgbackup is copied onto the box), and I was wondering if there was a cunning way that I could split the pgbackup into lots of smaller compressed pgbbackup files, delete the original large pgbackup and then iterate over the smaller pgbackups, decompressing, executing them and then deleting them, so that I can end up with the entire dataset in a PostgreSQL database given the disk constraints.

  • Would it be possible to run the restore over the network instead, from a server that does have enough space? – Jenny D Mar 11 '13 at 13:14

Here is a bunch of possible workarounds. Chose one meeting your requirements.

via stdin

The answer is quite simple - use a pipe:

xzcat dumpFile.xz | pg_restore ....

This way you don't need additional disk space for extracting the compressed dump file.

See man pg_restore:

-f filename, --file=filename

Specify output file for generated script, or for the listing when used with -l.

Default is the standard output.

via stdin and ssh

If you run to low of disk space to copy the compressed dump file to the server use ssh for invocation:

xzcat dumpFile.xz |ssh -T user@dbserver pg_restore ... 

pg_restore via network

Also an alternative is to reconfigure your postgreSQL database to allow the connection from your local workstation. In this case you may use pg_restore on your local system with setting PGHOST, PGPORT, PGUSER to the correct values.

splitting the dump

  1. Download the dump to a machine with enough disk space.
  2. Using the split command (or any similar tool) to cut it down to smaller pieces.
  3. Remove the dump from the database server.
  4. On the database server create a job like waiting for receiving partial dump files, e.g.

    while sleep 10
      if [ -r dumpPart ] 
        cat  dumpPart 
        echo "next part is loaded" >&2
        rm -f dumpPart
    done | pg_restore ....
  5. Upload step for step your dump parts with the destination name dumpPart. Ensure that the speed is reasonable lower as the sleep statement above.

  • That'd work if I had 75 GB or more space (enough for the underlying pgbackup of 15 GB and the uncompressed 60 GB version). Sadly I only have 69 GB. – Noxville Mar 11 '13 at 13:10
  • @Noxville see update with ssh usage – H.-Dirk Schmitt Mar 11 '13 at 13:18
  • I don't really see the point in downloading the entire dump onto another machine to restore it. Technically the information should be uncompressable in a piece-wise manner (not just one big bang shot). I also don't have access to another machine where I could ssh-dump from. – Noxville Mar 11 '13 at 13:32
  • @Noxville See update with split hack. – H.-Dirk Schmitt Mar 11 '13 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.