importing into postgreSQL from oracle

[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.(including procedures)

Thank you

[rski]

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.

[san1014]

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.

[rski]

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

[san1014]

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

[rski]

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?

[san1014]

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

[debasisdas]

Oracle creates crash dump file with the extension .DMP.

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

[rski]

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

[foneman]

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.

[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

[rski]

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.

[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-exported-restored-to18395521.html
please please please help.Thank you....

[rski]

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

host  all all remote_ip/32 trust
 

in pg_hba.conf and restart server.

[bibico]

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?

[bibico]

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...