Re: Re: Upgrade from psql version 9.3 to 9.6.

On Tue, May 7, 2019 at 1:29 PM SAMMY CRISWELL <Sammy_R_Criswell(at)raytheon(dot)com>
wrote:

> Keith,
>
>
>
> Thanks for this information. I have one other question. One of the old
> Db’s is going to change names on the new server (old is stash new will be
> Bitbucket). Can I the restore or migrate the old DB’s tables into the new
> bitbucket DB?
>
>
>
> Thanks.
>
> Sam
>
>
>

Yes. Normally a database has to exist first when using pg_restore to
restore a dump. The -C is optional to have it created for you as part of
the dump using its original name. Otherwise, it restores into the database
you give it with the -d option. So just create that database first and pass
it to -d.

> *From:* Keith Fiske <keith(dot)fiske(at)crunchydata(dot)com>
> *Sent:* Tuesday, May 7, 2019 10:52 AM
> *To:* SAMMY CRISWELL <Sammy_R_Criswell(at)raytheon(dot)com>
> *Cc:* pgsql-admin(at)lists(dot)postgresql(dot)org
> *Subject:* [External] 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
>

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