pg_dump large-file support > 16GB

I found on http://www.madeasy.de/7/ext2.htm (in german)
ext2 can't have bigger files than 16GB if blocksize is 1k.
Ext3 is ext2 with journaling.

Rafael Martinez Guerrero wrote:

On Thu, 17 Mar 2005 14:05:35 +0100, Rafael Martinez Guerrero
<r.m.guerrero@usit.uio.no> wrote:

Hello

We are having problems with pg_dump.

We are trying to dump a 30GB+ database using pg_dump with the --file
option. In the beginning everything works fine, pg_dump runs and we get
a dumpfile. But when this file becomes 16GB it disappears from the
filesystem, pg_dump continues working without giving an error until it
finnish (even when the file does not exist)(The filesystem has free
space).

I can generate without problems files bigger than 16GB with other
programs.

Some information:
---------------------------
OS: Red Hat Enterprise Linux WS release 3 (Taroon Update 4)
Kernel: 2.4.21-27.0.2.ELsmp #1 SMP i686
PG: 7.4.7

LVM version 1.0.8-2(26/05/2004)
EXT3 FS 2.4-0.9.19, 19 August 2002 on lvm(58,6), internal journal
EXT3-fs: mounted filesystem with ordered data mode.
----------------------------

Any ideas? It looks like pg_dump has a limit of 16GB? How can we solve
this?

Have you tried piping the dump through split so that each file's size
is limited?

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
L. Friedman netllama@gmail.com
LlamaLand http://netllama.linux-sxs.org

On Thu, 2005-03-17 at 15:05, Michael Kleiser wrote:

I found on http://www.madeasy.de/7/ext2.htm (in german)
ext2 can't have bigger files than 16GB if blocksize is 1k.
Ext3 is ext2 with journaling.

[............]

We use 4k. And as I said, we can generate files bigger than 16GB with
other programs in the same filesystem.

From tune2fs:
---------------------------------------------------------------
tune2fs 1.32 (09-Nov-2002)
Filesystem volume name: <none>
Last mounted on: <not available>
Filesystem UUID: 80ecbce4-dcef-4668-ae84-887de850ed57
Filesystem magic number: 0xEF53
Filesystem revision #: 1 (dynamic)
Filesystem features: has_journal filetype needs_recovery
sparse_super large_file
Default mount options: (none)
Filesystem state: clean
Errors behavior: Continue
Filesystem OS type: Linux
Inode count: 5128192
Block count: 10240000
Reserved block count: 102400
Free blocks: 10070728
Free inodes: 5128097
First block: 0
Block size: 4096
Fragment size: 4096
Blocks per group: 32768
Fragments per group: 32768
Inodes per group: 16384
Inode blocks per group: 512
---------------------------------------------------------------

Regards.
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

On Thu, 2005-03-17 at 15:09, Lonni J Friedman wrote:

On Thu, 17 Mar 2005 14:05:35 +0100, Rafael Martinez Guerrero
<r.m.guerrero@usit.uio.no> wrote:

Hello

We are having problems with pg_dump.

We are trying to dump a 30GB+ database using pg_dump with the --file
option. In the beginning everything works fine, pg_dump runs and we get
a dumpfile. But when this file becomes 16GB it disappears from the
filesystem, pg_dump continues working without giving an error until it
finnish (even when the file does not exist)(The filesystem has free
space).

I can generate without problems files bigger than 16GB with other
programs.

Some information:
---------------------------
OS: Red Hat Enterprise Linux WS release 3 (Taroon Update 4)
Kernel: 2.4.21-27.0.2.ELsmp #1 SMP i686
PG: 7.4.7

LVM version 1.0.8-2(26/05/2004)
EXT3 FS 2.4-0.9.19, 19 August 2002 on lvm(58,6), internal journal
EXT3-fs: mounted filesystem with ordered data mode.
----------------------------

Any ideas? It looks like pg_dump has a limit of 16GB? How can we solve
this?

Have you tried piping the dump through split so that each file's size
is limited?

It is a possibility. But when you have several hundred databases, the
amount of backup files you have to take care of, grow in a exponencial
form ;).

