3

This is a very urgent situation.

We have a postgres 9.4 database installed on a CentOs machine.

We are trying to take a backup from a Heroku Postgres database and restore it to the database on CentOs machine.

The commands we are using: pg_dump -Fc -h ec2-99-99-99-99.compute-1.amazonaws.com -p 5762 -U xyz -d dbname > file.dump

The dump created by pg_dump seems to be missing data from columns that have the type of binary (these columns contain images in binary format).

When we restore this backup to the database on CentOS using following command, all the images from the image i.e. binary type columns are missing:

pg_restore -d onlinedb ~/file.dump

We have to go live in the morning and are completely bummed by this unexpected issue.

Output of \d attachments (attachments is the problem table) command:

             Table "public.attachments"
      Column      |          Type          | Modifiers 
------------------+------------------------+-----------
 id               | integer                | not null
 configuration_id | integer                | 
 style            | character varying(255) | 
 file_contents    | bytea                  |

More information: The heroku postgres database from which we are creating backup is PostgreSQL 9.2.6. The one we are restoring to is PostgreSQL 9.4.

septerr
  • 141
  • 4
  • 1
    Very urgent situation -> consider http://www.postgresql.org/support/professional_support/ – Craig Ringer Jan 05 '15 at 02:38
  • There's no "binary" type, do you mean `bytea` ? Show the schema - output from `psql`'s `\d` command on one of the problem tables. Then show the output of `pg_restore -t the_table_name --schema-only`. – Craig Ringer Jan 05 '15 at 02:41
  • er, I meant `\d tablename` on one of the tables with binary data that's causing you problems. – Craig Ringer Jan 05 '15 at 02:44
  • X-posted to http://dba.stackexchange.com/q/87529/7788 . Tip: Commenters don't get notified about edits to posts, but do get notified if you comment. – Craig Ringer Jan 05 '15 at 04:02
  • OK, the other part? Output of `pg_restore -t the_table_name` (I think that syntax is right). Also, which pg_dump and pg_restore versions are you using? – Craig Ringer Jan 05 '15 at 04:03
  • Hi, we have cleared out that db and are trying some configuration changes, so couldn't send you the output of `pg_restore -t the_table_name`. For whatever it is worthd `pg_restore -t the_table_name` worked fine on my local computer (mac, postgres 9.3.5) with a backup created from heroku. – septerr Jan 05 '15 at 04:21
  • Sounds like you might want to be running with `--clean --on-error-stop` then... – Craig Ringer Jan 05 '15 at 04:22

1 Answers1

0

We finally were able to restore the backup. We tried so many things that I am not sure what the fix was, but our conclusion is that the problem was the bytea_output setting in postgresql.conf. We think Heroku was using value of 'escape' while our database server was using 'hex' and this caused the problem.

septerr
  • 141
  • 4