Thread: pg_largeobject.sql script not run after upgrade
Dear community Last week we upgraded our database from 8.4 to 9.1. The upgrade seemed to go fine and the database seems to have been working fine ever since (around a week now). However, today I noticed the output from the pg_upgrade command contained the following: | Your installation contains large objects. | The new database has an additional large object | permission table so default permissions must be | defined for all large objects. The file: | /tmp/pg_largeobject.sql | when executed by psql by the database super-user | will define the default permissions. I missed this at the time, my fault, it was at the end of a stressful migration evening. Is it safe to run this script now, a week in to using the upgraded database? Can this be done while the database is live? Would appreciate your advice. Stuart Ford This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended onlyfor the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or itscontent may result in legal liability. If you have received the email in error, please immediately notify us by telephoneon +44 333 666 5555 or email glide@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internettransmission. Any views/opinions expressed within this email and any attachments are that of the individual and notnecessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham,B1 1TT. Registered in England & Wales. Registered Company No. 06194523.
On Mon, Jun 24, 2013 at 11:13:08AM +0000, Stuart Ford wrote: > Dear community > > Last week we upgraded our database from 8.4 to 9.1. The upgrade seemed to > go fine and the database seems to have been working fine ever since > (around a week now). > > However, today I noticed the output from the pg_upgrade command contained > the following: > > | Your installation contains large objects. > | The new database has an additional large object > | permission table so default permissions must be > | defined for all large objects. The file: > | /tmp/pg_largeobject.sql > | when executed by psql by the database super-user > | will define the default permissions. > > I missed this at the time, my fault, it was at the end of a stressful > migration evening. > > Is it safe to run this script now, a week in to using the upgraded > database? Can this be done while the database is live? Sure, you can run it anytime. Until you run it your large object permissions might not be accurate. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Mon, Jun 24, 2013 at 08:51:46AM -0400, Bruce Momjian wrote:
> On Mon, Jun 24, 2013 at 11:13:08AM +0000, Stuart Ford wrote:
> > Dear community
> >
> > Last week we upgraded our database from 8.4 to 9.1. The upgrade seemed to
> > go fine and the database seems to have been working fine ever since
> > (around a week now).
> >
> > However, today I noticed the output from the pg_upgrade command contained
> > the following:
> >
> > | Your installation contains large objects.
> > | The new database has an additional large object
> > | permission table so default permissions must be
> > | defined for all large objects. The file:
> > | /tmp/pg_largeobject.sql
> > | when executed by psql by the database super-user
> > | will define the default permissions.
> >
> > I missed this at the time, my fault, it was at the end of a stressful
> > migration evening.
> >
> > Is it safe to run this script now, a week in to using the upgraded
> > database? Can this be done while the database is live?
>
> Sure, you can run it anytime. Until you run it your large object
> permissions might not be accurate.
Looking further, here is the command that is executed:
SELECT pg_catalog.lo_create(t.loid)
FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) AS t;
If you have created _new_ large objects since the upgrde, the script
might throw an error, as there is already metadata for those large
objects. You might need to delete the rows in pg_largeobject_metadata
before running the script; this will reset all the large object
permissions to default.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
On 24/06/2013 14:00, "Bruce Momjian" <bruce@momjian.us> wrote: > >Looking further, here is the command that is executed: > > SELECT pg_catalog.lo_create(t.loid) > FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) AS t; > >If you have created _new_ large objects since the upgrde, the script >might throw an error, as there is already metadata for those large >objects. You might need to delete the rows in pg_largeobject_metadata >before running the script; this will reset all the large object >permissions to default. There doesn't appear to be, if this command, which returns 0, is correct: select count(*) from pg_catalog.pg_largeobject_metadata ; So it's OK to go ahead and run at any time? Stuart This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended onlyfor the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or itscontent may result in legal liability. If you have received the email in error, please immediately notify us by telephoneon +44 333 666 5555 or email glide@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internettransmission. Any views/opinions expressed within this email and any attachments are that of the individual and notnecessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham,B1 1TT. Registered in England & Wales. Registered Company No. 06194523.
On Mon, Jun 24, 2013 at 03:25:44PM +0000, Stuart Ford wrote: > On 24/06/2013 14:00, "Bruce Momjian" <bruce@momjian.us> wrote: > > > > > >Looking further, here is the command that is executed: > > > > SELECT pg_catalog.lo_create(t.loid) > > FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) AS t; > > > >If you have created _new_ large objects since the upgrde, the script > >might throw an error, as there is already metadata for those large > >objects. You might need to delete the rows in pg_largeobject_metadata > >before running the script; this will reset all the large object > >permissions to default. > > There doesn't appear to be, if this command, which returns 0, is correct: > > select count(*) from pg_catalog.pg_largeobject_metadata ; > > So it's OK to go ahead and run at any time? Yep. If it fails for some reason, just delete the contents of pg_largeobject_metadata and run it again. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 24/06/2013 17:18, "Bruce Momjian" <bruce@momjian.us> wrote: >On Mon, Jun 24, 2013 at 03:25:44PM +0000, Stuart Ford wrote: >> On 24/06/2013 14:00, "Bruce Momjian" <bruce@momjian.us> wrote: >> >> >> > >> >Looking further, here is the command that is executed: >> > >> > SELECT pg_catalog.lo_create(t.loid) >> > FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) AS t; >> > >> >If you have created _new_ large objects since the upgrde, the script >> >might throw an error, as there is already metadata for those large >> >objects. You might need to delete the rows in pg_largeobject_metadata >> >before running the script; this will reset all the large object >> >permissions to default. >> >> There doesn't appear to be, if this command, which returns 0, is >>correct: >> >> select count(*) from pg_catalog.pg_largeobject_metadata ; >> >> So it's OK to go ahead and run at any time? > >Yep. If it fails for some reason, just delete the contents of >pg_largeobject_metadata and run it again. Do you know if not running this script would explain the fact that our dump file sizes have been much smaller than expected? Stuart This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended onlyfor the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or itscontent may result in legal liability. If you have received the email in error, please immediately notify us by telephoneon +44 333 666 5555 or email glide@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internettransmission. Any views/opinions expressed within this email and any attachments are that of the individual and notnecessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham,B1 1TT. Registered in England & Wales. Registered Company No. 06194523.
On Mon, Jun 24, 2013 at 06:03:40PM +0000, Stuart Ford wrote: > On 24/06/2013 17:18, "Bruce Momjian" <bruce@momjian.us> wrote: > > > >On Mon, Jun 24, 2013 at 03:25:44PM +0000, Stuart Ford wrote: > >> On 24/06/2013 14:00, "Bruce Momjian" <bruce@momjian.us> wrote: > >> > >> > >> > > >> >Looking further, here is the command that is executed: > >> > > >> > SELECT pg_catalog.lo_create(t.loid) > >> > FROM (SELECT DISTINCT loid FROM pg_catalog.pg_largeobject) AS t; > >> > > >> >If you have created _new_ large objects since the upgrde, the script > >> >might throw an error, as there is already metadata for those large > >> >objects. You might need to delete the rows in pg_largeobject_metadata > >> >before running the script; this will reset all the large object > >> >permissions to default. > >> > >> There doesn't appear to be, if this command, which returns 0, is > >>correct: > >> > >> select count(*) from pg_catalog.pg_largeobject_metadata ; > >> > >> So it's OK to go ahead and run at any time? > > > >Yep. If it fails for some reason, just delete the contents of > >pg_largeobject_metadata and run it again. > > Do you know if not running this script would explain the fact that our > dump file sizes have been much smaller than expected? It might be possible if lack of pg_largeobject_metadata values causes your large objects not to be dumped; I have not tested this. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 24/06/2013 19:20, "Bruce Momjian" <bruce@momjian.us> wrote: >On Mon, Jun 24, 2013 at 06:03:40PM +0000, Stuart Ford wrote: >> >> Do you know if not running this script would explain the fact that our >> dump file sizes have been much smaller than expected? > >It might be possible if lack of pg_largeobject_metadata values causes >your large objects not to be dumped; I have not tested this. This did turn out to be the case in the end, FYI. Dump sizes returned to normal after the permissions were created. Many thanks for your assistance, much appreciated. Stuart This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended onlyfor the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or itscontent may result in legal liability. If you have received the email in error, please immediately notify us by telephoneon +44 333 666 5555 or email glide@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internettransmission. Any views/opinions expressed within this email and any attachments are that of the individual and notnecessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham,B1 1TT. Registered in England & Wales. Registered Company No. 06194523.