Latest topics for the forum "Tuning and Migration Wizard"

migration from "vanilla" jdbc database

ccurvey — GMT

I'm looking to migrate (or at least copy data) from a fairly obscure RDBMS to Postgres. My obscure RDBMS does have a JDBC driver, tho. Can I use the migration tool to do this? Or is the tool limited to Oracle, Sybase, MySQL and MS-SQL?

function multiset(numeric) does not exist

luan_version_three — GMT

Hi all,
Nice day all !
My func:
[code]
CREATE OR REPLACE FUNCTION fn_test(xx character varying)
RETURNS attr____x AS
$BODY$
declare
rs attr____x;
begin
select
multiset
(
select t.id
from test t
where t.temp = xx
) as attr____x
into rs
from
dual;
return rs;
end;[/code]
When I call func: select fn_test('abc') ;
It raise errors:
[code]
No function matches the given name and argument types. You might need to add explicit type casts.
function multiset(numeric) does not exist
[/code]

I cant solve my problems. Can edb-er share me some ideas or solutions ?
Thanks !!!
Regard

Luân !

Migration tool kit issue

jshweta1412 — GMT

I am trying to migrate mysql db to postgres plus using migration toolkit
mysql server version - 5.5.28
ppas server version - 9.2
when i rum runMTK.sh i get No suitable driver found error.
I have copied mysql jdbc connector 5.1.23 /opt/PostgresPlus/9.0AS/jre/lib/ext and edited toolkit.properties file also.
Where exactly i need to change to resolve this issue.

Thanks!

Migration Wizard without Advanced Server

ericsound — GMT

Hi,

I run Drupal on Postgres and need to convert a third party website from MySQL. The Migration Toolkit looks very promising but there is a problem because the documentation states to put the mysql-connector-java-ver-bin.jar in the \jre\lib\ext directory under Advanced Server.
However, the documentation states the command line migration tool is not dependent on AS and since I'm running OSX installing Advanced Server is not an option. I currently have the connector installed in the lib directory alongside the other .jar files.

Is there any way I can mimic the directory structure for AS to make Migration Toolkit work with the connector or some configuration change I can make?

Kind regards,
eric

Table Exclusion List

dallasg — GMT

Hello all,
I have a PostgreSQL 8.4 to 9.2 migration on my plate and am trying to carve the process up in segments. To save time, I plan to exclude a list of lesser-used tables from a very large database, then import them after the main import. I'm able to create the "exclusion table" backup through the use of a "for loop" in BASH then referencing a simple text file containing a list of +100 tables to create a separate backup for each table.

My question; is there a better way I can leverage this list through pg_dump other than using +100 '-T' statements in my query?

Thanks!

Tuning wizard cannot access postgresql.conf

janhec — GMT

Hi,
Using standard postgres 9.2.2 (32 bit on windows 7) with the pgdata directory moved to another disk (D:\postgresdata), the server works.
Tuning wizard lets me select the (correct) installation to tune, but after pressing Next says:
"The postgresql.conf for the selected server is not accessible. Please try again by running the TuningWizard as an administrator."
Running as admin makes no difference whatever. Presumably tuningwizard cannot find the config file or data directory in the first place.
Environment variable PGDATA is set to point at the data directory, which also contains postgresql.conf.
Registry (currentcontrolset -> services) has: C:/Program Files (x86)/PostgreSQL/9.2/bin/pg_ctl.exe runservice -N "postgresql-9.2" -D "D:/Postgresdata"
Local_machine -> software -> postgresql has keys Installations and Services without further data.
I hope somebody can explain me how I should run tuning wizard in this case.
Thanks in advance,
Jan

datetime migration from SQL Server to PostgreSQL

andrewshved — GMT

migrator leaves timestamps blank instead of populating with timestamps. Is this a bug or I am doing something wrong?

i did dataonly dump

./runMTK.sh -offlineMigration /migrate -dataOnly -safeMode -loaderCount 3 -sourcedbtype sqlserver -targetdbtype postgres myschema

when I go to migrate directory and open insert statement for a table that has date in it, I see empty space between 2 commas. It has a value in SQL SERVER 2008 EXPRESS

Migration from Oracle, dbms_medatada.get_ddl

spulatkan — GMT

Hi,

