Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1ZECUY-0003LP-Mh for pgsql-admin@arkaria.postgresql.org; Sun, 12 Jul 2015 08:20:06 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1ZECUX-0007cD-8c for pgsql-admin@arkaria.postgresql.org; Sun, 12 Jul 2015 08:20:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1ZECSv-0005lM-Md for pgsql-admin@postgresql.org; Sun, 12 Jul 2015 08:18:25 +0000 Received: from mx2.exxoss.com ([46.16.240.32]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1ZECSs-0008JF-Jm for pgsql-admin@postgresql.org; Sun, 12 Jul 2015 08:18:24 +0000 Received: from [46.16.244.130] (helo=[10.1.20.222]) by mx2.exxoss.com with esmtpsa (TLS1.0:DHE_RSA_AES_128_CBC_SHA1:16) (Exim 4.72) (envelope-from ) id 1ZECSo-0002Mk-09; Sun, 12 Jul 2015 10:18:20 +0200 To: Keith , pgsql-admin@postgresql.org References: <559FC1A4.10302@exxoss.com> From: Matthieu Lejeune Message-ID: <55A222CC.1050601@exxoss.com> Date: Sun, 12 Jul 2015 10:18:20 +0200 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:38.0) Gecko/20100101 Thunderbird/38.0.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------030605070903010506060103" X-SA-Do-Not-Run: Yes X-SA-Exim-Connect-IP: 46.16.244.130 X-SA-Exim-Rcpt-To: pgsql-admin@postgresql.org, keith@keithf4.com X-SA-Exim-Mail-From: matthieu.lejeune@exxoss.com X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on taupeman X-Spam-Level: X-Spam-Status: No, score=-2.9 required=3.0 tests=ALL_TRUSTED,BAYES_00, HTML_MESSAGE autolearn=ham version=3.3.1 Subject: Re: Automatic restore corruption problem X-SA-Exim-Version: 4.2.1 (built Mon, 22 Mar 2010 06:26:47 +0000) X-SA-Exim-Scanned: Yes (on mx2.exxoss.com) X-Pg-Spam-Score: -3.3 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------030605070903010506060103 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Hi thank for your reply My target is to give a database for buisness testing query and they are modify the database during the buisness day. Now I got this error if I keep the file backup_label : root@p2prddnmdbc:/var/lib/postgresql/9.3/main# /etc/init.d/postgresql start [....] Starting PostgreSQL 9.3 database server: main[....] The PostgreSQL server failed to start. Please check the log output: 2015-07-12 10:12:45 CEST LOG: database system was shut down at 2015-07-12 10:07:10 CEST 2015-07-12 10:12:45 CEST LOG: invalid checkpoint record 2015-07-12 10:12:45 CEST FATAL: could not locate required checkpoint record 2015-07-12 10:12:45 CEST HINT: If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.3/main/backup_label". 2015-07-12 10:12:45 CEST LOG: startup process (PID 28492) exited with exit code 1 2015-07-12 10:12:45 CEST LOG: abo[FAIL startup due to startup process failure ... failed! failed! If I put the recovery.conf the database is waiting for the wal to relaunch the replication. postgres 27817 0.7 0.9 631212 39892 ? S 09:55 0:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main postgres 27818 0.0 0.0 631472 2076 ? Ss 09:55 0:00 \_ postgres: startup process waiting for 0000000100000178000000B9 root@p2prddnmdbc:/var/lib/postgresql/9.3/main# su - postgres postgres@p2prddnmdbc:~$ psql energycomm psql: FATAL: the database system is starting up Have you got an idea to stop the replication process and start the database ? Kind regards Matthieu Le 10/07/15 16:46, Keith a écrit : > A recent, relevant post > > http://tbeitr.blogspot.com/2015/07/deleting-backuplabel-on-restore-will.html > > On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge > > wrote: > > Hi, > > Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune" > > > a écrit : > > > > 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? > > Sure. Don't remove the backup_label file, and add the > recovery.conf file. > > -- > Guillaume > > --------------030605070903010506060103 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Hi thank for your reply

My target is to give a database for buisness testing query and they are modify the database during the buisness day.

Now I got this error if I keep the file backup_label :

root@p2prddnmdbc:/var/lib/postgresql/9.3/main# /etc/init.d/postgresql start
[....] Starting PostgreSQL 9.3 database server: main[....] The PostgreSQL server failed to start. Please check the log output: 2015-07-12 10:12:45 CEST LOG: database system was shut down at 2015-07-12 10:07:10 CEST 2015-07-12 10:12:45 CEST LOG: invalid checkpoint record 2015-07-12 10:12:45 CEST FATAL: could not locate required checkpoint record 2015-07-12 10:12:45 CEST HINT: If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.3/main/backup_label". 2015-07-12 10:12:45 CEST LOG: startup process (PID 28492) exited with exit code 1 2015-07-12 10:12:45 CEST LOG: abo[FAIL startup due to startup process failure ... failed!
 failed!


If I put the recovery.conf the database is waiting for the wal to relaunch the replication.

postgres 27817  0.7  0.9 631212 39892 ?        S    09:55   0:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main
postgres 27818  0.0  0.0 631472  2076 ?        Ss   09:55   0:00  \_ postgres: startup process   waiting for 0000000100000178000000B9
root@p2prddnmdbc:/var/lib/postgresql/9.3/main# su - postgres
postgres@p2prddnmdbc:~$ psql energycomm
psql: FATAL:  the database system is starting up

Have you got an idea to stop the replication process and start the database ?

Kind regards
Matthieu

Le 10/07/15 16:46, Keith a écrit :

On Fri, Jul 10, 2015 at 10:07 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:

Hi,

Le 10 juil. 2015 3:02 PM, "Matthieu Lejeune" <matthieu.lejeune@exxoss.com> a écrit :
>
> 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?

Sure. Don't remove the backup_label file, and add the recovery.conf file.

--
Guillaume




--------------030605070903010506060103--