[postgis-users] postgres and postgis upgrade
Racine, Sylvain
syracine at sympatico.ca
Wed Jul 17 16:21:30 PDT 2013
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 <javascript:_e({}, 'cvml',
'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 <http://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
>> <http://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 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/20130717/a62de230/attachment.html>
More information about the postgis-users
mailing list