I'm working on a migration from a very old server (Ubuntu 10.04/Postgres 8.x, etc) to a new instance (18.04) with up-to-date versions of everything in our web stack.

Our PostgreSQL database uses a few extensions—notably PostGIS and fuzzystrmatch—as well as several custom functions. Probably pretty typical.

I expect a pg_dump like this..

pg_dump -U username -h dbname > dbname.backup.sql

..will include everything: extensions, custom functions, and data. But, I don't want the old extensions.

If I initially create a same-named, but empty db, like this..

createdb -U postgres -T template0 --owner=dbowner dbname

..then manually add the extensions like this..

psql -p 5432
\connect dbname;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

..so that everything is recent and up-to-date..

Will a db restore from the .sql file created from a vanilla pg_dump, like above, actually overwrite (a'la CREATE OR REPLACE) any same-named functions in the new extensions?

I'm starting to assume so, and I'm re-thinking this.

Is the best way to do this to first invoke the --data-only option in pg_dump for our db backup, then build our new, same-named schema manually, individually add our custom functions, then import the data-only .sql file?

FWIW I'm hoping to avoid that ^ approach, because I don't want to overlook any of our custom functions by moving them manually. While many of them have a prefix signalling our organization, unfortunately, some don't. Furthermore, I'm concerned if I do a whole db restore, then try to upgrade the extensions, the upgrade process will be painful because the source db/extension versions are so out-of-date to begin with.

  • Personally I'd go with a data only dump that you can also use otherwise, and a fresh setup. PosgreSQL has undergone significant changes between 3 major versions, and it would actually be necessary for PostGIS < 2.0 due to the different extension implementations (more e.g. here)! And you could at least try to get your functions by owner/role...if those were not created by the superuser. – ThingumaBob Aug 8 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.