Thread: Why doesn't PGAdmin support backing up of schema only or data only?
<div class="WordSection1"><p class="MsoNormal">Greetings!<p class="MsoNormal"> <p class="MsoNormal">I have been curious forseveral years: Why doesn’t PGAdmin’s backup screen allow me to save data only or schema only in custom format?<p class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal">I am trying to test a long-running application that uses a PostgreSQLdatabase. I occasionally need to change or add a database function or make some other change that does not affecttable structures. I also need to save backups of the database at various times as the application runs so I can restartthe application from that point. In the past, if I changed a database function, I needed to open all of the databasecopies I’ve been using and apply the change to each one individually. <p class="MsoNormal"> <p class="MsoNormal">Itoccurred to me recently that if I save the data and schema separately, then I can create a database,load the latest version of the schema (with the changed function) into it, and then choose which state of data toload (beginning of the application, middle, near the end, or whatever). <p class="MsoNormal"> <p class="MsoNormal">WithPGAdmin’s backup screen, I only get a choice of data only or schema only if I select a plain formatfor my backup file. But then, if I use psql to restore my data, all triggers are active, and the effects of the triggersmean that the restored data is not the same as the data that was backed up. psql does not have a disable triggersoptions. On the other hand, pg_restore does have an option to disable triggers, ensuring that the restored datais identical to the saved data. But pg_restore only works on custom format files. PGAdmin’s backup screen is merelya GUI wrapper around the pg_dump program, and with pg_dump there’s no problem saving schema only or data only intocustom-format files. So why doesn’t PGAdmin let us do it?<p class="MsoNormal"> <p class="MsoNormal">Thanks very much.<pclass="MsoNormal"> <p class="MsoNormal">RobR</div>
Re: Why doesn't PGAdmin support backing up of schema only or data only?
From
Guillaume Lelarge
Date:
On Fri, 2014-02-07 at 14:32 +0000, Rob Richardson wrote: > Greetings! > > I have been curious for several years: Why doesn't PGAdmin's backup screen allow me to save data only or schema only incustom format? > > > I am trying to test a long-running application that uses a PostgreSQL database. I occasionally need to change or add adatabase function or make some other change that does not affect table structures. I also need to save backups of the databaseat various times as the application runs so I can restart the application from that point. In the past, if I changeda database function, I needed to open all of the database copies I've been using and apply the change to each oneindividually. > > It occurred to me recently that if I save the data and schema separately, then I can create a database, load the latestversion of the schema (with the changed function) into it, and then choose which state of data to load (beginning ofthe application, middle, near the end, or whatever). > > With PGAdmin's backup screen, I only get a choice of data only or schema only if I select a plain format for my backupfile. But then, if I use psql to restore my data, all triggers are active, and the effects of the triggers mean thatthe restored data is not the same as the data that was backed up. psql does not have a disable triggers options. Onthe other hand, pg_restore does have an option to disable triggers, ensuring that the restored data is identical to thesaved data. But pg_restore only works on custom format files. PGAdmin's backup screen is merely a GUI wrapper aroundthe pg_dump program, and with pg_dump there's no problem saving schema only or data only into custom-format files. So why doesn't PGAdmin let us do it? > To be honest, I have no idea. And it doesn't make any sense to me. When I added the section options to pgAdmin, I allowed it to be used, even with binaries format. It isn't consistent. If noone objects, it would be good to allow schema and data only with binaries format. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Re: Why doesn't PGAdmin support backing up of schema only or data only?
From
Guillaume Lelarge
Date:
On Sat, 2014-02-08 at 18:28 +0100, Guillaume Lelarge wrote: > On Fri, 2014-02-07 at 14:32 +0000, Rob Richardson wrote: > > Greetings! > > > > I have been curious for several years: Why doesn't PGAdmin's backup screen allow me to save data only or schema onlyin custom format? > > > > > > I am trying to test a long-running application that uses a PostgreSQL database. I occasionally need to change or adda database function or make some other change that does not affect table structures. I also need to save backups of thedatabase at various times as the application runs so I can restart the application from that point. In the past, if Ichanged a database function, I needed to open all of the database copies I've been using and apply the change to each oneindividually. > > > > It occurred to me recently that if I save the data and schema separately, then I can create a database, load the latestversion of the schema (with the changed function) into it, and then choose which state of data to load (beginning ofthe application, middle, near the end, or whatever). > > > > With PGAdmin's backup screen, I only get a choice of data only or schema only if I select a plain format for my backupfile. But then, if I use psql to restore my data, all triggers are active, and the effects of the triggers mean thatthe restored data is not the same as the data that was backed up. psql does not have a disable triggers options. Onthe other hand, pg_restore does have an option to disable triggers, ensuring that the restored data is identical to thesaved data. But pg_restore only works on custom format files. PGAdmin's backup screen is merely a GUI wrapper aroundthe pg_dump program, and with pg_dump there's no problem saving schema only or data only into custom-format files. So why doesn't PGAdmin let us do it? > > > > To be honest, I have no idea. And it doesn't make any sense to me. When > I added the section options to pgAdmin, I allowed it to be used, even > with binaries format. It isn't consistent. > > If noone objects, it would be good to allow schema and data only with > binaries format. > I have a patch for this. Barring any objections, I'll apply it next week. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
Attachment
Re: Why doesn't PGAdmin support backing up of schema only or data only?
From
Guillaume Lelarge
Date:
On Sun, 2014-02-09 at 11:18 +0100, Guillaume Lelarge wrote: > On Sat, 2014-02-08 at 18:28 +0100, Guillaume Lelarge wrote: > > On Fri, 2014-02-07 at 14:32 +0000, Rob Richardson wrote: > > > Greetings! > > > > > > I have been curious for several years: Why doesn't PGAdmin's backup screen allow me to save data only or schema onlyin custom format? > > > > > > > > > I am trying to test a long-running application that uses a PostgreSQL database. I occasionally need to change or adda database function or make some other change that does not affect table structures. I also need to save backups of thedatabase at various times as the application runs so I can restart the application from that point. In the past, if Ichanged a database function, I needed to open all of the database copies I've been using and apply the change to each oneindividually. > > > > > > It occurred to me recently that if I save the data and schema separately, then I can create a database, load the latestversion of the schema (with the changed function) into it, and then choose which state of data to load (beginning ofthe application, middle, near the end, or whatever). > > > > > > With PGAdmin's backup screen, I only get a choice of data only or schema only if I select a plain format for my backupfile. But then, if I use psql to restore my data, all triggers are active, and the effects of the triggers mean thatthe restored data is not the same as the data that was backed up. psql does not have a disable triggers options. Onthe other hand, pg_restore does have an option to disable triggers, ensuring that the restored data is identical to thesaved data. But pg_restore only works on custom format files. PGAdmin's backup screen is merely a GUI wrapper aroundthe pg_dump program, and with pg_dump there's no problem saving schema only or data only into custom-format files. So why doesn't PGAdmin let us do it? > > > > > > > To be honest, I have no idea. And it doesn't make any sense to me. When > > I added the section options to pgAdmin, I allowed it to be used, even > > with binaries format. It isn't consistent. > > > > If noone objects, it would be good to allow schema and data only with > > binaries format. > > > > I have a patch for this. Barring any objections, I'll apply it next > week. > Done. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com