I am trying to migrate from Oracle 10.2.0.1.0 to PostgreSQL Plus Advanced Server 9.2.1.3

In one of my procedures, I have used dbms_metadata.get_ddl function to retrieve DDL of indexes on a table. I am dropping indexes before a long and heavy insert operations and at the end of this process i was creating indexes again. On Oracle I am able to get DDL of these indexes with dbms_metadata.get_ddl function. On migration of this code, closest I got is : [url]http://pldoc.sourceforge.net/maven-site/EnterpriseDB.html[/url]

Unfortunately dba_source view does not have type of INDEX. It only supports TRIGGER, PACKAGE BODY, PACKAGE, FUNCTION and PROCEDURE (or something went wrong on my migration and I am missing other types ???)

Could you please help me to find a way to generate DDL of indexes (of a table given as parameter) in procedure of a package?

Thanks

Seckin

Migration from Oracle, dbms_xmlparser

spulatkan — GMT

Hi,

I am trying to migrate from Oracle 10.2.0.1.0 to PostgreSQL Plus Advanced Server 9.2.1.3

On one of my Oracle package, I have used xmlparser (xdb.dbms_xmlparser) package. I have read that some of Oracle function packages were being supported on PostgreSQL Plus Advanced Server as [url=http://www.enterprisedb.com/products-services-training/products/postgres-plus-advanced-server/advanced-server-oracle-features]Oracle compatibility features[/url] but I guess dbms_xmlparser is not one of them; I receive following error from migration toolkit:
[code]
Error Creating Package Body OS_COMMAND_PKG: ERROR: package "dbms_xmlparser" does not exist at position 5823
; p xdb.dbms_xmlparser.parser
^
Stack Trace:
java.sql.SQLException: ERROR: package "dbms_xmlparser" does not exist at position 5823
; p xdb.dbms_xmlparser.parser
^
at com.edb.common.MTKMetaData.executeScript(MTKMetaData.java:976)
at com.edb.MigrationToolkit.copyPackages(MigrationToolkit.java:3996)
at com.edb.MigrationToolkit.copyPackages(MigrationToolkit.java:4020)
at com.edb.MigrationToolkit.importSchema(MigrationToolkit.java:2968)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:1389)
[/code]

What is the best way to migrate such functions to PostgreSQL Plus?
My code is very similar to domsample.sql on http://docs.oracle.com/cd/B10500_01/appdev.920/a96621/adx20ppl.htm

Migration from Oracle, ROLLBACK TO SAVEPOINT

spulatkan — GMT

Hi,

I am trying to migrate from Oracle 10.2.0.1.0 to PostgreSQL Plus Advanced Server 9.2.1.3

At the moment I have problem with ROLLBACK TO SAVEPOINT statements on a package, and following error message is displayed by migration toolkit:
[quote]
Error Creating Package Body CUMULUS_PKG: ERROR: syntax error at or near "TO" at position 24782
; WHEN OTHERS THEN BEGIN -- try to return to savepoint ROLLBACK TO SAVEPOINT before_create_draw
^
Stack Trace:
java.sql.SQLException: ERROR: syntax error at or near "TO" at position 24782
; WHEN OTHERS THEN BEGIN -- try to return to savepoint ROLLBACK TO SAVEPOINT before_create_draw
^
at com.edb.common.MTKMetaData.executeScript(MTKMetaData.java:976)
at com.edb.MigrationToolkit.copyPackages(MigrationToolkit.java:3996)
at com.edb.MigrationToolkit.copyPackages(MigrationToolkit.java:4020)
at com.edb.MigrationToolkit.importSchema(MigrationToolkit.java:2968)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:1389)
[/quote]

I believe postgreSQL supports ROLLBACK TO SAVEPOINT statement. Do you have an idea about what could be the problem?

ps: the package is successfully compiled on Oracle. If I modify the line to only ROLLBACK; then it passes that line and gives error on another line which has ROLLBACK TO SAVEPOINT xxxxx;

Status of Tuning Wizard for PostgreSQL 9.x

david.blaymires — GMT

Hi

Could you provide an update on what is happening with the Tuning Wizard and its ability to work with to PostgreSQL v9.x on Windows and Mac OS X please? When you run Stackbuilder on both platforms, the Tuning Wizard shows as an option to download, yet when you install and run it, v1.4 (the latest version) does not find nor recognise the running instance of PostgreSQL v9.

