Greetings, * David G. Johnston (david.g.johnston@xxxxxxxxx) wrote: > On Tue, Mar 21, 2017 at 9:40 AM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote: > > * Thorsten Schöning (tschoening@xxxxxxxxxx) wrote: > > > Guten Tag Keith, > > > am Donnerstag, 23. Februar 2017 um 18:57 schrieben Sie: > > > > > > > You can go directly from 8.3 to 9.5. Just be sure and use the > > > > pg_dump from 9.5 to generate the dump file from 8.3. > > > > > > Is there a specific reason for that? My current approach for upgrades > > > was dumping the old database using the old pg_dump, uninstalling the > > > old Postgres, installing the new one and restore from the old dump. > > > > Yes. Using the older pg_dump may result in dumps that can't be restored > > into the newer version of PG because things like keywords have been > > added and must now be quoted. There are possibly other things that have > > been changed between the old version and the new one which would also > > require the new pg_dump to be used, but keywords are the big one. > The proper solution to the keyword/identifier quoting problem is to use > "--quote-all-identifiers". Using the version of pg_dump to which you are upgrading is an entirely supported and 'proper' approach to dealing with that issue. Using --quote-all-identifiers, in my opinion anyway, leads to rather ugly results. As I also mention, there are other things in newer versions that pg_dump does its best to address (including things like checking for role names in older versions starting with "pg_", which is not allowed in 9.6+). Using the pg_dump from the version of PG to which you are upgrading is the correct and supported approach to doing upgrades. Using --quote-all-identifiers is not. Thanks! Stephen
Attachment:
signature.asc
Description: Digital signature
