Database backup gives errors

[MrFlibble]

I ran a backup of my PT3 database using the pgAdmin tool and it gave the following error for each of the tables. Is it anything to worry about does anyone know?
pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used
pg_dump: dumping contents of table lookup_tourney_table_type

I'm using postgres 8.2.

[MrFlibble]

Bump. Has anyone backed up a large database without getting errors?

[keggler]

i am pretty sure this is fixed in 8.3 - you might want to upgrade...

if you want to stay with 8.2, you could always try a brute force restore to another postgres db to verify the integrity of the tables and schemas...

susan

[DerekG]

I have bad news for you bud. I ran into a similar situation a long time ago and it basically means that, while your db may be fine, you're unable to make a backup after it gets over 2GB in size. Apparently there is some problem with PSQL (not PT3). This was supposed to be addressed a long, long time ago and I thought it was fixed as far back as 8.2.4 but from the sound of your complaint it has returned. A user by the name of BugsBunny helped me research what was happening, but I couldn't figure out how to get hold of someone at PostgreSQL to hammer on about this bug.

What 8.2.x version are you running?

More info in this thread here:
archives/PT2/viewtopic.php?t=18675

[keggler]

Derek,

you talking about this:

http://www.mail-archive.com/pgsql-hacke ... 85241.html ????


susan

[bugsbunny]

Yes that's the thread. An upgrade should solve his problem. If he doesn't want to dump and restore (he can still dump to plain text) then a point upgrade to 8.2.8 should solve the problem
http://www.postgresql.org/ftp/binary/v8.2.8/win32/
Get postgresql-8.2.8-1.zip

Just unzip the entire file then run the upgrade.bat file (this is only to upgrade from an earlier 8.2.x release). To upgrade from 8.1x (or earlier) a dump and restore is needed (as it is to go from 8.2.x to 8.3.x)

[MrFlibble]

I'm on 8.2.1.

So the story is that I can backup fine if I don't compress.
I am able to upgrade to from 8.2.1 to 8.2.8 by just running the installer at postgresql-8.2.8-1.zip, but can't go straight from 8.2.1 to 8.3.x as a dump is required.

So the best way to go about this is to
Do an uncompressed dump.
Upgrade to 8.2.8.
Try a compressed backup.
Then optionally install 8.3.2.

Is that about right?

[keggler]

i think Mr. bunny has it about right:

The internal data storage format typically changes in every major release of PostgreSQL. Therefore, if you are upgrading across releases, you must back up and restore your data. If you are upgrading within releases, the new version can use your current data files so you can skip the backup and restore steps - but, you've always got a double backup with HH's in case of DB integrity compromise anyway, haven't you???? :o


Susan

[MrFlibble]

Sigh of relief as the upgrade to 8.2.8 goes smoothly without a backup.

Slightly off topic, but the next step in the backup saga;
there are options for Blob, With OIDs, Insert Commands and Disable $ quoting in the pg admin backup tool window. Only Blob is selected by default. Do it it make sense to use any of the other three options?

[bugsbunny]

Sigh of relief as the upgrade to 8.2.8 goes smoothly without a backup.

Slightly off topic, but the next step in the backup saga;
there are options for Blob, With OIDs, Insert Commands and Disable $ quoting in the pg admin backup tool window. Only Blob is selected by default. Do it it make sense to use any of the other three options?

Nah - default is fine. You actually don't need Blobs, but it won't hurt either. OIDS is a bad idea, they're deprecated anyways. Insert commands will make restores MUCH slower, you don't want them. Disable $ quoting is a standards compliance thing. You don't have to worry about it.