[postgis-users] postgres and postgis upgrade

Wed Jul 24 15:40:32 PDT 2013

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

On Wed, Jul 17, 2013 at 5:21 PM, Racine, Sylvain <syracine at sympatico.ca>wrote:

>  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.
>
>
> Sylvain Racine, geomatic technician and PHP programmer
> 50, St-Hubert, #5
> Granby, Quebec, Canada
> Phone: +1 (450) 770-9974
> email: sracine at igreffe.net
> Site: http://www.igreffe.net (only in French)
>
>  On Tuesday, July 16, 2013, Racine, Sylvain wrote:
>
>>  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: sracine at igreffe.net
>> Site: http://www.igreffe.net (only in French)
>>
>>  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
>>
>>
>>
>>
>>
>> On Fri, Jul 12, 2013 at 8:52 PM, Racine, Sylvain <syracine at sympatico.ca>wrote:
>>
>>  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
>>
>>
>> Le 2013-07-12 13:51, Marcos Cano a écrit :
>>
>>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttp://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130724/c165bca2/attachment.html>