Thread: Two Questions Re: Warm Backup

Greetings:

We are researching implementing a warm backup solution for our existing
databases. We have a two node cluster running RH which are connected to a
SAN. There is a total of 11 database clusters with the two node linux cluster
balancing the load. At the moment, we are not doing any WAL archiving.
exp=# select version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20070626 (Red Hat 4.1.2-14)
(1 row)

Q1: Can we set up a scenario where there is more that one warm standby? I want
a warm standby locally and one that is hundreds of miles away connected with
a T4 data circuit.

Q2: Am I correct in assuming that ALL changes to any of the production schema
will be written to the warm standby? For example, if I drop a constraint in
production I assume the same will occur on the warm standby. If I create and
drop a table in production, I assume it will occur on the warm standby.

TIA
--

     Terry Lee Tucker writes

> Q1: Can we set up a scenario where there is more that one
> warm standby?

Yes. But you'll have to consider what you want to happen when one
standby is correctly receiving the WAL files and another is not,
because the archive_command has to either fail or succeed, it can't
return "half-done".
You may need to provide your own layer that retry pushing local copies
of WAL files to the remote nodes and deleting them only when they've
been received by every standby server.

> Q2: Am I correct in assuming that ALL changes to any of the
> production schema will be written to the warm standby?
> For example, if I drop a constraint in production I assume
> the same will occur on the warm standby. If I create and
> drop a table in production, I assume it will occur on the warm
> standby.

It will, all DDL is replicated.

Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
 http://www.manitou-mail.org

On Saturday 02 May 2009 13:08, Daniel Verite wrote:
>      Terry Lee Tucker writes
>
> > Q1: Can we set up a scenario where there is more that one
> > warm standby?
>
> Yes. But you'll have to consider what you want to happen when one
> standby is correctly receiving the WAL files and another is not,
> because the archive_command has to either fail or succeed, it can't
> return "half-done".
> You may need to provide your own layer that retry pushing local copies
> of WAL files to the remote nodes and deleting them only when they've
> been received by every standby server.
>
> > Q2: Am I correct in assuming that ALL changes to any of the
> > production schema will be written to the warm standby?
> > For example, if I drop a constraint in production I assume
> > the same will occur on the warm standby. If I create and
> > drop a table in production, I assume it will occur on the warm
> > standby.
>
> It will, all DDL is replicated.
>
> Best regards,

Daniel:

Thanks for the reply. I later read the answer to the first question in the
docs. I expected the answer to number 2 to be affirmative, but just wanted to
make sure.

Thanks for the help...
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of OHL
2251 Jesse Jewell Pkwy
Gainesville, GA 30501
tel: (336) 372-6812 cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com