We already have several well tested backup/maintenace/restore scripts
that we would like to continue using.

My question is why is this limit (16GB) there, when my OS does not have
that limit? Is it possible to take it away in a easy way? It looks like
pg_dump is compiled with large-file support because it can work with
files bigger than 4GB.

More ideas? :)

Regards.
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

On Thu, 17 Mar 2005, Rafael Martinez Guerrero wrote:

My question is why is this limit (16GB) there, when my OS does not have
that limit? Is it possible to take it away in a easy way? It looks like
pg_dump is compiled with large-file support because it can work with
files bigger than 4GB.

More ideas? :)

Things to try:

a) shell redirection:
$ pg_dump ... > outfile

b) some pipes:
$ pg_dump ... | cat > outfile
$ pg_dump ... | dd of=outfile

a) may fail if there's something with pg_dump and large files.
b) is different in that it's the right side of the pipe that outputs
to the filesystem.

.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it

Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes:

We are trying to dump a 30GB+ database using pg_dump with the --file
option. In the beginning everything works fine, pg_dump runs and we get
a dumpfile. But when this file becomes 16GB it disappears from the
filesystem, pg_dump continues working without giving an error until it
finnish (even when the file does not exist)(The filesystem has free
space).

Is that a plain text, tar, or custom dump (-Ft or -Fc)? Is the behavior
different if you just write to stdout instead of using --file?

regards, tom lane

Would it help to use a different filesystem like SGI's XFS ? Would it be
possible to even implement that at you site at this stage ?

Tom Lane wrote:

Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes:

We are trying to dump a 30GB+ database using pg_dump with the --file
option. In the beginning everything works fine, pg_dump runs and we get
a dumpfile. But when this file becomes 16GB it disappears from the
filesystem, pg_dump continues working without giving an error until it
finnish (even when the file does not exist)(The filesystem has free
space).

Is that a plain text, tar, or custom dump (-Ft or -Fc)? Is the behavior
different if you just write to stdout instead of using --file?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--
Aly Dharshi
aly.dharshi@telus.net

"A good speech is like a good dress
that's short enough to be interesting
and long enough to cover the subject"

On Thu, 2005-03-17 at 10:17 -0500, Tom Lane wrote:

Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes:

We are trying to dump a 30GB+ database using pg_dump with the --file
option. In the beginning everything works fine, pg_dump runs and we get
a dumpfile. But when this file becomes 16GB it disappears from the
filesystem, pg_dump continues working without giving an error until it
finnish (even when the file does not exist)(The filesystem has free
space).

Is that a plain text, tar, or custom dump (-Ft or -Fc)? Is the behavior
different if you just write to stdout instead of using --file?

regards, tom lane

- In this example, it is a plain text (--format=p).
- If I write to stdout and redirect to a file, the dump finnish without
problems and I get a dump-text-file over 16GB without problems.

--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

On Thu, 2005-03-17 at 10:41 -0700, Aly Dharshi wrote:

Hello

Would it help to use a different filesystem like SGI's XFS ?

I do not see the connection between this problem and using another
filesystem. I think we would have this problem with all the programs in
the system if we had a problem with the filesystem we are using.

Would it be
possible to even implement that at you site at this stage ?

We can not do this if we want support from our "operative system
departament", they do not support XFS at the present.

--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

Rafael Martinez <r.m.guerrero@usit.uio.no> writes:

On Thu, 2005-03-17 at 10:17 -0500, Tom Lane wrote:

Is that a plain text, tar, or custom dump (-Ft or -Fc)? Is the behavior
different if you just write to stdout instead of using --file?

- In this example, it is a plain text (--format=3Dp).
- If I write to stdout and redirect to a file, the dump finnish without
problems and I get a dump-text-file over 16GB without problems.

In that case, you have a glibc or filesystem bug and you should be
reporting it to Red Hat. The *only* difference between writing to
stdout and writing to a --file option is that in one case we use
the preopened "stdout" FILE* and in the other case we do
fopen(filename, "w"). Your report therefore is stating that there
is something broken about fopen'd files.

regards, tom lane

