Re: WAL files backup

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@cable.net.co>
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@freenode.net 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@cable.net.co>
> >
> > 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@postgresql.org 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