Restore of database failed on 8.3

[keggler]

you could use the -Ft switch in pg_dump to create a .tar compress...


susan

[MrFlibble]

So is the command like this?
F:\postgres\bin>pg_dump -U postgres -Ft "PT3 DB" > G:\tarbkup

I ran that and got this error.
pg_dump: [tar archiver] actual file length (80550) does not match expected (77824)

[keggler]

try the blobs switch as well: -b


susan

[MrFlibble]

Same errror I'm afraid.
F:\postgres\bin>pg_dump -U postgres -b -Ft "PT3 DB" > G:\tarbkup
pg_dump: [tar archiver] actual file length (80550) does not match expected (77824)

[MrFlibble]

It looks like I'm going to have to bite the bullet and dump over the network or buy another HD. Before I do that I want to make sure that the restored database is really broken. I've ran a load of queries on it and it seems ok. Do you think the errors that the restore created (in my initial post) are critical?

[keggler]

hmmmm... yet the restore was giving you "syntax error at or near "PGDMP" or whatever, yet when you run some queries against this restore, the integrity looks ok [ish]???


susan

[MrFlibble]

Yeah, ish. Queries run OK but the tables are smaller than they are on the original database so I guess the new db isn't OK.
I'll start reimporting from HHs to the new db, might be the best option at this stage. Thanks for your help.

[kraada]

I'd recommend using a custom format from pg_dump, though, with -Fc, as the custom format is compressed. It's zlib compression (ie- zip file compression), which should work really well for this sort of data (and be reasonably quick). Also the -Z flag sets the compression level; I think 9 is the strongest compression, so if you went with -Fc -Z 9 you'd probably be able to compress it 95% or more (from 500G -> 25G which is a much more manageable backup size), though obviously since the compression is happening at backup time, the backup process will take longer.

[bugsbunny]

Based on your first post it looks like you had set a tablespace (other than the default) on the 8.2 DB. That was the root cause of your problem. If there was still data in the original tablespace then a new tablespace can't be created there in the new DB (since the directory must a) exist, b) be accessible by postgres c) be empty.

If you create the needed tablespace (use the same name as in 8.2, just point to a new location) I think that the restore would take - although I'm not 100% sure since I haven't tried it. If it doesn't work then there are a number of ways around the problem. The size of the DB leads to some problems here, but not insurmountable.

If you had the room what I would do is (modify commands as need for hosts, DB names, usernames, ports. Check help for exact formats if needed.)
1) dump the users and tablespaces-> pg_dumpall -U postgres --globals-only -h 127.0.0.1 -p 5432
(the above assumes a postgres superuser name, abilty to login without specifying a password. locolhost (127.0.0.1), port 5432)
2) dump just the schemas (you need to run this for each of your DB's if you have more than one):
pg_dump -U postgres --schema-only -h 127.0.0.1 -p 5432 -f <fully qualified output file name> <DBNAME>
3) dump just the data (you need to run this for each of your DB's if you have more than one):
pg_dump -U postgres --data-only -h 127.0.0.1 -p 5432 -f <fully qualified output file name> <DBNAME>

I don't think I missed any required parameters. Check help.

1 and 2 will be very fast and take up minimal space (a couple of kb). You can the open up those dumps and make any adjustments to tablespaces that are needed/wanted. Restore the users/tablespaces. Verify that they exist as needed.
restore the schema (if you changed the tablespace name you'll need to go in here and make a global edit change)

Both of the above are simply sql files so you can even run them via the sql query portion of pgadmin, or through psql etc

Then you take the data only dump and restore that into the empty database.

The best way to handle this is probably still using the method I gave you in a previous post - after you ensure that the needed tablespaces exist in the 8.3 install. Quoting from pg_dumpall:

pg_dumpall requires all needed tablespace directories to exist before
the restore or database creation will fail for databases in non-default
locations.

The previous post:
viewtopic.php?f=17&t=6333&start=10

That eliminates all intermediate files as the output from the dump is piped directly into the input for the new DB creation. It'll also be faster since the dump and restore are happening at the same time.

edit: this may not work in this case though, since it will try and recreate the tablespaces as defined in 8.2, and those directories are currebntly in use. Note that the dumpall quote says the directories must exist - not the tablespaces themselves. Sp the method I outlined above may be still be your best bet. Hmm - might still be able to eliminate the intermediate files though. Don't run the data dump, just the usernames, tablespaces, and schemas. Get all those installed and verify that they're correct. The try running this (same assumptions as in the linked post, and run it from the 8.3 directory):
pg_dump -U postgres --data-only -h 127.0.0.1 -p 5432 <8.2-DBNAME> |psql -U postgres -p 5433 -d <8.3-DBNAME>

That should work - if I didn't make a syntactical error.

[bugsbunny]

Also - just to verify. 8.3.3 is out so you might as well make sure you're using the latest version