Re: Upgrade from psql version 9.3 to 9.6.

On Tue, May 7, 2019 at 11:14 AM SAMMY CRISWELL <
Sammy_R_Criswell(at)raytheon(dot)com> wrote:

> I am upgrading PostgreSQL from version 9.3.5 to 9.6.12. The 9.6.12 is on a
> new server. I am going to use pg_dump to make a backup of the DB on 9.3 and
> pg_restore on the new 9.6 server.
>
> Should I already have the onwers of the different DBs created as well as
> empty databases on the new server when I do the restore?
>
>
>
> Any other advice would be appreciated.
>
>
>
> *Sam Criswell* | Senior Systems Engineer I
>
> Infrastructure Engineering
> Intelligence, Information and Services (IIS)
>
> CityLine Phone: +1 972.664.8870
> * Raytheon Company*
> 1737 East Cityline Drive | Richardson, TX 75082 USA
>
>
>
> *Upcoming PTO: 8/5/19 – 8/13/19*
>
>
>

Roles are global and their creation is not part of a single database's
pg_dump, so they have to be created before restoring a pg_dump file.
Otherwise the grants that are applied during the restore will fail. You can
get the role (and all global information) using pg_dumpall first

pg_dumpall -U postgres -g -f globals.sql

https://www.postgresql.org/docs/9.6/app-pg-dumpall.html

Then just feed that file into the new system with psql

psql -U postgres -f globals.sql

https://www.postgresql.org/docs/9.6/app-psql.html

You can use the -C option to pg_restore to have it automatically create the
originally named database for you as part of the restore. Otherwise you
will have to create it first.

https://www.postgresql.org/docs/9.6/app-pgrestore.html

Also, it is highly recommended that you use the pg_dump binary from the
version you're upgrading to to perform the dump, not the original version's
pg_dump. This better ensures any fixes to catalogs and other items are
applied to the dump file that is created. If you don't want to perform the
dump across the network, you can copy the pg_dump binary from the new
system to the old one as long as they're the same architecture.

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com