Re: Postgres questions

On 05/28/2011 06:08 PM, Trenta sis wrote:
>
> Dbi link was configured with instruction form dbi-link. Where I can find
> this scripts:

Write them.

> Queries in mssql are sql select from talbe on mssql with about 400.000 rows.

Well, no wonder it's slow. As far as I know DBI-Link can't push
predicates down into queries to the remote database. If you write:

SELECT * FROM some_remote_table t WHERE t.some_field > 42;

then (as far as I know) the whole of some_remote_table is retrieved,
then the filter is applied. It executes:
"SELECT * FROM underlying_table"
on the remote server, not
"SELECT * FROM underlying_table WHERE underlying_table.some_field > 42"

For more information, read:

http://stackoverflow.com/questions/1328601/postgresql-and-sql-server-2008-dbi-link

A full SQL/MED implementation *might* be able to push down predicates,
but even then it's hard to do reliably because of possible differences
in locales, collation rules, etc.

For that kind of data size you're usually better off periodically
copying the data from the remote table to a local table with the same
structure. Then query the remote table. Think of it as a materialized
view of the remote table. You can use DBI-link to refresh the local copy
and PgAgent to schedule periodic refreshes.

> About replication now I can't upgrade to 9.0 with 8.3 what possible
> options exists, if is active/passive and is not real ha also it can be OK

Use Google. Type in "postgresql replication" and the first result should be:

http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

--
Craig Ringer