Oracle vs. PostgreSQL - a comment
Hi, I know, this list is not for this, but I just couldn't resist. Please forgive me. Being an Oracle DBA for two decades now (back then starting with Oracle 8.0.5) and only doing PostgreSQL since version 10.3, I feel compelled to share some of my experiences with both. Quick facts: All installations were performed on the same host except for Oracle Data Guard and Patroni with etcd, which both require at least three nodes, or an odd number >2 respectively) to establish a democracy to determine the master/primary and replica/standby databases. However, all machines have the same hardware and operating system: OS: openSUSE Leap 15.1 (server setup, not desktop) CPU: Intel i7-7700T CPU RAM: 32 GB Disk Hardware: SSD Also, Oracle requires 161 additional packages to be installed, many of which are 32-bit packages, for a supposedly 64-bit only software! This results in 150 MB additional disk space needed and swamps the system with 32-bit packages! PostgreSQL only requires a few packages to be installed depending on the options one chooses to compile the source with. Anyway, none of these packages require a 32-bit version! Size of installation: Software: $ du -sh /data/postgres/12.3 /data/oracle/product/19.6 62M /data/postgres/12.3 8.5G/data/oracle/product/19.6 Databases: $ du -sh /data/oradb/* 3.3G/data/oradb/cdb01# Oracle Container w/ 1 PDB 1.8G/data/oradb/sdb01# Oracle stand alone database $ du -sh /data/pgdb/sdb01 659M/data/pgdb/sdb01 # PostgreSQL 12.3 database cluster All databases are a clean setup, no schemas, users, tables, data, etc. Just an empty base. Installation: Oracle 19c: ~2h unzip to ORACLE_HOME runInstaller unzip newest OPatch p6880880_20_Linux-x86-64 apply p30797938_19_Linux-x86-64 (19.6.1) PostgreSQL 12.3 compiled from source: ~3m30s bunzip postgresql-12.3.tar.bz2 to PGHOME make install-world Create database: Oracle: create stand alone database: ~30m create container database (cdb$root): ~47m create pluggable database (pdb): ~26s Memory to run a database reasonably well: at least 1 GB SGA for a stand alone database at least 4 GB SGA for a container database PostgreSQL: initdb: <1s create database: <200ms Memory: 128 MB db_buffers Start/stop database: Oracle: startup (standard, 1 GB SGA): ~15s shutdown immediate (standard, 1 GB SGA): ~21s startup (container, 4 GB SGA):~16s shutdown immediate (container, 4 GB SGA): ~23s PostgreSQL: pg_ctl start: 0.1s pg_ctl stop: 0.2s Other discoveries/experiences: Oracle: Set up Data Guard (2 nodes) with observer (3. node): ~4h Applying a PSU or RU often requires downtime of 60m-90m. Migrating a major version often requires downtime of 60m-90m. Migrating a new major version requires a lot of work in advance. Switching Data Guard takes ~1m. PostgreSQL: Set up Patroni (2 nodes) with etcd (3 nodes): ~30m Applying a new minor version requires downtime of <2s. Migrating a new major version requires downtime of <20s. Migrating a new major version requires a few minutes work in advance. Switching Patroni takes ~1s. Oracle has some good concepts. I like the conecpt of separate UNDO and TEMP tablespaces and not having to care about vacuuming. Also, I like the idea of global container/cluster-wide views such as CDB_TABLES, etc., a thing which I definitely and seriously miss about PostgreSQL. What I especially hate about Oracle (despite the license costs, of course) is that it has so many bugs, bugs and even more bugs and one keeps on searching for patches all day, generating lot of downtime. Applying a PSU or RU is mostly not enough. So bottom line, PostgreSQL beats Oracle by far in my opinion, at least as far as installing it and sizes are concerned. So, guess what I think is wrong with Oracle after 20 years of working with it... Sorry for the rant. ;-) Cheers, Paul
Re: Oracle vs. PostgreSQL - a comment
Paul Förster writes: > Hi, > > I know, this list is not for this, but I just couldn't resist. Please forgive > me. > > Being an Oracle DBA for two decades now (back then starting with Oracle > 8.0.5) and only doing PostgreSQL since version 10.3, I feel compelled to > share some of my experiences with both. > > Quick facts: > > All installations were performed on the same host except for Oracle Data > Guard and Patroni with etcd, which both require at least three nodes, or an > odd number >2 respectively) to establish a democracy to determine the > master/primary and replica/standby databases. However, all machines have the > same hardware and operating system: > > OS: openSUSE Leap 15.1 (server setup, not desktop) > CPU: Intel i7-7700T CPU > RAM: 32 GB > Disk Hardware: SSD > > Also, Oracle requires 161 additional packages to be installed, many of which > are 32-bit packages, for a supposedly 64-bit only software! This results in > 150 MB additional disk space needed and swamps the system with 32-bit > packages! > > PostgreSQL only requires a few packages to be installed depending on the > options one chooses to compile the source with. Anyway, none of these > packages require a 32-bit version! > > Size of installation: > > Software: > $ du -sh /data/postgres/12.3 /data/oracle/product/19.6 > 62M /data/postgres/12.3 > 8.5G/data/oracle/product/19.6 > Databases: > $ du -sh /data/oradb/* > 3.3G /data/oradb/cdb01# Oracle Container w/ 1 PDB > 1.8G /data/oradb/sdb01# Oracle stand alone database > $ du -sh /data/pgdb/sdb01 > 659M /data/pgdb/sdb01 # PostgreSQL 12.3 database cluster > > All databases are a clean setup, no schemas, users, tables, data, etc. Just > an empty base. > > Installation: > Oracle 19c: ~2h > unzip to ORACLE_HOME > runInstaller > unzip newest OPatch p6880880_20_Linux-x86-64 > apply p30797938_19_Linux-x86-64 (19.6.1) > PostgreSQL 12.3 compiled from source: ~3m30s > bunzip postgresql-12.3.tar.bz2 to PGHOME > make install-world > > Create database: > Oracle: > create stand alone database: ~30m > create container database (cdb$root): ~47m > create pluggable database (pdb): ~26s > Memory to run a database reasonably well: > at least 1 GB SGA for a stand alone database > at least 4 GB SGA for a container database > PostgreSQL: > initdb: <1s > create database: <200ms > Memory: 128 MB db_buffers > > Start/stop database: > Oracle: > startup (standard, 1 GB SGA): ~15s > shutdown immediate (standard, 1 GB SGA): ~21s > startup (container, 4 GB SGA):~16s > shutdown immediate (container, 4 GB SGA): ~23s > PostgreSQL: > pg_ctl start: 0.1s > pg_ctl stop: 0.2s > > Other discoveries/experiences: > Oracle: > Set up Data Guard (2 nodes) with observer (3. node): ~4h > Applying a PSU or RU often requires downtime of 60m-90m. > Migrating a major version often requires downtime of 60m-90m. > Migrating a new major version requires a lot of work in advance. > Switching Data Guard takes ~1m. > PostgreSQL: > Set up Patroni (2 nodes) with etcd (3 nodes): ~30m > Applying a new minor version requires downtime of <2s. > Migrating a new major version requires downtime of <20s. > Migrating a new major version requires a few minutes work in advance. > Switching Patroni takes ~1s. > > Oracle has some good concepts. I like the conecpt of separate UNDO and TEMP > tablespaces and not having to care about vacuuming. Also, I like the idea of > global container/cluster-wide views such as CDB_TABLES, etc., a thing which I > definitely and seriously miss about PostgreSQL. > > What I especially hate about Oracle (despite the license costs, of course) is > that it has so many bugs, bugs and even more bugs and one keeps on searching > for patches all day, generating lot of downtime. Applying a PSU or RU is > mostly not enough. > > So bottom line, PostgreSQL beats Oracle by far in my opinion, at least as far > as installing it and sizes are concerned. > > So, guess what I think is wrong with Oracle after 20 years of working with > it... > > Sorry for the rant. ;-) > > Cheers, > Paul I also worked with Oracle for 20 years, mainly as a developer, but some DBA work as well. I didn't encounter the bugs you seem to have unless I wondered off into their 'add-ons'. Sticking with the base RDBMS, I found it to be pretty solid and reliable. However, I prefer Postgres. The main reason is that with Oracle, you really need to choose one road or the other - either be a developer or be a DBA. This is especially true with the introduction of things like DAtaGuard, Gol
Re: lib/libecpg.so.6.11 && valgrind
On Fri, May 29, 2020 at 02:37:29PM +0200, Matthias Apitz wrote: > We're getting to the finish line in the port of our servers to > PostgreSQL 11.4. and started valgrind'ing the code, as the PostgreSQL is > new in the servers. Of course we have our own homework to do, but there > are also complaints of valgrind pointing to the lib/libecpg.so.6.11, for > example: > > ==9773== 9 bytes in 1 blocks are still reachable in loss record 3 of 52 > ==9773==at 0x4C2E2E3: malloc (vg_replace_malloc.c:299) > ==9773==by 0x1439F749: strdup (in /lib64/libc-2.26.so) > ==9773==by 0x146DF448: ecpg_strdup (in > /usr/local/sisis-pap/pgsql/lib/libecpg.so.6.11) > ==9773==by 0x146E048E: ECPGconnect (in > /usr/local/sisis-pap/pgsql/lib/libecpg.so.6.11) > ==9773==by 0xB03E846: DB_opdbP (dbcall.pgc:794) > ==9773==by 0xB03DFDB: DB_opdb (dbcall.pgc:556) > ==9773==by 0x5CC22F2: EC_General::OpenDatabase(_SQL_EXCL) > (eC_general.C:121) > ==9773==by 0x40AEB8: SlnpInitDaemon (ACQDaemon.C:644) > ==9773==by 0x40A878: main (ACQDaemon.C:213) > > How the PostgreSQL community likes us to deal with this? ECPGconnect() in src/interfaces/ecpg/ecpglib/connect.c is rather careful in freeing any resource it allocates with ecpg_strdup() in the various code paths where the call returns, particularly after calling PQconnectdbParams(), and a quick installcheck run on a valgrind'ed instance does not show me any leaks similar to what you have here after testing on HEAD and REL_11_STABLE. It is likely possible that we are missing something though, so could you send a test case to reproduce what you are seeing? -- Michael signature.asc Description: PGP signature
Postgresql 9.6 -> AWS RDS Postgresql 12.2 with pg_logical
Hello, I need to replicate Postgresql 9.6 to AWS RDS Postgresql 12.2 with pg_logical. AWS RDS Pg 12.2 (target) only supports pg_logical 2.3.0. Can I use v2.3.1 on the source and v2.3.0 on the target? Thank you in advance Igor
Re: Oracle vs. PostgreSQL - a comment
Hi Tim, > On 30. May, 2020, at 23:14, Tim Cross wrote: > I didn't encounter the bugs you seem to have unless I wondered off into their > 'add-ons'. use Oracle Text for example and you'll sooner or later run into severe bugs. My current favorite is ORA-20084 which bugs me for almost a year now. Text index corruption is really painful and Oracle does not feel inclined to fix it. > Sticking with the base RDBMS, I found it to be pretty solid and reliable. yes, but who does that? We have loads of third party applications which do all the stuff we (DBAs) don't want them to do. > The oracle installation process is horrible. that's why I scripted the whole create database thing, including PDBs, and their parameters, file paths, etc. For example, my script to create a container database is 782 lines long, whereas PostgreSQL just needs an "initdb". And my script to create a PDB still has 277 lines whereas in PostgreSQL, you can do it with a simple "create database" line. Even moving a database to another path is a nightmare as you'd have to create new controlfiles, etc. With PostgreSQL you just change the PGDATA variable after moving/copying the whole database cluster and that's it. Well, if you copy it and want to run both at the same time, you still have to change the port in postgresql.conf of course. Installing database software and creating a database is pretty easy compared to other Oracle stuff. Did you ever install an Oracle Enterprise Manager or Oracle Universal Directory? Don't! That's when the pain really starts. > Worse still, some of those bugs have been there for 20 years and are just > 'known' issues experienced DBAs deal with. Their documentation site is > also horrible. sic! :-) > The way they handle indexes and updates is also much faster than postgres and > you have to worry less about the structure of your queries with respect to > performance. and then, some day, a developer approaches a DBA with a query which is generated and, if printed out in a 11pt. sized font, can fill a billboard on a street, to optimize it or search for what's wrong with it, or why it performs so slow... That's usually when I play BOFH because I'm not willing to debug 10 pages which its creator hasn't even cared to take a look at first. :-P :-) Same goes for the app guys sending me 10 MB of Java stack trace by email containing one single line of ORA-x. They should send only that line along with a (approximate) time when it occurred. If I get the full stack trace, I send it back to them telling them they should come back when they find the line containing the ORA message. They usually don't come back because they don't know how to grep. :-) Some do, though, and those are the ones I try to help. > If I'm in an environment where someone else is responsible for all the DBA > stuff, Oracle is nice to work with. yes, leave the cardiac arrest to us DBAs. :-P > apart from this, Oracle licensing is an absolute nightmare. Apart from > the expense, the complexity is unbelievable and it is almost impossible > to know with any certainty what you will be paying in 12, 24 or more > months. that's why we are migrating more and more to PostgreSQL and why I came in touch with PostgreSQL at all. There will be some applications which are bound to Oracle and which we never get rid of but we move as many applications to PostgreSQL as is possible to reduce license costs. And it already has payed out! Cheers, Paul
