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
