Richard's Diary

Sunday, December 14, 2008

High Availablity Followup for the Future To enable high availability (warm standby), just keeping sucking up wal logs from master
  1. create directory mkdir /var/log/postgresqlwal/
  2. chown postgres /var/log/postgresqlwal
    #you can set archive_timeout to force the server to switch to a new WAL segment file at least that often
  3. Begin recovery on the standby server from the local WAL archive, using a recovery.conf that specifies a restore_command that WAITS.
    "The magic that makes the two loosely coupled servers work together is simply a restore_command used on the standby that WAITS for the next WAL file to become available from the primary. The restore_command is specified in the recovery.conf file on the standby server. Normal recovery processing would request a file from the WAL archive, reporting failure if the file was unavailable. For standby processing it is normal for the next file to be unavailable, so we must be patient and wait for it to appear. A waiting restore_command can be written as a custom script that loops after polling for the existence of the next WAL file. There must also be some way to trigger failover, which should interrupt the restore_command , break the loop and return a file-not-found error to the standby server. This ends recovery and the standby will then come up as a normal server."
-log shipping requires dropping cluster data directory with each import if you want to be able to read from warm standby (Master-Slave allows the slaves to be read)
-log shipping does not help replicate across versions (pgdump is great for replicating across versions if you can get over the file size limit)
-a cold standby is just a backup waiting to get the latest WAL logs (archived + unarchived) so it can be restored (can you playback WAL logs that haven't been archived? I guess by definition you can read WAL logs that have been archived, even if they are after the STOP point of your backup?)
-you could have a backup that is just used for reads to do datawarehousing
-hot standby as a term does not make sense when you have multi master because the other masters are the hot "standbys"
-asynchronous multimaster sounds like a disaster synching up different master dbs because the writes are not done in order (because the writes were done on different masters)

Data partitioning
Haven't found a need to import postgres logs into db so don't need csv format yet

© 2010 Picky Ricky, Inc. originalblog