[postgis-users] postgres and postgis upgrade
Racine, Sylvain
syracine at sympatico.ca
Fri Jul 12 19:52:56 PDT 2013
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
> 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/20130712/34cdc614/attachment.html>
More information about the postgis-users
mailing list