importing into postgreSQL from oracle

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • san1014
    New Member
    • Jul 2007
    • 37

    importing into postgreSQL from oracle

    Hi
    Where can i get the import and export software for postgreSQL(Full version) and
    in postgreSQL how can we write procedures and functions? and
    please tell me , how to import the dump file from oracle into postgreSQL.(inc luding procedures)

    Thank you
  • rski
    Recognized Expert Contributor
    • Dec 2006
    • 700

    #2
    Originally posted by san1014
    Hi
    Where can i get the import and export software for postgreSQL(Full version) and
    in postgreSQL how can we write procedures and functions? and
    please tell me , how to import the dump file from oracle into postgreSQL.(inc luding procedures)

    Thank you
    I didn't hear about postgres trial or demo version, so what is postgres full version :)?
    After installing postgres you receive the apps: pg_dump and pg_restore, which do buckups and restore database from backups.
    Of course you can also install some graphic tools like pgadmin3, and operate on postgres via it.
    I don't know oracle well so how do you create backups in oracle. What is buckup file format.
    Importing oracle procedures may be hard i suppose, i'm not sure if oracle and postgres have the same syntax. If you write procedure in pl/sql it may not work under postgres.

    Comment

    • san1014
      New Member
      • Jul 2007
      • 37

      #3
      Originally posted by rski
      I didn't hear about postgres trial or demo version, so what is postgres full version :)?
      After installing postgres you receive the apps: pg_dump and pg_restore, which do buckups and restore database from backups.
      Of course you can also install some graphic tools like pgadmin3, and operate on postgres via it.
      I don't know oracle well so how do you create backups in oracle. What is buckup file format.
      Importing oracle procedures may be hard i suppose, i'm not sure if oracle and postgres have the same syntax. If you write procedure in pl/sql it may not work under postgres.
      Hi rski
      thanks for your reply
      I have installed postgres(postgr eSQL 8.3) full version only.(In that i got command prompt,pg admin3,psql to postgres).But i didn't get that pg_dump and pg_restore.
      Can u please give me the site where can i get all those and also tell me how to use that.
      In oracle, to export the data we can use the command as
      exp username/password@databa se file=filename full=y -----command prompt

      i am very new to postgres please tell me indetail.

      Thank you very much.

      Comment

      • rski
        Recognized Expert Contributor
        • Dec 2006
        • 700

        #4
        Originally posted by san1014
        Hi rski
        thanks for your reply
        I have installed postgres(postgr eSQL 8.3) full version only.(In that i got command prompt,pg admin3,psql to postgres).But i didn't get that pg_dump and pg_restore.
        Can u please give me the site where can i get all those and also tell me how to use that.
        In oracle, to export the data we can use the command as
        exp username/password@databa se file=filename full=y -----command prompt

        i am very new to postgres please tell me indetail.

        Thank you very much.
        How did it happen you got psql but you haven't pg_dump and pg_restore (they're also command line tools). I'm not sure if they exist standalone, so you can download it. Did you download postgres from offical site postgresql.org?
        At this site you find all information you need see here for example

        Comment

        • san1014
          New Member
          • Jul 2007
          • 37

          #5
          Originally posted by rski
          How did it happen you got psql but you haven't pg_dump and pg_restore (they're also command line tools). I'm not sure if they exist standalone, so you can download it. Did you download postgres from offical site postgresql.org?
          At this site you find all information you need see here for example
          http://www.postgresql.org/docs/8.3/s...pp-pgdump.html
          Ya I'm downloaded from
          http://www.postgresql. org/ftp/binary/v8.3.0/win32/.

          If i have the pg_dump and pg_restore by default after installation then how to see those.
          or else
          Please tell me the site where can i get pg_dump and pg_restore, which do buckups and restore database from backups.

          Thank you

          Comment

          • rski
            Recognized Expert Contributor
            • Dec 2006
            • 700

            #6
            Originally posted by san1014
            Ya I'm downloaded from
            http://www.postgresql. org/ftp/binary/v8.3.0/win32/.

            If i have the pg_dump and pg_restore by default after installation then how to see those.
            or else
            Please tell me the site where can i get pg_dump and pg_restore, which do buckups and restore database from backups.

            Thank you
            pg_dump and pg_restorwe should be in the same directory where psql is. You can call it from command line. I didn't use postgres under windows for long time how do you use it, using Cygwin or something else?

            Comment

            • san1014
              New Member
              • Jul 2007
              • 37

              #7
              Originally posted by rski
              pg_dump and pg_restorwe should be in the same directory where psql is. You can call it from command line. I didn't use postgres under windows for long time how do you use it, using Cygwin or something else?
              Hello rski
              I have re installed the postgreSQL.
              Now i'm able to call the pg_dump and pg_restore in cmd, but after calling,when i entered the password it is giving the error
              pg_dump: [archiver (db)] connection to database "sandeep" failed: FATAL: password authentication failed for user "sandeep"

              When installation i have given password as databaseadminis trator
              and the superuser password as admin and
              my system password is victory
              if i type any one of those it is giving that error.
              What was the exact password i have to give.
              Please tell me

              Thank you

              Comment

              • debasisdas
                Recognized Expert Expert
                • Dec 2006
                • 8119

                #8
                Oracle creates crash dump file with the extension .DMP.

                If that is the same in postgre then try to import.

                Comment

                • rski
                  Recognized Expert Contributor
                  • Dec 2006
                  • 700

                  #9
                  Originally posted by san1014
                  Hello rski
                  I have re installed the postgreSQL.
                  Now i'm able to call the pg_dump and pg_restore in cmd, but after calling,when i entered the password it is giving the error
                  pg_dump: [archiver (db)] connection to database "sandeep" failed: FATAL: password authentication failed for user "sandeep"

                  When installation i have given password as databaseadminis trator
                  and the superuser password as admin and
                  my system password is victory
                  if i type any one of those it is giving that error.
                  What was the exact password i have to give.
                  Please tell me

                  Thank you
                  look for pg_hba.conf file and see what is authentication type. If you find password or md5 change it to trust, restart server and try again

                  Comment

                  • foneman
                    New Member
                    • Mar 2008
                    • 15

                    #10
                    pgAdmin III is a GUI frontend for postgres. You can do everything in it, you don't need the command line at all. You can backup and restore from the menu.

                    Comment

                    • bibico
                      New Member
                      • Jul 2008
                      • 5

                      #11
                      i am having the same problem on doing a backup of a database from one computer to another, from postgresql to prostgresql though and not oracle. I got the same problem about the password being asked when using the command prompt. i was able to backup and restore using pgadmin but types were not exported/restored - so some columns are missing. Also where can i find a tutorial on pgadmin and prosgresql where i could operate on dummy database? Thanks. I am going nuts for a week already looking for the way on how to completely backp/restore database and i must be able to do it by the next day... waaah! pls help

                      Comment

                      • rski
                        Recognized Expert Contributor
                        • Dec 2006
                        • 700

                        #12
                        Originally posted by bibico
                        i am having the same problem on doing a backup of a database from one computer to another, from postgresql to prostgresql though and not oracle. I got the same problem about the password being asked when using the command prompt. i was able to backup and restore using pgadmin but types were not exported/restored - so some columns are missing. Also where can i find a tutorial on pgadmin and prosgresql where i could operate on dummy database? Thanks. I am going nuts for a week already looking for the way on how to completely backp/restore database and i must be able to do it by the next day... waaah! pls help
                        Show here your pg_hb.conf file, put here only these lines without leading #.
                        pg_hba.conf file shoud be in the database cluster.

                        Comment

                        • bibico
                          New Member
                          • Jul 2008
                          • 5

                          #13
                          when i open pg_hba from File menu pf pgadmin - it opens a window explorer asking which file, if i click on cancel, nothing is written inside. If i open the pg_hba.conf file from data directory of postgresql installation, this is what i have, excluding those lines with #.
                          host all all 127.0.0.1/32 md5
                          i think this is just for local host. what i have been trying to do is backup/transfer the database from one computer to another. I used the backup option of pgadmin III packaged with postgresql 8.2 - save the backup file - xx.backup. I then went to the other computer where i want to restore the backu and use restore option from there - opening the xx.backup file from the source system. Only the public schema was restored and when i looked on the tables on the public schema - some columns are missing, and Types were not there. I cannot figure out how to insert image in this forum. i posted the pics in anorther pgadmin forum. Please take a look at it at this url: http://www.nabble.com/Types-not-exported-restored-to18395521.html
                          please please please help.Thank you....

                          Comment

                          • rski
                            Recognized Expert Contributor
                            • Dec 2006
                            • 700

                            #14
                            Originally posted by bibico
                            when i open pg_hba from File menu pf pgadmin - it opens a window explorer asking which file, if i click on cancel, nothing is written inside. If i open the pg_hba.conf file from data directory of postgresql installation, this is what i have, excluding those lines with #.
                            host all all 127.0.0.1/32 md5
                            i think this is just for local host. what i have been trying to do is backup/transfer the database from one computer to another. I used the backup option of pgadmin III packaged with postgresql 8.2 - save the backup file - xx.backup. I then went to the other computer where i want to restore the backu and use restore option from there - opening the xx.backup file from the source system. Only the public schema was restored and when i looked on the tables on the public schema - some columns are missing, and Types were not there. I cannot figure out how to insert image in this forum. i posted the pics in anorther pgadmin forum. Please take a look at it at this url: http://www.nabble.com/Types-not-expo...o18395521.html
                            please please please help.Thank you....
                            I'll look at it when i find some time, but if you do not want do be asked for password change the string md5 to trust and restart server.
                            If you want to connect from remote computer without password just add line
                            Code:
                            host  all all remote_ip/32 trust
                            in pg_hba.conf and restart server.

                            Comment

                            • bibico
                              New Member
                              • Jul 2008
                              • 5

                              #15
                              thanks, Ü
                              is there any setting that i have to check on at pgadmin before using the backup utility and doing the restore so everything would be included? Or, maybe, is there anything wrong with the process i did?

                              Comment

                              Working...