Transcription
Czech and Slovak PostgreSQL Users GroupPostgreSQL for Oracle DBAOracle database logo should be there. but as of this page it can't be:.Remember that you are generally not able to use Oracle logos unlessyou are specifically licensed or authorized to use them. .Aleš Zelený (zeleny.ales@gmail.com), CSPUG, CC BY-SA
PostgreSQLAgendaThe world's most advancedopen source database. Mind Migration Some terminology “Architecture” Security Backup and Recovery High Availability / Disaster recovery Other unordered stuff to consider17.2.2016PostgreSQL for Oracle DBAs2
Purpose of these slidesPostgreSQLThe world's most advancedopen source database. Understand differences Migration guide Commons in behavior RDBMS ranking Displeasure prevention Feature matrix17.2.2016PostgreSQL for Oracle DBAs3
Mind Migration PostgreSQLThe world's most advancedopen source database.PostgreSQL is not an Oracle databaseOracle and PostgreSQL are both superb databases,their relation is not like Red Hat and CentOSDo not expect equivalents for all of the OracleRDBMS features in PostgreSQLDon't hesitate to be impressed by PostgreSQL broadrange of data typesPostgreSQL has extensions17.2.2016PostgreSQL for Oracle DBAs4
PostgreSQLTerminology The world's most advancedopen source database.Architecture/concepts �Tuple Wiki: A tuple is a finite ordered list of elements–Cluster–Instance––Yes, a cluster can simply mean a grouping ofrelated things, but context is everything.Shaun M. Thomasquick browse through manuals will help anddon't take ages–Architectural Fundamentals–Documentation index17.2.2016PostgreSQL for Oracle DBAs5
Oracle and DB relation PostgreSQLThe world's most advancedopen source database.From installed software to database (simplified)–Instance is software loaded into memory workingwith ONE database (12c PDB changed that rule)Oracle SW installationORACLE HOME/u01/app/oracle/product/11.2.0.4/dbOracle SW installationORACLE HOME/u01/app/oracle/product/12.1.0.2/dbOracle instanceORACLE SIDSALESOracle instanceORACLE SIDEMPLOYEEOracle instanceORACLE SIDDWHOracle instanceORACLE tabaseINWEB17.2.2016PostgreSQL for Oracle DBAs6
PostgreSQL and DB relation PostgreSQLThe world's most advancedopen source database.From installed software to database (simplified)–SW installed from RPM/APT/compiled from sourcePostgreSQL installation 9.4POSTGRESQL HOME/usr/pgsql-9.4/ : /usr/pgsql-9.4/libPostgreSQL instanceRunning postgres te017.2.2016Databasetemplate1PostgreSQL installation 9.3POSTGRESQL HOME/usr/bin/postgres : /usr/lib64/libpq.soPostgreSQL instanceRunning postgres resDatabaseINWEBPostgreSQL for Oracle nsApplicationsdatabasesdatabases7
Pg & Oracle – compared PostgreSQLThe world's most advancedopen source database.From installed software to database (simplified)PostgreSQL installation 9.4POSTGRESQL HOME/usr/pgsql-9.4/ : /usr/pgsql-9.4/libOracle SW installationORACLE HOME/u01/app/oracle/product/12.1.0.2/dbPostgreSQL instanceRunning postgres processPGDATA/var/lib/pgsql/9.4/datatemplate0 template117.2.2016postgresINWEBDWHOracle instanceORACLE SIDDWHOracle instanceORACLE SIDINWEBDatabaseDWHDatabaseINWEBSALESPostgreSQL for Oracle DBAs8
PostgreSQLOracle – DB physical structureThe world's most advancedopen source database. Oracle RAC (Real Application Clusters)–Even with RAC setup, an Oracle instance serves ONE database Multitenant pluggable databases in version 12 breaks that rule.Oracle database physical components (files)Host: firstInstance: MYDB1Host: secondInstance: MYDB2Parameter filePassword fileData filesHost: thirdInstance: MYDB3Control files Redo LogfilesHost: .nthInstance: MYDB N Archived LogsDatabaseMYDBUNDO TS17.2.2016PostgreSQL for Oracle DBAs9
PostgreSQLPostgres – cluster physical structureThe world's most advancedopen source database.Not means clustering like Oracle RAC Postgres uses directory (might be referred by environment variable PGDATA) traditionallycalled database cluster to store all necessary data managed by Postgres instance–Which is mandatory parameter for starting Postgres instance–Contains configuration files, and in default setup also files for all databases residing within aparticular Postgres cluster, see documentation Database File Layout-bash-4.3 ps -fu postgresUIDPID PPID C STIME TTYpostgres 30007 1 0 19:03 ?base/global/pg clog/pg hba.confpg ident.confpg log/pg multixact/pg notify/pg serial/pg snapshots/17.2.2016database clusterTIME CMD00:00:00 /usr/bin/postgres -D /var/lib/pgsql/data -p 5433pg stat/pg stat tmp/pg subtrans/pg tblspc/pg twophase/PG VERSIONpg onfiguration filesDatabases directorypostgres # select oid, datname frompg database;-bash-4.3 du -sh base/*oid datnamebase/1------- ----------- 6.4Mbase/129681 template1 6.4Mbase/1297312968 template0 6.5M12973 postgrestree base head -5(3 rows)base 1 12706PostgreSQL for Oracle DBAs10 12706 fsm 12706 vm
PostgreSQLPostgres – mapping to Oracle filesThe world's most advancedopen source database. Postgres similar to Oracle might create archive of online logs for PITRand other purposes–Instead of “Oracle Archiver” server processes, Postgres used to call any externalcommand responsible for copying inactive online log to some other destination-bash-4.3 ps -fu postgresUIDPID PPID C STIME TTYpostgres 30007 1 0 19:03 ?base/global/pg clog/pg hba.confParameterpg ident.conffilepg log/pg multixact/pg notify/pg serial/pg snapshots/database clusterTIME CMD00:00:00 /usr/bin/postgres -D /var/lib/pgsql/data -p 5433Configuration filespg stat/Databases directorypg stat tmp/pg subtrans/postgres # select oid, datname frompg database;pg tblspc/oid datnamepg twophase/tree base head -5------- ----------basePG VERSION1 template1 1Data filespg xlog/ Online Redo12968 template0 12706postgresql.confParameter file 12973 postgres 12706 fsm(3 rows)postmaster.opts 12706 vmpostmaster.pid17.2.2016Control files11archive command .Password filePostgreSQL for Oracle DBAsdocumentationArchived Logs
Tablespaces and filesystems The world's most advancedopen source database.Tablespaces might reside on different filesystem–Outage prevention–Data & storage tier life cycle management PostgreSQLOnline active data on SSDArchive data on rotating disksTablespace for temporary files – fast might be unprotectedstorage, no data loss riskWiki page - File System Layouts17.2.2016PostgreSQL for Oracle DBAs12
Where is UNDO tablespace? PostgreSQLThe world's most advancedopen source database.Answer: inside the data filesIs this feature free of charge?No, space maintenance (vacuum) is needed to avoidtable bloat.Data change trn. New data in table blockBefore image isStored in UNDOsegmentUndo segmentsCyclic buffer17.2.2016Postgres manages data consistencyusing MVCC model (MultiversionConcurrency Control)–Transaction isolation for each session–Snapshot of data visible to each sessionbased on transaction number–Minimize locking contention–Readers never blocks writes–Serializable snapshot Isolation is availablePostgreSQL for Oracle DBAs13
Online REDO WAL files PostgreSQLThe world's most advancedopen source database.Online REDOlogs are cyclic buffer in Oracle–“cleaned up” by archiver process–Static amount of redolog Groups each with one ormore members within a redolog group–Log switch tunningWAL – Write Ahead Log files (XLOGs)–“cyclic buffer space” with only soft limit in size–File reuse – rename already archived file–archive command is used called each time WAL isswitched to new file (there is no “archiver” process inpostgres)17.2.2016PostgreSQL for Oracle DBAs14
PostgreSQLWAL files The world's most advancedopen source database.Place them on separate filesystem–Up to 9.4 space requirement for XLOG filesystem –(2 checkpoint completion target) *checkpoint segments 1 or checkpoint segments wal keep segments 1 files. Each segment file is normally16 MB.Starting with 9.5 wal min size (default 80MB 5 xlogs) and wal max size(default 1GB 64 xlogs)–Amount of WAL segments between automatic WALcheckpoint (higher values potentially more data fordatafiles recovery after server crash)–Both configurations are SOFT limit only17.2.2016PostgreSQL for Oracle DBAs15
Archive mode and WAL level The world's most advancedopen source database.Archive mode– PostgreSQLoff, on, always (archive again on streaming replica)wal level–minimal –Archive –Used for streaming replication Oracle DataGuardhot standby 17.2.2016Used for crash recovery only Oracle noarchivelogUsed for streaming replication with read only access toreplica Oracle Active DataGuardPostgreSQL for Oracle DBAs16
PostgreSQLMemoryThe world's most advancedopen source database.InstanceOracle structure:SGAShared poolParameters relation .Data buffercacheOraclePostgresdb cache sizeshared bufferssort area size(pga aggregate target)work mem,temp bufferslog buffer sizewal buffersSMONDBW0PMONmaintetance work memeffective cache sizePostgres structure:Nice description can befound at link:PostgreSQL 9.0 Architecture17.2.2016 Data Dictionary CacheCKPTWork memMaintenanace work memTemp bufferCatalog cacheOptimizer/executorPostgreSQL for Oracle DBAsLGWRostatníSHARED MEMORY Per process MEMORY Library CacheRedo logbuffer shared bufferswal buffersCLOG buffersLocks spaceOther buffers17
PostgreSQLArchitecture – database structureThe world's most advancedopen source database. Oracle database structure entData fileExtentOperatingsystem blockOracle block17.2.2016PostgreSQL for Oracle DBAs18
PostgreSQLArchitecture – database structureThe world's most advancedopen source database. PostgreSQL database structure (simplified)LogicalDB clusterPhysicalPGDATATablespacespg default PGDATA/baseCluster wide tableseg. pg databaseDefaulttablespacepg global PGDATA/globalcustomTablespaces:symlinks tophysicaldirectoriespg tblspc/DatabaseFile name:pg class.relfilenodeDB Objectstable, index.Data files free space map ( fsm) visibility map ( vm)1GB default size limit,subsequent files are createdautomatically8k pages17.2.2016PostgreSQL for Oracle DBAsOperatingsystem block19
PostgreSQLArchitecture – database structureThe world's most advancedopen source database. PostgreSQL database structure (simplified)datafiledatafiledatafileDatabase IDatabase IIDatabase IIITablespace ADB ault tablespacePGDATA/base17.2.2016datafilePostgreSQL for Oracle DBAsdatafiledatafileTablespace B20
Architecture - connections Oracle– Process named listener isresponsible to handle newconnections––Dedicated server processesper client––Multi-threaded server 17.2.2016Master process postgres listens fornew connections – pg hba.conf (user/database/networkrestrictions)postgresql.conf (TCP port, kerberos,RDBMS cofiguration.)Dedicated server only Shared memory and semaphores areused for inter process synchronizationConnection pooling by otherproducts Always used on WindowsThe world's most advancedopen source database.PostgreSQLlistener.ora (networkrestrictions, TCP port)sqlnet.ora (protocolconfiguration, kerberos.) PostgreSQL PgBouncerpgpool-IIPostgreSQL for Oracle DBAs21
PostgreSQLArchitecture notes Oracle––17.2.2016The world's most advancedopen source database. Decided that RDBMSis right and only placeto manage databasebuffersPromotes its ASM tohave a direct controlon file management(ASM is kind of LVMdedicated to Oracle)PostgreSQL–Relies on (believes to)OS file cachemanagement–Do not re-implementfeatures alreadyimplemented in OS,thus it use file system tostore its data files (noRAW device support)PostgreSQL for Oracle DBAs22
Security observations I –Users and Roles are defined on DB level (not applies for PDB)–Users and Roles are different entitiesPostgres has roles onlySome roles might be granted “with login” permissionOracle schema consist from a single user objects (schema user)– The world's most advancedopen source database.Oracle has users and roles– PostgreSQLSchema is not an object, so it can't be grantedPostgres schema is a grantable name-space object–Ownership and usage on schema might be granted to roles–Objects owned by different roles (users) might reside within a singleschema–Public schema might (and should) be dropped17.2.2016PostgreSQL for Oracle DBAs23
PostgreSQLSecurity observationsThe world's most advancedopen source database.SW installation:/oracle/product/12.1.0.2/db 1OS access control to filesuser1RoleOneXDoes't ser2Schema:user2FunctionCSchema:user3X - Does't work17.2.2016PostgreSQL for Oracle DBAs24
Security observations II quotas might be used to limit tablespace usage by usersPostgres tablespace is defined at cluster level–“create” on TS might be granted to a role–TS ownership to a role might be defined–There are no space usage quotas on tablespace, check FS free spaceOracle database contains users defined inside DB, there is nodatabase ownership concept– The world's most advancedopen source database.Oracle tablespace always belongs to a database– PostgreSQLGrant scope is always within a database (PDB global users exceptionexists)Postgres database might be owned by a specific role–One role might have granted access on objects from multiple databases–Role attributes possible in scope of database – alter role XXX seserach path YYY,ZZZ in MY DATABASE17.2.2016PostgreSQL for Oracle DBAs25
Security observations III PostgreSQLThe world's most advancedopen source database.Oracle distinguish–System privileges (create table., select any . )–Object privileges (grant select on )Postgres does not have such strong difference–Login permission is cluster wide kind of “system”privilege–Mostly all privileges are related to some objectsincluding database object itself 17.2.2016Grant connect on database myDBGrant usage on .Grant create on .PostgreSQL for Oracle DBAs26
Security observations IV PostgreSQLThe world's most advancedopen source database.Oracle Advanced Security–Transparent Data Encryption–Kerberos (MS AD integration) is available withoutAdvanced security as of 12.1 release, applies to olderreleases–Many other security features (VPD, RLS.)Postgres–SSO available–Row Security Policies are available with 9.5 release–TDE is not available–Encryption is covered by separate module pgcrypto17.2.2016PostgreSQL for Oracle DBAs27
Security observations V The world's most advancedopen source database.Oracle remote access control–IP address level: sqlnet.ora – PostgreSQLtcp.validnode checking yestcp.invited nodes (hostname1, hostname2)tcp.excluded nodes (192.168.10.3)username password and create session is evaluated asnext stepPostgres–pg hba.conf File 17.2.2016username/role membership, database name, source IP addressand authentification method is evaluated prior password validationPassword is evaluated as next stepPostgreSQL for Oracle DBAs28
Security observations VI PostgreSQLThe world's most advancedopen source database.Oracle [public] synonyms–Synonyms are used to reference another user (schema)objects–Might be defined as public – accessible to all usersPostgres–search path session environment is used to define scope ofvisible objects, used similar to PATH in OS 17.2.2016Might be defined at cluster levelUsers might have specified different search path values in particulardatabasesALTER ROLE { role specification ALL } [ INDATABASE database name ] SETconfiguration parameter { TO } { value DEFAULT }PostgreSQL for Oracle DBAs29
Security features. The world's most advancedopen source database.ALL macro in grant commands–Expands to all at time of execution existing objects satisfyinggrant scope criteria PostgreSQLGrant execute on ALL functions in schema my schema to Alter default privileges–Does not affect existing objects, applied to newly created ones–Doc: ALTER DEFAULT PRIVILEGESALTER DEFAULT PRIVILEGES[ FOR { ROLE USER } target role[, .] ][ IN SCHEMA schema name [, .] ]abbreviated grant or revoke17.2.2016PostgreSQL for Oracle DBAs30
Backup and recovery PostgreSQLThe world's most advancedopen source database.Database [full or partial] dump–Oracle exp/imp, expdp/impdp–Postgres pg dump / pg restore– pg dumpall (use it for cluster globals only)– Load dump by call to psqlThanks to MVCC, there is no “ORA-1555” risk duringdump–17.2.2016“directory” format supports parallel dumpsFor sure, the backup is consistent even if the database is usedduring the dumpPostgreSQL for Oracle DBAs31
Binary backups and recovery Offline! Works for Oracle, Postgres Online Oracle database backups– The world's most advancedopen source database.Manual –PostgreSQLAlter database (tablespace) begin backup, Copy corresponding datafiles, alterdatabase (tablespace) end backup, store archived redologs needed forrecoveryOr use Oracle RMAN utilityOnline Postgres cluster backup–Backup Control Functions –pg start backup(), pg stop backup(), same as above for Oracle [no TS levelavailable]pg basebackup 17.2.2016Handle calls to backup control functions and might produce copy of postgrescluster or tar archive with the backup. Some features are available liketablespace mapping for convenient backup procedure/higher flexibilityPostgreSQL for Oracle DBAs32
Binary backups and recovery The world's most advancedopen source database.pgBarman–Some features similar to oracle RMAN PostgreSQLRecovery window / # of copiesStores archived WALs together with Barman backupsBackup reportsDoes not use “rman catalog”, backed up files with some barmanmetadata files are enoughSingle backup might be aechived to tape (tape integration is notpart of pgBarman) – it disappears from backup reports, onceretrieved from tape, pgBarman can use the backup againpgBackRest–17.2.2016More complicated configuration than Barman, incrementalbackups seems to be implemented slightly betterPostgreSQL for Oracle DBAs33
PostgreSQLHA & DR OS clusterware (RHEL Pace Maker,PowerHA )– The world's most advancedopen source database.Simply worksThere is no usable technology like OracleRAC for PostgreSQL server–Sharding (Postgres XL) is not about sharing datafiles between nodes–Oracle 12.2 seems to provide some support forsharding17.2.2016PostgreSQL for Oracle DBAs34
Oracle replication PostgreSQLThe world's most advancedopen source database.Oracle DataGuard–Log shipping (log archive dest n) by archiver –ARCHIVE LAG TARGETRedo transmit by LGWR ASYNCSYNCDelayed recovery - DELAY minutes attribute of theLOG ARCHIVE DEST n–Logical standby–Active Data Guard–Golden Gate17.2.2016PostgreSQL for Oracle DBAs35
RDBMS replication PostgreSQLThe world's most advancedopen source database.PostgresLog-Shipping Standby Servers– Streaming Replication– –archive timeoutASYNC (default)SYNC - Synchronous ReplicationStandby Server Settings recovery min apply delay available from9.4Logical Standby Slony, Bucardo, logical decoding framework–Hot Standby ( read only accessible standby )–BDR provides asynchronous multi-master logical replication.17.2.2016PostgreSQL for Oracle DBAs36
PostgreSQLOthers I The world's most advancedopen source database.psql command line client–Comfortable interface, but be aware of defaultAUTOCOMMIT behavior CZ fast reference by Pavel Stěhule --data-checksums initdb option–Page check-sums are calculated for all object in alldatabases in cluster–use pgbench to vefiry performance impact–Checksum is calculated on page read–Backup operate at file level, checksums are not calculatedduring backup17.2.2016PostgreSQL for Oracle DBAs37
PostgreSQLOthers II The world's most advancedopen source database.Oracle dual table–select function() from dual;–SQL Loader, External tables–db linksPostgreSQL–select function();–copy command (client side, server side), file fdw forCSV files, format compatible with COPY commandrequired–Foreign Data Wrappers for many kinds of data sources,including Oracle database17.2.2016select 5/8;PostgreSQL for Oracle DBAs38
PostgreSQLOthers III The world's most advancedopen source database.Porting from Oracle PL/SQL–Oracle / Postgres – often similar, not always the same ORA: trunc(date variable, format)PG: date trunc('field', source) Pipelined functions are not implemented Group by can use column alias in postgreSQLopen2300db select date trunc('hour', rec datetime) as record time,round(avg(temp out), 2) as avg temp,max(wind speed max) as max wind maxfrom open2300.weather whererec datetime now() - interval '3 hour'group by record timeorder by record time desc;record time avg temp max wind max------------------------ ---------- -------------2016-02-15 22:00:00 01 3.04 2.42016-02-15 21:00:00 01 3.23 2.52016-02-15 20:00:00 01 3.66 2.32016-02-15 19:00:00 01 4.11 2.617.2.2016PostgreSQL for Oracle DBAs39
Others partitioning The world's most advancedopen source database.Postgres partitioning is implemented on top ofinheritance feature–Declarative partitioning like in Oracle is not available Some basic development for 9.6 Constraint on child tables Trigger on master table PostgreSQL–Static IF requires trigger compilation if new childpartition tables are added–Trigger builds dynamic SQL – more overheadNo global indexes on partitioned tables17.2.2016PostgreSQL for Oracle DBAs40
Others get table filename PostgreSQLThe world's most advancedopen source database.Bonus link:How to find out which PostgreSQL table a file ondisk corresponds toQ & A /* end of slides */17.2.2016PostgreSQL for Oracle DBAs41
17.2.2016 PostgreSQL for Oracle DBAs 5 PostgreSQL The world's most advanced open source database. Architecture/concepts - Cluster - Instance - Database - Tablespace Terminology Logical - Role - User - Schema - Tuple Wiki: A tuple is a finite ordered list of elements quick browse through manuals will help and don't take ages
