SEO

hi there

I lightly read this
http://www.postgresql.org/docs/8.1/s...up-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

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/s...up-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

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)

On 2/12/07, pedro noticioso <cucnews@yahoo.com> wrote:
>
> hi there
>
> I lightly read this
> http://www.postgresql.org/docs/8.1/s...up-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
>

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/s...RCHIVE-COMMAND
http://www.postgresql.org/docs/8.1/s...-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/s...up-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

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/s...html#GUC-ARCHI
>VE-COMMAND
> http://www.postgresql.org/docs/8.1/s...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/s...up-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

for the base backup I tried with the procedure
outlined in

http://www.postgresql.org/docs/8.1/s...UP-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/s...html#GUC-ARCHI
> >VE-COMMAND
> >
>
http://www.postgresql.org/docs/8.1/s...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/s...up-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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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/s...UP-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/s...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

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.

---------------------------(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

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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/s...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/s...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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org