pg_dump (PostgreSQL) 9.4.1 - delay in checking if file exists

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

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general