On Mac OS X, you can select the PostgreSQL.conf file in the data directory, and it will run against that file, but is it coming up with the right results? Are the changes that it is making correct for the platform it is running on?

Thanks.

Regards,
David

installing tuning wizard

Post_gisuser — GMT

Hi,
I have postgresql 8.4.12 on windows 7 . What version of tuning wizard I need to download and How to manually install it. My stackbuilder doesn't work during proxy server settings. So, I need to manually install tuning wizard. Any help ? Thanks.

Tuning wizard for PostgreSQL 9.1 on Mac OS X Lion (10.7.4)

david.blaymires — GMT

Hi,

I downloaded the Tuning Wizard using Stackbuilder after installing PostgreSQL 9.1.4 on a new Mac Mini running Mac OS X 10.7.4 (Lion). When I opened the Tuning Wizard, it could not find the Postgres instance of v9.1 and the only options that showed in the opening dialogue were for PostgreSQL 8.x varieties. Does the Tuning Wizard not work for v9.x on Mac?

Regards
David

Oracle To Postgres Migration error

sambaheerathan — GMT

Hi

I am getting the following error while migrating an oracle DB to Enterprise DB using Enterprise migration studio 9.0. i have already copied oracle 10g driver to C:\Program Files\PostgresPlus\9.0AS\migrationstudio\lib and C:\Program Files\PostgresPlus\9.1AS\jre\lib\ext but still im getting the error. can anyone suggest me a solution?

[Starting Migration]
Source database connectivity info...
conn =jdbc:oracle:thin:@192.168.1.38:1521:hris
user =enterprisedb
password=******
Target database connectivity info...
conn =jdbc:edb://localhost:5432/postgres
user =postgres
password=******
Connecting with source Oracle database server...
Connecting with target EnterpriseDB database server...
Exception in thread "Thread-19" java.lang.NoSuchMethodError: com.edb.Driver.setPrintInfo(Z)V
at com.edb.dbhandler.enterprisedb.EnterpriseDBConnection.(EnterpriseDBConnection.java:39)
at com.edb.common.MTKFactory.createMTKConnection(MTKFactory.java:227)
at com.edb.MigrationToolkit.createNewTargetConnection(MigrationToolkit.java:4619)
at com.edb.MigrationToolkit.initToolkit(MigrationToolkit.java:2526)
at com.edb.MigrationToolkit.main(MigrationToolkit.java:1247)
at com.enterprisedb.migration.MigrationDialog$MigrationThread.run(MigrationDialog.java:1619)

Migration tool problems

sebastien.maye — GMT

Hello everybody,

I'm trying to migrate my Jforum from Oracle 9i to Postgres but I face two problems:
Fisrt, the date are truncated: 'date' data type in Oracle containing "13/05/2008 09:05:40" results in "2008-05-13" (data type 'date') after migration.
Second, the data of type BLOB and CLOB after migration are displayed as hexadecimal in the forum:
\xfeff004f00750069002c002000e00020006c0027006f0063006300610.....
Il matches exactly what i see in table but I dont know why it can't be displayed correctly.
My Oracle DB parameter for NLS_CHARACTERSET is WE8MSWIN1252, and i think i made a mistake in the configuration of my Postgres DB:
CREATE DATABASE own_jforum
WITH OWNER = own_jforum_role
ENCODING = 'UTF8'
TABLESPACE = jforum_data
LC_COLLATE = 'French_France.1252'
LC_CTYPE = 'French_France.1252'
CONNECTION LIMIT = -1;
GRANT ALL ON DATABASE own_jforum TO own_jforum_role WITH GRANT OPTION;
GRANT ALL ON DATABASE own_jforum TO public;

Can someone help me ?

Thanks !!

