Re: WAL files backup

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/static/backup-online.html#BACKUP-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/static/runtime-config-wal.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