Data transfer from PostgreSQL to MySQL

Based on a tip from codesmell.org blog I eventually transferred data from PostgreSQL to MySQL. My client - what is unusual - decided to stay at MySQL as homogeneous environment and drop PostgreSQL.

The main problem is to

• preserve encoding
• keep all quoted characters
• change boolean from T/F to 1/0, and
• auto_increment what was serialized.

Transfering schema is planned for future post. The main guidelines are:

• change type inet to text
• remove serial and nextval
• remove evrything after "::" in field description using this command:

echo "default 'USD'::bpchar" | awk '{ i=index($0, ":"); print i; print substr($0, 0, i); }'

Exporting data

The first step is to compose string of the definition of every table for export to CSV:

table_name(first_column,second_column...)

Script parses all tables definitions and composes the desired string:
users(id,created,login,name,email,description)
ads(id,user_id,subject,text)

PostgreSQL versions

To parse PostgreSQL data I needed psql client in the same version as server. After download proper version I compiled and installed only client and necessary libraries. Create short script in source directory to automate it:

#!/bin/bashmake -C src/bin installmake -C src/bin/psql installmake -C src/include installmake -C src/interfaces installmake -C doc install

Then run ./configure --prefix=$HOME/local

Client in ~/local/bin/psql is ready to use. Execute it using full path or supplement $PATH:
export PATH=$HOME/local/bin:$PATH

Actual function:

function definitions {
while read table; do
if [[ $table != '' ]]; then
# weird empty lines
definition="$table("        # start the string with table name
while read column_def; do
column=`echo "$column_def" | awk '{ print $1; }'`
definition="${definition}${column}" # add column name
definition="$definition,"  # add comma
done < <($PGSQLCONN -t -c "\d $table" | awk '{ print $1; }')
echo "$definition)" | sed 's/,,)/)/' # add closing bracket and remove double commas
fi
done < <($PGSQLCONN -t -c "\dt" | grep -v "pg_" | awk '{ print $3; }') > $TABLE_DEFS
# for the while loop use all tables list from command psql> \dt

Some tables depend on data from other. To put FOREIGN KEY with reference to user's ids, I needed to put users table first. Identify all such tables and move them on top of imported tables.

tmpf="/tmp/tmp_table_definitions.txt"
grep -E "^user" $TABLE_DEFINITIONS > $PRIORITY_DEFINITIONS
grep -E "^ad" $TABLE_DEFINITIONS >> $PRIORITY_DEFINITIONS
grep -E -v "(^user|^ad)" $TABLE_DEFINITIONS > $tmpf
mv $PRIORITY_DEFINITIONS $TABLE_DEFINITIONScat $tmpf >> $TABLE_DEFINITIONS

Generation of a list table from table definition.

function tables {
DIR="$PROJHOME/csv"rm -rf $DIRmkdir $DIRtmpf="/tmp/tmp.csv"
# Ensure there's enough disk space on /tmp
rm $LISTA_TABEL 2>/dev/null
for definition in `cat $TABLE_DEFINITIONS`; do
echo "$definition" | \
awk 'BEGIN { FS = "(" } ; { print $1; }' >> $TABLE_LIST
done}

Dump of the data from PostgreSQL:

function dump {
### Data dump from PostgreSQL to CSV
for definition in `cat $TABLE_DEFINITIONS`; do
tabela=`echo $definition | \
awk 'BEGIN { FS = "(" } ; { print $1; }'`
file="${DIR}/${tabela}.csv"
nice ~/local/bin/psql --host=ip_address \
--user=account_name \
-c "COPY $definition TO STDOUT" db_name > $file
rows=`wc -l $file`
echo ">>File of the table $table has $rows rows"
done
}

Statements of table creation:

function create {
$MYSQLCONN < $RECREATE_TABLE
}

RECREATE_FILE contains complete database definition. Its based on PostgreSQL dump of schema with modifications to meet MySQL requirements.

Pouring the data into MySQL:

function tip {
tmpf="/tmp/tmp.csv"
for table in `cat $TABLE_LIST`; do
csv_file="$PROJHOME/csv/${table}.csv"
nice cat "$csv_file" | nice sed 's/\tt\t/\t1\t/g' | \
nice sed 's/\tf\t/\t0\t/g' > "$tmpf"
mv "$tmpf" "$csv_file"
# echo "TRUNCATE TABLE $tabela;" | $MYSQLCONN
echo "LOAD DATA LOCAL INFILE '$csv_file' INTO TABLE $table;" | \
$MYSQLCONN
done
}

Adding auto_increment behaviour to id columns:

function auto_increment {
$MYSQLCONN < $AUTO_INCREMENT

Where AUTO_INCREMENT file looks like:

ALTER TABLE user_activity MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE add_statistics MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT;
ALTER TABLE search_history MODIFY COLUMN `id` INTEGER NOT NULL AUTO_INCREMENT;

I added the above statements for all tables with id.