Re: Transferring from MS SQL (2000) to PostgreSQL
David Livingston wrote: If you just want a single IP to be able to connect the format is 555.555.555.555/0 . Obviously you replace the 555 stuff with your IP. It is the other way around. /32 means 1 IP address, /0 means all IP addresses. The number after the slash is the number of bits in the IP address that is significant. For example the CIDR 145.94.90.156/16 means the IP address 145.94.90.156 with 16 significant bits: IP:10010010 0101 01011011 1001 mask: So every IP address that starts with 10010010 0101 (or 145.94) falls in that CIDR. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266361 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Okay, so far I'm *really* liking Pg. I have successfully imported all of my tables and data, and have everything fixed with one exception: updating the currval() of the sequence for the 'auto_number' fields. I can go through each sequence one at a time with the GUI and update it, but I'd obviously prefer to do so via script. Here's what I've tried, but it throws a syntax error: ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM table_name); Apparently it does not like the query to be used as the value. I'm still reading through the docs to see if I can find where/how to do what I'm trying to do, but figured I'd cheat and post back to you guys as well. Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266422 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
I don't know why i wrote the '+1' in there when I wrote the email. I'm not using it in the actual statement. The specific syntax error is that the ( is invalid. On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote: Okay, so far I'm *really* liking Pg. I have successfully imported all of my tables and data, and have everything fixed with one exception: updating the currval() of the sequence for the 'auto_number' fields. I can go through each sequence one at a time with the GUI and update it, but I'd obviously prefer to do so via script. Here's what I've tried, but it throws a syntax error: ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM table_name); Apparently it does not like the query to be used as the value. I'm still reading through the docs to see if I can find where/how to do what I'm trying to do, but figured I'd cheat and post back to you guys as well. Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266425 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Matt, I sent in a previous e-mail but you may have missed it: SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence ('#table#','#table_id#'), (SELECT max(#table_id#) FROM #table#), true); That way you don't have to provide the sequence name. HTH, Jon On Jan 12, 2007, at 2:41 PM, Matt Quackenbush wrote: I don't know why i wrote the '+1' in there when I wrote the email. I'm not using it in the actual statement. The specific syntax error is that the ( is invalid. On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote: Okay, so far I'm *really* liking Pg. I have successfully imported all of my tables and data, and have everything fixed with one exception: updating the currval() of the sequence for the 'auto_number' fields. I can go through each sequence one at a time with the GUI and update it, but I'd obviously prefer to do so via script. Here's what I've tried, but it throws a syntax error: ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM table_name); Apparently it does not like the query to be used as the value. I'm still reading through the docs to see if I can find where/how to do what I'm trying to do, but figured I'd cheat and post back to you guys as well. Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266430 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Jon, Thanks for the reply. Indeed I did miss it. I was hoping to write one long ..sql script and run it from my query editor and update all tables in a single connection. Are you aware of a way to do that? If not, I'll use your CF solution :-) Matt On 1/12/07, Jon Clausen [EMAIL PROTECTED] wrote: Matt, I sent in a previous e-mail but you may have missed it: SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence ('#table#','#table_id#'), (SELECT max(#table_id#) FROM #table#), true); That way you don't have to provide the sequence name. HTH, Jon On Jan 12, 2007, at 2:41 PM, Matt Quackenbush wrote: I don't know why i wrote the '+1' in there when I wrote the email. I'm not using it in the actual statement. The specific syntax error is that the ( is invalid. On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote: Okay, so far I'm *really* liking Pg. I have successfully imported all of my tables and data, and have everything fixed with one exception: updating the currval() of the sequence for the 'auto_number' fields. I can go through each sequence one at a time with the GUI and update it, but I'd obviously prefer to do so via script. Here's what I've tried, but it throws a syntax error: ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM table_name); Apparently it does not like the query to be used as the value. I'm still reading through the docs to see if I can find where/how to do what I'm trying to do, but figured I'd cheat and post back to you guys as well. Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266442 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Truthfully,I've always done it with CF so I don't know what the fully query would be with postgres. You can run the following query to get the table names and primary key names (where they are defined) select table_name, column_name from information_schema.constraint_column_usage ; Then just loop the query. HTH, Jon On Jan 12, 2007, at 3:38 PM, Matt Quackenbush wrote: Jon, Thanks for the reply. Indeed I did miss it. I was hoping to write one long ..sql script and run it from my query editor and update all tables in a single connection. Are you aware of a way to do that? If not, I'll use your CF solution :-) Matt On 1/12/07, Jon Clausen [EMAIL PROTECTED] wrote: Matt, I sent in a previous e-mail but you may have missed it: SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence ('#table#','#table_id#'), (SELECT max(#table_id#) FROM #table#), true); That way you don't have to provide the sequence name. HTH, Jon On Jan 12, 2007, at 2:41 PM, Matt Quackenbush wrote: I don't know why i wrote the '+1' in there when I wrote the email. I'm not using it in the actual statement. The specific syntax error is that the ( is invalid. On 1/12/07, Matt Quackenbush [EMAIL PROTECTED] wrote: Okay, so far I'm *really* liking Pg. I have successfully imported all of my tables and data, and have everything fixed with one exception: updating the currval() of the sequence for the 'auto_number' fields. I can go through each sequence one at a time with the GUI and update it, but I'd obviously prefer to do so via script. Here's what I've tried, but it throws a syntax error: ALTER SEQUENCE seq_name RESTART WITH (SELECT max(id) + 1 FROM table_name); Apparently it does not like the query to be used as the value. I'm still reading through the docs to see if I can find where/how to do what I'm trying to do, but figured I'd cheat and post back to you guys as well. Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266458 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Jon, Your solution worked just fine actually :-) Thanks again! Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266459 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Glad to help. Have fun. :-) -Jon On Jan 12, 2007, at 4:52 PM, Matt Quackenbush wrote: Jon, Your solution worked just fine actually :-) Thanks again! Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266463 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
All- I am very pleased to announce that I have the entire SQL Server database duplicated on PostgreSQL, indexed, PKs, FKs, clustered, etc, and it ROCKS! Not only that, but my 43-line queries all work properly and are lightening fast! Unless I come across something completely unexpected, I feel confident in saying that you can add me to the ever-growing list of PostgreSQL converts! Thanks for ALL of you guys' help! And thank you to those who enticed me to open my eyes to a solution other than MS SQL. :-) Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266472 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Hmmm... I setup PostgreSQL on one of my production (remote to me) servers. It's all working wonderfully from localhost. To prepare it for allowing remote access, I added the following line to the pg_hba.conf file: hostsameuserall (the IP, e.g. 555.555.555.555)password I then added the following line to the postgresql.conf file, and restarted the service: listen_addresses = '*' I then downloaded/installed ADS here on my test box, and setup the connection. After the connection timed out, it dawned on me that the port was blocked. So, I contacted my host and had them open up the appropriate port. Now that the correct port is open, virtually instantly upon trying to connect, I receive the following error message: Connection failed: FATAL: missing or erroneous pg_hba.conf file Surely it's not looking for the pg_hba file on my local machine whilst trying to connect to a remote address? I know I must be doing something wrong, and that it should be really obvious to me as to what it is, but I cannot seem to clear the clutter in the brain and figure it out. Any help would be appreciated. Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266274 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Matt Quackenbush wrote: Hmmm... I setup PostgreSQL on one of my production (remote to me) servers. It's all working wonderfully from localhost. To prepare it for allowing remote access, I added the following line to the pg_hba.conf file: hostsameuserall (the IP, e.g. 555.555.555.555)password This is a bad combination because they result in a cleartext communication and even a cleartext password. Best would be to install a certificate and use hostssl instead of host, but at least you should use md5 instead of psssword. I then added the following line to the postgresql.conf file, and restarted the service: listen_addresses = '*' You don't need to restart the service, you can signal it with pg_ctl reload. Now that the correct port is open, virtually instantly upon trying to connect, I receive the following error message: Connection failed: FATAL: missing or erroneous pg_hba.conf file That should have generated an error in PostgreSQLs log file or even the Event Log. Does that error give a line number? Restore your backup of pg_hba.conf, signal the server and try to connect locally. Then try your modifications to pg_hba.conf and try to connect locally again. Then try connecting remotely. Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266287 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Jochem, This is a bad combination because they result in a cleartext communication and even a cleartext password. Best would be to install a certificate and use hostssl instead of host, but at least you should use md5 instead of psssword. Thank you for the tip on the ssl/md5/password issue. At the moment, I am setting this up on a server purely for my testing purposes, and the server in question does not have an SSL cert. I did however change it to 'md5'. You don't need to restart the service, you can signal it with pg_ctl reload. I ran the 'pg_ctl reload' command, and it returned the following message: pg_ctl: no database directory specified and environment variable PGDATA unset I then ran the 'pg_ctl --help' command (as instructed), but I'm not intelligent enough to figure out what the issue is from the info that it responded with. ;-) Now that the correct port is open, virtually instantly upon trying to connect, I receive the following error message: Connection failed: FATAL: missing or erroneous pg_hba.conf file That should have generated an error in PostgreSQLs log file or even the Event Log. Does that error give a line number? The log files show the following: LOG: invalid IP mask password in file C:/Program Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host After changing the METHOD to md5, the log files show the updated, but virtually identical message: LOG: invalid IP mask md5 in file C:/Program Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host Restore your backup of pg_hba.conf, signal the server and try to connect locally. Then try your modifications to pg_hba.conf and try to connect locally again. Then try connecting remotely. Connecting locally is still a breeze. Remotely, well, I'm obviously still doing something wrong :-) Thank you for your assistance in my education. Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266293 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Matt Quackenbush wrote: This is a bad combination because they result in a cleartext communication and even a cleartext password. Best would be to install a certificate and use hostssl instead of host, but at least you should use md5 instead of psssword. Thank you for the tip on the ssl/md5/password issue. At the moment, I am setting this up on a server purely for my testing purposes, and the server in question does not have an SSL cert. I did however change it to 'md5'. Generate your own certificate with OpenSSL. I ran the 'pg_ctl reload' command, and it returned the following message: pg_ctl: no database directory specified and environment variable PGDATA unset I then ran the 'pg_ctl --help' command (as instructed), but I'm not intelligent enough to figure out what the issue is from the info that it responded with. ;-) It needs to know which PostgreSQL instance to reload. The directory it wants is the directory with your pg_hba.conf in it. From there it can figure out the rest. LOG: invalid IP mask password in file C:/Program Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host You are missing a column in your pg_hba.conf. Do you have the real one somewhere? Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266318 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
On 1/11/07, Jochem van Dieten [EMAIL PROTECTED] wrote: LOG: invalid IP mask password in file C:/Program Files/PostgreSQL/8.2/data/pg_hba.conf line 72: Unknown host You are missing a column in your pg_hba.conf. Do you have the real one somewhere? Jochem Which column am I missing? According to the docs ( http://www.postgresql.org/docs/8.2/static/auth-pg-hba-conf.html), the record can take one of seven forms: local *database* *user* *auth-method* [*auth-option*] host *database* *user* *CIDR-address* *auth-method* [*auth-option*] hostssl*database* *user* *CIDR-address* *auth-method* [*auth-option*] hostnossl *database* *user* *CIDR-address* *auth-method* [*auth-option*] host *database* *user* *IP-address* *IP-mask* *auth-method* [*auth-option*] hostssl*database* *user* *IP-address* *IP-mask* *auth-method* [*auth-option*] hostnossl *database* *user* *IP-address* *IP-mask* *auth-method* [*auth-option*] My pg_hba.conf file contains the following lines (below all of the instructions): # TYPEDATABASEUSERIP-ADDRESSMETHOD hostall all 127.0.0.1/32 md5 hostsameuserall(my.IP.addy.here)md5 The first line allows me to connect from localhost (CF DSN works wonderfully, etc). The second, as I understand it, *should* allow my IP to connect. Am I completely misunderstanding something here? Thanks, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266325 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Matt Quackenbush wrote: host *database* *user* *CIDR-address* *auth-method* [*auth-option*] host *database* *user* *IP-address* *IP-mask* *auth-method* [*auth-option*] My pg_hba.conf file contains the following lines (below all of the instructions): # TYPEDATABASEUSERIP-ADDRESSMETHOD hostall all 127.0.0.1/32 md5 hostsameuserall(my.IP.addy.here)md5 The first line allows me to connect from localhost (CF DSN works wonderfully, etc). The second, as I understand it, *should* allow my IP to connect. How did you write your IP address? Did you add the /32 to use *CIDR-address* notation? Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266330 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Jochem, Thank you. That was the problem. I didn't realize that it was needed. Remote connection up and running; time to play! :-) Matt On 1/11/07, Jochem van Dieten [EMAIL PROTECTED] wrote: How did you write your IP address? Did you add the /32 to use *CIDR-address* notation? Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266335 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
If you just want a single IP to be able to connect the format is 555.555.555.555/0 . Obviously you replace the 555 stuff with your IP. Dave On Jan 11, 2007, at 4:14 PM, Matt Quackenbush wrote: Jochem, Thank you. That was the problem. I didn't realize that it was needed. Remote connection up and running; time to play! :-) Matt On 1/11/07, Jochem van Dieten [EMAIL PROTECTED] wrote: How did you write your IP address? Did you add the /32 to use *CIDR-address* notation? Jochem ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266350 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
I have found this software, but it appears to be brand new. Has anyone had any experience with it? Or with something similar that you would recommend? http://www.download3k.com/Business-Finance/Database-Management/Download-MS-SQL-Server-PostgreSQL-Import-Export-Convert-Software.html ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266171 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transferring from MS SQL (2000) to PostgreSQL
I have used Aqua Data Studio for just that task. (mysql and mssql to postgre). I had to do it table by table, but I did it for millions of records with relative ease. Export to delimited text file or file with insert statements, then import to the other db. http://www.aquafold.com/ Joshua Cyr Savvy Software 866.870.6358 www.besavvy.com -Original Message- From: Matt Quackenbush [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 3:50 PM To: CF-Talk Subject: Transferring from MS SQL (2000) to PostgreSQL Hello, I have a heavily used and ever-growing MS SQL database that I would like to transfer over to PostgreSQL. It is currently 143mb (plus about 67mb in log files). The database is relatively basic, in that it does not currently use any stored procedures, and the tables makeup is pretty sql-generic. In a nutshell, I am looking for info and advice on how to best handle the transfer. Thanks in advance, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266172 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Joshua Cyr wrote: I have used Aqua Data Studio for just that task. (mysql and mssql to postgre). I had to do it table by table, but I did it for millions of records with relative ease. Export to delimited text file or file with insert statements, then import to the other db. http://www.aquafold.com/ I'll +1 Aqua Data Studio...but I don't think you will need to do it table by table...if you right click on the database, and go to Tools/Script Generator, it will bring up a wizard that will create a single file with CREATE statements for all of your tables, views, indexes, etc. and INSERT statements for all of your data (basically a file like mysqldump or pg_dump creates). It allows you some customization in the export, such as what the statement separator is and what to wrap object names in ([] vs ``)...I haven't used it going from MS-SQL --- PostgreSQL, but I have gone MS-SQL --- MySQL and the created file needed little modification. Unfortunately they just raised the price ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266176 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transferring from MS SQL (2000) to PostgreSQL
Wasn't Aqua Data Studio free at one point? I swear I didn't buy it... but could be forgetting. I don't do the warez thing, so either it was legitimately free at one point or very inexpensive or I am losing too many brain cells on WOW. -Original Message- From: Jim Wright [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 10, 2007 4:17 PM To: CF-Talk Subject: Re: Transferring from MS SQL (2000) to PostgreSQL Joshua Cyr wrote: I have used Aqua Data Studio for just that task. (mysql and mssql to postgre). I had to do it table by table, but I did it for millions of records with relative ease. Export to delimited text file or file with insert statements, then import to the other db. http://www.aquafold.com/ I'll +1 Aqua Data Studio...but I don't think you will need to do it table by table...if you right click on the database, and go to Tools/Script Generator, it will bring up a wizard that will create a single file with CREATE statements for all of your tables, views, indexes, etc. and INSERT statements for all of your data (basically a file like mysqldump or pg_dump creates). It allows you some customization in the export, such as what the statement separator is and what to wrap object names in ([] vs ``)...I haven't used it going from MS-SQL --- PostgreSQL, but I have gone MS-SQL --- MySQL and the created file needed little modification. Unfortunately they just raised the price ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266177 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Joshua Cyr wrote: Wasn't Aqua Data Studio free at one point? I swear I didn't buy it... but could be forgetting. I don't do the warez thing, so either it was legitimately free at one point or very inexpensive or I am losing too many brain cells on WOW. Prior to the current version 6, it was free for personal use. The previous version (4.7) is still available from their download page. A fully functional/non-expiring copy of version 6 can also be downloaded, you just aren't supposed to use it for anything other than evaluation. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266178 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Joshua, Thanks for the pointer and the link. Jim, Are you referring to v6 RC1? That's all I've seen so far on their site. Thanks, Matt On 1/10/07, Jim Wright [EMAIL PROTECTED] wrote: Joshua Cyr wrote: Wasn't Aqua Data Studio free at one point? I swear I didn't buy it... but could be forgetting. I don't do the warez thing, so either it was legitimately free at one point or very inexpensive or I am losing too many brain cells on WOW. Prior to the current version 6, it was free for personal use. The previous version (4.7) is still available from their download page. A fully functional/non-expiring copy of version 6 can also be downloaded, you just aren't supposed to use it for anything other than evaluation. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266181 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Matt Quackenbush wrote: Jim, Are you referring to v6 RC1? That's all I've seen so far on their site. Yes, I believe that is the latest version...but I think the 4.7 version also had the export capability I was describing. I went ahead and started using the RC because I wanted to use the new schema diff tool. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266184 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Here is what I have done in the past. Use enterprise manager to script out the tables to a sql script. Then do a find an replace on the script to convert the data types and remove all of the MS specific stuff. Once you have a good generic SQL script run it on postgres to create all of your tables. Then on your MS SQL server setup an ODBC connection to the postgres server. Once you have that setup you can use Enterprise Managers export (DTS) functions to map the tables and move the data over into the postgres server. The hardest data types are id fields and dates. For Id fields set the postgres field to and integer then after the data is moved over you can flip it to and auto incrementing id. It's been a while since I have done this so I can't remember what I did about dates but maybe the odbc drivers have gotten better and will transfer better. Hope that helps. Dave On Jan 10, 2007, at 2:49 PM, Matt Quackenbush wrote: Hello, I have a heavily used and ever-growing MS SQL database that I would like to transfer over to PostgreSQL. It is currently 143mb (plus about 67mb in log files). The database is relatively basic, in that it does not currently use any stored procedures, and the tables makeup is pretty sql- generic. In a nutshell, I am looking for info and advice on how to best handle the transfer. Thanks in advance, Matt ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266185 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Am I correctly understanding what I'm reading on their site that Aqua Data Studio supports ALL of the listed databases in the same client? (As opposed to having a different one for each DB?) ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266188 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Matt Quackenbush wrote: Am I correctly understanding what I'm reading on their site that Aqua Data Studio supports ALL of the listed databases in the same client? (As opposed to having a different one for each DB?) Yes...it is very nice having the same interface for multiple database systems (and multiple operating systems). It was actually SQL 2005 that drove me to it...Management Studio pissed me off so much when I first started using it, and ADS allowed me to connect to SQL 2000 and 2005 with an interface that was much more like Enterprise Manager than Management Studio. I've since come around on Management Studio...but I'm still more likely to fire up ADS, even with a SQL 2005 database. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266192 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transferring from MS SQL (2000) to PostgreSQL
On Jan 10, 2007, at 5:13 PM, Matt Quackenbush wrote: Am I correctly understanding what I'm reading on their site that Aqua Data Studio supports ALL of the listed databases in the same client? (As opposed to having a different one for each DB?) Yes. It rocks! Dave L's instructions are probably the easiest to migrate the data - if you can use DTS. If you don't want to use DTS you can set your export preferences in Aqua Data Studio to format the query for Postgres compatibility.You'll still need to do some tweaking especially on date/time formats of the data (most of the time you can simply place {} around the timestamps), but if you use Enterprise Manager or Studio Express to generate the export script, you'll have a lot more to clean up since it generates a bunch of MS-specific stuff in the export query. Some common datatype conversions for your adventure: :-) MSSQL | Postgres IDENTITY(1,1) = serial varchar = character varying datetime= timestamp without time zone int = integer text= text If you migrate the data using DTS and then change the column types to serial later, you may need to run the following script on each table to establish the next sequence value for auto-incrementing (With your variables inserted for the table variables of course): SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('#table#', '#table_id#'), (SELECT max(#table_id#) FROM #table#), true); Good luck! Jon ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266197 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Sweet (regarding the single interface)! Jon, I was under the impression that varchar() was still a valid datatype in Postgre, and would not require changing to 'character varying'. http://www.postgresql.org/docs/8.2/static/datatype-character.html Am I mis-reading that? Dave, Thanks for the tips! Thanks, Matt On 1/10/07, Jon Clausen [EMAIL PROTECTED] wrote: Some common datatype conversions for your adventure: :-) MSSQL | Postgres IDENTITY(1,1) = serial varchar = character varying datetime= timestamp without time zone int = integer text= text If you migrate the data using DTS and then change the column types to serial later, you may need to run the following script on each table to establish the next sequence value for auto-incrementing (With your variables inserted for the table variables of course): SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('#table#', '#table_id#'), (SELECT max(#table_id#) FROM #table#), true); Good luck! Jon ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266203 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
No you're right - now days. I've had some issues with older drivers where they would throw a syntax error on me so I always use character varying with Pg just to be on the safe side. On Jan 10, 2007, at 6:10 PM, Matt Quackenbush wrote: Sweet (regarding the single interface)! Jon, I was under the impression that varchar() was still a valid datatype in Postgre, and would not require changing to 'character varying'. http://www.postgresql.org/docs/8.2/static/datatype-character.html Am I mis-reading that? Dave, Thanks for the tips! Thanks, Matt On 1/10/07, Jon Clausen [EMAIL PROTECTED] wrote: Some common datatype conversions for your adventure: :-) MSSQL | Postgres IDENTITY(1,1) = serial varchar = character varying datetime= timestamp without time zone int = integer text= text If you migrate the data using DTS and then change the column types to serial later, you may need to run the following script on each table to establish the next sequence value for auto-incrementing (With your variables inserted for the table variables of course): SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('#table#', '#table_id#'), (SELECT max(#table_id#) FROM #table#), true); Good luck! Jon ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266206 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
I concur with Jon. I'd use Character Varying. Dave On Jan 10, 2007, at 5:31 PM, Jon Clausen wrote: No you're right - now days. I've had some issues with older drivers where they would throw a syntax error on me so I always use character varying with Pg just to be on the safe side. On Jan 10, 2007, at 6:10 PM, Matt Quackenbush wrote: Sweet (regarding the single interface)! Jon, I was under the impression that varchar() was still a valid datatype in Postgre, and would not require changing to 'character varying'. http://www.postgresql.org/docs/8.2/static/datatype-character.html Am I mis-reading that? Dave, Thanks for the tips! Thanks, Matt On 1/10/07, Jon Clausen [EMAIL PROTECTED] wrote: Some common datatype conversions for your adventure: :-) MSSQL | Postgres IDENTITY(1,1) = serial varchar = character varying datetime= timestamp without time zone int = integer text= text If you migrate the data using DTS and then change the column types to serial later, you may need to run the following script on each table to establish the next sequence value for auto-incrementing (With your variables inserted for the table variables of course): SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence ('#table#', '#table_id#'), (SELECT max(#table_id#) FROM #table#), true); Good luck! Jon ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266207 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transferring from MS SQL (2000) to PostgreSQL
Am I correctly understanding what I'm reading on their site that Aqua Data Studio supports ALL of the listed databases in the same client? (As opposed to having a different one for each DB?) Sure. It's a JDBC client, just like CF. I doubt it provides GUI management functionality found in most platform-specific management tools, though. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266214 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transferring from MS SQL (2000) to PostgreSQL
Dave Watts wrote: Am I correctly understanding what I'm reading on their site that Aqua Data Studio supports ALL of the listed databases in the same client? (As opposed to having a different one for each DB?) Sure. It's a JDBC client, just like CF. I doubt it provides GUI management functionality found in most platform-specific management tools, though. It does have some platform-specific management tools built in, mainly for MS-SQL, Oracle and Sybase...but you are correct, most of it is generic functionality. It is good at what it does, though...and I rarely find myself needing to fire up another tool. ~| Create robust enterprise, web RIAs. Upgrade integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266215 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4