Re: Problem Backing Up a DB

Don't forget to cc: the mailing list.

Peter Neu wrote:
> Hello,
> 
> thanks for the fast reply. This works. I don't really need the dumpall
> because I usually just back up one db. 
> 
> I noticed the dump is now a binary file. Means that I will have to migrate 
> to 7.4.17 if this is the preferable release because 7.4.2 can't read the
> binary file I suppose. 

Backup files in general don't work between versions. This is why you 
were having problems. However, pg_dump knows about older versions of the 
database and can read from them. If you want to transfer data from 7.4 
to 8.1, use the pg_dump/pg_restore from 8.1. If you want to go backwards 
(8.1 to 7.4) you can't (in general - you can usually tweak an SQL file 
to get you there).

The binary file can be used to generate your SQL with INSERTs or COPY 
commands as desired, or even do selective restores (--list/--use-list) 
direct to a database.

> The windows box was just for testing purposes. The productive database will
> run again on a linux machine if the current one fails. 
> 
> So, is this problem I encountered just happening because I switched from 7.x
> to the 8.x release or is there a general problem with this particular
> PostgreSQL release?

A dump for one 7.4 database should transfer to another 7.4 database just 
fine.

> The standby linux machine would also run 7.4.2 because it ships with SuSE ES
> 9. 

Presumably ES9 includes bugfixes, so it must include a version later 
than 7.4.2 (or perhaps have its own numbering system for updates e.g. 
7.4.2-11).

If not, I'd suggest:
1. Cancelling your subscription for SuSE support and spending the money 
on a crate of good single-malt whisky(*) instead. The whisky will 
provide the same level of reliability for your database and is also more 
sociable.
2. Upgrade to 7.4.17 - that way you'll have the 15 different sets of bug 
fixes that the developers have provided. If you want to know what they 
all are, go here:
http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4-17
Some of them are important to have, so you *do* want them.

(*) Other beverages are available. If you do not partake of alcohol, 
consider a small artwork instead.

HTH
-- 
   Richard Huxton
   Archonet Ltd