Download as PDF, PPTX
![Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Transactional DDL BEGIN; CREATE TABLE foo ( id BIGSERIAL PRIMARY KEY, fname TEXT NOT NULL CHECK(fname ~ ‘[^[:alnum:]_]’) ); COMMIT/ROLLBACK; BEGIN; ALTER TABLE foo ADD COLUMN lname TEXT NOT NULL; SELECT test_foo_column_laname(); COMMIT/ROLLBACK;](https://image.slidesharecdn.com/postgresfororaclepeople-170112164842/75/PostgreSQL-for-Oracle-Developers-and-DBA-s-24-2048.jpg)
Uploaded byGerger
PostgreSQL for Oracle Developers and DBA's
This document provides information about PostgreSQL and Command Prompt Inc. It discusses that Command Prompt offers professional services for migrating from Oracle to PostgreSQL. It then covers aspects of PostgreSQL like its licensing, large international community, low costs, mature codebase, enterprise features, and technical capabilities like its SQL implementation, replication, foreign data wrappers and user-defined functions. It also notes tools that can help ease migration from Oracle and some differences from Oracle.
PostgreSQL for Oracle Developers and DBA's
- 1.
- 2. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc ProfessionalServices For professional services or support contracts for all of your Oracle to PostgreSQL or Open Source needs, please contact CMD at: ● +1.503.667.4564 ● sales@commandprompt.com ● https://www.commandprompt.com/
- 3. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc WhoAm I ● @jdatcmd/@linuxhiker ● jd@commandprompt.com ● Lead Architect – Command Prompt, Inc. ● Director & Founder – PgUS (501c3)
- 4. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc WhyPostgreSQL ● Licensing ● Community ● Business ● Tech!
- 5. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Licensing ●BSD licensed ● Can I?
- 6. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc CanI? ● Fork it?: Yes ● Close it?: Yes ● Modify it?: Yes ● Not share it?: Yes ● Throw it away?: Yes ● Sue you?: Nein!
- 7. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Community ●Large International Community: – Postgresql.org ● Postgresql.us ● Postgresql.eu – Germany, Nordic, France, Italy... ● Postgresql.jp ● Postgres.cn
- 8.
- 9. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Costs ●PostgreSQL is 100% free. – Zero cost to download – Zero cost to install – Zero cost to support (assuming expertise)
- 10. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Establishedcompanies (Support) ● Command Prompt, Inc (oldest, yes that’s us) ● Crunchy ● Credativ ● OmniTI ● OpenSCG ● 2ndQuadrant (list goes on and on)
- 11. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc EnterpriseDB? ●They specialize in a closed source fork not Open Source PostgreSQL
- 12. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Tech ●The basics ● Features ● Quirks
- 13. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Thebasics ● ACID Compliant ● Rich datatypes – Standards such as varchar(), integer, numeric etc.. – XML, JSON/B, Money, Hstore... ● Rich builtin functions – to_char(),to_date(),overlaps(),enum_first(),xpath(),greatest(),in(),any(),some(),trim,overlay()... ● Constraints – CHECK, PRIMARY KEY, NOT NULL… ● Referential Integrity – Foreign Keys (Deferrable, Initially Deferred) ● Partitioning ● SCHEMA (namespaces)
- 14. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Features ●Solid SQL implementation ● Mature code base ● “Enterprise” ● Cool stuff’ ● Of note
- 15. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc SolidSQL Implementation No current version of any database management system claims full conformance to Core SQL:2011. PostgreSQL supports most of the major features of SQL:2011. Out of 179 mandatory features required for full Core conformance, PostgreSQL conforms to at least 160. In addition, there is a long list of supported optional features.
- 16. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc ISO/IECSQL Implementation ● ISO/IEC 9075-1 (SQL/Framework) ● ISO/IEC 9075-2 (SQL/Foundation) ● ISO/IEC 9075-9 (SQL/MED) – FDW, DBI-LINK ● ISO/IEC 9075-11 (SQL/Schemata) ● ISO/IEC 9075-14 (SQL/XML) – Native XML handling and functions ● ISO/IEC 9075-13 (SQL/JRT(Java)) – PL/JAVA
- 17. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Maturecode base PostgreSQL started as Ingres at the University of California, Berkeley in the 1970s. Current iterations have been in continual development since 1997.
- 18. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Enterprisefeatures ● It’s Free! No licensing audits. ● FDW ● Backup and recovery ● Replication ● Table spaces ● Transactional DDL ● UDF – We don’t have packages
- 19. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc It’sfree. No licensing audits ● Oracle has spawned an entire industry to defend against the possibility of an audit. ● PostgreSQL has spawned an entire industry letting businesses and developers use a database.
- 20. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc FDW ForeignData Wrappers Connect to external data sources including Oracle! (CREATE DATABASE LINK)
- 21. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Backupsand Recovery ● Online logical backups (pg_dump/pg_dumpall) ● Online binary backups (pg_basebackup, pgBackrest, (tar,cpio,cp,rsync)) ● Point In Time Recovery (PITR)
- 22. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Replication ●Logical Replication (pgLogical, Slony, Londiste) ● Binary Replication (Warm,Cold and Hot Standby). SYNC AND ASYNC ● Load balancing – Pgpool-II->Master->(n)Slaves
- 23. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc TableSpaces Postmaster Warehouse TBLSpace Transactional TBLSpace Indexes
- 24. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc TransactionalDDL BEGIN; CREATE TABLE foo ( id BIGSERIAL PRIMARY KEY, fname TEXT NOT NULL CHECK(fname ~ ‘[^[:alnum:]_]’) ); COMMIT/ROLLBACK; BEGIN; ALTER TABLE foo ADD COLUMN lname TEXT NOT NULL; SELECT test_foo_column_laname(); COMMIT/ROLLBACK;
- 25. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc UDF ●Can be written in a number of languages – C – SQL – PL/PgSQL (similar to PL/SQL) – Python – Ruby – Perl – V8 (JavaScript) – Java – Lots more
- 26. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Coolstuff ● Savepoints ● Two Phase Commit – XA ● SELinux integration ● Column, Row permissions (versus just relation) ● Extensions ● GIS/Postgis
- 27. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Savepoints ●Similar to Oracle: BEGIN; INSERT INTO table1 VALUES (1); SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (2); ROLLBACK TO SAVEPOINT my_savepoint; INSERT INTO table1 VALUES (3); COMMIT;
- 28. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc TwoPhase Commit ● Intended use by external transaction managers – XA implementation via JDBC driver ● Can be used via SQL ● Review: max_prepared_transactions configuration parameter ● Always clean up, will interfere with maintenance
- 29. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc selinux ●For more information visit: https://wiki.postgresql.org/wiki/SEPostgreS QL_SELinux_Overview
- 30. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Column/RowPermissions ● Column level privs are set using standard GRANT/REVOKE ● ROW level privs are set via a policy method: https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html
- 31. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Extensions ●Column Database (Citus) ● Auditing (PgAudit) ● Case insensitive index search (Citext) ● And more (pgxn.org)
- 32. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Ofnote ● Use TEXT for CLOB, BYTEA (or lo) for BLOB ● NULL is different: postgres=# create table bar(a text); CREATE TABLE postgres=# insert into bar values(''); postgres=# select * from bar where a IS NULL; a --- (0 rows) postgres=# insert into bar values(NULL); postgres=# select * from bar where a IS NULL; a --- (1 row) ● PostgreSQL has a boolean data type.
- 33. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc GIS/PostGIS ●Full Geometric support including – Datatypes – Functions ● Extended support through PostGIS (external project).
- 34. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Ofnote (2) ● No connect by – Use “WITH RECURSIVE” ● PostgreSQL is much more reliant on the host operating system than Oracle. Make sure you are running an LTS, UNIX (Solaris) or FreeBSD.
- 35. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Toolsfor easing migration ● Ora2pg – Complete migration toolkit ● Orafce – Implements compatibility layers that are 10g compatible including but not limited to: ● Oracle date functions ● Oracle.date data type ● Oracle data operators etc...
- 36. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Whatwe don’t have ● No RAC ● No Packages ● No Flashback (on main system, can be achieved via PITR/Cold standby)
- 37. Command Prompt, Inc. http://www.commandprompt.com/ @cmdpromptinc Questions? ●Business ● Community ● Technical