Import Postgres 8.3 database in Postgres 10.5

Question

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.

Answer 1

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.