Обсуждение: Backup hot-standby database.
Using PostgreSQL 9.0.x I cannot use pg_dump to generate a backup of a database on a hot- standby server, because it is, by definition, read-only. However, it seems that I can use COPY TO within a serializable transaction to create a consistent set of data file(s). For example, BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; COPY t1 TO '/tmp/t1'; COPY t2 TO '/tmp/t2'; ... etc ... COPY t<n> TO '/tmp/t<n>'; COMMIT TRANSACTION; I can then use pg_dump to export the corresponding database schema from the master DBMS. Is this going to scale to a multi-GB database, where it will take hours to export the data from all of the tables, or are there scalability issues of which I should be aware? Thanks in advance, - SteveR
Stephen Rees <srees@pandora.com> wrote: > I cannot use pg_dump to generate a backup of a database on a hot- > standby server, because it is, by definition, read-only. That seems like a non sequitur -- I didn't think pg_dump wrote anything to the source database. Have you actually tried? If so, please show your commands and the error. -Kevin
On Tue, Mar 15, 2011 at 5:50 PM, Stephen Rees <srees@pandora.com> wrote: > Using PostgreSQL 9.0.x > > I cannot use pg_dump to generate a backup of a database on a hot-standby > server, because it is, by definition, read-only. That really makes no sense :-) You can use pg_dump on a read-only slave, but I think the issue that people tend to run into is that the pg_dump operations get canceled out by incoming changes before it can finish. You can of course modify the configs to work around this somewhat, but eventually it becomes a problem. > However, it seems that I > can use COPY TO within a serializable transaction to create a consistent set > of data file(s). For example, > > BEGIN TRANSACTION; > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; > COPY t1 TO '/tmp/t1'; > COPY t2 TO '/tmp/t2'; > > ... etc ... > > COPY t<n> TO '/tmp/t<n>'; > COMMIT TRANSACTION; > > I can then use pg_dump to export the corresponding database schema from the > master DBMS. > > Is this going to scale to a multi-GB database, where it will take hours to > export the data from all of the tables, or are there scalability issues of > which I should be aware? > Well, basically that's in in a nutshell. You have to stop replay while you are doing the dumps like this, so eventually that delay becomes unbearable for most people (especially on the order of hours). There are several ways to work around this... you can use filesystem snapshots to make copies and dump from there; great if you have the option. If you don't you might want to look into omnipitr, it can create filesystem level backups from a slave (not the same as a logical export, but it might do). Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/lg
Robert, Thank you for reply. I had the wrong end of the stick regarding pg_dump and hot-standby. I will take a look at omnipitr, as you suggest. Per your comment > You have to stop replay while you are doing the dumps like this how do I stop, then resume, replay with both the master and hot- standby available throughout? - Steve On Mar 15, 2011, at 3:04 PM, Robert Treat wrote: > On Tue, Mar 15, 2011 at 5:50 PM, Stephen Rees <srees@pandora.com> > wrote: >> Using PostgreSQL 9.0.x >> >> I cannot use pg_dump to generate a backup of a database on a hot- >> standby >> server, because it is, by definition, read-only. > > That really makes no sense :-) You can use pg_dump on a read-only > slave, but I think the issue that people tend to run into is that the > pg_dump operations get canceled out by incoming changes before it can > finish. You can of course modify the configs to work around this > somewhat, but eventually it becomes a problem. > >> However, it seems that I >> can use COPY TO within a serializable transaction to create a >> consistent set >> of data file(s). For example, >> >> BEGIN TRANSACTION; >> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; >> COPY t1 TO '/tmp/t1'; >> COPY t2 TO '/tmp/t2'; >> >> ... etc ... >> >> COPY t<n> TO '/tmp/t<n>'; >> COMMIT TRANSACTION; >> >> I can then use pg_dump to export the corresponding database schema >> from the >> master DBMS. >> >> Is this going to scale to a multi-GB database, where it will take >> hours to >> export the data from all of the tables, or are there scalability >> issues of >> which I should be aware? >> > > Well, basically that's in in a nutshell. You have to stop replay while > you are doing the dumps like this, so eventually that delay becomes > unbearable for most people (especially on the order of hours). > > There are several ways to work around this... you can use filesystem > snapshots to make copies and dump from there; great if you have the > option. If you don't you might want to look into omnipitr, it can > create filesystem level backups from a slave (not the same as a > logical export, but it might do). > > > Robert Treat > play: xzilla.net > work: omniti.com > hiring: l42.org/lg
All, I have recently upgraded to Postgres9 and am building a hot standby for reporting. Unfortunately, my end users are providing requirements for 1) real time data replication (which the hot standby does) and 2) the ability to create temporary tables as part of their reporting jobs (which is expressly prohibited in a hot standby. Has anyone run into this already and have an idea for a work around? I am primarily an Oracle guy and in that environment I would set up a second DB with database links to the hot standby, then they could connect somewhere they could create tables and use the links to pull the real time data...keeping them away from production with ad hoc code. I'm not sure if there is any way to do that with postgres. Thanks, John John P Weatherman Sr DBA Centerstone
hi. On Mar 18, 2011, at 5:25 PM, John P Weatherman wrote: > All, > > I have recently upgraded to Postgres9 and am building a hot standby for > reporting. Unfortunately, my end users are providing requirements for > 1) real time data replication (which the hot standby does) and 2) the > ability to create temporary tables as part of their reporting jobs > (which is expressly prohibited in a hot standby. As you rightly note, if you are seeking replication for reporting and need to change the reporting database, then you do*not* want to use PostgreSQL 9's replication/hot-standby features, as WAL shipping disallows any writes to the database,or differences. Instead, you will want to look at the following for realtime replication/failover, if you have not already: http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling Pay particular attention to the references, particularly from the Mailing List, and links. You might find Slony-I or Londiste or Bucardo to be the right answer for your needs. Here's more on Replication with pgpool-II and slony-I: http://pgsqlpgpool.blogspot.com/2010/06/pgpool-ii-and-hot-standby.html http://pgsqlpgpool.blogspot.com/2010/06/talk-with-author-of-streaming.html http://scanningpages.wordpress.com/2010/10/09/9-0-streaming-replication-vs-slony/ http://stackoverflow.com/questions/3692493/pgpool-ii-for-postgres-is-it-what-i-need > Has anyone run into this already and have an idea for a work around? I > am primarily an Oracle guy and in that environment I would set up a > second DB with database links to the hot standby, then they could > connect somewhere they could create tables and use the links to pull the > real time data...keeping them away from production with ad hoc code. > I'm not sure if there is any way to do that with postgres. I too am creating a similar set-up. I too would value the wisdom of the ML. According to the Slony-I page, it appears thatslaves might be able to be writable reporting databases. Is this true? I second the what $REP_TECH be used to replicate to a reporting database, where reporters want to write to the reportingdatabase? +1 on "how have people done this?" In my researches so far, I've found the following informative: http://archives.postgresql.org/pgsql-admin/2010-08/msg00173.php http://www.sraoss.co.jp/event_seminar/2010/20100702-03char10.pdf http://momjian.us/main/writings/pgsql/replication.pdf http://www.fastware.com.au/docs/PostgreSQL_HighAvailability.pdf Thanks. have a day.yad jdpf
On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman <jweatherman91@alumni.wfu.edu> wrote: > > Has anyone run into this already and have an idea for a work around? I > am primarily an Oracle guy and in that environment I would set up a > second DB with database links to the hot standby, you can use the contrib module dblink for this: http://www.postgresql.org/docs/9.0/static/dblink.html -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL
On Sun, Mar 20, 2011 at 8:04 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman > <jweatherman91@alumni.wfu.edu> wrote: >> >> Has anyone run into this already and have an idea for a work around? I >> am primarily an Oracle guy and in that environment I would set up a >> second DB with database links to the hot standby, > > you can use the contrib module dblink for this: > http://www.postgresql.org/docs/9.0/static/dblink.html > obviously you need to install the module libraries in both the master and the slave and the sql functions that create objects in master only -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL
hi. On Mar 20, 2011, at 9:05 PM, Jaime Casanova wrote: > On Sun, Mar 20, 2011 at 8:04 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: >> On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman >> <jweatherman91@alumni.wfu.edu> wrote: >>> Has anyone run into this already and have an idea for a work around? I >>> am primarily an Oracle guy and in that environment I would set up a >>> second DB with database links to the hot standby, >> >> you can use the contrib module dblink for this: >> http://www.postgresql.org/docs/9.0/static/dblink.html > > obviously you need to install the module libraries in both the master > and the slave and the sql functions that create objects in master only Perhaps I'm in the clueless n00b category, here, but how does: "dblink [] a module which supports connections to other PostgreSQL databases from within a database session." Even approach solving the problem of Database Replication for Data Warehousing aka Reports DB? dblink looks more like SSH or Telnet for DB access than a db replication solution to me. Please explain. Thanks. have a day.yad jdpf
On Fri, Mar 18, 2011 at 4:55 PM, Stephen Rees <srees@pandora.com> wrote: > Robert, > > Thank you for reply. I had the wrong end of the stick regarding pg_dump and > hot-standby. > I will take a look at omnipitr, as you suggest. > > Per your comment >> >> You have to stop replay while you are doing the dumps like this > > how do I stop, then resume, replay with both the master and hot-standby > available throughout? > If you are using WAL file based replication, you need some logic in your restore script that will enable it to stop feeding xlog segments into the slave (think "if pause file exists, return, else cp xlog file"). This would leave the slave available, just with no updates coming in. If you are using streaming, I think it's much harder. There are some new function to pause and resume streaming WAL coming in 9.1, it might be possible to back-patch those, but we haven't looked at it yet. Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/lg
On Sun, Mar 20, 2011 at 10:38 PM, jonathan ferguson <jdpf@hoozinga.com> wrote: > hi. > > On Mar 20, 2011, at 9:05 PM, Jaime Casanova wrote: > >> On Sun, Mar 20, 2011 at 8:04 PM, Jaime Casanova <jaime@2ndquadrant.com> wrote: >>> On Fri, Mar 18, 2011 at 4:25 PM, John P Weatherman >>> <jweatherman91@alumni.wfu.edu> wrote: >>>> Has anyone run into this already and have an idea for a work around? I >>>> am primarily an Oracle guy and in that environment I would set up a >>>> second DB with database links to the hot standby, >>> >>> you can use the contrib module dblink for this: >>> http://www.postgresql.org/docs/9.0/static/dblink.html >> >> obviously you need to install the module libraries in both the master >> and the slave and the sql functions that create objects in master only > > Perhaps I'm in the clueless n00b category, here, but how does: > > "dblink [] a module which supports connections to other PostgreSQL databases from within a database session." > your question had 2 parts... > 1) real time data replication (which the hot standby does) and for this one hot standby is almost that. ok, you have a little delay but is that delay that bad that you can't use it? if it is, 9.1 will ship with synchronous replication > 2) the ability to create temporary tables as part of their reporting jobs > (which is expressly prohibited in a hot standby. AFAIU, the links you say you create in Oracle are for this... no? that's why i suggest using dblink... if i misunderstood how you use the database links, please explain... -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL
On Mar 19, 2011, at 2:55 AM, John P Weatherman wrote: > I have recently upgraded to Postgres9 and am building a hot standby for > reporting. Unfortunately, my end users are providing requirements for > 1) real time data replication (which the hot standby does) and 2) the > ability to create temporary tables as part of their reporting jobs > (which is expressly prohibited in a hot standby. > Yes. On Hotstandby you cannot create Tables. > Has anyone run into this already and have an idea for a work around? Looking your requirement, I would recommend to use Slony Replication, which replicate data from mater to slave and also,gives ability to create temporary table, without allowing user to tamper/modify the data of Replicated Tables of (Master). Thanks & Regards, Vibhor Kumar vibhor.aim@gmail.com Blog:http://vibhork.blogspot.com