Обсуждение: pg_restore ignores -C when using a restore list -L
Hi, pg_restore silently ignores the inclusion of -C when you do use a restore list. postgres$ pg_dump -Fc postgres > postgres.dump postgres$ pg_restore -C postgres.dump | grep 'CREATE DATABASE' CREATE DATABASE postgres WITH TEMPLATE = template0 ENCODING = 'UTF8'; ## Create a restore list postgres$ pg_restore -l postgres.dump > list.txt ## Attempt to restore using the list postgres$ pg_restore -L list -C postgres.dump | grep 'CREATE DATABASE' The final result does not include an \connect or CREATE DATABASE statements. Expected behavior would be to have CREATE DATABASE in both cases. It would be at least desirable to remove the footgun and error if you are not allowed to use -C in this context. The example above does not involve the use of -d as a connection database. The results are particularly unexpected when you restore an entire database into the postgres by using pg_restore -L list -C -d postgres big_db.dump. Regards Russell
Russell Smith <mr-russ@pws.com.au> writes:
> pg_restore silently ignores the inclusion of -C when you do use a
> restore list.
It would work as you expect if you use -C when creating the list file.
The reason for this is that -C basically means "don't skip the DATABASE
entry". When you use -l without -C, you get a list file that doesn't
include the DATABASE entry. In the last step, you need both -C and
the DATABASE entry listed in the list file in order to have the CREATE
DATABASE command emitted.
We could possibly "fix" this by emitting the DATABASE entry during -l
with or without -C. I'm not sure if that would have any bad side
effects, but a quick test suggests that it will still skip the DATABASE
entry if it's listed in the list file but -C isn't given. Of course,
if you manually remove the DATABASE entry from the list file, you lose
anyway.
regards, tom lane
On 13/05/10 03:39, Tom Lane wrote:
> Russell Smith <mr-russ@pws.com.au> writes:
>
>> pg_restore silently ignores the inclusion of -C when you do use a
>> restore list.
>>
> It would work as you expect if you use -C when creating the list file.
> The reason for this is that -C basically means "don't skip the DATABASE
> entry". When you use -l without -C, you get a list file that doesn't
> include the DATABASE entry. In the last step, you need both -C and
> the DATABASE entry listed in the list file in order to have the CREATE
> DATABASE command emitted.
>
> We could possibly "fix" this by emitting the DATABASE entry during -l
> with or without -C. I'm not sure if that would have any bad side
> effects, but a quick test suggests that it will still skip the DATABASE
> entry if it's listed in the list file but -C isn't given. Of course,
> if you manually remove the DATABASE entry from the list file, you lose
> anyway.
>
By the operation of other items (-C --data-only) passed with -l, it only
produces to contents that would be restored with the other switches
provided. If that's expect behavior, then the documentation of the
switch is incorrect and should read something more like
-l
--list List the items in the archive that would be restored
taking into account any other switches provided. The output of this
operation can be used with the -L option to further restrict and
reorder the items that are restored.
Something like that as documentation for the switch would be clearer
about what it's actually doing. The previous statement of listing the
contents of the archive is clearly not what the switch is doing.
If -l really should list the contents of the archive, then it must list
the entire contents all the time.
Regards
Russell
On 13/05/10 19:26, Russell Smith wrote: > On 13/05/10 03:39, Tom Lane wrote: > >> Russell Smith <mr-russ@pws.com.au> writes: >> >> >>> pg_restore silently ignores the inclusion of -C when you do use a >>> restore list. >>> >>> >> It would work as you expect if you use -C when creating the list file. >> The reason for this is that -C basically means "don't skip the DATABASE >> entry". When you use -l without -C, you get a list file that doesn't >> include the DATABASE entry. In the last step, you need both -C and >> the DATABASE entry listed in the list file in order to have the CREATE >> DATABASE command emitted. >> >> We could possibly "fix" this by emitting the DATABASE entry during -l >> with or without -C. I'm not sure if that would have any bad side >> effects, but a quick test suggests that it will still skip the DATABASE >> entry if it's listed in the list file but -C isn't given. Of course, >> if you manually remove the DATABASE entry from the list file, you lose >> anyway. >> >> > By the operation of other items (-C --data-only) passed with -l, it only > produces to contents that would be restored with the other switches > provided. If that's expect behavior, then the documentation of the > switch is incorrect and should read something more like > > > -l > --list List the items in the archive that would be restored > taking into account any other switches provided. The output of this > operation can be used with the -L option to further restrict and > reorder the items that are restored. > > > Something like that as documentation for the switch would be clearer > about what it's actually doing. The previous statement of listing the > contents of the archive is clearly not what the switch is doing. > > If -l really should list the contents of the archive, then it must list > the entire contents all the time. > Further to these comments, both scenarios should make -l or -L incompatibe with -C --data-only -I -n --schema-only -T -t -x as all these alter the contents of what is restored. You should either use the list to control the items restored or the switches. Using both just created confusion. Regards Russell
Russell Smith <mr-russ@pws.com.au> writes:
>> By the operation of other items (-C --data-only) passed with -l, it only
>> produces to contents that would be restored with the other switches
>> provided. If that's expect behavior, then the documentation of the
>> switch is incorrect and should read something more like
>>
>> -l
>> --list List the items in the archive that would be restored
>> taking into account any other switches provided. The output of this
>> operation can be used with the -L option to further restrict and
>> reorder the items that are restored.
Yeah, -l is limited by other filtering switches, and this definitely
ought to be stated more clearly in the documentation. (The content
of the produced file implies it, since it says *Selected* TOC Entries,
but that's hardly clear enough.)
> Further to these comments, both scenarios should make -l or -L
> incompatibe with -C --data-only -I -n --schema-only -T -t -x as all
> these alter the contents of what is restored. You should either use the
> list to control the items restored or the switches. Using both just
> created confusion.
I believe that allowing the filter switches to act on -l is a useful
behavior, and anyway it's been like that for many years and nobody's
complained before. So I'm not excited about taking out the
functionality.
However, I think -C is a special case because it's quite un-obvious
to the user that it effectively acts as a filter switch --- in fact a
de-filtering switch, because the lack of -C is what filters out the
DATABASE item.
I'm inclined to think that we should document that the output of -l
is restricted by -n and similar switches, but change the code so that
-C doesn't affect -l output. Comments?
regards, tom lane
Excerpts from Tom Lane's message of vie may 14 13:26:06 -0400 2010: > However, I think -C is a special case because it's quite un-obvious > to the user that it effectively acts as a filter switch --- in fact a > de-filtering switch, because the lack of -C is what filters out the > DATABASE item. > > I'm inclined to think that we should document that the output of -l > is restricted by -n and similar switches, but change the code so that > -C doesn't affect -l output. Comments? +1 --
On 15/05/10 03:26, Tom Lane wrote:
> Russell Smith <mr-russ@pws.com.au> writes:
>
>>> By the operation of other items (-C --data-only) passed with -l, it only
>>> produces to contents that would be restored with the other switches
>>> provided. If that's expect behavior, then the documentation of the
>>> switch is incorrect and should read something more like
>>>
>>> -l
>>> --list List the items in the archive that would be restored
>>> taking into account any other switches provided. The output of this
>>> operation can be used with the -L option to further restrict and
>>> reorder the items that are restored.
>>>
> Yeah, -l is limited by other filtering switches, and this definitely
> ought to be stated more clearly in the documentation. (The content
> of the produced file implies it, since it says *Selected* TOC Entries,
> but that's hardly clear enough.)
>
>
>> Further to these comments, both scenarios should make -l or -L
>> incompatibe with -C --data-only -I -n --schema-only -T -t -x as all
>> these alter the contents of what is restored. You should either use the
>> list to control the items restored or the switches. Using both just
>> created confusion.
>>
> I believe that allowing the filter switches to act on -l is a useful
> behavior, and anyway it's been like that for many years and nobody's
> complained before. So I'm not excited about taking out the
> functionality.
>
> However, I think -C is a special case because it's quite un-obvious
> to the user that it effectively acts as a filter switch --- in fact a
> de-filtering switch, because the lack of -C is what filters out the
> DATABASE item.
>
> I'm inclined to think that we should document that the output of -l
> is restricted by -n and similar switches, but change the code so that
> -C doesn't affect -l output. Comments?
>
Sounds good. -L could do with some man page help as well;
--use-list=list-file
Restore elements in list-file only, and in the order
they appear in the file. Lines can be moved and can also be commented out by
placing a ; at the start of the line. (See below for
examples.)
That indicates that "list-file only" lacks clarity when you can further
restrict with command line parameters.
Thanks
Russell
On 15/05/10 05:15, Alvaro Herrera wrote: > Excerpts from Tom Lane's message of vie may 14 13:26:06 -0400 2010: > > >> However, I think -C is a special case because it's quite un-obvious >> to the user that it effectively acts as a filter switch --- in fact a >> de-filtering switch, because the lack of -C is what filters out the >> DATABASE item. >> >> I'm inclined to think that we should document that the output of -l >> is restricted by -n and similar switches, but change the code so that >> -C doesn't affect -l output. Comments? >> > +1 > Was this discussed change made? Was it backported, or only applied to head? Thanks Russell
On Thu, Jun 10, 2010 at 3:58 AM, Russell Smith <mr-russ@pws.com.au> wrote: > On 15/05/10 05:15, Alvaro Herrera wrote: >> Excerpts from Tom Lane's message of vie may 14 13:26:06 -0400 2010: >> >> >>> However, I think -C is a special case because it's quite un-obvious >>> to the user that it effectively acts as a filter switch --- in fact a >>> de-filtering switch, because the lack of -C is what filters out the >>> DATABASE item. >>> >>> I'm inclined to think that we should document that the output of -l >>> is restricted by -n and similar switches, but change the code so that >>> -C doesn't affect -l output. =A0Comments? >>> >> +1 >> > Was this discussed change made? =A0Was it backported, or only applied to = head? I believe this is the commit: http://git.postgresql.org/gitweb?p=3Dpostgresql.git;a=3Dcommit;h=3D3a524653= d18f29676b91f740634a673b72beb6b5 It looks like the code was changed, but I don't see any doc updates. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> writes: > I believe this is the commit: > http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3a524653d18f29676b91f740634a673b72beb6b5 > It looks like the code was changed, but I don't see any doc updates. Eh? http://archives.postgresql.org/pgsql-committers/2010-05/msg00191.php and following entries regards, tom lane
On Thu, Jun 10, 2010 at 9:39 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> I believe this is the commit: > >> http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3a524653d18f29676b91f740634a673b72beb6b5 > >> It looks like the code was changed, but I don't see any doc updates. > > Eh? > http://archives.postgresql.org/pgsql-committers/2010-05/msg00191.php > and following entries Ah, missed that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company