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.
