Re: Streaming replication and temp table operations

On Tue, Jun 21, 2011 at 9:15 PM, Joel Stevenson <jstevenson(at)bepress(dot)com> wrote:

> Does anyone know if temp tables and the operations on them (like inserting or copying to) are replicated from master to standby servers via the new PG WAL shipping replication?  Given that temp tables are only accessible per-session it would seem 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 it live on a production environment and so I was considering creating a temp table to populate with a sizable chunk of representative test 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 master to a value high enough to ensure that old WAL segments are not recycled too early, while the standby might still need them to catch up. If the standby falls behind too much, it needs to be reinitialized from a new base backup. If you set up a WAL archive that's accessible from the standby, wal_keep_segments is not required as the standby can always use the archive to catch up."
>
> Which, in the streaming replication w/o file-based continuous archiving scenario, seems like I'd want to be absolutely certain that this setting was big enough to handle whatever data was being imported into the temp table via a COPY...FROM even 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 of file-based continuous archiving)?

So you shouldn't be concerned.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services