Re: Cluster seems broken after pg_basebackup

2015-02-09 16:10 GMT-05:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 2/9/15 11:51 AM, Guillaume Lelarge wrote:
>
>> According to this page
>> <https://msdn.microsoft.com/en-ca/library/cc704588.aspx>, exception
>> 0xC0000005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx
>> referenced memory at 0x%08lx. The memory could not be %s. This is not of
>> much help to me.
>>
>
> In my experience that means that your data is corrupted.
>

That wouldn't be too dramatic since I can start over with another base
backup. It just takes several hours... :(

What I want to achieve in the end is to transfer my production database
from one machine to another. So here's how I did before things started to
go bad:

1. On the source machine, I took a base backup using pg_basebackup
2. I installed PGSQL on the destination machine
3. I copied the backup and extracted it on the destination machine
4. I created a PGDATA environment variable pointing to 'data' from the
extracted backup. In this backup, most of my data was in a created
tablespace so I updated the junction link (in data­\pg_tblspc) to reflect
the location of the tablespace on the new machine (It seems like when using
pg_basebackup, junction links are not saved as junction links but as empty
directories).
5. I verified I could connect to my database using psql or pgadmin. It
worked.
6. I wanted to move my tablespace to pg_default so I ran ALTER DATABASE
mydb SET TABLESPACE pg_default;
7. Ran pg_basebackup on my cluster
8. Tried to connect to database, no success...

It it the right way to proceed for this kind of operation?

Thanks!

> I hope these additional bits of information can help someone figuring
>> out a solution to get my cluster up and running again.
>>
>> PS. I was thinking of reinstalling PGSQL over my current install but
>> keeping my PGDATA. I've done it in the past for fixing problems with
>> starting the service and it worked. What do you think?
>>
>
> You could try it, but as Guillaume Drolet mentioned I don't see this
> helping.
>
> Since this is happening on your original database, I suspect that's what's
> been corrupted. In my experience, this means you either have faulty
> hardware, or there's a misconfiguration that means fsync isn't doing what
> it's supposed to do.
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>