Re: WAL files backup

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(at)commandprompt(dot)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