Forum OpenACS Q&A: pg_dump max file limits?

Posted by Vince T on
hi, i have a fairly large openacs install and i back the database up regularly by doing the doc's "pg_dump -f" method.

it seems that i have reached some sort of limit in dump file size at 2147483647 bytes. all my nightly dumps stay at this level until i manually shrink the database, afterwhich it starts to grow again, and top out once again.

was wondering if there is a setting that needs to be done for me to dump a database larger than this. or if there is a better method to backup a database.

thanks in advance.


Posted by Ryan Gallimore on
Hi Vincent,

This issue is not specific to postgresql. Linux filesystems have a 2 GB file size limit.



Posted by Steve Manning on
You'll probably have to start using compression or split the output file using a pipe and the split command:

pg_dump yourdb | split -b 1024m - yourdb_

which will create 1Gb files called yourdb_aa, yourdb_ab, yourdb_ac etc.

Then you can import them using cat:

cat yourdb_* | psql yourdb

- Steve

Posted by Steve Manning on
It doesnt have to be a filesystem limit. I just checked one our our large nightly dumpd and its 2.9Gb. This particular machine is a 2.4 kernel with ext3 running pg 7.3.4.

I think its something to do with Large File Support in the kernel and whether the LFS is utilised by the program. We have another machine with the same kernel etc. and it hit the 2gig limit on the log file it was writing. Perhaps someone who knows about these things could explain why this would be?

- Steve

Posted by Roel Canicula on
You can also compress the dump file by using "pg_dump -Fc ...".