This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.
PostgreSQL is capable of handling files larger than the allowed maximum file size on your system. (Of course, this also depends on your type system and hardware capacity.) When a dumpall is performed on a database, the files storing the data will most likely be larger than the maximum size limit allowed on your system. To solve this problem, you can use one of several different methods available to decrease the file size when pg_dump writes to the standard output.
Some of the methods available are:
Compression programs like gzip can be used. It compresses the output of the dump into a zipped file. The command to use gzip is:
pg_dump dbname | gzip > filename.gz
If you want to restore it, you must first create a database to store the data and then perform the gunzip command:
createdb databasename gunzip-c filename.gz | psql databasename
Or you can use the cat command in place of the previous gunzip command:
cat filename.gz | gunzip | psql databasename
This separates the output into several chunks that does not exceed the system's maximum file size limit. You can specify how large you want to make the chunks using the split command:
pg_dump databasename | split -b 1m - filename
![]() | Split Parameters |
---|---|
You can specify the number of bytes the file should be split into by using -b number . The letter k (kilobyte) or m (megabyte) can be added to the number field to specify the exact number of bytes. Or you can split the output into files with a specific number of lines. Use the -l number . The default number of lines is 1000. |
To restore the dump, use the following:
createdb databasename gzip -d filename.gz | psql databasename
If your system was built with the the zlib compression library, then you can use the custom dump format to compress the data when it writes to the output file.
Use the following command:
pg_dump -Fc databasename > filename
To restore a database, use the pg_restore command. For more information, refer to the reference pages.
 
Continue to: