Message-ID: <559FC8E8.8080809@sqlexec.com>
Date: Fri, 10 Jul 2015 09:30:16 -0400
From: "michael@sqlexec.com" <michael@sqlexec.com>
User-Agent: Postbox 4.0.2 (Windows/20150522)
MIME-Version: 1.0
To: Matthieu Lejeune <matthieu.lejeune@exxoss.com>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Automatic restore corruption problem
References: <559FC1A4.10302@exxoss.com>
In-Reply-To: <559FC1A4.10302@exxoss.com>
Content-Type: multipart/alternative;
 boundary="------------050003070301040204080302"
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

This is a multi-part message in MIME format.
--------------050003070301040204080302
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Perhaps you did not start up the new database in restore mode pointing 
to the WAL logs that were generated during your rsync operation that 
were not captured.

Regards,
Michael

> Matthieu Lejeune <mailto:matthieu.lejeune@exxoss.com>
> Friday, July 10, 2015 8:59 AM
> Hi all,
>
> I have a script for restoring a database every night to an other 
> postgresql database
>
> root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
> #/bin/bash
> /etc/init.d/postgresql stop
> mv /var/log/postgresql/postgresql-9.3-main.log 
> /var/log/postgresql/postgresql-9.3-main.log.old
> cd /var/lib/postgresql/9.3/main
> psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT 
> pg_start_backup('sync');"
> rsync -av --delete root@10.10.11.1:/var/lib/postgresql/9.3/main/* 
> /var/lib/postgresql/9.3/main/
> rm backup_label
> chown -R postgres:postgres *
> psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT 
> pg_stop_backup();"
> /etc/init.d/postgresql start
> chmod 777 /var/log/postgresql/postgresql-9.3-main.log
> psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
> psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
> root@p2prddnmdbc:~#
>
>
> But during the day when the user are using the new database we got 
> error like this :
>
> 2015-06-25 16:20:58 CEST ERROR:  could not read block 257985 in file 
> "base/16386/14064061.1": read only 0 of 8192 bytes
> 2015-06-22 15:21:11 CEST ERROR:  could not read block 256801 in file 
> "base/16386/14064061.1": read only 0 of 8192 bytes
>
> I have check the : filesystem on the vm, on the HW SAN,...
>
> Any idea to fix this problem?
>
> Thanks
>
> Matthieu
>
>


--------------050003070301040204080302
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html><head>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
</head><body bgcolor="#FFFFFF" text="#000000">Perhaps you did not start 
up the new database in restore mode pointing to the WAL logs that were 
generated during your rsync operation that were not captured.<br>
<br>
Regards,<br>
Michael<br>
<span>

</span><br>
<blockquote style="border: 0px none;" 
cite="mid:559FC1A4.10302@exxoss.com" type="cite">
  <div style="margin:30px 25px 10px 25px;" class="__pbConvHr"><div 
style="display:table;width:100%;border-top:1px solid 
#EDEEF0;padding-top:5px">   <div 
style="display:table-cell;white-space:nowrap;vertical-align:middle;width:100%">
   	<a moz-do-not-send="true" href="mailto:matthieu.lejeune@exxoss.com" 
style="color:#737F92 
!important;padding-right:6px;font-weight:bold;text-decoration:none 
!important;">Matthieu Lejeune</a></div>   <div 
style="display:table-cell;white-space:nowrap;vertical-align:middle;">   
  <font color="#9FA2A5"><span style="padding-left:6px">Friday, July 10, 
2015 8:59 AM</span></font></div>    </div></div>
  <div style="color:#888888;margin-left:24px;margin-right:24px;" 
__pbrmquotes="true" class="__pbConvBody">Hi all,
<br>
<br>I have a script for restoring a database every night to an other 
postgresql database
<br>
<br>root@p2prddnmdbc:~# cat /var/admin/script/restoredb.sh
<br>#/bin/bash
<br>/etc/init.d/postgresql stop
<br>mv /var/log/postgresql/postgresql-9.3-main.log 
/var/log/postgresql/postgresql-9.3-main.log.old
<br>cd /var/lib/postgresql/9.3/main
<br>psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT 
pg_start_backup('sync');"
<br>rsync -av --delete <a class="moz-txt-link-abbreviated" href="mailto:root@10.10.11.1:/var/lib/postgresql/9.3/main/*">root@10.10.11.1:/var/lib/postgresql/9.3/main/*</a> 
/var/lib/postgresql/9.3/main/
<br>rm backup_label
<br>chown -R postgres:postgres *
<br>psql --host=p2prddnmdbm --username=replicator postgres -c "SELECT 
pg_stop_backup();"
<br>/etc/init.d/postgresql start
<br>chmod 777 /var/log/postgresql/postgresql-9.3-main.log
<br>psql -U postgres -c "ALTER USER xxxx WITH PASSWORD 'XXXX';"
<br>psql -U postgres xxxx -c "CREATE EXTENSION dblink;"
<br>root@p2prddnmdbc:~#
<br>
<br>
<br>But during the day when the user are using the new database we got 
error 
like this :
<br>
<br>2015-06-25 16:20:58 CEST ERROR:  could not read block 257985 in file
 
"base/16386/14064061.1": read only 0 of 8192 bytes
<br>2015-06-22 15:21:11 CEST ERROR:  could not read block 256801 in file
 
"base/16386/14064061.1": read only 0 of 8192 bytes
<br>
<br>I have check the : filesystem on the vm, on the HW SAN,...
<br>
<br>Any idea to fix this problem?
<br>
<br>Thanks
<br>
<br>Matthieu
<br>
<br>
<br></div>
</blockquote>
<br>
</body></html>

--------------050003070301040204080302--