[postgis-users] postgres and postgis upgrade

[Marcos Cano]

So I'm trying to upgrade Postgres and postgis.. My current versions are 8.3.2 and 1.3 respectively. And trying to upgrade to postgis 2.0.4 and Postgres 9.2.4

I've been trying a lot of options like:hard upgrade of postgis to 1.5.8 in the Postgres 8.3 ( as I'm sure that version of postgis is compatible with Postgres 8.3 and 9.2.4)

Then installing postgres 9.2.4 + postgis 1.5.8 and do a pg_upgrade and finally do a hard upgrade of postgis to 2.0.4 in the postgres 9.2.4 installation. It  seems to work until an error happened during the pg_upgrade

Your installation contains the "name" data type in user tables.  This data type changed its internal alignment between your old and new  clusters so this cluster cannot currently be upgraded.  You can remove the problem tables and restart the upgrade.

So I tried another option but I don't know if this will work. Here's my idea:

Do a custom dump of the DB in Postgres 8.3.2 + pgis 1.3 .

Install 9.2.4 with postgis 2.0.4

And do a restore with perl script included in the postgis binary folder  (perl utils/postgis_restore.pl)

do you think it will work?

[Paragon Corporation]

Yes (custom dump of 8.3.2 + pgis, create new postgis 2.0.4 in 9.2.4 and restore backup) is the recommended way.  9.2.4 + 1.5.8 are borderline compatible so I would avoid that mix and if your ultimate goal is to go to 2.0, 1.5.8 requires a hard upgrade anyway so not worth the hassle.

---

From: postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Marcos Cano
Sent: Wednesday, July 03, 2013 10:43 AM
To: postgi...@lists.osgeo.org
Subject: [postgis-users] postgres and postgis upgrade

[Marcos Cano]

what version of pg_dump should i use?... i tried the 8..3.2 and i think it works, but trying the suggested one, wich is the latest (9.2.4) seems just to not work properly because it does not dump my entire database (i assume is because of the mismatch of postgis versions)

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

[Racine, Sylvain]

You have to use pg_dump version 8.3.2 to backup your database,e.g. the same version of your source database. To restore, use the Perl script and postgis.sql given with  Postgis 2.0.4. This script calls pg_dump command. It must be pg_dump version 9.2.4, e.g. your destination database version. Use "pg_dump --version" to know the version of your command.

You seem use 2 differents versions of PostgreSQL and PostGIS on the same computer. To get a particular version of a command, type the whole path of the command.

Regard

Sylvain Racine

[Marcos Cano]

well i guess while installing and making the postgis i installed it against the 9.2.4  (with this : "./configure --with-pgconfig=/usr/local/pgsql9.2.4/bin/pg_config" )

the postgis.sql you mention is to create a spatially enabled database? or is it part of the restore process?

and yes im using the full path to the command to do everything.

thank you very much i really appreciate it

[Paragon Corporation]

In theory it shouldn't matter, but as far as dumping goes we usually use the pg_dump that came with the version of PostgreSQL we are dumping.

So in this case pg_dump for 8.3 to guarantee it dumps everything allowed to be dumped in that version.

 

and then restore with the pg_restore that comes with newer version we are restoring to.  In this case pg_restore packaged with 9.2.4

 

Note when using postgis_restore.pl  make sure the right pg_restore is first found.

 

Not sure what platform you are on but FWIW this is how we do it under windows:

 

http://trac.osgeo.org/postgis/wiki/UsersWikiWinUpgrade

 

Under Linux/Unix it would be much the same except Perl is always available so no need for perl install and instead of using

 

set   you use export , ${...} instead of %variable% I think. (You probably also want to move the version of PG first in path if you have multiple installation in your path)

 

Hope that helps,

Leo and Regina

 

---

From: postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Marcos Cano

Sent: Thursday, July 04, 2013 10:08 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] postgres and postgis upgrade

[Racine, Sylvain]

The postgis.sql is a part of the restore process. Because you'll make hard upgrade of PostGIS, you have to use Perl script postgis_restore.pl. This script removes old PostGIS functions from your backup and installs the new ones in the new database. Then, you have to give the path of postgis.sql (or lwpostgis.sql) when you call postgis_restore.pl on command line.

I'm not really fan of the new procedure using "CREATE EXTENSION postgis". It's an automatic process enabled in PostgreSQL 9.1 and more. With this procedure, you have to use PostGIS who is embedded with PostgreSQL package. I encountered earlier some errors when I tried to install PostGIS using this procedure on a Windows box. But, using the old procedure I described above, I had the complete control of the installation and I always got a functionnal database, even with PostgreSQL 9.2.

Regard

Sylvain Racine

