Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 2C7259FB899 for ; Thu, 15 Feb 2007 22:55:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 83605-07 for ; Thu, 15 Feb 2007 22:55:41 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from web55414.mail.re4.yahoo.com (web55414.mail.re4.yahoo.com [206.190.58.208]) by postgresql.org (Postfix) with SMTP id C866E9FB841 for ; Thu, 15 Feb 2007 22:55:41 -0400 (AST) Received: (qmail 12391 invoked by uid 60001); 16 Feb 2007 02:55:39 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=GnC0NQtfJQUeKRzTfR1ALuS+XC14vil85PKQic9M05drUZTYi+tXeDQI1RPekaoSWO09ZFmlaXJ3RiJ67pdud+klgfiUeIN8grcjyYDR7qldlUDX40PW034REVW4Unf2v6AwG5Jwtb3t7WeCWeDtbvTJ/NMg917xXxYD0I79vcw=; X-YMail-OSG: otZYLp8VM1nxKwVWFqpclCszHjiymY56URn2nWdlvMhxbHAHrOMAUL5Cx7KfBksiuusrzg0_aUlqfoSLgeMZqmpZ8iO2TyXI5ERhsjuG7aQtORHfOcLHXhTT5qV65.piRsHKbRH32HNW2vMpeETZg4WEtw-- Received: from [200.53.62.220] by web55414.mail.re4.yahoo.com via HTTP; Thu, 15 Feb 2007 18:55:39 PST Date: Thu, 15 Feb 2007 18:55:39 -0800 (PST) From: pedro noticioso Subject: Re: WAL files backup To: pgsql-admin@postgresql.org In-Reply-To: <20070215190304.40190.qmail@web55411.mail.re4.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Message-ID: <945295.11491.qm@web55414.mail.re4.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.469 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/228 X-Sequence-Number: 24693 I created a log of my complete procedure so far, and the error message at the end, pleasse help me find exactly what is wrong, thanks 1) Postgres recien instalado - POSTGRES NEW INSTALL 2) Creando db de Pruebas - - CREATE TESTING DATABASE # createdb Test 3) iniciamos procedimiento de respaldo - START BACKUP PROCEDURE # SELECT pg_start_backup('1'); copiamos wal otro lado - COPY WAL FILES SOMEWHERE ELSE # SELECT pg_stop_backup(); 4) populamos Test - 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 - FIFTY THOUSAND TIMES WITH UNIQUE ID'S 5) # mkdir /home/postgres/backup # mkdir /home/postgres/backup/oficial # cd /home/postgres/backup # tar zcf oficial-sin-drop-17-10-01-05.tgz oficial 6) borramos tabla Test - ERASE 'Test' DB # dropdb Test 7) modificamos postgresql.conf en las siguientes lineas - UNCOMMENTED postgresql.conf THESE LINES fsync = on wal_sync_method = fsync full_page_writes = on wal_buffers = 8 commit_delay = 0 commit_siblings = 5 checkpoint_segments = 3 checkpoint_timeout = 300 checkpoint_warning = 30 archive_command = 'cp -i %p /home/postgres/respaldos/oficial/%f wrote: > for the base backup I tried with the procedure > outlined in > > http://www.postgresql.org/docs/8.1/static/backup-online.html#BACKUP-BASE-BACKUP > and using > > tar zcvf /usr/local/pgsql/data/ > > and to restore deleted WAL files at pg_xlog because > they are older than the WAL files in /backup/wals > dir > mentioned it the archive_command and restore_command > > I: > created a database > added data to the database > Added to postgresql.conf: > archive_command = 'cp -i %p /backup/wals/%f > created recovery.conf > name = 'revocery1' > restore_command = 'cp /backup/wals/%f %p' > recovery_target_timeline = 'latest' > backed up > deleted database files > recovered database from archive > > and this is my log file, any thoughts? thanks! > > > > > > LOG: transaction ID wrap limit is 2147484146, > limited > by database "postgres" > NOTICE: using pg_pltemplate information instead of > CREATE LANGUAGE parameters > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "users_pkey" for table "users" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "customers_pkey" for table > "customers" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "colors_pkey" for table "colors" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "clothestypecategories_pkey" for > table > "clothestypecategories" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "clothestypes_pkey" for table > "clothestypes" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "xmltransactions_pkey" for table > "xmltransactions" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "xmlattributes_pkey" for table > "xmlattributes" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "messages_pkey" for table "messages" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "sucursal_pkey" for table "sucursal" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "issues_pkey" for table "issues" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "usermessages_pkey" for table > "usermessages" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "xmltransmissionlog_pkey" for table > "xmltransmissionlog" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "receivedtransactions_pkey" for table > "receivedtransactions" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "orderitems_pkey" for table > "orderitems" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "returncauses_pkey" for table > "returncauses" > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "damagehistory_pkey" for table > "damagehistory" > LOG: archived transaction log file > "000000010000000000000002" > FATAL: lock file "postmaster.pid" already exists > HINT: Is another postmaster (PID 12187) running in > data directory "/usr/local/pgsql/data"? > LOG: database system was interrupted at 2007-02-15 > 05:41:34 CST > LOG: checkpoint record is at 0/3D545D0 > LOG: redo record is at 0/3D545D0; undo record is at > 0/0; shutdown FALSE > LOG: next transaction ID: 16118; next OID: 24752 > LOG: next MultiXactId: 1; next MultiXactOffset: 0 > LOG: database system was not properly shut down; > automatic recovery in progress > LOG: record with zero length at 0/3D54614 > LOG: redo is not required > LOG: database system is ready > LOG: transaction ID wrap limit is 2147484146, > limited > by database "postgres" > LOG: received fast shutdown request > LOG: shutting down > LOG: database system is shut down > LOG: database system was shut down at 2007-02-15 > 05:58:34 CST > LOG: checkpoint record is at 0/3D54658 > LOG: redo record is at 0/3D54658; undo record is at > 0/0; shutdown TRUE > LOG: next transaction ID: 16120; next OID: 24752 > LOG: next MultiXactId: 1; next MultiXactOffset: 0 > LOG: database system is ready > LOG: transaction ID wrap limit is 2147484146, > limited > by database "postgres" > LOG: archived transaction log file > "000000010000000000000003.00D5469C.backup" > LOG: received fast shutdown request > LOG: shutting down > LOG: database system is shut down > LOG: database system was shut down at 2007-02-15 > 06:44:12 CST > LOG: could not open file > "pg_xlog/000000010000000000000003" (log file 0, > segment 3): No such file or directory > LOG: invalid primary checkpoint record > LOG: could not open file > "pg_xlog/000000010000000000000003" (log file 0, > segment 3): No such file or directory > LOG: invalid secondary checkpoint record > PANIC: could not locate a valid checkpoint record > LOG: startup process (PID 12556) was terminated by > signal 6 > LOG: aborting startup due to startup process > failure > > > > --- "Eduardo J. Ortega" > wrote: > > > hi: > > > > You enable WAL archiving by setting an appropiate > > archive_command in your > > postgresql.conf (probably something copying the > WAL > > files somewhere you store > > them). You MUST restart postgres after changing > this > > file. > > > > About the WAL file size: sorry, i don't know > exactly > > how to do that. I do know > > that it requires that you recompile postgres (or > > that's what i read > > somewhere). There's an alternative, if you switch > to > > version 8.2. I haven't > > actually tried it, but it appears that in 8.2, in > > addition to archiving > > everytime the WAL grows to 16 MB, it also archives > > the WAL records every > > certain amount of time that you can configure on > > postgresql.conf. So you can > > store WAL files, say, every 30 or 60 minutes, > > regardless of how big it is. > > This way maybe the DB speed won't be seriuosly > > affected. > > > > Small question, though. How are you taking your > base > > backup? > > > > Regards, > > > > Eduardo. > > On Wednesday 14 February 2007 18:38, pedro > noticioso > > wrote: > > > Everyone please excuse the mistake in my > previous > > > question, there is a lot in my mind. > > > > > > We are already creating a complete backup every > > day, > > > and would like to have WAL files to restore up > to > > the > > > last minute of course. > > > > > > Acording to > > > > > > > > > http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html#GUC-ARCHI > > >VE-COMMAND > > > > > > === message truncated === ____________________________________________________________________________________ Need Mail bonding? Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users. http://answers.yahoo.com/dir/?link=list&sid=396546091