Re: [ADMIN] Oracle and Postgresql Play Nice Together on Same Computer?
Basically, we want to take 3 of the 10 applications running on Oracle, move them to Postgresql on the same computer/server and just make sure it runs about the same (really speed, memory usage, and space are the big issues). I'm not concerned with how hard the migration will be and things like that. The database is very low in complexity so the migration should be cake. Thank you! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, January 20, 2005 4:20 AM To: Troyston Campano Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org Subject: Re: [ADMIN] Oracle and Postgresql Play Nice Together on Same Computer? Troyston Campano [EMAIL PROTECTED] wrote on 20.01.2005, 06:03:28: I am an Oracle DBA and I want do a Postgresql 'proof of concept' at the large corporation where I work to test the benefits of using Postgresql in our environment. I want to install Postgresql onto a production server that currently runs Oracle. Are there any problems with running Postgresql and Oracle on the same machine? I mean, I've heard that the way Sybase and DB2 UDB are architected to handle memory hurts Sybase when DB2 UDB is installed on the same machine as the Sybase Server (something about UDB eating up all the memory and not giving it back to Sybase). Are there any issues running Postgresql and Oracle on the same machine.anything special to know about memory, disk layout, and things like that? I just want to make sure the two engines play together on this same server. I had a hard time finding information about this via google. There should be no issues running both on the same machine. Running both together at the same time isn't a good way of doing a benchmark though... I would question your intent slightly. Should it be a relative comparison? Or should it be an assessment of what PostgreSQL is capable of and whether that fits a sufficient number of your needs to make it worth adopting? There are many ways to structure a decision as to whether PostgreSQL is suitable for your (business?) needs. Which structure you choose is likely to prejudice your decision, one way or the other. i.e. if capital acquisition costs are the decising factor, then PostgreSQL would always win. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [ADMIN] Oracle and Postgresql Play Nice Together on Same Computer?
On Thu, Jan 20, 2005 at 08:03:42AM -0500, Troyston Campano wrote: Basically, we want to take 3 of the 10 applications running on Oracle, move them to Postgresql on the same computer/server and just make sure it runs about the same (really speed, memory usage, and space are the big issues). I'm not concerned with how hard the migration will be and things like that. So you want Postgres to be a cheaper Oracle. Hmm. Maybe it will work, but as Marco Colombo says, you are not going to see Postgres shining by using that simplistic approach. If you want that, maybe you should look closely to see where you can find the rusty corner that needs to be polished. The database is very low in complexity so the migration should be cake. If you do things as simple as select count(*) from table, then you will have to be careful to be really fair in your comparison; you could misleadingly find that Postgres is much slower on that query. -- Alvaro Herrera ([EMAIL PROTECTED]) Tulio: oh, para qué servirá este boton, Juan Carlos? Policarpo: No, aléjense, no toquen la consola! Juan Carlos: Lo apretaré una y otra vez. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Re: [ADMIN] Oracle and Postgresql Play Nice Together on Same Computer
It depends how you have your Oracle instance configured. If you have it set up so that the total SGA and UGA ammount consume around 80%-90% of total available memory, then this is obviously only going to leave postgress with 20% or less of the total available memory. Postgres is also designed to take advantage of the file caching behaviour of the OS. The OS however is probably going to prioritize Oracle running memory over cached file pages, and therefore I would think that running the two together is a less than optimal configuration. If you are going to do it, I would make sure that you at least reduce the SGA for Oracle down below 40% of the box's total RAM so you give postgresql enough space to work in. Even doing this, there is a chance that the OS will try to cache the Oracle Tablespace files if they are small enough, duplicating the buffering effort that Oracle is doing, and also reducing the amount of memory for Postgresql file cache. All in all, I would personaly be very wary of running both together if you are planning on doing any benchmarking. Alex Turner NetEconomist On Thu, 20 Jan 2005 10:20:02 +0100, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Troyston Campano [EMAIL PROTECTED] wrote on 20.01.2005, 06:03:28: I am an Oracle DBA and I want do a Postgresql 'proof of concept' at the large corporation where I work to test the benefits of using Postgresql in our environment. I want to install Postgresql onto a production server that currently runs Oracle. Are there any problems with running Postgresql and Oracle on the same machine? I mean, I've heard that the way Sybase and DB2 UDB are architected to handle memory hurts Sybase when DB2 UDB is installed on the same machine as the Sybase Server (something about UDB eating up all the memory and not giving it back to Sybase). Are there any issues running Postgresql and Oracle on the same machine.anything special to know about memory, disk layout, and things like that? I just want to make sure the two engines play together on this same server. I had a hard time finding information about this via google. There should be no issues running both on the same machine. Running both together at the same time isn't a good way of doing a benchmark though... I would question your intent slightly. Should it be a relative comparison? Or should it be an assessment of what PostgreSQL is capable of and whether that fits a sufficient number of your needs to make it worth adopting? There are many ways to structure a decision as to whether PostgreSQL is suitable for your (business?) needs. Which structure you choose is likely to prejudice your decision, one way or the other. i.e. if capital acquisition costs are the decising factor, then PostgreSQL would always win. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] Oracle to PostgreSQL
We want to export a database from Oracle and we need to know informations about : - Tablespace - Memoryspace - Logical and physical space - Diskspace - How to make the export Thanks. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Oracle and PostgreSQL servers incompatibility
Hello everybody, We plan to install on a same Solaris server an Oracle8i and a PostgreSQL 7.3.4 database. During our impact tests, we encountered a situation where no more semaphore was available for Oracle server (while we where bulk loading on postgreSQL using copy statement. There was only one PG connection while there were many on Oracle server). I quote that our kernel configuration (semaphore and shared memory parameters) had been previously correctly positionned as required in Oracle and postgreSQL documentation. I wanted to know if someone experienced this kind or other troubles in a similar configuration (having Oracle and postgreSQL on the same Unix server). Thanks for your contribution, L.Jouneau ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Oracle and PostgreSQL servers incompatibility
On Fri, Nov 14, 2003 at 12:07:40PM +0100, Jouneau Luc wrote: During our impact tests, we encountered a situation where no more semaphore was available for Oracle server (while we where bulk loading on postgreSQL using copy statement. There was only one PG connection while there were many on Oracle server). I quote that our kernel configuration (semaphore and shared memory parameters) had been previously correctly positionned as required in Oracle and postgreSQL documentation. Sure, it might be correctly configured according to those docs, but neither of them can know about other programs' semaphore use. You probably need more available semaphores from your kernel. A -- Andrew Sullivan 204-4141 Yonge Street Afilias CanadaToronto, Ontario Canada [EMAIL PROTECTED] M2P 2A8 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Oracle and PostgreSQL servers incompatibility
Tom Lane wrote: Jouneau Luc [EMAIL PROTECTED] writes: We plan to install on a same Solaris server an Oracle8i and a PostgreSQL 7.3.4 database. During our impact tests, we encountered a situation where no more semaphore was available for Oracle server (while we where bulk loading on postgreSQL using copy statement. There was only one PG connection while there were many on Oracle server). I quote that our kernel configuration (semaphore and shared memory parameters) had been previously correctly positionned as required in Oracle and postgreSQL documentation. Obviously not. I suspect you forgot to add up the semaphore requirements of each package ... Also, we grab all semaphores we will need for max connections at startup so we don't fail under load so that might have surprised you. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
RE : [ADMIN] Oracle to PostgreSQL
Marco Roda [EMAIL PROTECTED] wrote .. Hi, I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have scripts to create the base on Oracle and PostgreSQL (it is about 40 tables), but I need to port a great amount of data to PostgreSQL. Can anybody help me? I never see any documents about migrating from Oracle8 to PostgreSQL, but I've already done this kind of migration, here's my warrior's way : - Make a SQL dump of the tables (structure + constraints + sequences + data), and try to add this dump file into a PostgreSQL database (via psql). You will probably need to do several search and replaces in the SQL code in order to get it PostgreSQL compliant. But as PostgreSQL try to follow the SQL norm, it shouldn't be very difficult. - The most difficult step is to migrate the stored procedures. The biggest work is to put PL/SQL packages into PL/PGSQL functions (Unfortunately, PostgreSQL don't manages PL/PGSQL packages, that's still in the TODO list). So your packages functions contacts.add() (for example) will have to be renamed to contacts_add(); and you will also have to patch all the clients that have to access this database. This migration may be quite long, so leave the Oracle database running. Keep a note of all the changes you've done to have your SQL dump valid for PostgreSQL, and make a script (in Perl, PHP, or shell,...). When everything is OK, stop writes to the Oracle DB (allows only select queries), do the SQL dump (with the latests datas), execute your script on the sql DUMP, do the migration and switch the users to the new database. (Be sure to have the new clients ready before doing the big jump). The job of migrating from Oracle to PostgreSQL is a serious job (and sometimes difficult) but the migration wins are really interesting. I Hope this will help and that you will understand my english. :-) Regards, -- --- Bruno BAGUETTE - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Oracle to PostgreSQL
On Wed, 6 Aug 2003, Marco Roda wrote: Hi, I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have scripts to create the base on Oracle and PostgreSQL (it is about 40 tables), but I need to port a great amount of data to PostgreSQL. Can anybody help me? There's a script in the contrib directory called something like ora2pg that's been helpful to some folks. EMS makes a migration program http://www.ems-hitech.com/pgsqlutils that might help too. Haven't used it, but it's only $39 and I think they have trial versions available. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
RE : [ADMIN] Oracle to PostgreSQL
hi, At 10:25 07.08.2003 +0200, Bruno BAGUETTE wrote: Marco Roda [EMAIL PROTECTED] wrote .. Hi, I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have scripts to create the base on Oracle and PostgreSQL (it is about 40 tables), but I need to port a great amount of data to PostgreSQL. Can anybody help me? I never see any documents about migrating from Oracle8 to PostgreSQL, but I've already done this kind of migration, here's my warrior's way : - Make a SQL dump of the tables (structure + constraints + sequences + data), and try to add this dump file into a PostgreSQL database (via psql). You will probably need to do several search and replaces in the SQL code in order to get it PostgreSQL compliant. But as PostgreSQL try to follow the SQL norm, it shouldn't be very difficult. I have good experiences with: http://www.samse.fr/GPL/ora2pg/ora2pg.html Rafal ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: RE : [ADMIN] Oracle to PostgreSQL
What are the legal implications of copying Oracle's own PL/SQL procedures code and porting them to PgSQL as you suggested? Bruno BAGUETTE wrote: Marco Roda [EMAIL PROTECTED] wrote .. Hi, I need to port data from an Oracle 8.0 base to PostgreSQL 7.2. I have scripts to create the base on Oracle and PostgreSQL (it is about 40 tables), but I need to port a great amount of data to PostgreSQL. Can anybody help me? I never see any documents about migrating from Oracle8 to PostgreSQL, but I've already done this kind of migration, here's my warrior's way : - Make a SQL dump of the tables (structure + constraints + sequences + data), and try to add this dump file into a PostgreSQL database (via psql). You will probably need to do several search and replaces in the SQL code in order to get it PostgreSQL compliant. But as PostgreSQL try to follow the SQL norm, it shouldn't be very difficult. - The most difficult step is to migrate the stored procedures. The biggest work is to put PL/SQL packages into PL/PGSQL functions (Unfortunately, PostgreSQL don't manages PL/PGSQL packages, that's still in the TODO list). So your packages functions contacts.add() (for example) will have to be renamed to contacts_add(); and you will also have to patch all the clients that have to access this database. This migration may be quite long, so leave the Oracle database running. Keep a note of all the changes you've done to have your SQL dump valid for PostgreSQL, and make a script (in Perl, PHP, or shell,...). When everything is OK, stop writes to the Oracle DB (allows only select queries), do the SQL dump (with the latests datas), execute your script on the sql DUMP, do the migration and switch the users to the new database. (Be sure to have the new clients ready before doing the big jump). The job of migrating from Oracle to PostgreSQL is a serious job (and sometimes difficult) but the migration wins are really interesting. I Hope this will help and that you will understand my english. :-) Regards, ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Oracle data - PostgreSQL
We're currently in the process of migrating an e-commerce customer off a
24x7 Oracle system that includes standby databases,
production-to-development refresh processes, data warehousing, etc. Many of
the tables run in the millions of rows. One runs almost 20 million rows. Not
big in enterprise-class database terms but big enough that existing Perl
approaches fell over almost immediately. The ora2pg scripts in particular
had so many errors: bad datatype conversions, bad data conversions,
exceptionally poor performance on large tables, etc. that we eventually gave
up on them. Most errors were potentially repairable but fixing the
performance just wasn't happening and we got tired of chasing bugs and
wondering what was going to bite us next.
We found the best solution to be using Oracle procedures written in PL/SQL
to do the schema and data extractions. The process was much cleaner and
significantly (sometimes order-of-magnitude) faster. With the schema and
data properly extracted the load process can be managed more cleanly and the
old Oracle data is right there ready to archive to CD/etc. in a
non-Oracle-specific data format before you shut down Oracle for the last
time. After all, you don't want to have to find an old copy of Oracle to use
when you discover you might have had a data conversion problem. For example,
forgetting to set the NLS_DATE_FORMAT when you extract your date fields
thereby losing the timestamps. Been there, done that. Don't want to go there
again.
There are numerous other issues not mentioned in the existing online
documentation such as it is. Things like properly extracting lob and long
data, numerous variations in DDL, dealing with Pg's inability to skip
validation of foreign key constraints (serious time sink on conversion and
even one broken link causes failure), etc. We're working to create a package
of Oracle procedures and documenation for this process and hope to release
it in the future. In the meantime take the ora2pg and online documentation
as a simplistic starting point and expect to put some serious energy into
migrating an Oracle installation of any complexity. It *can* be done. But
it's not going to be as simple as firing up ora2pg and coming back in a
couple hours.
ss
Scott Shattuck
President/CEO
Technical Pursuit Inc.
- Original Message -
From: Michael G. Martin [EMAIL PROTECTED]
To: Leonardo Camargo [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, July 03, 2002 2:25 PM
Subject: Re: [ADMIN] Oracle data - PostgreSQL
If I were tasked to do it, I'd probably use PERL and DBI/DBD. Once you
get all the Oracle tables converted and built in Postgres, you could
write a PERL program to open a database descriptor to your oracle
database and postgres database at the same time. Then, copy all the
records in each table from one descriptor to the other.
Here is a code snip I use to syncronize some tables across two postgres
dbs, but one descriptor could easily be an oracle descriptor. $dbhM is
the master descriptor, and $common::dbh is the local descriptor.
As long as your data types are consistant across the tables, you
shouldn't have too many problems.
If you want to do two steps, you can always write a custom dump program
for each table in some delimitted format from oracle, then write a
loader to put the data back in. This may also be a better option if you
are unable to access both databases at the same time.
sub syncTable {
#sync table from primary
my $table=shift(@_);
my $fromDate=shift(@_); #inclusive date to start
my $fromDateStr=;
if (defined $fromDate $fromDate eq ) {
undef $fromDate;
}
my $sth;
if (defined $fromDate $fromDate ne ) {
$sth=$dbhM-prepare(select * from $table where date = '$fromDate');
$fromDateStr=From Date $fromDate.;
}
else {
$sth=$dbhM-prepare(select * from $table);
}
$sth-execute();
if ($DBI::err) {
warn (Error processing request. $DBI::errstr);
return;
}
my $totalRows=$sth-rows;
my $numFields=$sth-{NUM_OF_FIELDS};
print Syncronizing table $table from $dbConfig::dbPrimaryHost
($totalRows rows. $numFields columns. $fromDateStr )\n;
$common::dbh-{AutoCommit} = 0;
if (! defined $fromDate) {
#common::doSql(truncate table $table);
common::doSql(delete from $table);
}
else {
common::doSql(delete from $table where date = '$fromDate');
}
my $insertSql=insert into $table values (;
for (my $i=0; $i $numFields; $i++) {
if ($i 0) {
$insertSql.=,;
}
$insertSql.=?;
}
$insertSql.=);
my $sthLocal=$common::dbh-prepare($insertSql);
my $count=0;
while (my @row=$sth-fetchrow_array()) {
$sthLocal-execute(@row);
$count++;
if ($count % 1000 == 0) {
print $table $count / $totalRows records\n;
}
}
$common::dbh-{AutoCommit} = 1;
}
--Michael
Leonardo Camargo wrote:
Hi
How do i migrate data from an Oracle db
Re: [ADMIN] Oracle data - PostgreSQL
We're working to create a package of Oracle procedures and documenation for this process and hope to release it in the future. Release early and often. Cheers, -- Tim Ellis DBA, Gamet ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Oracle data - PostgreSQL
Leonardo Camargo wrote: Hi How do i migrate data from an Oracle db to a PostgreSQL db ? Is there an article, paper, site, howto, aboutm it? Any point would be helpful Sure, see /contrib/oracle/ora2pg and techdocs.postgresql.org. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] Oracle data - PostgreSQL
If I were tasked to do it, I'd probably use PERL and DBI/DBD. Once you
get all the Oracle tables converted and built in Postgres, you could
write a PERL program to open a database descriptor to your oracle
database and postgres database at the same time. Then, copy all the
records in each table from one descriptor to the other.
Here is a code snip I use to syncronize some tables across two postgres
dbs, but one descriptor could easily be an oracle descriptor. $dbhM is
the master descriptor, and $common::dbh is the local descriptor.
As long as your data types are consistant across the tables, you
shouldn't have too many problems.
If you want to do two steps, you can always write a custom dump program
for each table in some delimitted format from oracle, then write a
loader to put the data back in. This may also be a better option if you
are unable to access both databases at the same time.
sub syncTable {
#sync table from primary
my $table=shift(_);
my $fromDate=shift(_); #inclusive date to start
my $fromDateStr=;
if (defined $fromDate $fromDate eq ) {
undef $fromDate;
}
my $sth;
if (defined $fromDate $fromDate ne ) {
$sth=$dbhM-prepare(select * from $table where date = '$fromDate');
$fromDateStr=From Date $fromDate.;
}
else {
$sth=$dbhM-prepare(select * from $table);
}
$sth-execute();
if ($DBI::err) {
warn (Error processing request. $DBI::errstr);
return;
}
my $totalRows=$sth-rows;
my $numFields=$sth-{NUM_OF_FIELDS};
print Syncronizing table $table from $dbConfig::dbPrimaryHost ($totalRows rows.
$numFields columns. $fromDateStr )\n;
$common::dbh-{AutoCommit} = 0;
if (! defined $fromDate) {
#common::doSql(truncate table $table);
common::doSql(delete from $table);
}
else {
common::doSql(delete from $table where date = '$fromDate');
}
my $insertSql=insert into $table values (;
for (my $i=0; $i $numFields; $i++) {
if ($i 0) {
$insertSql.=,;
}
$insertSql.=?;
}
$insertSql.=);
my $sthLocal=$common::dbh-prepare($insertSql);
my $count=0;
while (my row=$sth-fetchrow_array()) {
$sthLocal-execute(row);
$count++;
if ($count % 1000 == 0) {
print $table $count / $totalRows records\n;
}
}
$common::dbh-{AutoCommit} = 1;
}
--Michael
Leonardo Camargo wrote:
Hi
How do i migrate data from an Oracle db to a PostgreSQL db ?
Is there an article, paper, site, howto, aboutm it?
Any point would be helpful
Thnx in advance.
Kal
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
