PostgreSQL for Oracle DBA

Document Sample
PostgreSQL for Oracle DBA Powered By Docstoc
					PostgreSQL for Oracle DBA

    Babette Turner-Underwood
       The Pythian Group
       Ottawa ON, Canada
      babette@pythian.com
OOUG April 23, 2006                                                2


                           Agenda
       •   PostgreSQL Overview
       •   Routine Tasks
       •   Comparison of PostgreSQL to Oracle
       •   Using PostgreSQL
       •   Moving to PostgreSQL
       •   Lessons Learned



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                3


         PostgreSQL Overview
       • Introduction
       • Installing PostgreSQL
           – www.postgres.org




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                4


         Configure PostgreSQL
       • Environment Variables (.profile)
       LD_LIBRARY_PATH=/usr/local/pgsql/lib
       PATH=/usr/local/pgsql/bin:$PATH
       export PATH
       MANPATH=/usr/local/pgsql/man:$MANPATH
       export MANPATH




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                5


         Configure PostgreSQL
       • postgresql.conf
           – Similar to init.ora




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                6


         Configure PostgreSQL
       • “alert.log” equivalent
       • Configure logs (postgresql.conf)
           redirect_stderr =TRUE
           archive_command =
            'cp -i %p /mnt/server/archivedir/%f </dev/null'
           – external log redirection
              pg_ctl start |
                rotatelogs /var/log/pgsql_log 86400




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                 7


                      Routine Tasks
       • Start PostgreSQL Database
       pg_ctl start -l logfile
       OR
       postmaster -i -D /data/pgsql/data >logfile 2>&1 &

       Eg : postmaster -i -D /data/pgsql/data >
                          /var/lib/pgsql/logfile           2>&1 &




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                8


                      Routine Tasks
       • Stop PostgreSQL Database
       pg_ctl stop
       OR
       /etc/rc.d/init.d/postgresql stop

       Or using KILL command:
       kill -INT `head -1
         /usr/local/pgsql/data/postmaster.pid`
         SIGTERM - Smart Shutdown – like Oracle NORMAL
         SIGINT - Fast Shutdown – like Oracle IMMEDIATE
         SIGKILL - Immediate Shutdown – like Oracle Abort



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                9


                      Routine Tasks
       • Force a Checkpoint
       # checkpoint;




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                10


                      Routine Tasks
       • Hot Backups
           – Include files :
                • postgresql.conf
                • pg_hba.conf
                • pg_ident.conf
           – Put database into hot backup mode :
           SELECT pg_start_backup('label');

           – End hot backup mode :
           SELECT pg_stop_backup();



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                11


                      Routine Tasks
       • Vacuum **important**
           – Cleans up unused UPDATE/DELETE space
           – EVERY table every 1 billion transactions
           – Vacuum
                • Does not immediately reclaim space to OS
           – Vacuum Full
                • Attempts immediate reclaim space to OS
                • Puts LOCK on table



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                                                  12


           PostgreSQL vs Oracle
• Monitoring
$ ps –ef | grep postgres

Postgres   30805       1 0    Mar20   ?   00:02:10   /usr/local/pgsql/bin/postmaster -D /data/pgsql/data
postgres   30807   30805 0    Mar20   ?   00:00:43   postgres: writer process
postgres   30808   30805 0    Mar20   ?   00:00:09   postgres: stats buffer process
postgres   30809   30808 0    Mar20   ?   00:01:09   postgres: stats collector process
postgres   29481   30805 0    01:34   ?   00:00:45   postgres: postgres postgres [local] SELECT
postgres    2500   30805 0    03:44   ?   00:00:00   postgres: postgres postgres [local] SELECT
postgres    2664   30805 48   03:46   ?   00:00:31   postgres: postgres postgres [local] SELECT




                   Babette Turner-Underwood                       Postgres for Oracle DBA
