Schema

Sometimes when we have an existing database and schema full of tables, there comes a time when we need to perform a migration. Maybe it’s because we want to move to or from a cloud service. Perhaps we have a small database and want to do a quick version upgrade via dump / restore. Whatever the reason, we may decide to clean up some technical debt while we’re making the transition.

Many Postgres experts recommend against creating objects in the public schema. This is the default schema that exists in nearly all Postgres databases, and there are often implicit grants that could make our objects available in unexpected scenarios. It’s also a cluttered namespace if all tables, views, functions, etc., are created there by default. Using it is sloppy and makes future data or operation segregation much more difficult.

So how can we move a bunch of existing stuff out of the public schema safely?

Sure, managing a Postgres cluster is great. We’ve got the software installed, more tool wrappers than we can wave several sticks at, and a running cluster that’s ready to do our bidding. But what do we do with it? In fact, what is a Postgres cluster anyway? How are databases involved? What about schemas? There’s critically important nomenclature that new users may find somewhat non-intuitive, so let’s demystify things a bit.