migration from SQL server To postgres [Don't work]

assadi — GMT

Hi, i try to move data from sqlServer to postgres
_______toolkit.properties___________________
SRC_DB_URL=jdbc:jtds:sqlserver://MYHOST:53804/MyDataBase
SRC_DB_USER=user1
SRC_DB_PASSWORD=user1

TARGET_DB_URL=jdbc:postgresql://MYNEWHOST:63308/MyDataBase
TARGET_DB_USER=user1
TARGET_DB_PASSWORD=user1
________________________________________
java -Dprop=toolkit.properties -jar lib/edb-migrationtoolkit.jar -sourcedbtype sqlserver -targetdbtype postgresql -allTables public

____RESULT____
Source database connectivity info...
.....
Connecting with source SQL Server database server...
Connecting with target Postgres database server...
Importing sql server schema public...

Schema public imported successfully.

Migration process completed successfully.

Migration logs have been saved to C:\Documents and Settings\aattouma010311\.enterprisedb\migration-toolkit\logs

******************** Migration Summary ********************

Total objects: 0
Successful count: 0
Failure count: 0

*************************************************************

Shrink Log database

herocobac — GMT

Dear All,

I have 1 program use postgres database.
After 2,3 day , database postgres create a file have space 1.1 GB.
I don't want postgres create file log.
What need I do ?

Sybase Migration - Migration Toolkit

maalini — GMT

Hi,

I am in the process of migrating from Sybase to PPAS. As I understand, Sybase do not use "Schema" method to store data even they have this facility (correct me if I'm wrong).
How can i perform the migration using migration toolkit since you will need to specify schema name in the command?

Thank you.

Migrating PostgreSQL 8.4.5 to 8.4.8

sanker_kambhapati — GMT

Hi All,
I am new for PostgreSQL,Currently we are using PostgreSQL 8.4.5 in RHEL5.5 Server and want to update it to 8.4.8.

Please help me how to upgrade it step by step.

initdb.exe cannot locate component

damien519 — GMT

this is the error im getting when i try to install postgreSQL.

The component it cannot locate is "WSOCK32.dll"

i've downloaded this component but am not sure wat to do with it now.

any suggestions as ive tried numerous times to install postgresql with NO SUCCESS. and the same error everytime.

please help me

tuning wizard on windows x64

antoine.guidi — GMT

Enterprisedb tuning wizard don't work with PostgreSQL 9.1 x64 or I'm missing something?

I cannot CONNECT TO MY SERVER

patricio.ramos.cl — GMT

For the last 5 hours, I've been trying to figure out what's going on with my PostgreSQL server. I've reinstalled it but no luck. It constantly gives me this error...
I'm using postgreSQL 9.0

An error has occurred:
Error connecting to the server: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.

Inside the logfile, I have this error
2011-11-19 23:53:09 PST LOG: could not receive data from client: Unknown winsock error 10038

The database was working fine this morning and all of a sudden, the connection just died.

Someone please help me.

excessive disk storage issued by an alter table add column statement

patricio.ramos.cl — GMT

executed a simple statement: Alter table employees add column uuid uuid not null default uuid_generate_v4(); on a partitioned table, The problem is that this table employees contains 7 billion+ rows of data.
We executed this statement and it executed for about 50 hours and then the disk drive filled up and the process terminated and rolled back. We got the database back up and we are running VACUUM FULL in an attempt to recovery disk space, but the vacuum will likely run 3-5 days based upon our evaluation of how far the vacuum got since it was started yesterday morning, and so far we haven't seen any meaningful/measureable storage recovered.

Question#1 Does Postgresql require double the disk space based upon its current size for this command to finish successfully?

Question#2 If we break up the process into two steps. i.e. 1.) alter table employees add column uuid uuid; 2.) alter table employees alter column uuid default uuid_generate_v4(); will this minimize disk storage for this table?

Question#3 If vacuum don't reclaim space, is there any other alternatives other than doing a restore to get back to our original footprint.

Question#4 Do you have products that can monitor the alter statement and track where it is in the process. i.e. what partition is currently being populated with the default value, etc.

Thanks

Oracle pipeline function

roko55 — GMT

We are now using Oracle but want to migrate to Postgres. One of the issues during migration is that we use the oracle pipeline function in a lot of our PL/SQL procedures and functions. Is there a way to automatically migrate these functions using the migration wizard?

PhpBB3 Forum, MySQL to PostgreSQL

blodgfx — GMT

I try to convert my forum phpbb3, it uses mysql.

I use [b]EnterpriseDB MySQL Migration Wizard[/b].

The conversion finished successfully but phpbb3 have this error message:

[code]General Error

SQL ERROR [ postgres ]

ERROR: operator does not exist: boolean = integer LINE 3: WHERE is_dynamic = 1 ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. []

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.[/code]

Do you have explanations or a solution has my problem please ? :(