Thread: pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists
I made mistake in a filename in pg_dump command, i.e. have used path from another server, which not exists on this one. pg_dump instead of checking permissions / existence of output file first dumped the whole database and at the end (after some time ... ) threw an error: (...) pg_dump: saving database definition pg_dump: [archiver] could not open output file "/home/.../dum-...._20150707_1059.sql": No such file or directory Is it correct behavior? Why wasting so much time and resources leaving checking the output file at the last moment? -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
I made mistake in a filename in pg_dump command, i.e. have used path from
another server, which not exists on this one. pg_dump instead of checking
permissions / existence of output file first dumped the whole database and
at the end (after some time ... ) threw an error:
(...)
pg_dump: saving database definition
pg_dump: [archiver] could not open output file
"/home/.../dum-...._20150707_1059.sql": No such file or directory
Is it correct behavior? Why wasting so much time and resources leaving
checking the output file at the last moment?
What version of PostgreSQL? What OS? What was the command line? On Linux x86_64, Fedora 22, PostgreSQL version 9.4.4, I did:
pg_dump -f /junk/x tsh009
and, almost immediately, got back:
pg_dump: [archiver] could not open output file "/junk/x": No such file or directory
I even looked at the source to pg_dump (not that I'm a good C developer!) and it appears to me that it basically parses the options, opens the output file, then connects to the database server.
Schrodinger's backup: The condition of any backup is unknown until a restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
John McKown wrote > What version of PostgreSQL? What OS? What was the command line? *OS*: Linux OptiPlex-760 3.8.0-19-generic #29-Ubuntu SMP Wed Apr 17 18:16:28 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux Linux Mint 15 Olivia *pg_dump*: pg_dump (PostgreSQL) 9.4.1 *command line:* opt/PostgreSQL/9.4/bin/pg_dump --host localhost --port 000 --username "000" --no-password --format plain --no-owner --create --clean --encoding UTF8 --inserts --column-inserts --no-privileges --no-tablespaces --ignore-version --verbose --no-unlogged-table-data --file "000-$(date +%Y%m%d_%H%M).sql" --schema "000" "db-000" -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856930.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/07/2015 05:23 AM, pinker wrote: > John McKown wrote >> What version of PostgreSQL? What OS? What was the command line? > > *OS*: Linux OptiPlex-760 3.8.0-19-generic #29-Ubuntu SMP Wed Apr 17 18:16:28 > UTC 2013 x86_64 x86_64 x86_64 GNU/Linux > Linux Mint 15 Olivia > > *pg_dump*: pg_dump (PostgreSQL) 9.4.1 > > *command line:* > opt/PostgreSQL/9.4/bin/pg_dump --host localhost --port 000 --username "000" > --no-password --format plain --no-owner --create --clean --encoding UTF8 > --inserts --column-inserts --no-privileges --no-tablespaces --ignore-version > --verbose --no-unlogged-table-data --file "000-$(date +%Y%m%d_%H%M).sql" > --schema "000" "db-000" So this was not the actual command you ran as I see no path specification. Some notes: 1) plain format is the default so it does not need to specified. 2) Either --inserts or --column-inserts not both. FYI this really slows down the restore process. 3) --ignore-version is deprecated in 9.4 and is itself ignored. > > > > > -- > View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856930.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > So this was not the actual command you ran as I see no path specification. > > Some notes: > > 1) plain format is the default so it does not need to specified. > > 2) Either --inserts or --column-inserts not both. FYI this really slows > down the restore process. > > 3) --ignore-version is deprecated in 9.4 and is itself ignored. command was copied from pgadmin, I've changed only parameters. I'm doing dump from my computer where I have pg_dump 9.4.1 installed from db which is 8.4. -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856940.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/07/2015 06:00 AM, pinker wrote: > Adrian Klaver-4 wrote >> So this was not the actual command you ran as I see no path specification. >> >> Some notes: >> >> 1) plain format is the default so it does not need to specified. >> >> 2) Either --inserts or --column-inserts not both. FYI this really slows >> down the restore process. >> >> 3) --ignore-version is deprecated in 9.4 and is itself ignored. > > > command was copied from pgadmin, I've changed only parameters. > I'm doing dump from my computer where I have pg_dump 9.4.1 installed from db > which is 8.4. So how did you get the wrong file name then, the pgAdmin backup dialog has a file selector? Or did you cut and paste into the file field? > > > > -- > View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856940.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > So how did you get the wrong file name then, the pgAdmin backup dialog > has a file selector? > > Or did you cut and paste into the file field? I've cut, pasted and changed parameters. -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856969.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Adrian Klaver-4 wrote > So how did you get the wrong file name then, the pgAdmin backup dialog > has a file selector? > > Or did you cut and paste into the file field? Anyway, shouldn't postgresql first check if dump can be saved? and then start doing it? -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856979.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
John McKown <john.archie.mckown@gmail.com> writes:
> On Tue, Jul 7, 2015 at 4:26 AM, pinker <pinker@onet.eu> wrote:
>> I made mistake in a filename in pg_dump command, i.e. have used path from
>> another server, which not exists on this one. pg_dump instead of checking
>> permissions / existence of output file first dumped the whole database and
>> at the end (after some time ... ) threw an error:
>>
>> (...)
>> pg_dump: saving database definition
>> pg_dump: [archiver] could not open output file
>> "/home/.../dum-...._20150707_1059.sql": No such file or directory
>>
>> Is it correct behavior? Why wasting so much time and resources leaving
>> checking the output file at the last moment?
> What version of PostgreSQL? What OS? What was the command line? On Linux
> x86_64, Fedora 22, PostgreSQL version 9.4.4, I did:
> pg_dump -f /junk/x tsh009
> and, almost immediately, got back:
> pg_dump: [archiver] could not open output file "/junk/x": No such file or
> directory
There is a noticeable delay if you dump a database with a significant
number of objects in it, eg with the regression test database I get:
$ time pg_dump -f /z/q regression
pg_dump: [archiver] could not open output file "/z/q": No such file or directory
real 0m1.164s
user 0m0.062s
sys 0m0.034s
However, I don't see that with any of the non-plain-text output formats:
$ time pg_dump -f /z/q -Fc regression
pg_dump: [custom archiver] could not open output file "/z/q": No such file or directory
real 0m0.005s
user 0m0.004s
sys 0m0.001s
The reason the non-plain-text output formats fail promptly is that they
try to open the output file in InitArchiveFmt_Custom and siblings.
But plain-text format does not, leaving it until RestoreArchive; which is
code that is shared with pg_restore. I'm not sure how painful that would
be to fix without breaking pg_restore, but almost certainly it would
involve duplicating the file open/close logic into two different places.
Dunno that this problem is significant enough to justify such effort.
You would need an awful lot of objects (not data, but DDL objects) for
the delay to be major.
regards, tom lane
On 07/07/2015 06:55 AM, pinker wrote: > Adrian Klaver-4 wrote >> So how did you get the wrong file name then, the pgAdmin backup dialog >> has a file selector? >> >> Or did you cut and paste into the file field? > > I've cut, pasted and changed parameters. I was talking about when you ran the backup from pgAdmin. The backup dialog has a file selector button to select the path/file you want to back up to. So I was wondering how you got an incorrect path in the first place? Then it occurred to me you might not have have used the selector, but directly pasted the path into the file field, is that the case? Another thought just occurred, that the delay is pgAdmin trying to verify the path/file you entered. I say this because of this line: pg_dump: saving database definition from your original post. I do not remember ever seeing that when using pg_dump directly. Have you tried doing the backup directly from the command line? > > > > > > -- > View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856969.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 07/07/2015 07:12 AM, pinker wrote: > Adrian Klaver-4 wrote >> So how did you get the wrong file name then, the pgAdmin backup dialog >> has a file selector? >> >> Or did you cut and paste into the file field? > > Anyway, shouldn't postgresql first check if dump can be saved? and then > start doing it? pgAdmin != Postgres. You are accessing the pg_dump via another program and so I am trying to help you figure out which one is causing the issue. So: 1) How did you provide the wrong path to pgAdmin? 2) What happens if you run the command from the command line, in other directly and not through pgAdmin? > > > > -- > View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856979.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 07/07/2015 07:12 AM, pinker wrote: > Adrian Klaver-4 wrote >> So how did you get the wrong file name then, the pgAdmin backup dialog >> has a file selector? >> >> Or did you cut and paste into the file field? > > Anyway, shouldn't postgresql first check if dump can be saved? and then > start doing it? Ignore my comment about 'saving database definition' Looking at the source that is from pg_dump and appears when you run with --verbose, which I do not do. That explains why I am not seen it. > > > > -- > View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856979.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > I was talking about when you ran the backup from pgAdmin. The backup > dialog has a file selector button to select the path/file you want to > back up to. So I was wondering how you got an incorrect path in the > first place? Then it occurred to me you might not have have used the > selector, but directly pasted the path into the file field, is that the > case? > > Have you tried doing the backup directly from the command line? > > -- > Sent via pgsql-general mailing list ( > pgsql-general@ > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general I'm doing it directly from command line, just took command with options from pgadmin window (because i don't like to read the whole documentation page with pg_dump options), but always running it from bash, changing only db-name, filepath, port etc. (in bash console) -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856999.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/07/2015 08:15 AM, pinker wrote: > Adrian Klaver-4 wrote >> I was talking about when you ran the backup from pgAdmin. The backup >> dialog has a file selector button to select the path/file you want to >> back up to. So I was wondering how you got an incorrect path in the >> first place? Then it occurred to me you might not have have used the >> selector, but directly pasted the path into the file field, is that the >> case? >> >> Have you tried doing the backup directly from the command line? >> >> -- >> Sent via pgsql-general mailing list ( > >> pgsql-general@ > >> ) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > I'm doing it directly from command line, just took command with options from > pgadmin window (because i don't like to read the whole documentation page > with pg_dump options), That is sort of dangerous:) As an example, do you really want --inserts or --column-inserts, they really slow down a restore. > but always running it from bash, changing only > db-name, filepath, port etc. (in bash console) Then see Tom Lanes explanation. > > > > > -- > View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5856999.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver-4 wrote > That is sort of dangerous:) As an example, do you really want --inserts > or --column-inserts, they really slow down a restore. I know, but this time I need it more for versioning/demo version prepariation so performence isn't important at all, what I care about is only ddl and dictionary data. -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5857333.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Tom Lane-2 wrote > $ time pg_dump -f /z/q regression > pg_dump: [archiver] could not open output file "/z/q": No such file or > directory > > real 0m1.164s > user 0m0.062s > sys 0m0.034s > > However, I don't see that with any of the non-plain-text output formats: In my case this is: pg_dump: reading rewrite rules pg_dump: reading large objects pg_dump: reading dependency data pg_dump: saving encoding = UTF8 pg_dump: saving standard_conforming_strings = off pg_dump: saving database definition pg_dump: [archiver] could not open output file "/djsklj.sql": No such file or directory real 1m6.841s user 0m0.412s sys 0m0.068s -- View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5857335.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 07/09/2015 01:24 AM, pinker wrote: > Tom Lane-2 wrote >> $ time pg_dump -f /z/q regression >> pg_dump: [archiver] could not open output file "/z/q": No such file or >> directory >> >> real 0m1.164s >> user 0m0.062s >> sys 0m0.034s >> >> However, I don't see that with any of the non-plain-text output formats: > > In my case this is: > > pg_dump: reading rewrite rules > pg_dump: reading large objects > pg_dump: reading dependency data > pg_dump: saving encoding = UTF8 > pg_dump: saving standard_conforming_strings = off > pg_dump: saving database definition > pg_dump: [archiver] could not open output file "/djsklj.sql": No such file > or directory > > real 1m6.841s > user 0m0.412s > sys 0m0.068s So per Toms post use the -Fc option tp pg_dump. This will get a compressed version of the dump. All is not lost though. pg_restore has the option of restoring to a database or to a file. If you restore to a file using -f then you get a plain text version. > > > > -- > View this message in context: http://postgresql.nabble.com/pg-dump-PostgreSQL-9-4-1-delay-in-checking-if-file-exists-tp5856890p5857335.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com