By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

importing into postgreSQL from oracle

P: 37
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.(including procedures)

Thank you
Feb 15 '08 #1
Share this Question
Share on Google+
15 Replies


Expert 100+
P: 700
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.(including 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.
Feb 15 '08 #2

P: 37
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(postgreSQL 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@database file=filename full=y -----command prompt

i am very new to postgres please tell me indetail.

Thank you very much.
Feb 18 '08 #3

Expert 100+
P: 700
Hi rski
thanks for your reply
I have installed postgres(postgreSQL 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@database 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
http://www.postgresql.org/docs/8.3/s...pp-pgdump.html
Feb 18 '08 #4

P: 37
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
Feb 18 '08 #5

Expert 100+
P: 700
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?
Feb 18 '08 #6

P: 37
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 databaseadministrator
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
Feb 19 '08 #7

debasisdas
Expert 5K+
P: 8,127
Oracle creates crash dump file with the extension .DMP.

If that is the same in postgre then try to import.
Feb 19 '08 #8

Expert 100+
P: 700
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 databaseadministrator
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
Feb 19 '08 #9

P: 15
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.
Mar 16 '08 #10

P: 5
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
Jul 12 '08 #11

Expert 100+
P: 700
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.
Jul 12 '08 #12

P: 5
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....
Jul 13 '08 #13

Expert 100+
P: 700
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
Expand|Select|Wrap|Line Numbers
  1. host  all all remote_ip/32 trust
  2.  
in pg_hba.conf and restart server.
Jul 13 '08 #14

P: 5
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?
Jul 13 '08 #15

P: 5
I attached here the pics, maybe you could take a look at it :)
thanks :)
the left one is the source database and the right one is the database backup created in another computer but no Types exported and some columns are missing...
Attached Images
File Type: jpg db_source.jpg (26.7 KB, 559 views)
File Type: jpg db_restored.jpg (31.7 KB, 649 views)
Jul 16 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.