pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to exclude blobs (large objects) from being loaded by pg_restore?
6+ messages / 5 participants
[nested] [flat]
* How to exclude blobs (large objects) from being loaded by pg_restore?
@ 2015-05-01 14:12 Alanoly Andrews <alanolya@invera.com>
0 siblings, 2 replies; 6+ messages in thread
From: Alanoly Andrews @ 2015-05-01 14:12 UTC (permalink / raw)
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; +Cc: pgsql-admin
Hello,
We have a database that has been unloaded using pg_dump. This database has a table with a field defined as "lo". When restoring this database to another system, we want to avoid loading this particular table as it is very large (about 8GB of blob data) and is not needed on the target system. I tried the following:
1. Create a list of all the tables in the pg_dump file using the -l option of pg_restore
2. Edit out the lines corresponding to the said table (with the "lo" column)
3. Run the pg_restore with the -L option to use the edited list of tables.
I have found that what this does is to exclude only the non-lo fields of the table. So after the load, the table itself is not visible in the target system. But the actual blob data does get loaded since they are contained in the pg_largeobject system table. This table does not occur in the listing produced in step 1 above and so cannot be edited out. Besides it is a system table and should not be excluded anyway.
I'd appreciate some input on how I can get the blob data of a specific table to be excluded from a pg_restore. This is an operation that we need to do on a monthly basis. We do not want to exclude the blobs from the dump itself (since the whole database is to be preserved as a monthly record), but only from the restore.
Postgres 9.1.4 on AIX.
Thanks.
Alanoly Andrews.
Invera Inc.
Montreal, Canada.
________________________________
If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
Si vous ne d?sirez plus recevoir de nos courriels, veuillez appuyer sur D?SABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un autre moyen.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to exclude blobs (large objects) from being loaded by pg_restore?
@ 2015-05-02 13:39 Vincent Veyron <vv.lists@wanadoo.fr>
parent: Alanoly Andrews <alanolya@invera.com>
1 sibling, 2 replies; 6+ messages in thread
From: Vincent Veyron @ 2015-05-02 13:39 UTC (permalink / raw)
To: Alanoly Andrews <alanolya@invera.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-admin
On Fri, 1 May 2015 14:12:08 +0000
Alanoly Andrews <alanolya@invera.com> wrote:
> Hello,
>
> We have a database that has been unloaded using pg_dump. This database has a table with a field defined as "lo". When restoring this database to another system, we want to avoid loading this particular table as it is very large (about 8GB of blob data) and is not needed on the target system. I tried the following:
> 1. Create a list of all the tables in the pg_dump file using the -l option of pg_restore
> 2. Edit out the lines corresponding to the said table (with the "lo" column)
> 3. Run the pg_restore with the -L option to use the edited list of tables.
>
use the -T option of pg_dump?
http://www.postgresql.org/docs/9.4/static/app-pgdump.html
-T table
--exclude-table=table
Do not dump any tables matching the table pattern.
> I have found that what this does is to exclude only the non-lo fields of the table. So after the load, the table itself is not visible in the target system. But the actual blob data does get loaded since they are contained in the pg_largeobject system table. This table does not occur in the listing produced in step 1 above and so cannot be edited out. Besides it is a system table and should not be excluded anyway.
>
> I'd appreciate some input on how I can get the blob data of a specific table to be excluded from a pg_restore. This is an operation that we need to do on a monthly basis. We do not want to exclude the blobs from the dump itself (since the whole database is to be preserved as a monthly record), but only from the restore.
>
> Postgres 9.1.4 on AIX.
>
> Thanks.
>
> Alanoly Andrews.
> Invera Inc.
> Montreal, Canada.
>
>
>
>
>
>
> ________________________________
>
> If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
>
>
>
>
> Si vous ne d?sirez plus recevoir de nos courriels, veuillez appuyer sur D?SABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un autre moyen.
--
Salutations, Vincent Veyron
https://legalcase.libremen.com/
Legal case, contract and insurance claim management software
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to exclude blobs (large objects) from being loaded by pg_restore?
@ 2015-05-02 14:24 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Vincent Veyron <vv.lists@wanadoo.fr>
1 sibling, 0 replies; 6+ messages in thread
From: Andreas Kretschmer @ 2015-05-02 14:24 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Vincent Veyron <vv.lists@wanadoo.fr> wrote:
> On Fri, 1 May 2015 14:12:08 +0000
> Alanoly Andrews <alanolya@invera.com> wrote:
>
> > Hello,
> >
> > We have a database that has been unloaded using pg_dump. This database has a table with a field defined as "lo". When restoring this database to another system, we want to avoid loading this particular table as it is very large (about 8GB of blob data) and is not needed on the target system. I tried the following:
> > 1. Create a list of all the tables in the pg_dump file using the -l option of pg_restore
> > 2. Edit out the lines corresponding to the said table (with the "lo" column)
> > 3. Run the pg_restore with the -L option to use the edited list of tables.
> >
>
> use the -T option of pg_dump?
i think, this isn't a solution.
i HAVE a complete dump, but now i need a restore without the lo - data.
And - you can't create a new dump with -T for several reasons now.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?
@ 2015-05-02 14:27 Alanoly Andrews <alanolya@invera.com>
parent: Vincent Veyron <vv.lists@wanadoo.fr>
1 sibling, 1 reply; 6+ messages in thread
From: Alanoly Andrews @ 2015-05-02 14:27 UTC (permalink / raw)
To: Vincent Veyron <vv.lists@wanadoo.fr>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-admin
Thanks Vincent. But as I have already mentioned, excluding tables from the dump isn't an option. I want to keep the dump complete, but exclude a table (or more) from the restore. The procedure I have outlined does work well with any table except the ones which have a "large object " column.
Alanoly.
Sent from my iPhone
> On May 2, 2015, at 9:39 AM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
>
> On Fri, 1 May 2015 14:12:08 +0000
> Alanoly Andrews <alanolya@invera.com> wrote:
>
>> Hello,
>>
>> We have a database that has been unloaded using pg_dump. This database has a table with a field defined as "lo". When restoring this database to another system, we want to avoid loading this particular table as it is very large (about 8GB of blob data) and is not needed on the target system. I tried the following:
>> 1. Create a list of all the tables in the pg_dump file using the -l option of pg_restore
>> 2. Edit out the lines corresponding to the said table (with the "lo" column)
>> 3. Run the pg_restore with the -L option to use the edited list of tables.
>
> use the -T option of pg_dump?
>
> http://www.postgresql.org/docs/9.4/static/app-pgdump.html
>
> -T table
> --exclude-table=table
>
> Do not dump any tables matching the table pattern.
>
>
>> I have found that what this does is to exclude only the non-lo fields of the table. So after the load, the table itself is not visible in the target system. But the actual blob data does get loaded since they are contained in the pg_largeobject system table. This table does not occur in the listing produced in step 1 above and so cannot be edited out. Besides it is a system table and should not be excluded anyway.
>>
>> I'd appreciate some input on how I can get the blob data of a specific table to be excluded from a pg_restore. This is an operation that we need to do on a monthly basis. We do not want to exclude the blobs from the dump itself (since the whole database is to be preserved as a monthly record), but only from the restore.
>>
>> Postgres 9.1.4 on AIX.
>>
>> Thanks.
>>
>> Alanoly Andrews.
>> Invera Inc.
>> Montreal, Canada.
>>
>>
>>
>>
>>
>>
>> ________________________________
>>
>> If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
>>
>>
>>
>>
>> Si vous ne d?sirez plus recevoir de nos courriels, veuillez appuyer sur D?SABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par retour de courriel ou par un autre moyen.
>
>
> --
> Salutations, Vincent Veyron
>
> https://legalcase.libremen.com/
> Legal case, contract and insurance claim management software
________________________________
If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?
@ 2015-05-02 15:03 Melvin Davidson <melvin6925@gmail.com>
parent: Alanoly Andrews <alanolya@invera.com>
0 siblings, 0 replies; 6+ messages in thread
From: Melvin Davidson @ 2015-05-02 15:03 UTC (permalink / raw)
To: Alanoly Andrews <alanolya@invera.com>; +Cc: Vincent Veyron <vv.lists@wanadoo.fr>; pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-admin
I'm not exactly positive, but I believe pg_extractor has an exclude type
option that might fit your requirement. You can download it here:
https://github.com/omniti-labs/pg_extractor
Also note that this is a Python program, so you might even be able to hack
some change into it to do what you need.
Good luck, and may the bytes be with you.
On Sat, May 2, 2015 at 10:27 AM, Alanoly Andrews <alanolya@invera.com>
wrote:
> Thanks Vincent. But as I have already mentioned, excluding tables from the
> dump isn't an option. I want to keep the dump complete, but exclude a table
> (or more) from the restore. The procedure I have outlined does work well
> with any table except the ones which have a "large object " column.
>
> Alanoly.
>
> Sent from my iPhone
>
> > On May 2, 2015, at 9:39 AM, Vincent Veyron <vv.lists@wanadoo.fr> wrote:
> >
> > On Fri, 1 May 2015 14:12:08 +0000
> > Alanoly Andrews <alanolya@invera.com> wrote:
> >
> >> Hello,
> >>
> >> We have a database that has been unloaded using pg_dump. This database
> has a table with a field defined as "lo". When restoring this database to
> another system, we want to avoid loading this particular table as it is
> very large (about 8GB of blob data) and is not needed on the target system.
> I tried the following:
> >> 1. Create a list of all the tables in the pg_dump file using the
> -l option of pg_restore
> >> 2. Edit out the lines corresponding to the said table (with the
> "lo" column)
> >> 3. Run the pg_restore with the -L option to use the edited list of
> tables.
> >
> > use the -T option of pg_dump?
> >
> > http://www.postgresql.org/docs/9.4/static/app-pgdump.html
> >
> > -T table
> > --exclude-table=table
> >
> > Do not dump any tables matching the table pattern.
> >
> >
> >> I have found that what this does is to exclude only the non-lo fields
> of the table. So after the load, the table itself is not visible in the
> target system. But the actual blob data does get loaded since they are
> contained in the pg_largeobject system table. This table does not occur in
> the listing produced in step 1 above and so cannot be edited out. Besides
> it is a system table and should not be excluded anyway.
> >>
> >> I'd appreciate some input on how I can get the blob data of a specific
> table to be excluded from a pg_restore. This is an operation that we need
> to do on a monthly basis. We do not want to exclude the blobs from the dump
> itself (since the whole database is to be preserved as a monthly record),
> but only from the restore.
> >>
> >> Postgres 9.1.4 on AIX.
> >>
> >> Thanks.
> >>
> >> Alanoly Andrews.
> >> Invera Inc.
> >> Montreal, Canada.
> >>
> >>
> >>
> >>
> >>
> >>
> >> ________________________________
> >>
> >> If you no longer wish to receive any of our emails, click on
> UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>
> This e-mail may be privileged and/or confidential, and the sender does not
> waive any related rights and obligations. Any distribution, use or copying
> of this e-mail or the information it contains by other than an intended
> recipient is unauthorized. If you received this e-mail in error, please
> advise me (by return e-mail or otherwise) immediately.
> >>
> >>
> >>
> >>
> >> Si vous ne d?sirez plus recevoir de nos courriels, veuillez appuyer sur
> D?SABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>
> Ce courriel est confidentiel et prot?g?. L'exp?diteur ne renonce pas aux
> droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou
> copie de ce message ou des renseignements qu'il contient par une personne
> autre que le (les) destinataire(s) d?sign?(s) est interdite. Si vous
> recevez ce courriel par erreur, veuillez m'en aviser imm?diatement, par
> retour de courriel ou par un autre moyen.
> >
> >
> > --
> > Salutations, Vincent Veyron
> >
> > https://legalcase.libremen.com/
> > Legal case, contract and insurance claim management software
>
> ________________________________
>
> If you no longer wish to receive any of our emails, click on
> UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>
> This e-mail may be privileged and/or confidential, and the sender does not
> waive any related rights and obligations. Any distribution, use or copying
> of this e-mail or the information it contains by other than an intended
> recipient is unauthorized. If you received this e-mail in error, please
> advise me (by return e-mail or otherwise) immediately.
>
> Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur
> DÉSABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>
> Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux
> droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou
> copie de ce message ou des renseignements qu'il contient par une personne
> autre que le (les) destinataire(s) désigné(s) est interdite. Si vous
> recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par
> retour de courriel ou par un autre moyen.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] How to exclude blobs (large objects) from being loaded by pg_restore?
@ 2015-05-02 17:57 Daniel Verite <daniel@manitou-mail.org>
parent: Alanoly Andrews <alanolya@invera.com>
1 sibling, 0 replies; 6+ messages in thread
From: Daniel Verite @ 2015-05-02 17:57 UTC (permalink / raw)
To: Alanoly Andrews <alanolya@invera.com>; +Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>; pgsql-admin
Alanoly Andrews wrote:
> This table does not occur in the listing produced in step 1 above and so
> cannot be edited out
Yes, pg_largeobject is not mentioned but the TOC should have an entry named
BLOBS, as well as other entries related to each large object.
If "BLOBS" is removed or commented, pg_restore -L from that list should not
create any large object into the destination database.
As for the table that contain the "lo" column with the references to large
objects, it can be kept or removed from the restore list, it's up to you, but
it does not determine whether blobs are restored or not.
If the table is kept, its "lo" column will have dangling references to
non-existing large objects, but it's irrelevant for pg_restore, as it doesn't
track those references. You can always update that column to NULL after the
load, or ignore it, or drop it, whatever is more convenient.
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2015-05-02 17:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-01 14:12 How to exclude blobs (large objects) from being loaded by pg_restore? Alanoly Andrews <alanolya@invera.com>
2015-05-02 13:39 ` Vincent Veyron <vv.lists@wanadoo.fr>
2015-05-02 14:24 ` Andreas Kretschmer <akretschmer@spamfence.net>
2015-05-02 14:27 ` Alanoly Andrews <alanolya@invera.com>
2015-05-02 15:03 ` Melvin Davidson <melvin6925@gmail.com>
2015-05-02 17:57 ` Daniel Verite <daniel@manitou-mail.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox