Re: Allowed DML on replicas?

On Thu, 2012-03-01 at 08:24 -0500, François Beausoleil wrote:
> Hi!
>
> I'm getting ready to build a reporting server, one where long-running queries and backups will be taken from. This new server will be a slave from the master where all changes are done. Some reports are better expressed with extracting a subset of the data and leaving it in a table to be reused, until the report set is done.
>
> In my specific case, I have a table with ~30M rows representing Twitter users. When I JOIN this table with the interactions I have on hand, it takes a long time, because PostgreSQL ends up doing a full table scan of the personas table. To make subsequent reporting steps easier, I do the JOIN only once, and write the results to a table.
>
> My question is:
>
> * Can a new schema be created on a replica?

No if you use a HotStandby. Yes if you use another kind of replication
(Slony for example).

> * Will this impact replication in any way?

No, because you can't with a HotStandby. No if you use another kind of
replication (Slony for example).

> * If I can't, what would you advise? dump / reload in a separate database without dropping the table, to keep the extra schemas around?
>

It depends. Using Slony is one way to do it.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com