Обсуждение: Upgrade from psql version 9.3 to 9.6.

Поиск
Список
Период
Сортировка

Upgrade from psql version 9.3 to 9.6.

От
SAMMY CRISWELL
Дата:

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

 

Re: Upgrade from psql version 9.3 to 9.6.

От
Keith Fiske
Дата:


On Tue, May 7, 2019 at 11:14 AM SAMMY CRISWELL <Sammy_R_Criswell@raytheon.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


Then just feed that file into the new system with psql

psql -U postgres -f globals.sql


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.


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

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

От
SAMMY CRISWELL
Дата:

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

 

From: Keith Fiske <keith.fiske@crunchydata.com>
Sent: Tuesday, May 7, 2019 10:52 AM
To: SAMMY CRISWELL <Sammy_R_Criswell@raytheon.com>
Cc: pgsql-admin@lists.postgresql.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@raytheon.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

 

 

Then just feed that file into the new system with psql

 

psql -U postgres -f globals.sql

 

 

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.

 

 

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

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

От
Keith Fiske
Дата:


On Tue, May 7, 2019 at 1:29 PM SAMMY CRISWELL <Sammy_R_Criswell@raytheon.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.fiske@crunchydata.com>
Sent: Tuesday, May 7, 2019 10:52 AM
To: SAMMY CRISWELL <Sammy_R_Criswell@raytheon.com>
Cc: pgsql-admin@lists.postgresql.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@raytheon.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

 

 

Then just feed that file into the new system with psql

 

psql -U postgres -f globals.sql

 

 

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.

 

 

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