Trying to dump a PostgreSQL-10 DB running in a CentOS 7 machine and restore it into a Windows 10 machine
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 '18 at 5:29
Laurenz Albe
47.7k102748
asked Nov 20 '18 at 4:29
leandro.marquesleandro.marques
62
add a comment |
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 '18 at 5:29
Laurenz Albe
47.7k102748
asked Nov 20 '18 at 4:29
leandro.marquesleandro.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 '18 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 '18 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 '18 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 '18 at 16:27
add a comment |
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 '18 at 5:29
Laurenz Albe
47.7k102748
asked Nov 20 '18 at 4:29
leandro.marquesleandro.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 '18 at 5:29
Laurenz Albe
47.7k102748
asked Nov 20 '18 at 4:29
leandro.marquesleandro.marques
62
edited Nov 21 '18 at 5:29
Laurenz Albe
47.7k102748
asked Nov 20 '18 at 4:29
leandro.marquesleandro.marques
62
edited Nov 21 '18 at 5:29
Laurenz Albe
47.7k102748
edited Nov 21 '18 at 5:29
Laurenz Albe
47.7k102748
edited Nov 21 '18 at 5:29
Laurenz Albe
47.7k102748
47.7k102748
asked Nov 20 '18 at 4:29
leandro.marquesleandro.marques
62
asked Nov 20 '18 at 4:29
leandro.marquesleandro.marques
62
asked Nov 20 '18 at 4:29
leandro.marquesleandro.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 '18 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 '18 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 '18 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 '18 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 '18 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 '18 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 '18 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 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 '18 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 '18 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 '18 at 16:27
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 '18 at 16:27
add a comment |
1 Answer
1
active
oldest
votes
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 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
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 '18 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 '18 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 '18 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
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 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
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 '18 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 '18 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 '18 at 12:09
add a comment |
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 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
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 '18 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 '18 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 '18 at 12:09
add a comment |
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 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
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 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
answered Nov 21 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
answered Nov 21 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
answered Nov 21 '18 at 5:27
Laurenz AlbeLaurenz Albe
47.7k102748
47.7k102748
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 '18 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 '18 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 '18 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 '18 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 '18 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 '18 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 '18 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 '18 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 '18 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 '18 at 8:42
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 '18 at 12:09
Maybe DBeaver has its own copy of pg_restore, thanks for noticing that
– leandro.marques
Nov 23 '18 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.
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 '18 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 '18 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 '18 at 13:13
I enabled the binary transfer and got the same results
– leandro.marques
Nov 22 '18 at 16:27