Обсуждение: Restoring using PG_DUMP in PG12 results in
Hello,
We tried importing into an empty database in PG 12 from the dump that was created in PG 10. Import was successful but we got an message that an error was ignored. We agin imported with -e option and the following message was printed:
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;
There are some blogs that ar pointing to that fact the error is because of different versions of PG Admin Utility namely different versions of pg_dump() and pg_resotre() command in source and destination versions. We use the following command for pg_dump and pg_restore.
pg_dump -U postgres -h 127.0.0.1 --verbose --port=5432 -j 5 -Fd mydb --no-synchronized-snapshots -f /var/mydata/dbbackup
pg_restore -W -h 127.0.0.1 -p 5432 -U postgres -d mydb -v "/var/mydata/dbbackup"
To mitigate the same, we took SQL (.sql) dump of the above database in PG10 and restored in an empty database in PG12, it worked. Using this process, we migrated our database from PG10 to 12.
To confirm whether the problem is really because of the difference in version, we performed 2 exercises, let’s call them approaches. Let me narrate them below.
Approach 1:
- Again, took Folder backup of mydb database using pg_dump in PG12 (The same was brought in PG 12 using SQL Import).
- Created another database named mydb_temp.
- Imported the dump taken in step-1 using pg_import command in temp database, it worked.
Conclusion of Approach 1:
- pg_dump and pg_import from 10 to 12 gave an error that “Schema public already exists”.
- pg_dump and pg_import from 12 to 12 did not give any error.
Approach 2:
- Took a dump of mydb PG10 database FROM PG12 machine using the below command. Not the IP here, instead of localhost, we gave ip of the machine with PG10 installation.
- FROM machine with PG12 binaries. pg_dump -U postgres -h 172.27.2.131 --verbose --port=5432 -j 5 -Fd mydb --no-synchronized-snapshots -f /var/mydata/dbbackup
- Restored in an empty database in PG 12 using command: pg_restore -W -h 127.0.0.1 -p 5432 -U postgres -d mydb -v "/var/mydata/dbbackup"
Conclusion of Approach 2:
- Both backup and restore through same version of utilities worked.
Question to you all:
- Have you faced this scenario?
- Off the approaches mentioned above, which one do you think we should opt for while doing migration from PG 10 to 12 (in different machines)?
PS: Below is the command we use to create database:
CREATE DATABASE mydb WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;
Thanks & Regards
Pranjal Shukla
On 3/14/22 06:39, Shukla, Pranjal wrote: > Hello, > > We tried importing into an empty database in PG 12 from the dump that > was created in PG 10. Import was successful but we got an message that > an error was ignored. We agin imported with -e option and the following > message was printed: > > pg_restore: while PROCESSING TOC: > > pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres > > pg_restore: error: could not execute query: ERROR:schema "public" > already exists Basically an informational error that tells you that the restore was trying to create an object that already exists, in this case the pubic schema. Not really an issue. > > Command was: CREATE SCHEMA public; > > There are some blogs that ar pointing to that fact the error is because > of different versions of PG Admin Utility namely different versions of > pg_dump() and pg_resotre() command in source and destination versions. > We use the following command for pg_dump and pg_restore. > > 1. Have you faced this scenario? > 2. Off the approaches mentioned above, which one do you think we should > opt for while doing migration from PG 10 to 12 (in different machines)? Honestly I could not follow what you did. Best practice when moving from older version to newer version is to use the newer version's pg_dump against the older version and then use the newer versions pg_restore or psql(for text dumps) to restore. > > PS: Below is the command we use to create database: > > /CREATE DATABASE mydb WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' > TEMPLATE template0;/ > > *Thanks & Regards* > > *Pranjal Shukla* > -- Adrian Klaver adrian.klaver@aklaver.com
Thanks Adrian,
Can we say that, "Despite an informational Error, entire data got imported with sanity in the original case"? Also, can
wesay that either of the approaches mentioned i.e. Approach 1 & 2 are equally good to do migration from PG 10 to PG
12?
Thanks & Regards
Pranjal Shukla
On 3/14/22, 8:25 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote:
On 3/14/22 06:39, Shukla, Pranjal wrote:
> Hello,
>
> We tried importing into an empty database in PG 12 from the dump that
> was created in PG 10. Import was successful but we got an message that
> an error was ignored. We agin imported with -e option and the following
> message was printed:
>
> pg_restore: while PROCESSING TOC:
>
> pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres
>
> pg_restore: error: could not execute query: ERROR:schema "public"
> already exists
Basically an informational error that tells you that the restore was
trying to create an object that already exists, in this case the pubic
schema. Not really an issue.
>
> Command was: CREATE SCHEMA public;
>
> There are some blogs that ar pointing to that fact the error is because
> of different versions of PG Admin Utility namely different versions of
> pg_dump() and pg_resotre() command in source and destination versions.
> We use the following command for pg_dump and pg_restore.
>
> 1. Have you faced this scenario?
> 2. Off the approaches mentioned above, which one do you think we should
> opt for while doing migration from PG 10 to 12 (in different machines)?
Honestly I could not follow what you did. Best practice when moving from
older version to newer version is to use the newer version's pg_dump
against the older version and then use the newer versions pg_restore or
psql(for text dumps) to restore.
>
> PS: Below is the command we use to create database:
>
> /CREATE DATABASE mydb WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C'
> TEMPLATE template0;/
>
> *Thanks & Regards*
>
> *Pranjal Shukla*
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On 3/14/22 23:42, Shukla, Pranjal wrote: > Thanks Adrian, > Can we say that, "Despite an informational Error, entire data got imported with sanity in the original case"? Yes. To verify see that the public schema is there and has tables and other objects in it. Also, can we say that either of the approaches mentioned i.e. Approach 1 & 2 are equally good to do migration from PG 10 to PG 12? As I said before I could not understand what you did. The best approach in your case is to: 1) Use the Postgres 12 version of pg_dump to dump the Postgres 10 server. 2) Use the Postgres 12 version of pg_restore to restore it to the Postgres 12 server. > > Thanks & Regards > Pranjal Shukla > > On 3/14/22, 8:25 PM, "Adrian Klaver" <adrian.klaver@aklaver.com> wrote: > > On 3/14/22 06:39, Shukla, Pranjal wrote: > > Hello, > > > > We tried importing into an empty database in PG 12 from the dump that > > was created in PG 10. Import was successful but we got an message that > > an error was ignored. We agin imported with -e option and the following > > message was printed: > > > > pg_restore: while PROCESSING TOC: > > > > pg_restore: from TOC entry 6; 2615 2200 SCHEMA public postgres > > > > pg_restore: error: could not execute query: ERROR:schema "public" > > already exists > > Basically an informational error that tells you that the restore was > trying to create an object that already exists, in this case the pubic > schema. Not really an issue. > > > > Command was: CREATE SCHEMA public; > > > > There are some blogs that ar pointing to that fact the error is because > > of different versions of PG Admin Utility namely different versions of > > pg_dump() and pg_resotre() command in source and destination versions. > > We use the following command for pg_dump and pg_restore. > > > > > 1. Have you faced this scenario? > > 2. Off the approaches mentioned above, which one do you think we should > > opt for while doing migration from PG 10 to 12 (in different machines)? > > Honestly I could not follow what you did. Best practice when moving from > older version to newer version is to use the newer version's pg_dump > against the older version and then use the newer versions pg_restore or > psql(for text dumps) to restore. > > > > > PS: Below is the command we use to create database: > > > > /CREATE DATABASE mydb WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' > > TEMPLATE template0;/ > > > > *Thanks & Regards* > > > > *Pranjal Shukla* > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com