On Fri, 2005-03-18 at 09:58 -0500, Tom Lane wrote:

Rafael Martinez <r.m.guerrero@usit.uio.no> writes:

On Thu, 2005-03-17 at 10:17 -0500, Tom Lane wrote:

Is that a plain text, tar, or custom dump (-Ft or -Fc)? Is the behavior
different if you just write to stdout instead of using --file?

- In this example, it is a plain text (--format=3Dp).
- If I write to stdout and redirect to a file, the dump finnish without
problems and I get a dump-text-file over 16GB without problems.

In that case, you have a glibc or filesystem bug and you should be
reporting it to Red Hat. The *only* difference between writing to
stdout and writing to a --file option is that in one case we use
the preopened "stdout" FILE* and in the other case we do
fopen(filename, "w"). Your report therefore is stating that there
is something broken about fopen'd files.

Thanks for the information. I will contact RH.

--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes:

We are trying to dump a 30GB+ database using pg_dump with the --file
option. In the beginning everything works fine, pg_dump runs and we get
a dumpfile. But when this file becomes 16GB it disappears from the
filesystem,

FWIW, I tried and failed to duplicate this problem on a Fedora Core 3
machine using an ext3 filesystem. I set up a dummy database that would
produce an approximately 18GB text dump and did
pg_dump big --file spare/big.dump
Seemed to work fine.

regards, tom lane

On Fri, 2005-03-18 at 15:58, Tom Lane wrote:

Rafael Martinez <r.m.guerrero@usit.uio.no> writes:

On Thu, 2005-03-17 at 10:17 -0500, Tom Lane wrote:

Is that a plain text, tar, or custom dump (-Ft or -Fc)? Is the behavior
different if you just write to stdout instead of using --file?

- In this example, it is a plain text (--format=3Dp).
- If I write to stdout and redirect to a file, the dump finnish without
problems and I get a dump-text-file over 16GB without problems.

In that case, you have a glibc or filesystem bug and you should be
reporting it to Red Hat. The *only* difference between writing to
stdout and writing to a --file option is that in one case we use
the preopened "stdout" FILE* and in the other case we do
fopen(filename, "w"). Your report therefore is stating that there
is something broken about fopen'd files.

Hello again

I have been testing a little more before I open a bug report at RH. I
have a simple test program to test 'fopen' in the samme filesystem I am
having problems. I can not reproduce the problem and the files I produce
with this program can get bigger than 16GB without problems.

Do you use any spesial option when you compile pg_dump, or in the
program that could influence how the program behaves and can help me to
reproduce the problem?

PS.- Be careful with this program ..... it won't stop and will consume
all the free space in your filesystem ;)

----------------------------------------------------------------
-bash-2.05b$ cat test_fopen.c

#include <stdio.h>
#include <unistd.h>

int main(int argc, char **argv){

FILE *fp;
char *filename = argv[1];

char output[1024];
int counter = 0;

if ((fp = fopen(filename,"w")) == NULL){
printf("fopen error\n");
}

while (1){

sprintf(output,"*** Testing the fopen function in a RHEL server -
Counter: %d ***\n",counter);

if (fputs(output,fp) == EOF){
printf("fputs error\n");
}

counter++;
}

fclose(fp);
return 0;
}

-bash-2.05b$ gcc -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 test_fopen.c -o
test_fopen
------------------------------------------------------------------

Thanks :)
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes:

Do you use any spesial option when you compile pg_dump, or in the
program that could influence how the program behaves and can help me to
reproduce the problem?

In a Linux system we'll add "-D_GNU_SOURCE" to the compile command line.
Also, pg_config.h sets some #define's that might affect things,
particularly "#define _FILE_OFFSET_BITS 64". I see you did both of
those in your test, but you might want to review pg_config.h to see if
anything else looks promising.

Another line of thought is that there is something broken about the
particular build of Postgres that you are using (eg it was affected by a
compiler bug). You might try building from source, or grabbing the src
RPM and rebuilding from that, and confirming the bug is still there ---
and if so, back off the CFLAGS to minimal optimization and see if it
changes.

regards, tom lane