Re: Need Help Recovering from Botched Upgrade Attempt

Rich Shepard wrote:

> According to the cp man page here, 'cp -a' is equivalent to 'cp -dpR'.

You're quite right. I was thinking "aah, a BSD-ism" but no, it's true
for Linux too. Sorry.

>> select pg_start_backup('migrate');
>>
>> or similar before starting the copy then you're going to have problems
>> using that data. You can copy a working postgresql instance's data
>> directories, but only if you've enabled WAL logging and you tell Pg about
>> it so it can write appropriate markers for recovery.
>
> This is interesting. I've not read about this before. As I'm the only one
> using the databases (primarily for the accounting data) I know that nothing
> was changed during the copy operation.

However, that may not be sufficient. Just because you didn't perform any
DB operations does *not* mean that Pg didn't write anything. Autovacuum,
for example, could be working away. With 8.1 that may not be such a
concern, but it's not the only thing that might be touching the DB files.

You need to stop the DB or use WAL archiving and pg_start_backup if you
want to be reasonably safe.

> Every file from /var/lib/pgsql/ before I started this is on the weekly
> backup tape from last Friday night. If need be I can restore from that and
> start over.

Well, no worries then. I'm sure you can understand that for many people
- way TOO many people - that is not the case, so it's well worth
stressing the point.

--
Craig Ringer