OOUG April 23, 2006                                                                  13


          PostgreSQL vs Oracle
     • Oracle vs PostgreSQL Internal equivalents
       Oracle                       Postgres
       SCN – System Change Number   XID – Transaction ID
       Control files                pg_clog/*
       Online Redo log files        pg_xlog/WAL log
       login.sql                    ~/.psqlrc
       init.ora                     postgresql.conf
       alert.log                    Database server log




                 Babette Turner-Underwood                  Postgres for Oracle DBA
OOUG April 23, 2006                                                14


         PostgreSQL vs Oracle
       • Tools

            Oracle                  PostgreSQL
            exp                     pg_dump
                                    pg_dumpall
            SQL*Plus                psql
            SQL*Loader              copy



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                            15


               Using PostgreSQL
     • psql –help
     $ psql --help
     This is psql 8.1devel, the PostgreSQL interactive terminal.

     Usage:
       psql [OPTIONS]... [DBNAME [USERNAME]]

     General options:
       -d DBNAME        specify database name to connect to (default: "postgres")
       -c COMMAND       run only single command (SQL or internal) and exit
       -f FILENAME      execute commands from file, then exit
       -l               list available databases, then exit
       -v NAME=VALUE    set psql variable NAME to VALUE
       -X               do not read startup file (~/.psqlrc)
       --help           show this help, then exit
       --version        output version information, then exit        . . .


              Babette Turner-Underwood           Postgres for Oracle DBA
OOUG April 23, 2006                                                16


               Using PostgreSQL
       • Table Creations
       CREATE    TABLE btu (btuid int, btuname text);
       insert    into btu values ( 1, 'text1');
       insert    into btu values ( 2, 'text2');
       insert    into btu values ( 3, 'text3');

       CREATE TABLE
       real_real_real_real_real_real_real_real_longname
       (col1 int) TABLESPACE my_tablespace;



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                17


               Using PostgreSQL
       • Using psql :
       $ psql
       Timing is on.
       Welcome to psql 8.1devel, the PostgreSQL
         interactive terminal.

       Type:     \copyright for distribution terms
                 \h for help with SQL commands
                 \? for help with psql commands
                 \g or terminate with semicolon to execute
                    query
                 \q to quit


              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                18


               Using PostgreSQL
       • Table Creations
       postgres=# select * from btu;
        btuid | btuname
       -------+---------
            1 | text1
            2 | text2
            3 | text3
       (3 rows)

       Time: 0.409 ms



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                19


               Using PostgreSQL
       • Date Mathematics
       WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                20


               Using PostgreSQL
       • Keywords
           Oracle                  PostgreSQL
           SYSDATE                 now()
           nextval(seqname)        SERIAL
           ROWNUM                  LIMIT




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                21


               Using PostgreSQL
       • Stored Procedures
       CREATE OR REPLACE FUNCTION getbtu(int)
                                RETURNS SETOF btu AS $$
       DECLARE
          v_btuid ALIAS for $1;
       BEGIN
          SELECT * FROM btu WHERE btuid = $1;
       END;
       $$ LANGUAGE 'plpgsql';



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                22


               Using PostgreSQL
       • Comments
       /* this is a
          multi line comment
       */
       • Allows comment nesting (with SQL)
       /* this is
          /* a nested comment */
          in a multi line Comment
       */


              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                 23


               Using PostgreSQL
       • testing nulls
              is                can test any true, false,
                                unknown, null
              is null           isnull
              is not null       notnull




              Babette Turner-Underwood    Postgres for Oracle DBA
OOUG April 23, 2006                                                24


         Moving to PostgreSQL
       • Configure Database
       • Automatic Table Creations




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                25
                     Integrating
                PostgreSQL & Oracle
       • Automating Data Transfer Between
         Oracle and PostgreSQL
       • Implementing Materialized Views
         Between Oracle and PostgreSQL
       • Common Monitoring



              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                26


                 Lessons Learned
       • Case Study – Overview of Project
         Requirements
           – Migrate Oracle 8 DB to postgreSQL
           – Integration with existing Oracle DB
           – Data to be moved
           – Stored Procedures, triggers to convert
           – Partition tables

              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                27


                 Lessons Learned
       • Lessons Learned
           – Needed better milestones
           – Break into smaller sections to test
           – Proof of concept testing
           – Underestimate time required




              Babette Turner-Underwood   Postgres for Oracle DBA
OOUG April 23, 2006                                                28


                         Summary
       • Additional Information
           http://www.postgresql.org/docs/8.0/interactive/




              Babette Turner-Underwood   Postgres for Oracle DBA