Re: WAL files backup

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