pg_restore ignores -C when using a restore list -L

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_restore ignores -C when using a restore list -L

Russell Smith
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


--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Tom Lane-2
Russell Smith <[hidden email]> 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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Russell Smith
On 13/05/10 03:39, Tom Lane wrote:

> Russell Smith <[hidden email]> 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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Russell Smith
On 13/05/10 19:26, Russell Smith wrote:

> On 13/05/10 03:39, Tom Lane wrote:
>  
>> Russell Smith <[hidden email]> 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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Tom Lane-2
Russell Smith <[hidden email]> 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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Álvaro Herrera
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
--

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Russell Smith
In reply to this post by Tom Lane-2
On 15/05/10 03:26, Tom Lane wrote:

> Russell Smith <[hidden email]> 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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Russell Smith
In reply to this post by Álvaro Herrera
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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Robert Haas
On Thu, Jun 10, 2010 at 3:58 AM, Russell Smith <[hidden email]> 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.  Comments?
>>>
>> +1
>>
> Was this discussed change made?  Was it backported, or only applied to head?

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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Tom Lane-2
Robert Haas <[hidden email]> 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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Reply | Threaded
Open this post in threaded view
|

Re: pg_restore ignores -C when using a restore list -L

Robert Haas
On Thu, Jun 10, 2010 at 9:39 AM, Tom Lane <[hidden email]> wrote:

> Robert Haas <[hidden email]> 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

--
Sent via pgsql-bugs mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs