On Thu, Apr 25, 2024 at 3:55 AM Zwettler Markus (OIZ) < markus.zwett...@zuerich.ch> wrote:
> we have to migrate from hosted PG12 to containerized PG16 on private > cloud. > > > > some of the installed PG12 extensions are not offered on the containerized > PG16, eg. PostGIS related extensions like pg_routing and ogr_fdw. > > some of these extensions are not needed anymore. some of these extensions > were installed in their own schema. > > we also need to change the database names and most role names due to > external requirements. > > > > > > I came up with this solution. > > > > dump all roles with pg_dumpall. > > edit this dumpfile and > > - exclude roles not needed > - change required role names > > > > dump all required databases with pg_dump > > - in plain text > - exclude all schemas not needed > > edit this dump file and > > - exclude any "create extension" command for not existing extensions > - change all required role names on permissions and ownerships > > > > > > any missings? > any better solutions? > How big of a database? Editing a giant SQL file in vim is painful. I'd do this, which is conceptually similar to your plan: * pg_dump -Fd * pg_restore --list * Edit the generated list * pg_restore --use-list=edited_list I wonder whether a plain text dump could lead to conversion problems or > something similar? > Maybe, if the collations are different between the source and destination.
