Re: Catching up Production from Warm Standby after maintenance - Please help

On Tue, Jul 7, 2009 at 10:42 AM, Jennifer
Spencer<jenniferm411@hotmail.com> wrote:
> We are using v. 8.3.1 at present.

You should really update to the latest 8.3.x version.  It's simple and fast.

> We anticipate a terabyte of data each
> year starting in November, and I am concerned about what happens
> maintenance-wise a couple of years down the line.  I think that we won't be
> able to do vacuuming/reindexing with the machine online and serving users if
> the database is over a certain size.  Am I wrong?

Depends on how fast your IO subsystem is really.  Are you planning for
enough IO capacity for your uses?  Using high quality RAID controllers
with plenty of drives (spinning OR solid state)?

> Our set up allows the users to create and delete ad-hoc tables in their own
> namespaces (each user has his own schema in addition to some overall schemas
> for the project).  Since Slony does not automatically handle "create table"
> and "drop table", I would have to incorporate that into the infrastructure
> APIs that create & drop tables and sequenes.  I think it would be
> non-trivial to implement a full-on, total replication scenario.  We are
> using Slony now for a select group of tables, but it's a separate API to add
> a table or remove it from replication.

So, you're allowing users to do adhoc DDL on a production system? No
testing / staging environment beforehand?  You are asking for trouble
IMHO.  OTOH, there are instances where well defined applications can
run DDL.

> What do you do when you have to do maintenance?

Define "maintenance".  Maybe you're bringing along a few more bad
habits from sybase.

>  Don't you take your primary
> offline and clean it?

I can (and have once or twice) because I use slony.

> Or is this old-school thinking?  I am coming from a
> Sybase environment, and previously I was able to use transaction logs to
> catch up post-maintenance.

Well, that's not gonna work here.

> It seems odd to me to have a fast, powerful machine left solely in warm
> standby recovery mode that  cannot be used to alleviate the pressures of DB
> maintenance.

Well, maybe you're thinking of log shipping replication in a different
way because of your experience.  8.5 might bring along true hot WAL
replication standbys.  They didn't make it into 8.4


> My systems admin has done nothing but complain about a
> "wasted" machine - he does not see the value of having the standby.

Then he's not a particularly well informed nor educated sys admin.  If
the primary server fails, hard, the seconday can take over in minutes.
 Is there no value in that?  If he can't see the value there, then
find a new sysadmin .

>  Of course, if it were Slony'd, we could use it, I suppose.

Yep, and if the hot WAL replication stuff was available.  I hear
there's a patch for it that works mostly


>
> Thanks for your help,
> Jennifer
>
> ________________________________
> Date: Tue, 7 Jul 2009 07:33:16 -0400
> Subject: Re: [ADMIN] Catching up Production from Warm Standby after
> maintenance - Please help
> From: scott.lists@enterprisedb.com
> To: jenniferm411@hotmail.com
> CC: scott.marlowe@gmail.com; pgsql-admin@postgresql.org
>
> On Tue, Jul 7, 2009 at 5:12 AM, Jennifer Spencer <jenniferm411@hotmail.com>
> wrote:
>
>>
>> If you've moved on, so to speak, with the new primary, you restart the
>> old primary, now warm standby, the same way you initially created the
>> warm standby. issue the start hot backup command to the primary, copy
>> over all the data dir and start shipping WAL files to it before you
>> start continuous recovery.
>
> If I do that, the primary will not be clean anymore.  It will be as
> unvacuumed and index-bloated as the warm standby.  Or am I missing
> something?
>
>    I think that Scott's point was that once you have brought the standby
> 'alive', you have no other option but to start over.  Warm-Standby isn't for
> reindex type operations, i.e. it's a failover mechanism, not to be confused
> with a switchover mechanism which lets you move back and forth easily.  Once
> you cut to the standby, you have to do a full re-sync to the old primary
> system.  What you're looking for is a replication system like Slony.
>
>     Are indexing and vacuuming hurting so much that you can't do them
> online?   Why not use 'create index concurrently' and set vacuum_cost_delay
> to help keep these operations from impacting your production system?  What
> version of PG are you using?
> -- Another Scott :-)
>
> ________________________________
> Lauren found her dream laptop. Find the PC that’s right for you.



--
When fascism comes to America, it will be intolerance sold as diversity.