[Marcos Cano]

so when running my script everything went well except that when i run  "psql --version" it still runs the 8.3.2 version... so to do psql (9.2.4) i have to indicate the full path to pgsql9.2.4/bin/psql ..

any idea on how to fix this?

[Racine, Sylvain]

Sorry for the delay. It was the week end. So I closed the computer. To disable psql command for 8.3.2 version, you have to remove the whole path of your old PostgreSQL installation from you PATH variable and replace it by the new one. Depending of the type of OS you use, ex. on Linux, type "echo PATH" to see the paths associated to your system or on Windows, type just "PATH" to see the paths.

If you need any help to remove the PostgreSQL 8.3.2 version from PATH variable, please indicate the type of OS you use.

Regard

Sylvain Racine

[Marcos Cano]

ubuntu server 12.04... and when i do "echo $PATH" the environment variable is blank

[Racine, Sylvain]

On 2013-07-08 16:25, Marcos Cano wrote:

ubuntu server 12.04... and when i do "echo $PATH" the environment variable is blank

Really weird...

Anyway, what you could do first it's to rename the 8.3.2 command to disable it. Locate "psql" command of your PostgreSQL version 8.3.2, ex. /usr/bin. Inside the directory, type "sudo mv -i psql psql-8.3.2". Now, type "psql --version". If the command is found and the version is changed to 9.2.4, your lucky. If a "command not found" message appears, you have to add the path of your new version of PostgreSQL commands to your PATH variable. Here below the process...

To edit PATH variable for your whole Ubuntu system, type "sudo gedit /etc/environment" (gedit or your favorite editor). A line like «PATH="....."» should appear. Add your 9.2.4 command path to the variable. Save the file, close your terminal and reopen it and it should work. Be carefull in your editing. You could scrap your PATH variable and lose all your Linux commands. You should make a copy of this file in your /home directory before editing it. If you scrap it, you could type the full path of your Linux command, e.g. "sudo /usr/bin/gedit /etc/environment" to correct the problem or reinstall the backup file.

If the path of your 8.3.2 version is NOT a general path like "/usr/bin" or "/usr/local/bin", you could remove it from your PATH environment variable to disable all old PostgreSQL commands. But if it is, you must rename all the old PostgreSQL command to disable them, ex. pg_dump, pg_restore, etc.

After those operations, you must type the full path to access old 8.3.2 PostgreSQL commands and type only the command without full path to access new 9.2.4 PostgreSQL commands.

Hope it will be usefull

Sylvain Racine

[Marcos Cano]

i think i screw it last time... i did not remember to do the echo $PATH in the postgres user...my bad ...

when i do it in the postgres user the outcome is:
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/pgsql/bin/

so  it is pointing to the 8.3 version (pgsql) my 9.2.4 is /usr/local/pgsql9.2.4/bin/

so how can i change the env variable without screwing it really bad?

thanks for all your support

[Marcos Cano]

do you think putting the database in a read-only mode before the pg_dump -Fc should be a good idea??

thank you

[Racine, Sylvain]

I answer to your first question about how to change PATH variable without screwing it.

