<div dir="ltr"><div><div>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 :<br>
<br>ERROR: constraint "spatial_ref_sys_srid_check" of relation "spatial_ref_sys" does not exist<br>ERROR: constraint "spatial_ref_sys_pkey" of relation "spatial_ref_sys" does not exist<br>
ERROR: type "histogram2d" already exists<br>ERROR: function "al_set_stat_celplan" already exists with same argument types<br>ERROR: function "al_set_stat_leasing" already exists with same argument types<br>
ERROR: function "round_time" already exists with same argument types<br>ERROR: function "array_accum" already exists with same argument types<br>ERROR: relation "ad_id_seq" already exists<br>
ERROR: relation "agosto_2012" already exists<br><br><br></div>in the logfile<br><br></div>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 <a href="http://postgis_restore.pl">postgis_restore.pl</a> <br>
<br><br><div><div><br></div></div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Fri, Jul 12, 2013 at 8:52 PM, Racine, Sylvain <span dir="ltr"><<a href="mailto:syracine@sympatico.ca" target="_blank">syracine@sympatico.ca</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
I answer to your first question about how to change PATH variable
without screwing it.<br>
<br>
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:<br>
<br>
PATH=/usr/local/pgsql9.2.4/bin/:$PATH<br>
export PATH<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
Hope it will be useful<br>
<br>
Sylvain Racine<br>
<br>
<br>
<div>Le 2013-07-12 13:51, Marcos Cano a
écrit :<br>
</div><div><div class="h5">
<blockquote type="cite">
<div dir="ltr">
<div>do you think putting the database in a read-only mode
before the pg_dump -Fc should be a good idea??<br>
<br>
</div>
thank you<br>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Wed, Jul 10, 2013 at 2:56 PM, Marcos
Cano <span dir="ltr"><<a href="mailto:mcano@stsa.info" target="_blank">mcano@stsa.info</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div dir="ltr">
<div>
<div>i think i screw it last time... i did not remember
to do the echo $PATH in the postgres user...my bad ...<br>
<br>
</div>
when i do it in the postgres user the outcome is:<br>
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/pgsql/bin/<br>
<br>
</div>
so it is pointing to the 8.3 version (pgsql) my 9.2.4 is
/usr/local/pgsql9.2.4/bin/ <br>
<br>
so how can i change the env variable without screwing it
really bad?<br>
<br>
thanks for all your support<br>
<div><br>
</div>
</div>
<div>
<div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On Tue, Jul 9, 2013 at 5:50
AM, Racine, Sylvain <span dir="ltr"><<a href="mailto:syracine@sympatico.ca" target="_blank">syracine@sympatico.ca</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
<div> <br>
<div>On 2013-07-08 16:25, Marcos Cano wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">ubuntu server 12.04... and
when i do "echo $PATH" the environment
variable is blank<br>
</div>
<div class="gmail_extra"><br>
</div>
</blockquote>
</div>
Really weird... <br>
<br>
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...<br>
<br>
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.<br>
<br>
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.<br>
<br>
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.<br>
<br>
Hope it will be usefull<span><font color="#888888"><br>
<br>
Sylvain Racine</font></span>
<div>
<div><br>
<br>
<blockquote type="cite">
<div class="gmail_extra"><br>
<div class="gmail_quote">On Mon, Jul 8,
2013 at 9:12 AM, Racine, Sylvain <span dir="ltr"><<a href="mailto:syracine@sympatico.ca" target="_blank">syracine@sympatico.ca</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF"> 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.<br>
<br>
If you need any help to remove the
PostgreSQL 8.3.2 version from PATH
variable, please indicate the type
of OS you use.<br>
<br>
Regard<br>
<br>
Sylvain Racine<br>
<br>
<div>Le 2013-07-05 11:41, Marcos
Cano a écrit :<br>
</div>
<div>
<div>
<blockquote type="cite">
<div dir="ltr">
<div>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
..<br>
</div>
any idea on how to fix
this?<br>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On
Thu, Jul 4, 2013 at 4:07
PM, Racine, Sylvain <span dir="ltr"><<a href="mailto:syracine@sympatico.ca" target="_blank">syracine@sympatico.ca</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF">
The postgis.sql is a
part of the restore
process. Because
you'll make hard
upgrade of PostGIS,
you have to use Perl
script <a href="http://postgis_restore.pl" target="_blank">postgis_restore.pl</a>.
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 <a href="http://postgis_restore.pl" target="_blank">postgis_restore.pl</a>
on command line.<br>
<br>
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.<br>
<br>
Regard<br>
<br>
Sylvain Racine<br>
<br>
<br>
<div>Le 2013-07-04
13:06, Marcos Cano
a écrit :<br>
</div>
<div>
<div>
<blockquote type="cite">
<div dir="ltr">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"
)<br>
<div><br>
the
postgis.sql
you mention is
to create a
spatially
enabled
database? or
is it part of
the restore
process?<br>
<br>
</div>
<div>and yes
im using the
full path to
the command to
do everything.<br>
<br>
</div>
<div>thank you
very much i
really
appreciate it<br>
</div>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On
Thu, Jul 4,
2013 at 9:51
AM, Racine,
Sylvain <span dir="ltr"><<a href="mailto:syracine@sympatico.ca" target="_blank">syracine@sympatico.ca</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div text="#000000" bgcolor="#FFFFFF"> 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.<br>
<br>
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.<br>
<br>
Regard<br>
<br>
Sylvain Racine<br>
<br>
<div>Le
2013-07-04
10:07, Marcos
Cano a écrit :<br>
</div>
<div>
<div>
<blockquote type="cite">
<div dir="ltr">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)<br>
</div>
<div class="gmail_extra"><br>
<br>
<div class="gmail_quote">On
Wed, Jul 3,
2013 at 12:00
PM, Paragon
Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial">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.</font></span></div>
<br>
<div dir="ltr" align="left" lang="en-us">
<hr> <font face="Tahoma"><b>From:</b>
<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>
[mailto:<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf
Of </b>Marcos
Cano<br>
<b>Sent:</b>
Wednesday,
July 03, 2013
10:43 AM<br>
<b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b>
[postgis-users]
postgres and
postgis
upgrade<br>
</font><br>
</div>
<div>
<div>
<div dir="ltr">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
<div><br>
</div>
<div>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)</div>
<div>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<br>
<br>
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.<br>
</div>
<div><br>
</div>
<div>So I
tried another
option but I
don't know if
this will
work. Here's
my idea:</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>Do a
custom dump of
the DB in
Postgres 8.3.2
+ pgis 1.3 .</div>
<div><br>
</div>
<div>Install
9.2.4 with
postgis 2.0.4</div>
<div>And do a
restore with
perl script
included in
the postgis
binary folder
(perl utils/<a href="http://postgis_restore.pl" target="_blank">postgis_restore.pl</a>)<br>
</div>
<div><br>
</div>
do you think
it will work?<br>
<div>
<div>
<div><img></div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users
mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users
mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing
list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</div>
</div>
</div>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br>
</blockquote>
</div>
<br>
</div>
</div>
</div>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset></fieldset>
<br>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
<br>
</div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br></div>