Re: [GENERAL] Offline Tablespaces and Partial Restore

2016-01-12 Thread Pedro França 
Thank you for your response David.

Yea, we are considering doing all the function calls and access to tables
with dblink and see how bad this would hurt performance. It will be tough
to explain this to the SQL Server guys.

Maybe there is another approach to deal with this kind of environment? Does
Postgres only relies on replication for high availability? Maybe an
extension that I don't know about?

Any help is appreciated.

Thank you

2016-01-11 22:11 GMT-02:00 David Steele :

> On 1/11/16 8:41 AM, Pedro França wrote:
>
>> I've tried to put the data on another database but it didn't help as
>> tablespaces are required cluster-wide on startup.
>>
>
> If putting the data in a separate database works for you then it wouldn't
> be a big leap to put it in a separate cluster. Functionality-wise it's
> pretty similar since you can't join across databases in Postgres.
>
> There are some advantages to this design since you can separate the buffer
> caches, tune optimizer variables, wal settings, etc.
>
> --
> -David
> da...@pgmasters.net
>



-- 

*PEDRO IVO* | Administrador de Banco de Dados GolSat | Líder em
Sustentabilidade

pedro.fra...@golsat.com.br  | (43) 3315.9545
| www.golsat.com.br

Re: [GENERAL] Offline Tablespaces and Partial Restore

2016-01-12 Thread David Steele 
On 1/12/16 6:02 AM, Pedro França wrote:
> Yea, we are considering doing all the function calls and access to
> tables with dblink and see how bad this would hurt performance. It will
> be tough to explain this to the SQL Server guys.

You should consider using the Postgres FDW
(http://www.postgresql.org/docs/current/static/postgres-fdw.html).  It's
a far more elegant solution to the problem of distributed data.

> Maybe there is another approach to deal with this kind of environment?
> Does Postgres only relies on replication for high availability? Maybe an
> extension that I don't know about?

There are a number of logical replication tools (Slony, Bucardo, BDR,
etc.) but I don't see how they would help in this case (because of your
restore requirements).


-- 
-David
da...@pgmasters.net



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Offline Tablespaces and Partial Restore

2016-01-11 Thread David Steele 

On 1/11/16 8:41 AM, Pedro França wrote:

I've tried to put the data on another database but it didn't help as
tablespaces are required cluster-wide on startup.


If putting the data in a separate database works for you then it 
wouldn't be a big leap to put it in a separate cluster. 
Functionality-wise it's pretty similar since you can't join across 
databases in Postgres.


There are some advantages to this design since you can separate the 
buffer caches, tune optimizer variables, wal settings, etc.


--
-David
da...@pgmasters.net


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Offline Tablespaces and Partial Restore

2016-01-11 Thread Pedro França 
I'm evaluating the use of PostgreSQL to our production mission-critical
application that currently runs on SQL Server 2012.

There is a huge partitioned table of archived information that is
occasionally queried (+5TB) and are not required to be 24/7 online. An
eventual failure on the filesystem that stores these tables must not
compromise the whole cluster.

Another concern is on an eventual restore of the database, as I havent
found a way to start the cluster without some of its tablespaces.

I've tried to put the data on another database but it didn't help as
tablespaces are required cluster-wide on startup.

Is there a way to take these tablespaces offline so I could do some
maintenace work or startup the database with the archived data offline?

Thanks in advance,
Pedro Ivo