There is another way to change PATH variable, but only for one account at the time. You have to add the following lines to a file called .bash_profile (don't miss the dot (.) at the beginning) located in your $HOME or ~ directory:

PATH=/usr/local/pgsql9.2.4/bin/:$PATH
export PATH

By this way, you only add a path to the PATH variable without removing the old pgsql path (who seem be /usr/local/pgsql/bin/ in your system). You might be aware that this way can make confusion in order to know which version of pgsql command will be called. I'm not sure, but I think that the order of the PATH command is as we read it, e.g. from left to right and from top to bottom. That's why I add pgsql9.2.4 path at the very beginning of the PATH variable. Remember also that the pgsql9.2.4 path will be accessible only for the account where you put your .bash_profile file.

Your second question is about to put or not the database in read-only mode before pg_dump -Fc. I want to tell you that you learned me a new feature in PostgreSQL that I didn't hear before. But, I read informations on internet and I learned that it's really a PostgreSQL feature.

I suppose you talk about the source database. There are some informations I don't know about your installation. How large is your setup? If  you have a small database in a university department where you can limit the access of the database to only one or two computers located in the same school room , it's obvious that you just need to turn off those computers, backup the data (pg_dump -Fc) on the server without read-only mode, restore them to the new database and set the new connection of those computers before the users can access the new database and that's it. But if it's a database who is the back-end of a large web site with many redundant servers, I suppose in that case that it's better first to put the web site offline, disconnect all the users from the source database before doing pg_dump -Fc on the master DB, restoring the data, propagate the new connection link, make some "home" tests and re-open the web site after. I think that the question to put or not in read-only mode your PostgreSQL database before your pg_dump -Fc is about to know if you have or not the control on the access of the database. It's obvious that you should not have modifications of the data in your source database between the moment you back them up and you restore them to your new database.

In my experience, I back up and restore regularly a small database on a desktop computer in a small office. I do it when users are not there and I never put PostgreSQL in read-only mode. If you experience the situation of a large web site, I think it's really a good choice to ask your question to and to be helped by a professionnal PostgreSQL specialist as EnterpriseDB.

Hope it will be useful

Sylvain Racine

[Marcos Cano]

thank you very much for your help and time, so my case is the second one... but that does not matter now because after doing the whole process of dumping and perl_restore, it is not working, some databases are empty after the dump and i see a lot of :

ERROR:  constraint "spatial_ref_sys_srid_check" of relation "spatial_ref_sys" does not exist
ERROR:  constraint "spatial_ref_sys_pkey" of relation "spatial_ref_sys" does not exist
ERROR:  type "histogram2d" already exists
ERROR:  function "al_set_stat_celplan" already exists with same argument types
ERROR:  function "al_set_stat_leasing" already exists with same argument types
ERROR:  function "round_time" already exists with same argument types
ERROR:  function "array_accum" already exists with same argument types
ERROR:  relation "ad_id_seq" already exists
ERROR:  relation "agosto_2012" already exists

in the logfile

so idk why im not able to really  (dump or restore??) i think it might be the restore process, as i obviously dont have full control over the large script postgis_restore.pl

[Racine, Sylvain]

See below...

Le 2013-07-16 17:03, Marcos Cano a écrit :

thank you very much for your help and time, so my case is the second one... but that does not matter now because after doing the whole process of dumping and perl_restore, it is not working, some databases are empty after the dump and i see a lot of :

ERROR:  constraint "spatial_ref_sys_srid_check" of relation "spatial_ref_sys" does not exist
ERROR:  constraint "spatial_ref_sys_pkey" of relation "spatial_ref_sys" does not exist

'spatial_ref_sys' table must be dumped in your backup file. If it's not, install spatial_ref_sys.sql script from your new database version to remove those errors.

ERROR:  type "histogram2d" already exists
ERROR:  function "al_set_stat_celplan" already exists with same argument types
ERROR:  function "al_set_stat_leasing" already exists with same argument types
ERROR:  function "round_time" already exists with same argument types
ERROR:  function "array_accum" already exists with same argument types
ERROR:  relation "ad_id_seq" already exists
ERROR:  relation "agosto_2012" already exists

It's sound like you try to restore to a database you already tried to restore a PostGIS database and you just deleted the tables before restoring another one.... If your restore failed, drop completely the new database and create a new one from scratch. The postgis_restore.pl script not just restore tables, but also any PGSQL functions, sequences and some other stuffs. I think that why your restoration failed.

I understand that you have one or many large databases to upgrade. I saw a progression in the resolution of your difficults. But, because new difficults always appear,  I'm not sure you will have enough time to resolve all the problems to get a safety upgrade. If you need faster help and/or want professionnal advices, I can help you using remote as SSH.

Regards

--
Sylvain Racine, geomatic technician and PHP programmer
50, St-Hubert, #5
Granby, Quebec, Canada
Phone: +1 (450) 770-9974
email: sra...@igreffe.net
Site: http://www.igreffe.net (only in French)

[Marcos Cano]

Wow thank you very much, so the way I install spatial_ref_sys is with 

psql -d example -f "path to spatial_ref_sys.sql"

?????

And how do I delete the database completely? I did a DROP database but I think it is not enough 

Thank you very much for your help 

[Racine, Sylvain]

See below...

Le 2013-07-17 15:02, Marcos Cano a écrit :

Wow thank you very much, so the way I install spatial_ref_sys is with 

psql -d example -f "path to spatial_ref_sys.sql"

?????

It's the good way.

And how do I delete the database completely? I did a DROP database but I think it is not enough 

According to PgAdmin III, the GUI interface for PostgreSQL, "DROP DATABASE example;" should delete completely the database and all its components. The components who are not deleted by this command are tablespaces, group roles and user roles, as I see in the left panel of PgAdmin III. I don't know about the old geometry_columns table who is now a VIEW of a system table hidden inside PostgreSQL since PostGIS 2.0. I suppose that the system tables are also deleted by DROP DATABASE.

If you plane to use the new features of PostGIS 2.0, run in your database the script "topology.sql" for topology functions and "rtpostgis.sql" for raster image functions after the restoration.

Thank you very much for your help 

Regards.

[Marcos Cano]

so finally it worked, and i will really like to documented so it may help others with this process