Updated 28/04/2017Migrate postgresql databases with PgAdmin4: problems and solutions

 

The problem discussed in this article has been solved by the pgAdmin team (tested on the pgAdmin 1.4). If you come across this problem and you have a pgAdmin version older than version 4, just download and install the latest version). Nevertheless this method can, always, be used to circumvent possible pgAdmin error by re-using the syntax recovered on the error window.

Recently, the new version  PostgreSQL 9.6 has been made available.  Among the new features , a new PgAdmin client interface is available ( PgAdmin 4 v1.1).
If you want to migrate databases from a previous version of PostgreSQL to a9.6 base, it won’t be easy !
First let’s set the scene: I ‘ll only talk about Windows work, I do not have any info whether the same happens in Linux.
Second, last month, I reported the bug to the PgAdmin team, but, up to this date nothing has changed. But, since we must continue working, here is a way around the problem.
Let’s start with the problem .
If you Have PostgreSQL databases in earlier versions (9.5 and below) and you want to migrate them towards a PostgreSQL 9.6 database

 

In this example, we want to migrate all the tables to the postgis_22_sample database server postgreSQL 9.5 to the server’s postgis_23_sample database postgreSQL 9.6.

The PgAdmin method  consists in backing up the first database to an external  file  (.backup),and,  then restore this file in the second base.

 

With PgAdmlin 4 there is a new option to  export a table to a file text, and then import it into the new database. But this applies to just one table.

 

be aware that  both methods crash…

We launch the backup of the database postgis_22_sample

 

We choose only the directory and name of the backup file , then we click on   backup.

In the lower right corner of the screen you see the process monitoring window:

 

After a few seconds , the message indicates an error :

 

Click on the link   Click here for details   To open the window of information

 

The error message indicates that the UTF-8 Codec has crashed .

It is this type of message you will have :

  • if you try to save a base from a previous version
  • if you try to restore a database from a file created under a previous version
  • if you try export a table with the Export / Import command

The way around the problem is to not use PgAdmin 4 to perform the operation . With the mouse , select the text of the ”   Running command   “

 

  •  Press Ctrl C to copy this text to the
  • Open a file text on your desktop ( right click on the desktop -> New -> Text Document )
  • Paste the text of the command

We will execute this command through a command window. The problem  is that, as it is, it will not run because of the spaces in the programme tree  (pgdump.exe). In order to run it, we have to surround the text

 

C: \ Program Files \ PostgreSQL \ 9.6 \ bin \ pg_dump.exe

With quotation marks. The command text in our file text is now:

   C: \ Program Files \ PostgreSQL \ 9.6 \ bin \ pg_dump.exe   -file «   C: /tmp/base95.backup   »-host«   localhost   ” -Harbor “   5432   -username «   postgres   -no-password -verbose -format = c -blobs “   postgis_22_sample   

Open a command window

 

Copy the command text  to the clipboard, right-click the Command window and select   Paste

 

Type Enter, the command runs without error, this time .

 

We have now our postgresql 9.5 database  backup file .

In order to load it into our postgresql 9.6 base  we will follow the same procedure :

  • use pgadmin control 4 that crashes
  • recover the text of the order
  • execute it in a command window

In pgAdmin 4 go to the 9.6 database that must gather the saved tables .

 

Open the context menu of this database and select   Restor

Complete the file to be  restored (the one we have saved in step previous) and click   Restore

The info window  should display at the right bottom of the screen

 

If it does not appear (which happens often) go to the pgAdmin 4 menu (the menu bar top of the window) and select File -> reset layout.

Click on the link   Click here for details

 

Select the command line and copy and paste this line in the file text.

Complete the restore.exe command with quotation marks:

   C: \ Program Files \ PostgreSQL \ 9.6 \ bin \ pg_restore.exe   »-host«   localhost   ” -Harbor “   5433   -username «   postgres   »-no-password – dbname «  postgis_23_sample   »-verbose«   C: /tmp/base95.backup   

Copy this -paper line in the press, then -The paste into the command window:

 

Once the order is executed, you can open the database in pgAdmin 4

 

And now, the tables have been loaded in the new postgresql 9.6 base.

Leave a Reply

Your email address will not be published. Required fields are marked *