Trying to dump a PostgreSQL-10 DB running in a CentOS 7 machine and restore it into a Windows 10 machine
up vote
1
down vote
favorite
I am trying to execute a backup of my PostgreSQL-10 database running on a CentOS 7 machine and then to restore it in a development machine running Windows 10, but I am getting errors during the restore process:
pg_restore: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used
I have made sure that the commands' parameters passed in both dump and restore are the same:
pg_dump --format=c --compress=9 --encoding=UTF-8 -n public --verbose --username=postgres databaseName -W -f /usr/local/production-dump.backup
However it does not work at all. Even though the schema is restored, the data is not, because right before the restore process is going to start restoring data, it gives a "pipe has ended" error and does not proceed with the full restore process. I am using the "custom" format because the plain SQL or tar formats generate huge backup files.
What am I doing wrong? Is there any parameter that I need to pass to the dump or restore commands?
postgresql pg-dump postgresql-10
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
asked Nov 20 at 4:29
leandro.marques
62
add a comment |
up vote
1
down vote
favorite
I am trying to execute a backup of my PostgreSQL-10 database running on a CentOS 7 machine and then to restore it in a development machine running Windows 10, but I am getting errors during the restore process:
pg_restore: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used
I have made sure that the commands' parameters passed in both dump and restore are the same:
pg_dump --format=c --compress=9 --encoding=UTF-8 -n public --verbose --username=postgres databaseName -W -f /usr/local/production-dump.backup
However it does not work at all. Even though the schema is restored, the data is not, because right before the restore process is going to start restoring data, it gives a "pipe has ended" error and does not proceed with the full restore process. I am using the "custom" format because the plain SQL or tar formats generate huge backup files.
What am I doing wrong? Is there any parameter that I need to pass to the dump or restore commands?
postgresql pg-dump postgresql-10
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
asked Nov 20 at 4:29
leandro.marques
62
What's the size of the backup file? Are you sure it was transferred in binary mode to the windows machine (no LF->CRLF conversion)? And what's the origin of the pg_restore binary on the windows host? An installer (which one and which exact version)?
– Daniel Vérité
Nov 20 at 11:17
@DanielVérité the size of the backup file is about 200MB. It was created in centOS using command line and then transferred to the windows host using WinSCP tool. On windows I used the command:pg_restore.exe --verbose --host=localhost --port=5432 --username=postgres --format=c --dbname=myDatabaseName. On both centOS and windows, the pg_dump and restore binaries are from postgresql-10
– leandro.marques
Nov 20 at 12:07
200MB is small enough that it can't be a 32-bit overflow problem. But for winscp, please make sure that you explicitly select the binary transfer mode otherwise it can corrupt your file. See winscp.net/eng/docs/transfer_mode#scp
– Daniel Vérité
Nov 20 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 at 16:27
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am trying to execute a backup of my PostgreSQL-10 database running on a CentOS 7 machine and then to restore it in a development machine running Windows 10, but I am getting errors during the restore process:
pg_restore: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used
I have made sure that the commands' parameters passed in both dump and restore are the same:
pg_dump --format=c --compress=9 --encoding=UTF-8 -n public --verbose --username=postgres databaseName -W -f /usr/local/production-dump.backup
However it does not work at all. Even though the schema is restored, the data is not, because right before the restore process is going to start restoring data, it gives a "pipe has ended" error and does not proceed with the full restore process. I am using the "custom" format because the plain SQL or tar formats generate huge backup files.
What am I doing wrong? Is there any parameter that I need to pass to the dump or restore commands?
postgresql pg-dump postgresql-10
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
asked Nov 20 at 4:29
leandro.marques
62
I am trying to execute a backup of my PostgreSQL-10 database running on a CentOS 7 machine and then to restore it in a development machine running Windows 10, but I am getting errors during the restore process:
pg_restore: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used
I have made sure that the commands' parameters passed in both dump and restore are the same:
pg_dump --format=c --compress=9 --encoding=UTF-8 -n public --verbose --username=postgres databaseName -W -f /usr/local/production-dump.backup
However it does not work at all. Even though the schema is restored, the data is not, because right before the restore process is going to start restoring data, it gives a "pipe has ended" error and does not proceed with the full restore process. I am using the "custom" format because the plain SQL or tar formats generate huge backup files.
What am I doing wrong? Is there any parameter that I need to pass to the dump or restore commands?
postgresql pg-dump postgresql-10
postgresql pg-dump postgresql-10
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
asked Nov 20 at 4:29
leandro.marques
62
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
asked Nov 20 at 4:29
leandro.marques
62
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
edited Nov 21 at 5:29
Laurenz Albe
43.8k102746
43.8k102746
asked Nov 20 at 4:29
leandro.marques
62
asked Nov 20 at 4:29
leandro.marques
62
asked Nov 20 at 4:29
leandro.marques
62
62
What's the size of the backup file? Are you sure it was transferred in binary mode to the windows machine (no LF->CRLF conversion)? And what's the origin of the pg_restore binary on the windows host? An installer (which one and which exact version)?
– Daniel Vérité
Nov 20 at 11:17
@DanielVérité the size of the backup file is about 200MB. It was created in centOS using command line and then transferred to the windows host using WinSCP tool. On windows I used the command:pg_restore.exe --verbose --host=localhost --port=5432 --username=postgres --format=c --dbname=myDatabaseName. On both centOS and windows, the pg_dump and restore binaries are from postgresql-10
– leandro.marques
Nov 20 at 12:07
200MB is small enough that it can't be a 32-bit overflow problem. But for winscp, please make sure that you explicitly select the binary transfer mode otherwise it can corrupt your file. See winscp.net/eng/docs/transfer_mode#scp
– Daniel Vérité
Nov 20 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 at 16:27
add a comment |
What's the size of the backup file? Are you sure it was transferred in binary mode to the windows machine (no LF->CRLF conversion)? And what's the origin of the pg_restore binary on the windows host? An installer (which one and which exact version)?
– Daniel Vérité
Nov 20 at 11:17
@DanielVérité the size of the backup file is about 200MB. It was created in centOS using command line and then transferred to the windows host using WinSCP tool. On windows I used the command:pg_restore.exe --verbose --host=localhost --port=5432 --username=postgres --format=c --dbname=myDatabaseName. On both centOS and windows, the pg_dump and restore binaries are from postgresql-10
– leandro.marques
Nov 20 at 12:07
200MB is small enough that it can't be a 32-bit overflow problem. But for winscp, please make sure that you explicitly select the binary transfer mode otherwise it can corrupt your file. See winscp.net/eng/docs/transfer_mode#scp
– Daniel Vérité
Nov 20 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 at 16:27
What's the size of the backup file? Are you sure it was transferred in binary mode to the windows machine (no LF->CRLF conversion)? And what's the origin of the pg_restore binary on the windows host? An installer (which one and which exact version)?
– Daniel Vérité
Nov 20 at 11:17
What's the size of the backup file? Are you sure it was transferred in binary mode to the windows machine (no LF->CRLF conversion)? And what's the origin of the pg_restore binary on the windows host? An installer (which one and which exact version)?
– Daniel Vérité
Nov 20 at 11:17
@DanielVérité the size of the backup file is about 200MB. It was created in centOS using command line and then transferred to the windows host using WinSCP tool. On windows I used the command:
pg_restore.exe --verbose --host=localhost --port=5432 --username=postgres --format=c --dbname=myDatabaseName. On both centOS and windows, the pg_dump and restore binaries are from postgresql-10– leandro.marques
Nov 20 at 12:07
@DanielVérité the size of the backup file is about 200MB. It was created in centOS using command line and then transferred to the windows host using WinSCP tool. On windows I used the command:
pg_restore.exe --verbose --host=localhost --port=5432 --username=postgres --format=c --dbname=myDatabaseName. On both centOS and windows, the pg_dump and restore binaries are from postgresql-10– leandro.marques
Nov 20 at 12:07
200MB is small enough that it can't be a 32-bit overflow problem. But for winscp, please make sure that you explicitly select the binary transfer mode otherwise it can corrupt your file. See winscp.net/eng/docs/transfer_mode#scp
– Daniel Vérité
Nov 20 at 13:13
200MB is small enough that it can't be a 32-bit overflow problem. But for winscp, please make sure that you explicitly select the binary transfer mode otherwise it can corrupt your file. See winscp.net/eng/docs/transfer_mode#scp
– Daniel Vérité
Nov 20 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 at 16:27
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 at 16:27
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
The likely explanation is that the file was modified during file transfer. Could you calculate a checksum of the file before and after transfer and verify that it is the same?
If the file did not change, then you have probably found a PostgreSQL bug. If you have a dump that you can share and that exhibits the problem, please report this problem to PostgreSQL.
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
Interestingly, the pg_restore worked when I used pgAdmin to run the restore process. I was using DBeaver enterprise to execute pg_restore, and, in theory, it should execute de same command. tks
– leandro.marques
Nov 22 at 16:27
Interesting. Does DBeaver have its own copy ofpg_restore? If yes, you could try to call thatpg_restoredirectly to see if you can reproduce the problem. Then DBeaver must have a buggy version. If DBeaver doesn't have its ownpg_restore, then it would be interesting to know whichpg_restoreit called and how.stracemay help to answer such questions.
– Laurenz Albe
Nov 23 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 at 12:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53386248%2ftrying-to-dump-a-postgresql-10-db-running-in-a-centos-7-machine-and-restore-it-i%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
up vote
0
down vote
The likely explanation is that the file was modified during file transfer. Could you calculate a checksum of the file before and after transfer and verify that it is the same?
If the file did not change, then you have probably found a PostgreSQL bug. If you have a dump that you can share and that exhibits the problem, please report this problem to PostgreSQL.
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
Interestingly, the pg_restore worked when I used pgAdmin to run the restore process. I was using DBeaver enterprise to execute pg_restore, and, in theory, it should execute de same command. tks
– leandro.marques
Nov 22 at 16:27
Interesting. Does DBeaver have its own copy ofpg_restore? If yes, you could try to call thatpg_restoredirectly to see if you can reproduce the problem. Then DBeaver must have a buggy version. If DBeaver doesn't have its ownpg_restore, then it would be interesting to know whichpg_restoreit called and how.stracemay help to answer such questions.
– Laurenz Albe
Nov 23 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 at 12:09
add a comment |
up vote
0
down vote
The likely explanation is that the file was modified during file transfer. Could you calculate a checksum of the file before and after transfer and verify that it is the same?
If the file did not change, then you have probably found a PostgreSQL bug. If you have a dump that you can share and that exhibits the problem, please report this problem to PostgreSQL.
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
Interestingly, the pg_restore worked when I used pgAdmin to run the restore process. I was using DBeaver enterprise to execute pg_restore, and, in theory, it should execute de same command. tks
– leandro.marques
Nov 22 at 16:27
Interesting. Does DBeaver have its own copy ofpg_restore? If yes, you could try to call thatpg_restoredirectly to see if you can reproduce the problem. Then DBeaver must have a buggy version. If DBeaver doesn't have its ownpg_restore, then it would be interesting to know whichpg_restoreit called and how.stracemay help to answer such questions.
– Laurenz Albe
Nov 23 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 at 12:09
add a comment |
up vote
0
down vote
up vote
0
down vote
The likely explanation is that the file was modified during file transfer. Could you calculate a checksum of the file before and after transfer and verify that it is the same?
If the file did not change, then you have probably found a PostgreSQL bug. If you have a dump that you can share and that exhibits the problem, please report this problem to PostgreSQL.
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
The likely explanation is that the file was modified during file transfer. Could you calculate a checksum of the file before and after transfer and verify that it is the same?
If the file did not change, then you have probably found a PostgreSQL bug. If you have a dump that you can share and that exhibits the problem, please report this problem to PostgreSQL.
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
answered Nov 21 at 5:27
Laurenz Albe
43.8k102746
43.8k102746
Interestingly, the pg_restore worked when I used pgAdmin to run the restore process. I was using DBeaver enterprise to execute pg_restore, and, in theory, it should execute de same command. tks
– leandro.marques
Nov 22 at 16:27
Interesting. Does DBeaver have its own copy ofpg_restore? If yes, you could try to call thatpg_restoredirectly to see if you can reproduce the problem. Then DBeaver must have a buggy version. If DBeaver doesn't have its ownpg_restore, then it would be interesting to know whichpg_restoreit called and how.stracemay help to answer such questions.
– Laurenz Albe
Nov 23 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 at 12:09
add a comment |
Interestingly, the pg_restore worked when I used pgAdmin to run the restore process. I was using DBeaver enterprise to execute pg_restore, and, in theory, it should execute de same command. tks
– leandro.marques
Nov 22 at 16:27
Interesting. Does DBeaver have its own copy ofpg_restore? If yes, you could try to call thatpg_restoredirectly to see if you can reproduce the problem. Then DBeaver must have a buggy version. If DBeaver doesn't have its ownpg_restore, then it would be interesting to know whichpg_restoreit called and how.stracemay help to answer such questions.
– Laurenz Albe
Nov 23 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 at 12:09
Interestingly, the pg_restore worked when I used pgAdmin to run the restore process. I was using DBeaver enterprise to execute pg_restore, and, in theory, it should execute de same command. tks
– leandro.marques
Nov 22 at 16:27
Interestingly, the pg_restore worked when I used pgAdmin to run the restore process. I was using DBeaver enterprise to execute pg_restore, and, in theory, it should execute de same command. tks
– leandro.marques
Nov 22 at 16:27
Interesting. Does DBeaver have its own copy of
pg_restore? If yes, you could try to call that pg_restore directly to see if you can reproduce the problem. Then DBeaver must have a buggy version. If DBeaver doesn't have its own pg_restore, then it would be interesting to know which pg_restore it called and how. strace may help to answer such questions.– Laurenz Albe
Nov 23 at 8:42
Interesting. Does DBeaver have its own copy of
pg_restore? If yes, you could try to call that pg_restore directly to see if you can reproduce the problem. Then DBeaver must have a buggy version. If DBeaver doesn't have its own pg_restore, then it would be interesting to know which pg_restore it called and how. strace may help to answer such questions.– Laurenz Albe
Nov 23 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 at 12:09
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 at 12:09
add a comment |
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53386248%2ftrying-to-dump-a-postgresql-10-db-running-in-a-centos-7-machine-and-restore-it-i%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
What's the size of the backup file? Are you sure it was transferred in binary mode to the windows machine (no LF->CRLF conversion)? And what's the origin of the pg_restore binary on the windows host? An installer (which one and which exact version)?
– Daniel Vérité
Nov 20 at 11:17
@DanielVérité the size of the backup file is about 200MB. It was created in centOS using command line and then transferred to the windows host using WinSCP tool. On windows I used the command:
pg_restore.exe --verbose --host=localhost --port=5432 --username=postgres --format=c --dbname=myDatabaseName. On both centOS and windows, the pg_dump and restore binaries are from postgresql-10– leandro.marques
Nov 20 at 12:07
200MB is small enough that it can't be a 32-bit overflow problem. But for winscp, please make sure that you explicitly select the binary transfer mode otherwise it can corrupt your file. See winscp.net/eng/docs/transfer_mode#scp
– Daniel Vérité
Nov 20 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 at 16:27