Thread: BUG #6266: Create temp tables on Slave
The following bug has been logged online: Bug reference: 6266 Logged by: Sally Email address: sally.nayer@tedata.net PostgreSQL version: 9.1 Operating system: Centos 5.5 Description: Create temp tables on Slave Details: We are Using replica (wal streaming replica)to replicate between Master and slave. We need to be able to create temp tables on Slave, Is there any workaround? How could we create temp database and tables on slave? Thanks and Regards, Sally
On Mon, Oct 24, 2011 at 8:48 AM, Sally <sally.nayer@tedata.net> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A06266 > Logged by: =A0 =A0 =A0 =A0 =A0Sally > Email address: =A0 =A0 =A0sally.nayer@tedata.net > PostgreSQL version: 9.1 > Operating system: =A0 Centos 5.5 > Description: =A0 =A0 =A0 =A0Create temp tables on Slave > Details: > > We are Using replica (wal streaming replica)to replicate between Master a= nd > slave. > > We need to be able to create temp tables on Slave, > you can't. this isn't a bug but a known limitation and is documented here: http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-USERS > Is there any workaround? > How could we create temp database and tables on slave? > not with streaming replication --=20 Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
Dear Jaime, Is there commercial solution that uses the same concept of wal streaming an= d allows the Creation of temp tables on slave I really appreciate your help Thanks and Regards, Sally Nayer=09 IT Systems Senior Engineer TE Data TE Data, Building A11- B90, Smart Village=20 Cairo- Alex Desert Road, 28 Km 6th of October 12577, Egypt T:=A0 2 02 33 32 0733 | F: 2 -02 33 32 0815| E: 2543 Visit: www.tedata.net =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=20 -----Original Message----- From: jcasanov@systemguards.com.ec [mailto:jcasanov@systemguards.com.ec] On= Behalf Of Jaime Casanova Sent: Tuesday, October 25, 2011 5:51 AM To: Sally Nayer Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #6266: Create temp tables on Slave On Mon, Oct 24, 2011 at 8:48 AM, Sally <sally.nayer@tedata.net> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A06266 > Logged by: =A0 =A0 =A0 =A0 =A0Sally > Email address: =A0 =A0 =A0sally.nayer@tedata.net > PostgreSQL version: 9.1 > Operating system: =A0 Centos 5.5 > Description: =A0 =A0 =A0 =A0Create temp tables on Slave > Details: > > We are Using replica (wal streaming replica)to replicate between Master a= nd > slave. > > We need to be able to create temp tables on Slave, > you can't. this isn't a bug but a known limitation and is documented here: http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-USERS > Is there any workaround? > How could we create temp database and tables on slave? > not with streaming replication --=20 Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitaci=F3n
On 10/25/11 12:11 AM, Sally Nayer wrote: > Is there commercial solution that uses the same concept of wal streaming and allows the Creation of temp tables on slave WAL files represent 'delta changes' between the state of the postgres file system. *anything* that changes database files on the slave side would render the WAL files inherently unusable. to do what you want would require using a fundamentally different replication method, such as Slony or Bucardo. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Well, it is imaginable for there to be some side mechanism to enable use of temporary tables on a WAL-based replica, after all, we have leapt through enough hoops to get read transactions working. That being said... A) a solution for how to do this has not yet emerged, so it seems not too likely that such a feature will enter 9.2, let alone earlier versions. It's not yet really scheduled for 9.2, so it would be wiser to assume 9.3 or later, ergo don't make any plans earlier than 2013... B) yes, indeed, "logical" replication systems like Slony, Londiste, and Bucardo are the alternative at present. I have been hearing comments to the effect of these systems being pass=E9 - when WAL replication does not support fairly important cases such as allowing reports to use temporary tables, that seems rather premature. On Oct 25, 2011 5:10 AM, "John R Pierce" <pierce@hogranch.com> wrote: > On 10/25/11 12:11 AM, Sally Nayer wrote: > >> Is there commercial solution that uses the same concept of wal streaming >> and allows the Creation of temp tables on slave >> > > WAL files represent 'delta changes' between the state of the postgres file > system. *anything* that changes database files on the slave side would > render the WAL files inherently unusable. > > to do what you want would require using a fundamentally different > replication method, such as Slony or Bucardo. > > > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast > > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-bugs<http://www.postgresql.org= /mailpref/pgsql-bugs> >
> you can't. this isn't a bug but a known limitation and is documented here: > http://www.postgresql.org/docs/9.1/static/hot-standby.html#HOT-STANDBY-US= ERS > >> Is there any workaround? >> How could we create temp database and tables on slave? >> > > not with streaming replication > > -- > Jaime Casanova=C2=A0 =C2=A0 =C2=A0 =C2=A0=C2=A0 www.2ndQuadrant.com > Professional PostgreSQL: Soporte 24x7 y capacitaci=C3=B3n > I wonder if a workaround is possible using dblink, pl/proxy, or FDW to write out to a different "temp" db?