Postgres Restore Not Restoring Binary DataPostgres pgpass windows - not workingPostgres backuppostgres restore from data files - no data showing upIs there any option to restore data after deleting from a MySQL tableHow can I restore a microsoft sql server database backup to postgres?Howto Byte-level “offline” backup of postgresql databasepg_restore on a single table not restoring indexesWindows Server Backup and Restore Command Line or pgAdminpg_restore changing object owner not working ,Backing up & restoring Postgres using pg_dumpall, split & gzip & --set ON_ERROR_STOP=on
Does an African-American baby born in Youngstown, Ohio have a higher infant mortality rate than a baby born in Iran?
Arcane Tradition and Cost Efficiency: Learn spells on level-up, or learn them from scrolls/spellbooks?
Can an escape pod land on Earth from orbit and not be immediately detected?
How could I create a situation in which a PC has to make a saving throw or be forced to pet a dog?
100-doors puzzle
Fastest path on a snakes and ladders board
How to know whether to write accidentals as sharps or flats?
How do credit card companies know what type of business I'm paying for?
Dedicated bike GPS computer over smartphone
Threading data on TimeSeries
Why not make one big CPU core?
Basic power tool set for Home repair and simple projects
How do you translate “talk shit”?
Struggling to present results from long papers in short time slots
How to test soql with For Update statement
The title "Mord mit Aussicht" explained
How can Caller ID be faked?
Is fission/fusion to iron the most efficient way to convert mass to energy?
Why is gun control associated with the socially liberal Democratic party?
Can Dive Down protect a creature against Pacifism?
Must a CPU have a GPU if the motherboard provides a display port (when there isn't any separate video card)?
Idiom for 'person who gets violent when drunk"
Co-worker is now managing my team. Does this mean that I'm being demoted?
New Site Design!
Postgres Restore Not Restoring Binary Data
Postgres pgpass windows - not workingPostgres backuppostgres restore from data files - no data showing upIs there any option to restore data after deleting from a MySQL tableHow can I restore a microsoft sql server database backup to postgres?Howto Byte-level “offline” backup of postgresql databasepg_restore on a single table not restoring indexesWindows Server Backup and Restore Command Line or pgAdminpg_restore changing object owner not working ,Backing up & restoring Postgres using pg_dumpall, split & gzip & --set ON_ERROR_STOP=on
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
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.
backup postgresql restore binary
asked Jan 5 '15 at 2:16
septerrsepterr
1113
|
show 2 more comments
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.
backup postgresql restore binary
asked Jan 5 '15 at 2:16
septerrsepterr
1113
1
Very urgent situation -> consider postgresql.org/support/professional_support
– Craig Ringer
Jan 5 '15 at 2:38
There's no "binary" type, do you meanbytea? Show the schema - output frompsql'sdcommand on one of the problem tables. Then show the output ofpg_restore -t the_table_name --schema-only.
– Craig Ringer
Jan 5 '15 at 2:41
er, I meantd tablenameon one of the tables with binary data that's causing you problems.
– Craig Ringer
Jan 5 '15 at 2:44
X-posted to 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 5 '15 at 4:02
OK, the other part? Output ofpg_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 5 '15 at 4:03
|
show 2 more comments
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.
backup postgresql restore binary
asked Jan 5 '15 at 2:16
septerrsepterr
1113
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.
backup postgresql restore binary
backup postgresql restore binary
asked Jan 5 '15 at 2:16
septerrsepterr
1113
asked Jan 5 '15 at 2:16
septerrsepterr
1113
edited Jan 5 '15 at 3:00
septerr
asked Jan 5 '15 at 2:16
septerrsepterr
1113
asked Jan 5 '15 at 2:16
septerrsepterr
1113
asked Jan 5 '15 at 2:16
septerrsepterr
1113
1113
1
Very urgent situation -> consider postgresql.org/support/professional_support
– Craig Ringer
Jan 5 '15 at 2:38
There's no "binary" type, do you meanbytea? Show the schema - output frompsql'sdcommand on one of the problem tables. Then show the output ofpg_restore -t the_table_name --schema-only.
– Craig Ringer
Jan 5 '15 at 2:41
er, I meantd tablenameon one of the tables with binary data that's causing you problems.
– Craig Ringer
Jan 5 '15 at 2:44
X-posted to 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 5 '15 at 4:02
OK, the other part? Output ofpg_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 5 '15 at 4:03
|
show 2 more comments
1
Very urgent situation -> consider postgresql.org/support/professional_support
– Craig Ringer
Jan 5 '15 at 2:38
There's no "binary" type, do you meanbytea? Show the schema - output frompsql'sdcommand on one of the problem tables. Then show the output ofpg_restore -t the_table_name --schema-only.
– Craig Ringer
Jan 5 '15 at 2:41
er, I meantd tablenameon one of the tables with binary data that's causing you problems.
– Craig Ringer
Jan 5 '15 at 2:44
X-posted to 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 5 '15 at 4:02
OK, the other part? Output ofpg_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 5 '15 at 4:03
1
1
Very urgent situation -> consider postgresql.org/support/professional_support
– Craig Ringer
Jan 5 '15 at 2:38
Very urgent situation -> consider postgresql.org/support/professional_support
– Craig Ringer
Jan 5 '15 at 2: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 5 '15 at 2:41
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 5 '15 at 2:41
er, I meant
d tablename on one of the tables with binary data that's causing you problems.– Craig Ringer
Jan 5 '15 at 2:44
er, I meant
d tablename on one of the tables with binary data that's causing you problems.– Craig Ringer
Jan 5 '15 at 2:44
X-posted to 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 5 '15 at 4:02
X-posted to 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 5 '15 at 4: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 5 '15 at 4:03
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 5 '15 at 4:03
|
show 2 more comments
1 Answer
1
active
oldest
votes
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.
answered Jan 5 '15 at 5:45
septerrsepterr
1113
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f656257%2fpostgres-restore-not-restoring-binary-data%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Jan 5 '15 at 5:45
septerrsepterr
1113
add a comment |
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.
answered Jan 5 '15 at 5:45
septerrsepterr
1113
add a comment |
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.
answered Jan 5 '15 at 5:45
septerrsepterr
1113
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.
answered Jan 5 '15 at 5:45
septerrsepterr
1113
answered Jan 5 '15 at 5:45
septerrsepterr
1113
answered Jan 5 '15 at 5:45
septerrsepterr
1113
answered Jan 5 '15 at 5:45
septerrsepterr
1113
1113
add a comment |
add a comment |
Thanks for contributing an answer to Server Fault!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f656257%2fpostgres-restore-not-restoring-binary-data%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Very urgent situation -> consider postgresql.org/support/professional_support
– Craig Ringer
Jan 5 '15 at 2:38
There's no "binary" type, do you mean
bytea? Show the schema - output frompsql'sdcommand on one of the problem tables. Then show the output ofpg_restore -t the_table_name --schema-only.– Craig Ringer
Jan 5 '15 at 2:41
er, I meant
d tablenameon one of the tables with binary data that's causing you problems.– Craig Ringer
Jan 5 '15 at 2:44
X-posted to 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 5 '15 at 4: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 5 '15 at 4:03