[postgis-users] postgres and postgis upgrade

[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

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 <javascript:_e({}, 'cvml',
> '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 :
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20130717/57d65a65/attachment.html>