Re: WAL files backup

OK, a couple of issues:

1) creation date and time on pg_xlog file is irrelevant, since AFAIK, they are
recycled.
2) Why do you start_backup, archive WAL files somewhere else and then stop
backup?

If i understand correctly, you are explaining the process of data creation,
backup and restoration on your DB. If that's it, here's the way i am doing
it, and it seems to be working (no funny error messages after recovery):
Assume you have already created a DB and have data on it. Also, assume you
want to store backups under /home/postgres/backup_storage_dir/ and your
postgresql.conf has archive_command so that it stores WAL files
under /home/postgres/WAL (something like cp %p /home/postgres/WAL/%f)

1) Start backup on PG
select pg_start_backup('label');
2) Archive PG data directory
tar czf /home/postgres/backup_storage_dir/backup.tgz /usr/local/pgsql/data
3) Stop backup on PG. This creates a file named something like 0000*3B.backup
under /home/postgres/WAL/
select pg_stop_backup()
4) here comes the tricky part. in order to use the backup you have on your tgz
you also need, at least, one WAL file. Which one, depends on the name of
your .backup file. For example, if after you issue pg_stop_backup you get a
0003B.backup (real names are longer, i know), you will need at least WAL file
0003B (which should be under /home/postgres/WAL/). So what i do is erasing
any files with name alphabetically smaller than the .backup file. For
example, i would erase 00001, 00002, ... , 00039, 0003A but would NOT erase
0003B, 0003C and so on. We do this on some script which i can send you
outside the mailing list if you want.
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.

5) Now, say you want to recover. First untar your tgz
to /usr/local/pgsql/data. Assume archived WAL files get extracted
under /home/postgres/WAL
6) next, erase ANY FILES under pg_xlog (including subdirectories) from PG's
data dir.
7) create recovery.config with something like
restore_command = 'cp /home/postgres/WAL/%f "%p"'
8) Fire postgres up
pg_ctl start -D /usr/local/pgsql/data

that's it. next time you make a backup, you'll get a new .backup file, say
00072.backup. You can erase all "less than files" (from the one preserved
before, say 0003B, 0003B.backup, 0003C, 0003D, ... up to 00071.

i hope this wa helpful.

Eduardo.

On Thursday 15 February 2007 21:55, pedro noticioso wrote:
> 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(at)yahoo(dot)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-BA
>CKUP
>
> > 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(at)cable(dot)net(dot)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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

--
Eduardo J. Ortega - Linux user #222873
"No fake - I'm a big fan of konqueror, and I use it for everything." -- Linus
Torvalds