Обсуждение: 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

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