Обсуждение: Is there a way to dump schema to files on disk and keep them in sync
I want to dump my postgres schema to disk in neat directories like pgadmin presents. Then I want to be able to edit the files and sync changes to the database and ideally if changes were made in the database to sync them back to the disk. Is there a tool that does this? Is there a tool that will dump the schema into separate directories and files like pgadmin does? Thanks.
On 12/12/20 8:58 PM, Tim Uckun wrote: > I want to dump my postgres schema to disk in neat directories like > pgadmin presents. Then I want to be able to edit the files and sync > changes to the database and ideally if changes were made in the > database to sync them back to the disk. That could get really time- and disk-consuming if one of those "edits" was to convert a column in a large 500M row table from numeric to text (or vice versa), or add a column to the "middle" of a table. Tricky to program, too, given all the foreign keys, partitions, etc, etc that can be part of a table. > Is there a tool that does this? Is there a tool that will dump the > schema into separate directories and files like pgadmin does? -- Angular momentum makes the world go 'round.
On 12/12/20 6:58 PM, Tim Uckun wrote: > I want to dump my postgres schema to disk in neat directories like > pgadmin presents. Then I want to be able to edit the files and sync > changes to the database and ideally if changes were made in the > database to sync them back to the disk. > > Is there a tool that does this? Is there a tool that will dump the > schema into separate directories and files like pgadmin does? pgAdmin does not create directories, it just organizes the contents of the system catalogs into GUI elements. For schema management I would suggest a tool like the one I use Sqitch(https://sqitch.org/). It will organize the process of schema creation and management. > > Thanks. > > -- Adrian Klaver adrian.klaver@aklaver.com
On 12/13/20 6:34 PM, Adrian Klaver wrote: > On 12/12/20 6:58 PM, Tim Uckun wrote: >> I want to dump my postgres schema to disk in neat directories like >> pgadmin presents. Then I want to be able to edit the files and sync >> changes to the database and ideally if changes were made in the >> database to sync them back to the disk. >> >> Is there a tool that does this? Is there a tool that will dump the >> schema into separate directories and files like pgadmin does? > > pgAdmin does not create directories, it just organizes the contents of > the system catalogs into GUI elements. > > For schema management I would suggest a tool like the one I use > Sqitch(https://sqitch.org/). It will organize the process of schema > creation and management. > Yeah, that was my thought too. Or maybe look at the other schema versioning tools available - we have a list on the wiki: https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques I'm sure it's incomplete, but it's helpful nevertheless. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
You want Alembic and an afternoon of python writing. You just described an ORM.
On Sun, Dec 13, 2020, 12:53 PM Tomas Vondra <tomas.vondra@enterprisedb.com> wrote:
On 12/13/20 6:34 PM, Adrian Klaver wrote:
> On 12/12/20 6:58 PM, Tim Uckun wrote:
>> I want to dump my postgres schema to disk in neat directories like
>> pgadmin presents. Then I want to be able to edit the files and sync
>> changes to the database and ideally if changes were made in the
>> database to sync them back to the disk.
>>
>> Is there a tool that does this? Is there a tool that will dump the
>> schema into separate directories and files like pgadmin does?
>
> pgAdmin does not create directories, it just organizes the contents of
> the system catalogs into GUI elements.
>
> For schema management I would suggest a tool like the one I use
> Sqitch(https://sqitch.org/). It will organize the process of schema
> creation and management.
>
Yeah, that was my thought too. Or maybe look at the other schema
versioning tools available - we have a list on the wiki:
https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques
I'm sure it's incomplete, but it's helpful nevertheless.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 12/13/20 10:03 AM, Benedict Holland wrote: > You want Alembic and an afternoon of python writing. You just described > an ORM. In other words out of the frying pan and into the fire. > > On Sun, Dec 13, 2020, 12:53 PM Tomas Vondra > <tomas.vondra@enterprisedb.com <mailto:tomas.vondra@enterprisedb.com>> > wrote: > > On 12/13/20 6:34 PM, Adrian Klaver wrote: > > On 12/12/20 6:58 PM, Tim Uckun wrote: > >> I want to dump my postgres schema to disk in neat directories like > >> pgadmin presents. Then I want to be able to edit the files and sync > >> changes to the database and ideally if changes were made in the > >> database to sync them back to the disk. > >> > >> Is there a tool that does this? Is there a tool that will dump the > >> schema into separate directories and files like pgadmin does? > > > > pgAdmin does not create directories, it just organizes the > contents of > > the system catalogs into GUI elements. > > > > For schema management I would suggest a tool like the one I use > > Sqitch(https://sqitch.org/ <https://sqitch.org/>). It will > organize the process of schema > > creation and management. > > > > Yeah, that was my thought too. Or maybe look at the other schema > versioning tools available - we have a list on the wiki: > > https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques > <https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques> > > I'm sure it's incomplete, but it's helpful nevertheless. > > > regards > > -- > Tomas Vondra > EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com> > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@aklaver.com
My primary use case for this is for development and experimentation, I have no intent on using it on production servers :) I normally use migrations for those. On Sun, Dec 13, 2020 at 9:04 PM Ron <ronljohnsonjr@gmail.com> wrote: > > On 12/12/20 8:58 PM, Tim Uckun wrote: > > I want to dump my postgres schema to disk in neat directories like > > pgadmin presents. Then I want to be able to edit the files and sync > > changes to the database and ideally if changes were made in the > > database to sync them back to the disk. > > That could get really time- and disk-consuming if one of those "edits" was > to convert a column in a large 500M row table from numeric to text (or vice > versa), or add a column to the "middle" of a table. > > Tricky to program, too, given all the foreign keys, partitions, etc, etc > that can be part of a table. > > > Is there a tool that does this? Is there a tool that will dump the > > schema into separate directories and files like pgadmin does? > > -- > Angular momentum makes the world go 'round. > >
>pgAdmin does not create directories, it just organizes the contents of the system catalogs into GUI elements. I realize that :). I meant organized in the same way but on disk.
On 12/13/20 1:04 PM, Tim Uckun wrote: >> pgAdmin does not create directories, it just organizes the contents of > the system catalogs into GUI elements. > > > I realize that :). I meant organized in the same way but on disk. > Nothing that I know of. You could do a pg_dump -d some_db -s/--schema-only and to get a starting point for schema definitions. Then create your own directory structure for the schema types(tables, functions, types, etc), with sub-directories for the individual items. I did something like that years ago and it worked until it fell over. For instance should triggers definitions stay with the table or be on their own, same for trigger functions. What happens if you rename something, do you rename your directory? And so on. That is why I moved to Sqitch, someone smarter then I came up with a solution that worked and allows me to do what you state is your goal in your previous post. Namely, use for development and experimentation. In addition once you have done the preceding can then roll out to production. -- Adrian Klaver adrian.klaver@aklaver.com
I mean, you want to dump a schema into a directory. Alembic, sqlalchemy and pathlib will do that in a few minutes. If you want to sync changes then write alembic change scripts and reflect those changes in the ORM, alembic does that. Modifying the ORM and reflecting those changes is a terrible idea and no one does it well. Every application has huge caveats baked into their application so I recommend not doing that. Buy seriously, this is a few hours of work or less.
Thanks,
Ben
On Sun, Dec 13, 2020, 4:05 PM Tim Uckun <timuckun@gmail.com> wrote:
>pgAdmin does not create directories, it just organizes the contents of
the system catalogs into GUI elements.
I realize that :). I meant organized in the same way but on disk.