Postgresql – How to improve restoring the PostgreSQL database

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:

mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
    --compact --no-create-info mysql samples > prepg.dump

sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump

psql session:

CREATE TABLE samples
(
    file_id integer PRIMARY KEY,
    file bytea
);

\i 'path/to/pg.dump'

The command you showed looks fine.

pg_restore -d target_database /path/to/the/backup.dbbackup

At a guess, there were errors during the restore but you didn't see them. Most likely you were running with a user ID that did not have the correct permissions to perform the restore.

If you are certain that there is no data of any value in the target database (which will be deleted by the following command) try:

sudo -u postgres pg_restore --create --clean -d postgres /path/to/dbbackup.sql

If you have issues, post the output in an edit to your question, then comment here.

Also: You do not need to restart the server. At most, you should restart your Rails application. Usually that means restarting Apache, but it depends a bit on how you're running Rails. Restarting the whole server is completely unnecessary.