I have a database in Postgres 8.3 (and PostGIS 4.2), which I need to import in Postgres 10.5.

I am a DB newbie and would appreciate any pointers to get started. Would you recommend a particular book or series of blogs, or is the process fairly simple?

  • This question was put on hold as off-topic because it is too useful :) – mitchus Oct 9 '18 at 20:03
  • The argument that solutions to such a question "quickly become obsolete" is obviously incorrect if you look at the accepted answer: the solution is applicable for versions spanning more than a decade. – mitchus Oct 9 '18 at 20:06
  • @MDCCL any comment on this point? – mitchus Oct 9 '18 at 21:34
  • or from @LowlyDBA? – mitchus Oct 9 '18 at 21:35

According to the manual, you have to use pg_dump:

24.4.1. Migrating data via pg_dump

To dump data from one major version of PostgreSQL and reload it in another, you must use pg_dump; file system level backup methods will not work. (There are checks in place that prevent you from using a data directory with an incompatible version of PostgreSQL, so no great harm can be done by trying to start the wrong server version on a data directory.)

(emphasis mine, this is no different for version 10 than for version 9).

You might be able to get this trick to work; it's a simple one-liner but I've never used it myself.

  • Wow, that's really straightforward. I wonder if it will work with such a big version jump though. Thanks! – mitchus Oct 9 '18 at 8:45

Not the answer you're looking for? Browse other questions tagged or ask your own question.