Difference between revisions of "Conversión de datos a PostgreSQL"

From PostgreSQL wiki
Jump to: navigation, search
Line 3: Line 3:
* [http://www.tv.com.pl/stepbystep/dbasepsql/ Convert ficheros .dbf a PostgreSQL] by Tomasz Judycki
* [http://www.tv.com.pl/stepbystep/dbasepsql/ Convert ficheros .dbf a PostgreSQL] by Tomasz Judycki
== MS Access ==
=== Introducción ===
=== Introducción ===

Revision as of 18:18, 4 May 2009

DBase II, III, IV+

MS Access


Estas página es intento de crear una guía rápida de usurario para convertir datos de MS Access a PostgreSQL. Esta basada en la experiencia del autor Microsoft Access to PostgreSQL Conversion by Jon Hutchings (2001-07-20), no se pretende con este documento sea utilizado como guía de conversión para otras versiones.

Paso 1 - tener corriendo en su Workstation MS Access

The first stage in the conversion process is to download the PostgreSQL upsizing tool. This can be obtained from the authors website. I strongly recommend reading the authors documentation and also note the limitations listed below of this tool.

This tool takes the form of an MS Access 97 database.

Once opened, the main screen (frmmain) has a number of options relating to the SQL conversion.

Select the database that you want to convert., analyse it using the Analyse function and amend any parameters you need to change.

It is important to fill out the SQL database name field with the name you wish to give your final postgres database otherwise you will have more work to do later.

Check the Export data option to move the data across - amend the file locations in the Miscellaneous options section if necessary.

Use Create SQL to make the SQL files and then Export SQL. This will place all the files needed in stage 2 in the directory specified on the Miscellaneous tab of the amend Parameters screen within pgupt.

FTP these files to a suitable empty directory on your postgres server.

Paso 2 - tener corriendo en su Servidor Linux PostgreSQL

Log into your server and change to the directory where you have uploaded the files

If you did not give the database a name earlier then the following must be performed before proceeding

   * edit the loadd.sh file (for example joe loadd.sh will open the file in the joe editor). 
   * Change all references to "test" in the file to the name which you want to give your database. 
   * HINT - Don't include non alphanumeric charaters in your database name (i.e. don't use -_ '?/!"\| etc.) 
   * Save and close the file. 
   * Open the create_schema.sql file and examine the table structure. Ensure that the fieldnames and data types are correct. 

Issue the following command to make the loadd.sh executable

chmod u+x loadd.sh

Execute the the loadd.sh script using the following command at your shell prompt.

./loadd.sh 2> output1

This will execute the script and put the errors and output in a file called output1. If everything is working correctly the screen should display a CREATE for each table in the database, if you only see two create statements and you have three tables , then one of the tables has not been converted. In this case examine the log file (output1 in this case), for details. A common cause for this is the naming of tables or fields using SQL reserved words. For a list of these see the Postgresql documentation for your version of postgres.

WARNING: Once you have succesfully converted your database and it is in use you should NOT rerun this script as it will reinitialize your database back to the state in which it was when you converted it. I suggest that when your database is working you remove the execute permissions on the loadd.sh script to prevent accidential execution.

Paso 3 - Testando su base de datos utilizando la consola del servidor.

Once you are happy that the conversion has worked, you can test your database by connecting to it with the Postgresql monitor in the normal way. For example, if you called your database "customerdata" you would use

psql customerdata

at a shell prompt on your linux postgres server to connect (you will probably need other command line options for usernames and passwords etc.). Once connected you should use the \dt command to display a list of tables and compare it with the source database. If these look correct, try some simple queries such as

select * FROM <tablename> ;

This will display (at some speed !) all the records, but more usefully at the end you will get a record count for that table which you can compare to ensure all the data has been converted correctly.

Paso 4 -Configuración de ODBC

If you wish to continue to use Microsoft Access97 to interact with the converted database you will need to set up a suitable ODBC driver and datasource on the client PC(s).

The first stage is to download the PostgreSQL ODBC driver. This can be obtained via the PostgreSQL ODBC website.

Once installed, use the ODBC control panel on your Windows 9x/NT 4.0/Windows 2000 PC and add a new User DSN or user data source. Select the PostgreSQL driver and fill in the details with the server name, port, database name, username and password, for your postgresql server.

Paso 5 - Configuración en MS Access.

To create an Microsoft Access based front end to the newly created PostgreSQL database, do the following:

Make a copy of your original Access database.

Open it in Microsoft Access.

Select the Tables tab.

Select New.

Select Link Table.

In the Files of Type list select ODBC Databases.

In the data sources dialogue find the data source you created earlier (on NT 4.0 this will typically be on the Machine Data Sources tab).

Select the tables which you wish to link.

Once linked you are free to remove the old local tables and compact the database.

NOTE - If any field names have changed during conversion (usually due to the use of non-alphabetic characters in the field and table names) then your queries, forms and reports etc. will need updating to reflect this.


En primer lugar, esta script solamente fue testado con Postgres 6.5.3 y Access 97, puede trabajar con otras combinaciones, pero no ha sido testado por el autor.

En segundo lugar, ther are some limitations to this tool, which I have discovered, these are

  1. The tool expects at least one relationship, so simple single table database conversions will fail.
  2. The tool assumes you will be using the standard postgesql server port.
  3. Using this tool it is possible to generate trigger and function names longer than 32 characters which can cause postgresql some problems


Utilizar la script "mysql2pgsql.perl"

A considerar:

* No se migra estructura de un atributo con comentarios.
* Los atributos no deben tener caracteres raros Ej.ñ.

Descargar la scipt y salvar con el mysql2pgsql.perl:

  • mysql2pgsql - A Perl script used to convert a MySQL database dump to PostgreSQL-compatible format, by Maxim Rudensky and Valentine Danilchuk

Pasos para utilizarla:

  • Sacar el backup de la estructura de base de datos en Mysql.
 mysqldump --database --table --no-data > mysql_.sql
  • Convertir para que PostgreSql lo pueda cargar.
 perl mysql2pgsql.perl mysql_.sql postgres_.sql --nodrop
    • La opción --nodrop elimina las eliminaciones de las tablas, si se quiere ver mas opciones --help.
su postgres
createdb NombreDB
psql -f postgres_.sql -u dad NombreDB
    • Para los datos. Exportar de MySql solo datos con la opción de --complete-insert y -c.