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
