Обсуждение: WAL files backup

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

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

What do you mean by indentical here? does it mean that they are same in size, if that is true then yes they should be same in size unless you specify archive_timeout (8.2 config parameter) setting to do a log switch after certain amount of time.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

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-ARCHIVE-COMMAND
http://www.postgresql.org/docs/8.1/static/backup-online.html#BACKUP-ARCHIVING-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/

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

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

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 </dev/null'


8)
CREATE THIS FILE WITH THE NEXT LINES
/usr/local/pgsql/share/recovery.conf

name = '5'
restore_command = 'cp
/home/postgres/respaldos/oficial/%f %p'
recovery_target_timeline = 'latest'             #
number or 'latest'

regresamos wal files a pg_xlog - COPY BACKED UP WAL
FILES BACK TO pg_xlog DIRECTORY


pwd
/home/postgres/backup

mv oficial oficial-con-drop-17-10-01-05
tar zxf oficial-sin-drop-17-10-01-05.tgz ; mkdir
pg_xlog-17-10-01-05
mv /usr/local/pgsql/data/pg_xlog/* pg_xlog-17-10-01-05
cp -R oficial/* /usr/local/pgsql/data/pg_xlog/
mkdir /usr/local/pgsql/data/pg_xlog/archive_status

reiniciamos posgtres - RESTART POSTGRES






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
13:45:24 CST
LOG:  could not open file
"pg_xlog/000000010000000000000005" (log file 0,
segment 5): No such file or directory
LOG:  invalid primary checkpoint record
LOG:  could not open file
"pg_xlog/000000010000000000000005" (log file 0,
segment 5): No such file or directory
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 13581) was terminated by
signal 6
LOG:  aborting startup due to startup process failure







--- pedro noticioso <cucnews@yahoo.com> 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
> </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
> > >
> >
>
=== 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

pedro noticioso wrote:

> regresamos wal files a pg_xlog - COPY BACKED UP WAL
> FILES BACK TO pg_xlog DIRECTORY

This step is wrong.  You don't have to manually put the pg_xlog files in
the pg_xlog directory -- you have to let the recovery_command copy them
back from the archive area.  Note that recovery.conf does not go into
the share/ directory, but in data/.  And it must be present _only_ when
you want the system to attempt the recovery, not before.

Also I didn't see any pg_start_backup() nor pg_stop_backup() calls in
your procedure.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

pedro noticioso <cucnews@yahoo.com> writes:
> 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

It looks to me like you've gotten confused about which installation is
the original and which is the attempted recovery.  This log message:

> LOG:  database system was shut down at 2007-02-15

should certainly not appear if you were starting a postmaster in a
database tree that had been copied from a live installation.  Also,
the next line ought to be "starting archive recovery"; since it's
not, that means the postmaster didn't see a recovery.conf file.

            regards, tom lane

OK, a couple of issues:

1) creation date and time on pg_xlog file is irrelevant, since AFAIK, they are
recycled.
2) Why do you start_backup, archive WAL files somewhere else and then stop
backup?

If i understand correctly, you are explaining the process of data creation,
backup and restoration on your DB. If that's it, here's the way i am doing
it, and it seems to be working (no funny error messages after recovery):
Assume you have already created a DB and have data on it. Also, assume you
want to store backups under /home/postgres/backup_storage_dir/ and your
postgresql.conf has archive_command so that it stores WAL files
under /home/postgres/WAL (something like cp %p /home/postgres/WAL/%f)

1) Start backup on PG
 select pg_start_backup('label');
2) Archive PG data directory
tar czf /home/postgres/backup_storage_dir/backup.tgz /usr/local/pgsql/data
3) Stop backup on PG. This creates a file named something like 0000*3B.backup
under /home/postgres/WAL/
select pg_stop_backup()
4) here comes the tricky part. in order to use the backup you have on your tgz
you also need, at least, one WAL file. Which one, depends on the name of
your .backup file. For example, if after you issue pg_stop_backup you get a
0003B.backup (real names are longer, i know), you will need at least WAL file
0003B (which should be under /home/postgres/WAL/). So what i do is erasing
any files with name alphabetically smaller than the .backup file. For
example, i would erase 00001, 00002, ... , 00039, 0003A but would NOT erase
0003B, 0003C and so on. We do this on some script which i can send you
outside the mailing list if you want.
After erasing the "less than  names" WAL files, we add to tar the remaining
WAL records (0003B, 0003C  and so on on the example). The more WAL files you
have after 0003B, the more up to date DB you get after restore (since it has
more WAL files indicating more transactions that took place after the backup.

5) Now, say you want to recover. First untar your tgz
to /usr/local/pgsql/data. Assume archived WAL files get extracted
under /home/postgres/WAL
6) next, erase ANY FILES under pg_xlog (including subdirectories) from PG's
data dir.
7) create recovery.config with something like
restore_command = 'cp /home/postgres/WAL/%f "%p"'
8) Fire postgres up
pg_ctl start -D /usr/local/pgsql/data


that's it. next time you make a backup, you'll get a new .backup file, say
00072.backup. You can erase all "less than files" (from the one preserved
before, say 0003B, 0003B.backup, 0003C, 0003D, ... up to 00071.

i hope this wa helpful.

Eduardo.

On Thursday 15 February 2007 21:55, pedro noticioso wrote:
> 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 </dev/null'
>
>
> 8)
> CREATE THIS FILE WITH THE NEXT LINES
> /usr/local/pgsql/share/recovery.conf
>
> name = '5'
> restore_command = 'cp
> /home/postgres/respaldos/oficial/%f %p'
> recovery_target_timeline = 'latest'             #
> number or 'latest'
>
> regresamos wal files a pg_xlog - COPY BACKED UP WAL
> FILES BACK TO pg_xlog DIRECTORY
>
>
> pwd
> /home/postgres/backup
>
> mv oficial oficial-con-drop-17-10-01-05
> tar zxf oficial-sin-drop-17-10-01-05.tgz ; mkdir
> pg_xlog-17-10-01-05
> mv /usr/local/pgsql/data/pg_xlog/* pg_xlog-17-10-01-05
> cp -R oficial/* /usr/local/pgsql/data/pg_xlog/
> mkdir /usr/local/pgsql/data/pg_xlog/archive_status
>
> reiniciamos posgtres - RESTART POSTGRES
>
>
>
>
>
>
> 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
> 13:45:24 CST
> LOG:  could not open file
> "pg_xlog/000000010000000000000005" (log file 0,
> segment 5): No such file or directory
> LOG:  invalid primary checkpoint record
> LOG:  could not open file
> "pg_xlog/000000010000000000000005" (log file 0,
> segment 5): No such file or directory
> LOG:  invalid secondary checkpoint record
> PANIC:  could not locate a valid checkpoint record
> LOG:  startup process (PID 13581) was terminated by
> signal 6
> LOG:  aborting startup due to startup process failure
>
> --- pedro noticioso <cucnews@yahoo.com> 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-BA
>CKUP
>
> > 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
>
> === 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
Eduardo J. Ortega - Linux user #222873
"No fake - I'm a big fan of konqueror, and I use it for everything." -- Linus
Torvalds

On 2/15/07, Eduardo J. Ortega <ejortegau@cable.net.co> wrote:Why bother trying to delete WAL files older than the .backup file?  When PostgreSQL is in recovery mode it knows which WAL files are necessary to perform the recovery.

Also, the documentation recommends excluding the pg_xlog directory when performing the base backup.  Likely when it comes time to recovery the online WAL files have been archived already, so it is a risk of confusion I am sure.



--
Chad
http://www.postgresqlforums.com/

Chad Wagner wrote:

On 2/15/07, Eduardo J. Ortega <ejortegau@cable.net.co> wrote:

After erasing the "less than  names" WAL files, we add to tar the remaining
WAL records (0003B, 0003C  and so on on the example). The more WAL files you
have after 0003B, the more up to date DB you get after restore (since it has
more WAL files indicating more transactions that took place after the backup.

Why bother trying to delete WAL files older than the .backup file?  When PostgreSQL is in recovery mode it knows which WAL files are necessary to perform the recovery.

Also, the documentation recommends excluding the pg_xlog directory when performing the base backup.  Likely when it comes time to recovery the online WAL files have been archived already, so it is a risk of confusion I am sure.

If the OP is doing the same as myself, the WAL files are being archived outside of pg_xlog (indeed outside of the PG data cluster) - it makes no sense keeping around WAL files older than the .backup file because they're not needed - in a day I generate ~5GB worth of WAL files which aren't needed after the full backup runs at 2am, so it's a waste of resources to keep them around or to worry about backing them up after this time.

Andy.

On 2/16/07, Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk> wrote:

I mentioned this in my other response, the only reason I ask is because of the potential need to recover from an earlier base backup up to a specific point -- perhaps before the most recent base backup.  Personally I don't think it is a good idea for every environment to do this, but I can see the value in it and if you truly feel it is unnecessary then I can certainly respect that.  Perhaps I am a bit too paranoid :), but I have seen people foolishly do a huge cleanup and then take a backup and realize "Oh crap, I did too much!" -- and if you don't have all the WAL files you certainly can't guarantee a specific PITR.


--
Chad
http://www.postgresqlforums.com/

Muchas gracia a todos los queme han respondido hasta
ahora
Thanks to all who have repplied so far

Ok entonces regreso y reinicio al procedimiento
completo para tratar de atinarle a lo que puede ser el
problema.
Ok so I go back and retry the complete procedure to
try to pin point what might be the problem.


1)
Postgres recien instalado - POSTGRES NEW INSTALL

2)
Creando db de Pruebas  - - CREATE TESTING DATABASE
# createdb Test

3)
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 - FIVE THOUSAND
TIMES WITH UNIQUE ID'S

4)
copiamos wal y data otro lado - 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)
populamos Test - 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)
copiamos wal y data otro lado - 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)
Recuperar al respaldo 1 y los archivos WAL del
respaldo 2 - 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)
crear al recovery.conf - CREATE recovery.conf

name = '5'
restore_command = 'cp
/home/postgres/respaldos/oficial/%f %p'
recovery_target_timeline = 'latest'

asi que inicio PG y me marca errores - 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

*****************
NOTA: ok asi que le entiendo, falta un archivo asi que
ahi va
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
NOTA: ubicacion en el evento en el log no es muy
exacto pero te da la idea de que ya no hay queja sobre
el hecho de que falte un archivo WAL de pg_xlog
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



--- Chad Wagner <chad.wagner@gmail.com> wrote:

> On 2/16/07, Andy Shellam (Mailing Lists) <
> andy.shellam-lists@mailnetwork.co.uk> wrote:
> >
> >
> > Why bother trying to delete WAL files older than
> the .backup file?  When
> > PostgreSQL is in recovery mode it knows which WAL
> files are necessary to
> > perform the recovery.
> >
> > Also, the documentation recommends excluding the
> pg_xlog directory when
> > performing the base backup.  Likely when it comes
> time to recovery the
> > online WAL files have been archived already, so it
> is a risk of confusion I
> > am sure.
> >
> >
> > If the OP is doing the same as myself, the WAL
> files are being archived
> > outside of pg_xlog (indeed outside of the PG data
> cluster) - it makes no
> > sense keeping around WAL files older than the
> .backup file because they're
> > not needed - in a day I generate ~5GB worth of WAL
> files which aren't needed
> > after the full backup runs at 2am, so it's a waste
> of resources to keep them
> > around or to worry about backing them up after
> this time.
> >
>
>
> I mentioned this in my other response, the only
> reason I ask is because of
> the potential need to recover from an earlier base
> backup up to a specific
> point -- perhaps before the most recent base backup.
>  Personally I don't
> think it is a good idea for every environment to do
> this, but I can see the
> value in it and if you truly feel it is unnecessary
> then I can certainly
> respect that.  Perhaps I am a bit too paranoid :),
> but I have seen people
> foolishly do a huge cleanup and then take a backup
> and realize "Oh crap, I
> did too much!" -- and if you don't have all the WAL
> files you certainly
> can't guarantee a specific PITR.
>
>
> --
> Chad
> http://www.postgresqlforums.com/
>




____________________________________________________________________________________
8:00? 8:25? 8:40? Find a flick in no time
with the Yahoo! Search movie showtime shortcut.
http://tools.search.yahoo.com/shortcuts/#news

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

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 <alvherre@commandprompt.com> 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

On 2/18/07, pedro noticioso <cucnews@yahoo.com> wrote:
This is because your archived logs are partial, it is expecting to read XLOG_BLCKSZ and it had a partial read.  Clearly you are doing something wrong, but there just isn't enough information.

I wrote an article about performing a hot backup with a Perl script that I wrote called pg_hotbackup (available on pgfoundry), you can look at it here:
http://www.postgresqlforums.com/wiki/Backup_%26_Recovery

But you must follow the PostgreSQL documentation for setting up the archive_command, which is more or less this:
archive_command = 'cp -i %p /var/lib/pgsql/backups/%f </dev/null'              


Also documented on that page is the recovery procedure.  It is all based on the PostgreSQL documentation and has worked fine in my testing.

--
Chad
http://www.postgresqlforums.com/

Im back working on PITR with WAL files.

I was busy creating a PITR based file backup procedure
in bash that works just like my boss want it to.

that was complementary to the backup I want to discuss
with you; shouldn't it work? I have repeated this
procedure a few times with the same results, so that
is wrong here?

thanks in advance

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





--- pedro noticioso <cucnews@yahoo.com> wrote:

> Muchas gracia a todos los queme han respondido hasta
> ahora
> Thanks to all who have repplied so far
>
> Ok entonces regreso y reinicio al procedimiento
> completo para tratar de atinarle a lo que puede ser
> el
> problema.
> Ok so I go back and retry the complete procedure to
> try to pin point what might be the problem.
>
>
> 1)
> Postgres recien instalado - POSTGRES NEW INSTALL
>
> 2)
> Creando db de Pruebas  - - CREATE TESTING DATABASE
> # createdb Test
>
> 3)
> 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 - FIVE
> THOUSAND
> TIMES WITH UNIQUE ID'S
>
> 4)
> copiamos wal y data otro lado - 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)
> populamos Test - 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)
> copiamos wal y data otro lado - 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)
> Recuperar al respaldo 1 y los archivos WAL del
> respaldo 2 - 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)
> crear al recovery.conf - CREATE recovery.conf
>
> name = '5'
> restore_command = 'cp
> /home/postgres/respaldos/oficial/%f %p'
> recovery_target_timeline = 'latest'
>
> asi que inicio PG y me marca errores - 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
>
> *****************
> NOTA: ok asi que le entiendo, falta un archivo asi
> que
> ahi va
> 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
> NOTA: ubicacion en el evento en el log no es muy
> exacto pero te da la idea de que ya no hay queja
> sobre
> el hecho de que falte un archivo WAL de pg_xlog
> 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
>
=== message truncated ===




____________________________________________________________________________________
Get your own web address.
Have a HUGE year through Yahoo! Small Business.
http://smallbusiness.yahoo.com/domains/?p=BESTDEAL