Hi all, After spending some time (~2 days to fully(?) understand the process) setting up a standby machine using WAL shipping and experimentations to be fairly sure it is working reliably, I started thinking of how I would like it to be done.
My dream-process of setting up a standby would be: - start up the postmaster on an empty directory (except maybe the postgres.conf and pg_hba.conf to be used after switching to active mode), with a "build standby" option; - the postmaster would start in a special standby mode, where it connects to the primary server using a normal connection (which in turn must be configured for e.g. in the command line, just as with psql); - the standby would issue a "STANDBY" command to the primary (possibly implemented at low level, i.e. it doesn't have to be a SQL command but could be at protocol level); - the primary would mark the start of the backup, just as today with the 'pg_start_backup' function; - the primary would start to stream on the connection all the files of the data base, interleaved with all the committed WAL records just as they commit (of course buffered somehow so it won't affect too much performance); - the standby would save the received files and store the WAL records for later replay; - when all the db files were transmitted, the primary would mark the end of the backup, just as today with 'pg_stop_backup', and signal this to the standby; - the standby can now start replaying the WAL records; - when all the saved WAL records up to the 'pg_stop_backup' mark were replayed, the standby is operational in the sense that it can be switched on in normal operation mode; - in the meantime the server keeps sending the WAL records as they become available, and the standby replays them as they arrive. This would make sure the standby is always as up to date as possible; - bringing the standby up would be a matter of loosing the connection to the master server, or some kind of local signalling; - the standby then would continue to work as a normal postmaster, using the configuration file which must be existing in the standby directory when starting the process; This process would only require from the user to know where he wants the standby and where's the master... no more scripting and worrying about partial WAL files. And it could provide some degree of automatic failover if relying on the connection failure is good enough sign of the primary server being down, but of course this is not mandatory. Is this scenario viable ? I realize it needs a lot of coding in the postmaster, and it must get somehow all WAL records as they commit. I'm mostly ignorant regarding the postgres code structure, so I have no idea how much work that would involve... so can you gurus please comment ? I really have no experience with C, but I would start doing some coding if it's deemed viable. Thanks, Csaba. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match