Hi
My understanding of the differences between Oracle and Postgres is
slightly different:
Oracle vs Postgres
------------------------------
database - cluster (see note i)
instance - database
user/schema - schema (see note ii)
user - user
i) One Oracle database can run one or more instances (SID each with their
own init<sid_name}.ora config file). Logically this looks like a postgres
database as you have to specify the SID when connecting to the Oracle
database (like specifying the Postgres database when connecting).
ii) In Oracle, users and schemas are synonymous. When you create objects
in a schema they belong to the user (with the same schema name). In
postgres users and schemas are separate and distinct objects.
Some other things that you might miss from Oracle in postgres are:
1) Synonyms - look at "search_path" setting to get around this
2) Nested transactions - apparantly coming soon, but there will no
equivalent to #PRAGMA AUTONOMOUS
3) AFAIK, there is no scripted exception handling within stored functions
- any error causes your transaction to abort - you cannot catch and handle
it
4) AFAIK, no support for materialised views
5) No dedicated SQL statements to enable/disable constraints or indexes
(looks like some work arounds available)
6) No "connect by prior" statement for managing hierarchical data - saw a
reference to a workaround
7) No fine grained control on where your objects are stored (tablespaces,
data files), but apparantly coming soon
There are probably some others, that I can't think of right now. Most
things that you will miss, you can probably work around without too much
difficulty by adopting a slightly different mindset.
Apologies if anyone feels that I keep harping on about Oracle, but it is
my main DB/SQL frame of reference for the moment.
John Sidney-Woollett
Erwan DUROSELLE said:
> Scott,
>
> Though there is not exact match, you may consider that:
> Oracle instance <=> Postgres cluster
> Oracle schema <=> Postgres database.
> But there is a schema concept in Postgres that is pretty similar to the
> one in Oracle.
> ( Actually, I think there is a lack of "database" concept in Oracle.)
>
> That's why you cannot "start" or "stop" a db. You start or stop a cluster.
> But you can deny acess to a database with pg_hba.conf as
> tatyana.krasnokutsky explained.
>
> Other gotchas for those who come from the Oracle world:
> - PostgreSQL = pg = postgres
> - autocommit is the default in pg. If you want to be able to rollback,
> you must issue a BEGIN first.
> - no Toad on pg ;-), though there are several graphical admin tools. I
> like Pgadmin III: http://www.pgadmin.org/pgadmin3/index.php
> - don't forget to VACUUM your db VERY often. Usually: more than once a
> day on active databases. Carefully read the docs on that subject.
> - no tablespace, no storage clause, no initial, next, .... Tablespaces
> should come up soon however (pg 7.5?)
> - no archive log, a.k.a. Point in Time Recovery. So there are only full
> backups.
> - the equivalent of initSID.ora is postgresql.conf
> - in postgresql.conf, always set fsync=TRUE unless you are ready to
> loose your db afret a power failure.
> - in postgresql.conf, shared_buffers is the equivalent db_block_buffers
> in initSID.ora. Don't set it to high, it is not usefull. Let the file
> system cache the data.
> - COPY is (roughly) the equivalent of SQL*LOAD
>
> HTH,
> Erwan
>
>>>> "scott.marlowe" <scott.marlowe@ihs.com> 09/01/2004 15:46:16 >>>
> On Mon, 5 Jan 2004 tatyana.krasnokutsky@gsa.gov wrote:
>
>> Hello,
>> I am new in PostgreSQL world coming from Oracle.
>> I have created two databases using command line as manual has suggested.
>> For instance I have done like the following:
>> createdb tanya
>> createdb eps
>> Now I would like to stop my "tanya" database and have "eps" database
>> running.
>> How can I do this?
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
