Обсуждение: Backup hot-standby database.

Поиск
Список
Период
Сортировка

Backup hot-standby database.

От
Stephen Rees
Дата:
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

Re: Backup hot-standby database.

От
"Kevin Grittner"
Дата:
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

Re: Backup hot-standby database.

От
Robert Treat
Дата:
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

Re: Backup hot-standby database.

От
Stephen Rees
Дата:
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


Hot-standby/Reporting database.

От
John P Weatherman
Дата:
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


Re: Hot-standby/Reporting database.

От
jonathan ferguson
Дата:
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


Re: Hot-standby/Reporting database.

От
Jaime Casanova
Дата:
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

Re: Hot-standby/Reporting database.

От
Jaime Casanova
Дата:
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

Re: Hot-standby/Reporting database.

От
jonathan ferguson
Дата:
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


Re: Backup hot-standby database.

От
Robert Treat
Дата:
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

Re: Hot-standby/Reporting database.

От
Jaime Casanova
Дата:
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

Re: Hot-standby/Reporting database.

От
Vibhor Kumar
Дата:
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