Thread: pg_dump selectively ignores extension configuration tables
(reposting" apparently I used a verboten word the firsttime around <sigh>. Sorry for any duplicates) The -t and -n options of pg_dump do not dump anything from an extension configuration table, whereas normal pg_dump will dump the user data. To see what I mean, in psql do (tested on pg9.2): 8<-------------------------- create extension postgis; insert into spatial_ref_sys values(42,'test',42,'foo','bar'); 8<-------------------------- Then in bash do: 8<-------------------------- pg_dump test|grep spatial_ref_sys pg_dump -t spatial_ref_sys test|grep spatial_ref_sys pg_dump -n public test|grep spatial_ref_sys 8<-------------------------- Is this intentional, or oversight, or missing feature? Thanks, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
On Fri, Mar 8, 2013 at 5:07 PM, Joe Conway <mail@joeconway.com> wrote: > (reposting" apparently I used a verboten word the first > time around <sigh>. Sorry for any duplicates) > > The -t and -n options of pg_dump do not dump anything from an extension > configuration table, whereas normal pg_dump will dump the user data. > > To see what I mean, in psql do (tested on pg9.2): > 8<-------------------------- > create extension postgis; > insert into spatial_ref_sys > values(42,'test',42,'foo','bar'); > 8<-------------------------- > > Then in bash do: > 8<-------------------------- > pg_dump test|grep spatial_ref_sys > pg_dump -t spatial_ref_sys test|grep spatial_ref_sys > pg_dump -n public test|grep spatial_ref_sys > 8<-------------------------- > > Is this intentional, or oversight, or missing feature? Hmm. It doesn't seem right to me. It seems like it should either dump everything, or dump just the user data portion, when the name matches. Not entirely sure which - probably the latter? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 03/13/2013 03:07 PM, Robert Haas wrote: >> Is this intentional, or oversight, or missing feature? > > Hmm. It doesn't seem right to me. It seems like it should either > dump everything, or dump just the user data portion, when the name > matches. Not entirely sure which - probably the latter? +1 I think it should dump the user data portion, especially since that matches what pg_dump would do if you did not specify the table or schema. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
Joe Conway <mail@joeconway.com> writes: > I think it should dump the user data portion, especially since that > matches what pg_dump would do if you did not specify the table or schema. +1 If you don't have time slots to fix that by then, I will have a look at fixing that while in beta. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On 03/13/2013 04:16 PM, Dimitri Fontaine wrote: > Joe Conway <mail@joeconway.com> writes: >> I think it should dump the user data portion, especially since that >> matches what pg_dump would do if you did not specify the table or schema. > > +1 > > If you don't have time slots to fix that by then, I will have a look at > fixing that while in beta. Here is a patch against 9.1. If there is agreement with the approach I'll redo for 9.2 and git head and apply. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
Attachment
On 03/14/2013 05:23 PM, Joe Conway wrote: > On 03/13/2013 04:16 PM, Dimitri Fontaine wrote: >> Joe Conway <mail@joeconway.com> writes: >>> I think it should dump the user data portion, especially since that >>> matches what pg_dump would do if you did not specify the table or schema. >> >> +1 >> >> If you don't have time slots to fix that by then, I will have a look at >> fixing that while in beta. > > Here is a patch against 9.1. If there is agreement with the approach > I'll redo for 9.2 and git head and apply. Any objections before I commit this? Thanks, Joe -- -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
Attachment
On Mar 25, 2013, at 9:56 AM, Joe Conway <mail@joeconway.com> wrote: > On 03/14/2013 05:23 PM, Joe Conway wrote: >> On 03/13/2013 04:16 PM, Dimitri Fontaine wrote: >>> Joe Conway <mail@joeconway.com> writes: >>>> I think it should dump the user data portion, especially since that >>>> matches what pg_dump would do if you did not specify the table or schema. >>> >>> +1 >>> >>> If you don't have time slots to fix that by then, I will have a look at >>> fixing that while in beta. >> >> Here is a patch against 9.1. If there is agreement with the approach >> I'll redo for 9.2 and git head and apply. > > Any objections before I commit this? > > Since, nobody has picked this one. If there is no objection,then I can test this patch against 9.1 & 9.2. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog:http://vibhork.blogspot.com
Vibhor Kumar escribió: > On Mar 25, 2013, at 9:56 AM, Joe Conway <mail@joeconway.com> wrote: > > > On 03/14/2013 05:23 PM, Joe Conway wrote: > >> On 03/13/2013 04:16 PM, Dimitri Fontaine wrote: > >>> Joe Conway <mail@joeconway.com> writes: > >>>> I think it should dump the user data portion, especially since that > >>>> matches what pg_dump would do if you did not specify the table or schema. > >>> > >>> +1 > >>> > >>> If you don't have time slots to fix that by then, I will have a look at > >>> fixing that while in beta. > >> > >> Here is a patch against 9.1. If there is agreement with the approach > >> I'll redo for 9.2 and git head and apply. > > > > Any objections before I commit this? > > > Since, nobody has picked this one. > > If there is no objection,then I can test this patch against 9.1 & 9.2. Thanks, yes, that would be helpful. Things to think about are whether this affect anything other than tables marked as config for any extension, and whether behavior is sane for them, (i.e. the "condition" thingy works right etc). The whole matter of extension configuration table has been rather tricky to get right ... hopefully we're not ending up with them being more broken now. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 03/25/2013 08:12 AM, Vibhor Kumar wrote: > Since, nobody has picked this one. > > If there is no objection,then I can test this patch against 9.1 & 9.2. Here are diffs for 9.1 and 9.2. The previous email was against 9.3 dev. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support
Attachment
On Mar 25, 2013, at 10:17 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Vibhor Kumar escribió: >> On Mar 25, 2013, at 9:56 AM, Joe Conway <mail@joeconway.com> wrote: >> >>> On 03/14/2013 05:23 PM, Joe Conway wrote: >>>> On 03/13/2013 04:16 PM, Dimitri Fontaine wrote: >>>>> Joe Conway <mail@joeconway.com> writes: >>>>>> I think it should dump the user data portion, especially since that >>>>>> matches what pg_dump would do if you did not specify the table or schema. >>>>> >>>>> +1 >>>>> >>>>> If you don't have time slots to fix that by then, I will have a look at >>>>> fixing that while in beta. >>>> >>>> Here is a patch against 9.1. If there is agreement with the approach >>>> I'll redo for 9.2 and git head and apply. >>> >>> Any objections before I commit this? >>> >> Since, nobody has picked this one. >> >> If there is no objection,then I can test this patch against 9.1 & 9.2. > > Thanks, yes, that would be helpful. Things to think about are whether > this affect anything other than tables marked as config for any > extension, and whether behavior is sane for them, (i.e. the "condition" > thingy works right etc). Sure, I will test and verify this. > The whole matter of extension configuration table has been rather > tricky to get right ... hopefully we're not ending up with them being > more broken now. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog:http://vibhork.blogspot.com
On Mar 25, 2013, at 10:48 AM, Joe Conway <mail@joeconway.com> wrote: > On 03/25/2013 08:12 AM, Vibhor Kumar wrote: >> Since, nobody has picked this one. >> >> If there is no objection,then I can test this patch against 9.1 & 9.2. > > Here are diffs for 9.1 and 9.2. The previous email was against 9.3 dev. Thanks Joe! will test both for 9.1 and 9.2 - Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog:http://vibhork.blogspot.com
On Mar 25, 2013, at 12:01 PM, Vibhor Kumar <vibhor.kumar@enterprisedb.com> wrote: > > On Mar 25, 2013, at 10:48 AM, Joe Conway <mail@joeconway.com> wrote: > >> On 03/25/2013 08:12 AM, Vibhor Kumar wrote: >>> Since, nobody has picked this one. >>> >>> If there is no objection,then I can test this patch against 9.1 & 9.2. >> >> Here are diffs for 9.1 and 9.2. The previous email was against 9.3 dev. > > Thanks Joe! > > will test both for 9.1 and 9.2 I did some testing on this patch with 9.1 and 9.2 source code. Testing included following: 1. Configured PostGIS with 9.1 and 9.2 2. verified all switches of pg_dump with regression db. 3. Checked other extensions, to verify if this impacting those. Everything is working as expected and I haven't found any issue during my test with this patch. While testing this patch, some thoughts came in my mind and thought to share on this thread. Is it possible, if we can have two switches for extension in pg_dump: 1. extension dump with user data in extension tables. 2. User data-only dump from extensions. Thanks & Regards, Vibhor Kumar EnterpriseDB Corporation The Enterprise PostgreSQL Company Blog:http://vibhork.blogspot.com
On 04/04/2013 01:03 PM, Vibhor Kumar wrote: > I did some testing on this patch with 9.1 and 9.2 source code. Testing included following: > 1. Configured PostGIS with 9.1 and 9.2 > 2. verified all switches of pg_dump with regression db. > 3. Checked other extensions, to verify if this impacting those. > > Everything is working as expected and I haven't found any issue during my test with this patch. Thanks Vibhor. Any other comments or concerns before I commit this? > While testing this patch, some thoughts came in my mind and thought to share on this thread. > Is it possible, if we can have two switches for extension in pg_dump: > 1. extension dump with user data in extension tables. > 2. User data-only dump from extensions. This might be worth considering for 9.4 Thanks, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support