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