Is there a way to dump schema to files on disk and keep them in sync

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 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/&gt;). 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&gt;

I'm sure it's incomplete, but it's helpful nevertheless.

regards

--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com <http://www.enterprisedb.com&gt;
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.

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: