Export Data: PostgreSQL or MySQL?

Postgres is relatevly slow due to the nature 
of the table constraints.

Example to export same tables to Postgres:
Table: CMPREGION total rows processed: 13828
Total time hh:mm:ss: 0:19:11

Table: CMXCHANNEL total rows processed: 13824
Total time hh:mm:ss: 0:24:14

Table: SISTRIPDH total rows processed: 56884
Total time hh:mm:ss:  0:34:20

MySQL handles connections and simple SELECTs very 
fast.

Example to export same tables to Mysql:
Table: CMPREGION total rows processed: 13828
Total time hh:mm:ss: 0:5:34

Table: CMXCHANNEL total rows processed: 13824
Total time hh:mm:ss: 0:5:30

Table: SISTRIPDH total rows processed: 56884
Total time hh:mm:ss: 0:22:13

  

Postgres and jbdc for Postgres are much worse 
in this aspect. Insert, update queries require 
special exceptions to catch SQL warnnigs or errors. 
Java PreparedStatement.setObject requires 
special codes to store NULL values in postgres. 
 
Example with CLOB data type:
Table created in postgres. Loading data ...
Exception in thread "main" Failed to store object - 
Exception: java.lang.NullPointerException

This is a typical error to store CLOB data types 
that has no any problems with Mysql. 
Also depending on machine configuration you might 
consider to limit postgres connections.

MySQL does very good job even on the busiest sites. 
Random disconnects and core dumps are exceptionally rare. 
Jdbc Mysql/Connector has no any extra messages to handle 
through java exceptions and is stable with any data types 
to store objects from java PreparedStatement.setObject. 

Postgres has transactions/rollbacks; 
FOREIGN KEYs and all that.

MySQL has some basic provisions for transactions 
and rollbacks.

Postgres has some important issues to be prepared to 
start postmaster with server-client options:
1. Set enable TCP/IP networking typing tcpip_socket = 1 
   in postgresql.conf file.   
   Usually this line looks like: #tcpip_socket = false;

2. Set access permission for machine connecting with 
   Postgres server: 
   In postgres datadir update pg_hba.conf file as: 
   host all "client IP address"  255.255.255.255 trust 

MySQL has very simple mechanism to establish server-client 
connection. 
For example:
GRANT USAGE ON cdfofprd.* TO dbreader@'hostname' 
      IDENTIFIED BY 'some_pass';
GRANT Select, Insert, Update, Delete ON 
      cdfofprd.* TO dbreader@'hostname';
FLUSH PRIVILEGES;
Where hostname is IP address or machine name that 
connecting with Mysql server as a client.

Postgres has similar features, but a little less 
fine-grained then Mysql. 
 
For example:
use "ups createuser postgres" to create user dbreader,
then:
 GRANT ALL PRIVILEGES ON  myTables TO dbreader;
 GRANT ALL PRIVILEGES ON  myUpdates TO dbreader;
 
 GRANT ALL ON mytables_mytablesid_seq  TO dbreader;
 GRANT ALL ON myupdates_myupdatesid_seq TO dbreader;

On the other hand Postgres can limit logins based on
different criteria - network segment, ident string, etc.

MySQL has exceptionally good fine-grained access control. 
You can GRANT and REVOKE whatever rights you want, based 
on user name, table name and client host name.
For example: 
GRANT USAGE ON cdfofprd.* TO dbreader@'hostname' 
      IDENTIFIED BY 'some_pass';
GRANT Select, Insert, Update, Delete 
      ON cdfofprd.* TO dbreader@'hostname';
FLUSH PRIVILEGES;
Where hostname is IP address or machine name that 
connecting with Mysql server as a client.

In Postgres, Large Objects are very special beasties. 
To create them special lo_create function is used that 
stores the result in a regular table. Large object support 
is broken in Postgres - pg_dump cannot dump LOBs; you need 
to develop your own backup mechanism. 
To export Oracle raw data type it has to be the latest 
version of postgres jdbc driver. jdbc7.1-1.2.jar.does not 
work. Also, autocommit should be off, because the LargeObject 
reference is only valid within a transaction. As soon as the 
sql is executed to get the large object reference, it is 
autocommitted and then this reference can't be used anymore 
since the transaction ended. To turn autocommit off use the 
setAutoCommit() method in Connection: 
Connection con = DriverManager.getConnection(url,user,password);
	con.setAutoCommit(false);

In MySQL, text and binary LOBs are just fields in the table. 
Nothing special - just INSERT, UPDATE, SELECT and DELETE it 
the way you like. There are some limitations on indexing and 
applying functions to these fields.

Postgres supports ALTER TABLE to some extent. 
You can ADD COLUMN, RENAME COLUMN and RENAME TABLE.

MySQL has all options in ALTER TABLE - you can ADD column, 
DROP it, RENAME or CHANGE its type on the fly - very good 
feature for busy servers, when you don't want to lock the 
entire database to dump it, change definition and reload 
it back.