Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id AAB419FB56A for ; Sun, 18 Feb 2007 09:36:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 28436-05 for ; Sun, 18 Feb 2007 09:36:48 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web55403.mail.re4.yahoo.com (web55403.mail.re4.yahoo.com [206.190.58.197]) by postgresql.org (Postfix) with SMTP id EF71F9FB442 for ; Sun, 18 Feb 2007 09:36:53 -0400 (AST) Received: (qmail 51851 invoked by uid 60001); 18 Feb 2007 13:36:52 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=dZOU18nMhWzHLCBwZJ3TNEZrDHLYXLHaD8zf8sgjUtvqgPNMqM0RGWPD6gsR9OloVW2BjjAdoD8nLP6h1SfUeGpxPIRvvXmlGmo+VhIl62KpqLd4eo2qWz4haFJb5kvfMaNqImPOWiKTgs+RmTCAv08ZeVkQVDpzB0DtLhcWpeU=; X-YMail-OSG: U5MilZQVM1mn7wf6cJq4.pANro2T7EVJuYaquNnogfBrGkZ45SEL4vnhvzPceJf8u18pXi0hHlFzs5mVPL4QbiHMMNCoVOzvIbEjWJeTZUsk9EALYd2TU3wSJmHV2ve41Xl__liabeOTe6Y- Received: from [189.169.9.174] by web55403.mail.re4.yahoo.com via HTTP; Sun, 18 Feb 2007 05:36:52 PST Date: Sun, 18 Feb 2007 05:36:52 -0800 (PST) From: pedro noticioso Subject: Re: WAL files backup To: Alvaro Herrera Cc: pgsql-admin@postgresql.org In-Reply-To: <20070217003331.GE9724@alvh.no-ip.org> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <525241.48113.qm@web55403.mail.re4.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.356 tagged_above=0 required=5 tests=BAYES_50, DNS_FROM_RFC_ABUSE, DNS_FROM_RFC_POST, DNS_FROM_RFC_WHOIS X-Spam-Level: *** X-Archive-Number: 200702/262 X-Sequence-Number: 24727 Thanks to all who have repplied so far Ok so I go back and retry the complete procedure to try to pin point what might be the problem. 1) POSTGRES NEW INSTALL 2) CREATE TESTING DATABASE # createdb Test 3) INSERT A LOT OF DATA INTO 'Test' # insert into users values (1 ,'name','800122','2 someones name',3,1); # insert into users values (2 ,'name','800122','2 someones name',3,1); ...cincuenta mil veces con su id unico - FIVE THOUSAND TIMES WITH UNIQUE ID'S 4) COPY WAL AND DATA FILES SOMEWHERE ELSE # psql Test # SELECT pg_start_backup('1'); # \q # cd /home/postgres/backup # tar zcf 070215-data-01.tgz /usr/local/pgsql/data # tar zcf 070215-oficial-01.tgz oficial # psql Test # SELECT pg_stop_backup(); # \q 5) INSERT A LOT OF DATA INTO 'Test' # insert into users values (5001 ,'name','800122','2 someones name',3,1); # insert into users values (5002 ,'name','800122','2 someones name',3,1); ...cincuenta mil veces con su id unico - FIVE THOUSAND TIMES WITH UNIQUE ID'S 6) COPY WAL AND DATA FILES SOMEWHERE ELSE # psql Test # SELECT pg_start_backup('2'); # \q # cd /home/postgres/backup # tar zcf 070215-data-02.tgz /usr/local/pgsql/data # tar zcf 070215-oficial-02.tgz oficial # psql Test # SELECT pg_stop_backup(); # \q 7) RECOVER DATA BACKUP 1 AND WAL FILES 2 # cd /home/postgres/backup # rm -rf /usr/local/pgsql/data/pg_xlog/* # rm -rf oficial # psql Test # SELECT pg_start_backup('2'); # \q # cd /home/postgres/backup # tar zxf 070215-data-01.tgz # tar zxf 070215-oficial-02.tgz # psql Test # SELECT pg_stop_backup(); # \q # ls -la oficial drwxr-sr-x 2 postgres staff 4096 2007-02-16 10:51 . drwxr-sr-x 4 postgres staff 4096 2007-02-16 10:47 .. -rw------- 1 postgres staff 234 2007-02-16 10:12 000000010000000000000003 -rw------- 1 postgres staff 234 2007-02-16 10:12 000000010000000000000004 -rw------- 1 postgres staff 234 2007-02-16 10:12 000000010000000000000005.0024FD70.backup # rm -rf 000000010000000000000003 # rm -rf 000000010000000000000004 8) CREATE recovery.conf name = '5' restore_command = 'cp /home/postgres/respaldos/oficial/%f %p' recovery_target_timeline = 'latest' SO I START PG AND GET THESE ERRORS LOG: archived transaction log file "000000010000000000000003" LOG: archived transaction log file "000000010000000000000004" ERROR: relation "foo" does not exist ERROR: syntax error at or near "4" at character 1 ERROR: duplicate key violates unique constraint "users_pkey" ERROR: duplicate key violates unique constraint "users_pkey" ERROR: duplicate key violates unique constraint "users_pkey" ERROR: duplicate key violates unique constraint "users_pkey" ERROR: duplicate key violates unique constraint "users_pkey" ERROR: syntax error at or near "pwd" at character 1 FATAL: role "root" does not exist LOG: database system was interrupted at 2007-02-16 10:10:27 CST LOG: could not open file "pg_xlog/000000010000000000000005" (log file 0, segment 5): No such file or directory LOG: invalid checkpoint record PANIC: could not locate required checkpoint record HINT: If you are not restoring from a backup, try removing the file "/usr/local/pgsql/data/backup_label". LOG: startup process (PID 14858) was terminated by signal 6 LOG: aborting startup due to startup process failure LOG: database system was interrupted at 2007-02-16 10:10:27 CST LOG: could not read from log file 0, segment 5 at offset 2416640: No such file or directory LOG: invalid checkpoint record ***************** NOTE: ok so I get it, there is a missing file so here it goes # cp oficial/000000010000000000000005.0024FD70.backup /usr/local/pgsql/data/pg_xlog/000000010000000000000005 NOTE: placement in the event in the log is not quite aqccurate but you get the idea that there is no complain about the WAL file missing from pg_xlog any more ***************** PANIC: could not locate required checkpoint record HINT: If you are not restoring from a backup, try removing the file "/usr/local/pgsql/data/backup_label". LOG: startup process (PID 14872) was terminated by signal 6 LOG: aborting startup due to startup process failure LOG: database system was interrupted at 2007-02-16 10:10:27 CST LOG: could not read from log file 0, segment 5 at offset 2416640: No such file or directory LOG: invalid checkpoint record PANIC: could not locate required checkpoint record HINT: If you are not restoring from a backup, try removing the file "/usr/local/pgsql/data/backup_label". LOG: startup process (PID 14881) was terminated by signal 6 LOG: aborting startup due to startup process failure 1 --- Alvaro Herrera wrote: > pedro noticioso wrote: > > Muchas gracia a todos los queme han respondido > hasta > > ahora > > Thanks to all who have repplied so far > > Pedro, I don't think you're doing anyone (nor > yourself) any favors by > writing both in spanish and english in this list. > This list is english > only. If you want to write in spanish, please use > the pgsql-es-ayuda > list and I'll be happy to help you there. If you > want to stick to this > list, please use english only. I find it hard to > follow what you're > doing in the format you're using. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom > Development, 24x7 support > ____________________________________________________________________________________ The fish are biting. Get more visitors on your site using Yahoo! Search Marketing. http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php