[postgis-users] postgres and postgis upgrade
Racine, Sylvain
syracine at sympatico.ca
Tue Jul 16 15:05:33 PDT 2013
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 <http://postgis_restore.pl>
>
>
>
>
>
> On Fri, Jul 12, 2013 at 8:52 PM, Racine, Sylvain
> <syracine at sympatico.ca <mailto: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 :
>> do you think putting the database in a read-only mode before the
>> pg_dump -Fc should be a good idea??
>>
>> thank you
>>
>>
>> On Wed, Jul 10, 2013 at 2:56 PM, Marcos Cano <mcano at stsa.info
>> <mailto:mcano at stsa.info>> wrote:
>>
>> 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
>>
>>
>>
>> On Tue, Jul 9, 2013 at 5:50 AM, Racine, Sylvain
>> <syracine at sympatico.ca <mailto:syracine at sympatico.ca>> wrote:
>>
>>
>> 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
>>
>>
>>>
>>> On Mon, Jul 8, 2013 at 9:12 AM, Racine, Sylvain
>>> <syracine at sympatico.ca <mailto:syracine at sympatico.ca>>
>>> wrote:
>>>
>>> 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
>>>
>>> Le 2013-07-05 11:41, Marcos Cano a écrit :
>>>> 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?
>>>>
>>>>
>>>> On Thu, Jul 4, 2013 at 4:07 PM, Racine, Sylvain
>>>> <syracine at sympatico.ca
>>>> <mailto:syracine at sympatico.ca>> wrote:
>>>>
>>>> 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 <http://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 <http://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
>>>>
>>>>
>>>> Le 2013-07-04 13:06, Marcos Cano a écrit :
>>>>> 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
>>>>>
>>>>>
>>>>> On Thu, Jul 4, 2013 at 9:51 AM, Racine,
>>>>> Sylvain <syracine at sympatico.ca
>>>>> <mailto:syracine at sympatico.ca>> wrote:
>>>>>
>>>>> 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
>>>>>
>>>>> Le 2013-07-04 10:07, Marcos Cano a écrit :
>>>>>> 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)
>>>>>>
>>>>>>
>>>>>> On Wed, Jul 3, 2013 at 12:00 PM, Paragon
>>>>>> Corporation <lr at pcorp.us
>>>>>> <mailto:lr at pcorp.us>> wrote:
>>>>>>
>>>>>> 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-users-bounces at lists.osgeo.org
>>>>>> <mailto:postgis-users-bounces at lists.osgeo.org>
>>>>>> [mailto:postgis-users-bounces at lists.osgeo.org
>>>>>> <mailto:postgis-users-bounces at lists.osgeo.org>]
>>>>>> *On Behalf Of *Marcos Cano
>>>>>> *Sent:* Wednesday, July 03, 2013 10:43 AM
>>>>>> *To:* postgis-users at lists.osgeo.org
>>>>>> <mailto:postgis-users at lists.osgeo.org>
>>>>>> *Subject:* [postgis-users] postgres
>>>>>> and postgis upgrade
>>>>>>
>>>>>> 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
>>>>>> <http://postgis_restore.pl>)
>>>>>>
>>>>>> do you think it will work?
>>>>>>
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at lists.osgeo.org
>>>>>> <mailto:postgis-users at lists.osgeo.org>
>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> _______________________________________________
>>>>>> postgis-users mailing list
>>>>>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org
>>>>> <mailto:postgis-users at lists.osgeo.org>
>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> <mailto:postgis-users at lists.osgeo.org>
>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>
>>>>
>>>>
>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> <mailto:postgis-users at lists.osgeo.org>
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>
>>>
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> <mailto:postgis-users at lists.osgeo.org>
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>
>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> http://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/20130716/005ea704/attachment.html>
More information about the postgis-users
mailing list