Thread: PG 9.1 wal archiving

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