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 </dev/null' 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" <ejortegau@xxxxxxxxxxxx> 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 > > > http://www.postgresql.org/docs/8.1/static/backup-online.html#BACKUP-ARCHIVI > >NG-WAL > > > > I tried a couple of them and got an error messages > > stating that WAL archiving was not activated, so, > how > > can I activate it? thanks > > > > these docs explain a lot but are not practical > like > > the ones in howtoforge.com for example ;) > > > > In regards to my previous question, I did not > check > > what I wrote, what I meant is that the > 16*1024*1024 > > WAL file size is too big for us because we handle > just > > a few KB per day of information, so are interested > in > > a smaller WAL file size, perhaps 1024*1024 or even > > less so that we can backup the files in less media > > space, but in the postgresql@xxxxxxxxxxxx IRC > channel > > I was told that I would make the database work > slower > > if I make a WAL file too small, what is your take > on > > the matter? recomendation? etc? > > > > thanks a lot! > > > > > > > > > > --- "Eduardo J. Ortega" <ejortegau@xxxxxxxxxxxx> > > > > wrote: > > > For what i understand, the WAL files only record > > > changes to the database, so > > > each WAL segment is absolutely different from > the > > > previous one. As a matter > > > of fact, each WAL file is the incremental backup > you > > > want to make (provided > > > you have already taken a base or level 0 > backup). > > > > > > On Monday 12 February 2007 13:42, pedro > noticioso > > > > > > wrote: > > > > hi there > > > > > > > > I lightly read this > > > > > http://www.postgresql.org/docs/8.1/static/backup-online.html > > > > > > and am interested in creating an incremental > > > > > > backup of > > > > > > > WAL files, but my database is small so each of > > > > > > this > > > > > > > WAL files must be almost identical to the > previous > > > > one. > > > > > > > > Is there a way to incrementally backup so that > it > > > > takes less space? > > > > > > > > thanks! > > > > > ___________________________________________________________________________ > > > > > >_________ Finding fabulous fares is fun. > > > > Let Yahoo! FareChase search your favorite > travel > > > > > > sites to find flight and > > > > > > > hotel bargains. > > > > > > > http://farechase.yahoo.com/promo-generic-14795097 > > > > > > > ---------------------------(end of > > > > > > broadcast)--------------------------- > > > > > > > TIP 1: if posting/reading through Usenet, > please > > > > > > send an appropriate > > > > > > > subscribe-nomail command to > > > > > > majordomo@xxxxxxxxxxxxxx so that your > > > > > > > message can get through to the mailing > list > > > > > > cleanly > > > > > > -- > > > Eduardo J. Ortega - Linux user #222873 > > > "No fake - I'm a big fan of konqueror, and I use > it > > > for everything." -- Linus > > > Torvalds > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 7: You can help support the PostgreSQL > project > > > by donating at > > > > > > > > > http://www.postgresql.org/about/donate > > > > > ___________________________________________________________________________ > >_________ Looking for earth-friendly autos? > > Browse Top Cars by "Green Rating" at Yahoo! Autos' > Green Center. > > http://autos.yahoo.com/green_center/ > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 2: Don't 'kill -9' the postmaster > > -- > Eduardo J. Ortega - Linux user #222873 > "No fake - I'm a big fan of konqueror, and I use it > for everything." -- Linus > Torvalds > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project > by donating at > > > http://www.postgresql.org/about/donate > ____________________________________________________________________________________ Get your own web address. Have a HUGE year through Yahoo! Small Business. http://smallbusiness.yahoo.com/domains/?p=BESTDEAL
