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