Thread: Streaming replication and temp table operations
Hi all, Does anyone know if temp tables and the operations on them (like inserting or copying to) are replicated from master to standbyservers via the new PG WAL shipping replication? Given that temp tables are only accessible per-session it wouldseem unnecessary but if the shipping is happening at the WAL log level is that sort of thing considered? Specifically I've got a table that I want to get some representative statistics and explain plans on prior to making it liveon a production environment and so I was considering creating a temp table to populate with a sizable chunk of representativetest data on the master database installation. The Streaming Replication docs ( 25.2.5 ) state: "If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the masterto a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still needthem to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you setup a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use thearchive to catch up." Which, in the streaming replication w/o file-based continuous archiving scenario, seems like I'd want to be absolutely certainthat this setting was big enough to handle whatever data was being imported into the temp table via a COPY...FROMeven if the actual table wasn't being replicated. Does anyone know if this is a valid concern and whether or not the temp table will be replicated (regardless of the use offile-based continuous archiving)? Thanks in advance, Joel
On Tue, Jun 21, 2011 at 2:15 PM, Joel Stevenson <jstevenson@bepress.com> wrote: > Hi all, > > Does anyone know if temp tables and the operations on them (like inserting or copying to) are replicated from master tostandby servers via the new PG WAL shipping replication? Given that temp tables are only accessible per-session it wouldseem unnecessary but if the shipping is happening at the WAL log level is that sort of thing considered? Pretty sure that with 9.0's streaming replication, the no WAL of temp tables gets turned off. I've certainly seen references to such float by. But I'd check the docs to be sure.
On Tue, Jun 21, 2011 at 9:15 PM, Joel Stevenson <jstevenson@bepress.com> wrote: > Does anyone know if temp tables and the operations on them (like inserting or copying to) are replicated from master tostandby servers via the new PG WAL shipping replication? Given that temp tables are only accessible per-session it wouldseem unnecessary but if the shipping is happening at the WAL log level is that sort of thing considered? There is no WAL for temp tables, so nothing is transferred via streaming replication. > Specifically I've got a table that I want to get some representative statistics and explain plans on prior to making itlive on a production environment and so I was considering creating a temp table to populate with a sizable chunk of representativetest data on the master database installation. The Streaming Replication docs ( 25.2.5 ) state: > > "If you use streaming replication without file-based continuous archiving, you have to set wal_keep_segments in the masterto a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still needthem to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you setup a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use thearchive to catch up." > > Which, in the streaming replication w/o file-based continuous archiving scenario, seems like I'd want to be absolutelycertain that this setting was big enough to handle whatever data was being imported into the temp table via a COPY...FROMeven if the actual table wasn't being replicated. > > Does anyone know if this is a valid concern and whether or not the temp table will be replicated (regardless of the useof file-based continuous archiving)? So you shouldn't be concerned. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services