[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: 9.0 standby - could not open file global/XXXXX
From:       Filip_Rembiałkowski <filip.rembialkowski () gmail ! com>
Date:       2019-02-27 16:00:22
Message-ID: CAP_rwwnZ6LxjEHsq-3v_50_K_NYzYpaM0SLhfjkNA9ZYuZp3xQ () mail ! gmail ! com
[Download RAW message or body]

OK I have it fixed;; just for anyone who's interested - the error was in
the base backup procedure.
When switched to plain "rsync -az" - it  works like a charm.

Most probably, the fault was I assumed that you can use the rsync --update
option when doing base backup.
You cannot, especially when time sync on both servers is not accurate. In
my case, destination server clock was few minutes in future.
So the pg_clog was broken due to this. Which means a completely corrupted
database.

thanks Stephen & Andres for your responses.



On Mon, Feb 25, 2019 at 8:06 PM Filip Rembia=C5=82kowski <
filip.rembialkowski@gmail.com> wrote:

> Hi.
>
> There is a large (>5T) database on PostgreSQL 9.0.23.
>
> I would like to setup new WAL-shipping standby.
> https://www.postgresql.org/docs/9.0/warm-standby.html
>
> On my way I find unexpected issues. Here's the story, in short:
>
> 1. WAL archiving to remote archive is setup & verified
>
> 2. base backup is transferred directly to new server using
> pg_start_backup + rsync + pg_stop_backup.
>
> 3. recovery.conf is created
>
> 4. Server is started and consumes all the remaining WAL segments
> accumulated in the archive - finishing with optimistic message LOG:
> consistent recovery state reached at 9FC1/112BEE10.
>
> 5. When I go to postgres on the standby and try to connect system
> "postgres" database psql: FATAL:  could not open file "global/11819":
> No such file or directory
>
> I guessed the OID refereds to the pg_authid, but other system tables
> might be affected too.
>
> What could be wrong here?
>
> Thanks!
>

[Attachment #3 (text/html)]

<div dir="ltr"><div><br></div><div>OK I have it fixed;; just for anyone who&#39;s \
interested - the error was in the base backup procedure.</div><div><div>When switched \
to plain &quot;rsync -az&quot; - it   works like a \
charm.</div><div><br></div></div><div> Most probably, the fault was I assumed that \
you can use the rsync --update option when doing base backup.</div><div> You cannot, \
especially when time sync on both servers is not accurate. In my case, destination \
server clock was few minutes in future.</div><div>So the pg_clog was broken due to \
this. Which means a completely corrupted database.</div><div><br></div><div>thanks \
Stephen &amp; Andres for your \
responses.<br></div><div><br></div><div><br></div></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Feb 25, 2019 at 8:06 PM \
Filip Rembiałkowski &lt;<a \
href="mailto:filip.rembialkowski@gmail.com">filip.rembialkowski@gmail.com</a>&gt; \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi.<br> <br>
There is a large (&gt;5T) database on PostgreSQL 9.0.23.<br>
<br>
I would like to setup new WAL-shipping standby.<br>
<a href="https://www.postgresql.org/docs/9.0/warm-standby.html" rel="noreferrer" \
target="_blank">https://www.postgresql.org/docs/9.0/warm-standby.html</a><br> <br>
On my way I find unexpected issues. Here&#39;s the story, in short:<br>
<br>
1. WAL archiving to remote archive is setup &amp; verified<br>
<br>
2. base backup is transferred directly to new server using<br>
pg_start_backup + rsync + pg_stop_backup.<br>
<br>
3. recovery.conf is created<br>
<br>
4. Server is started and consumes all the remaining WAL segments<br>
accumulated in the archive - finishing with optimistic message LOG:<br>
consistent recovery state reached at 9FC1/112BEE10.<br>
<br>
5. When I go to postgres on the standby and try to connect system<br>
&quot;postgres&quot; database psql: FATAL:   could not open file \
&quot;global/11819&quot;:<br> No such file or directory<br>
<br>
I guessed the OID refereds to the pg_authid, but other system tables<br>
might be affected too.<br>
<br>
What could be wrong here?<br>
<br>
Thanks!<br>
</blockquote></div>



[prev in list] [next in list] [prev in thread] [next in thread]