PG 9.1 wal archiving

From: Kasia Tuszynska <ktuszynska(at)esri(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: PG 9.1 wal archiving
Date: 2012-05-11 00:11:49
Message-ID: 232B5217AD58584C87019E8933556D1103765FDAB0@redmx2.esri.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi Everybody,
I am testing the new features in 9.1 related to backups and restores.
I want to establish a workflow for doing a base backup, archiving wals for a primary server and be able to restore the primary server from those.

Testing Environment:
Testing on Windows Server 2008 R2

Postgresql.conf settings, different from default:
wal_level = hot_standby
checkpoint_segments = 1
archive_mode = on
archive_command = 'copy "%p" "C:\\postgres_archive\\%f"'
max_wal_senders = 3
wal_keep_segments = 1

perform a plane basebackup:
pg_basebackup.exe -h <hostname> -D <location to backup to> -Fp -U <replication user>

So, I have a basebackup and a wal archive.
My questions are about the behavior of the wal creation and archive.

Q1: I tried to minimize the number of wals created with the checkpoing_segments and wal_keep_segments parameters, I was hoping that reducing the values for those parameters would result in about 3 wals. But I am still getting about 8 walls created.
What else could I do to limit or control the number of wals created? I am doing this just for testing purposes ...

Q2: If I load some data into the db I do see the date on a wal under pg_xlog to get updated which is the record of the transactions, I do see the wals being overwritten, which is the log switch when the 16megs gets overwritten, what I do not see is the immediate reflection of the last written to wal file in the archive location. Which is what I would expect.
I think I am seeing the archive location written to when pg cycles through all 8 wals. Which does not make sense to me, b/c in the case of failure I would be missing 7 wals for recovery.

Q3: When I do get this archiving business figured out I would like to perform a recovery of the primary server using pg_basebackup and archived wals.
I am assuming that I would need to apply the result of pg_basebackup + all wals archived since the backup + the last wal segment salvaged from the original PGDATA location.
If that assumption is correct than I would need to save the archive_status folder, and place it in the new PGDATA \pg_xlog location? I am assuming that the archive_status folder holds the wal segment that is currently being written into.

This is where I am most confused:
The result of the plane pg_basebackup would be copied and pasted into the PGDATA location
The archived wals would be read from the recovery.conf recovery_command, read after the cluster is started up again.
But the archive_status folder would be placed in the pg_xlog collation manually?
I guess there is no other way to do it, but I want to make sure that I did not miss something in the doc.

The doc discusses more complex scenarios dealing with a secondary server, at the moment I am only interested in recovering the primary server.

Thank you,
Sincerely,
Kasia

Browse pgsql-admin by date

  From Date Subject
Next Message Bruce Momjian 2012-05-11 00:29:52 Re: Advice/guideline on increasing shared_buffers and kernel parameters
Previous Message Kevin Grittner 2012-05-10 16:09:34 Re: Advice/guideline on increasing shared_buffers and kernel parameters