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.



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

Chad Wagner wrote:
> On 2/15/07, *Eduardo J. Ortega* <ejortegau@cable.net.co
> <mailto: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:
>
>
> 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/

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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

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...dsearch_v2.php

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On 2/18/07, pedro noticioso <cucnews@yahoo.com> wrote:
>
> -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


Are these files 234 bytes long? Also, where is the 05 logfile? Can you
post your archive_command?


> LOG: could not read from log file 0, segment 5 at
> offset 2416640: No such file or directory
>

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...p_%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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq