[GeoNetwork-users] Does anyone have a Linux squeeze Postgresql 8.4 pg_dump that I could use

GeoNetwork GeoNetwork User
1

Hi all,

Sorry for cross posting.

I have been trying to migrate an instance of GeoNetwork from one
computer to another. I keep having troubles with the Postgresql
database.

The source computer is running the Debian Lenny operating system with
Postgresql 8.3. This has all the GN configuration and metadata in it.

The target computer is running the Debian Squeeze operating system with
Postgresql 8.4. This is where I would like to copy the source GN onto.

I have another development computer on which I loaded Debian Lenny and
Postgresql 8.3. I thought that I successfully loaded a pg_dumpall of the
source machine into this database. I then did an upgrade of the
operating system to Debian Squeeze which also loaded Postgresql 8.4.

I then did:

  pg_dropcluster 8.4 main; # to get rid of the new database
  pg_upgradecluster 8.3 main; #to upgrade the database to 8.4

This should have worked but I kept on getting errors about hundreds of
different tables not existing.

I then tried:

  pg_dropcluster 8.4 main; # to get rid of the new database
  pg_dropcluster 8.3 main; # to get rid of the old database
  pg_createcluster 8.3 main; # to create a clean 8.3 cluster
  pg_ctlcluster 8.3 main start; # to start the clean 8.3 database
  psql -f dumpfile - postgres; # to load the dumpfile from the source

When I tried to do another pg_upgradecluster 8.3 main I kept on getting
errors "Server is 8.4 library is 8.3" and then hundreds of errors like:
psql:<stdin>:320652: ERROR: type "geometry" is only a shell
psql:<stdin>:320664: ERROR: aggregate public.accum(geometry) does not
exist

What I would like is a dump of some ones Postgresql 8.4 database created
on a Debian Squeeze operating system for GN 2.7 snapshot. Can anyone
provide this or suggest something else that could help?

Thanks.

John Hockaday

2

Hi Jesse,

Thanks for the prompt reply. Please see comments in line below:

On Wed, 2012-05-23 at 08:20 +0200, Jesse Eichar wrote:

I had a similar problem caused by the fact that I was using postgis
and when I upgraded I needed to install the new version of postgis.
The way I did it was terrible but it worked :).

Any solution that works will be good. ;--)

One note, GeoNetwork pollutes the main schema with its tables which is
a bit unfortunate when upgrading postgres versions and it would be
nice to one day make this configurable. But that is another story.

So my upgrade workflow when I had this problem:

1. pg_dump ....

Done.

2. create database in new postgres version

Is there another way to do this other than pg_createcluster 8.4 main?

Is it a GN database or just the cluster? If the former where can I get
the SQL commands to make the tables and functions?

3. manually install postgis into the new database

I tried 'sudo apt-get update postgis' and I got postgis is already the
latest version.

4. add dump back

Step for resulting in lots of errors but no rollbacks. and in the end
it worked. The errors were all of the sort Geometry already exists.

I too get a lot of errors. Can I ignore these and hope that GN will
still work?

I seem to have lost the metadata but I hope I can harvest it from the
original GN server.

Thanks in advance for any help you can provide.

JohnH

These errors were caused by the fact that I had already installed
postgis functions, objects, etc... But this was required because of
the differing versions. In the end this worked.

Maybe this will help you, maybe not but it is worth a try.

Jesse

On Wed, May 23, 2012 at 1:18 AM, john.hockaday
<john.hockaday@anonymised.com> wrote:
        Hi all,
        
        Sorry for cross posting.
        
        I have been trying to migrate an instance of GeoNetwork from
        one
        computer to another. I keep having troubles with the
        Postgresql
        database.
        
        The source computer is running the Debian Lenny operating
        system with
        Postgresql 8.3. This has all the GN configuration and metadata
        in it.
        
        The target computer is running the Debian Squeeze operating
        system with
        Postgresql 8.4. This is where I would like to copy the source
        GN onto.
        
        I have another development computer on which I loaded Debian
        Lenny and
        Postgresql 8.3. I thought that I successfully loaded a
        pg_dumpall of the
        source machine into this database. I then did an upgrade of
        the
        operating system to Debian Squeeze which also loaded
        Postgresql 8.4.
        
        I then did:
        
         pg_dropcluster 8.4 main; # to get rid of the new database
         pg_upgradecluster 8.3 main; #to upgrade the database to 8.4
        
        This should have worked but I kept on getting errors about
        hundreds of
        different tables not existing.
        
        I then tried:
        
         pg_dropcluster 8.4 main; # to get rid of the new database
         pg_dropcluster 8.3 main; # to get rid of the old database
         pg_createcluster 8.3 main; # to create a clean 8.3 cluster
         pg_ctlcluster 8.3 main start; # to start the clean 8.3
        database
         psql -f dumpfile - postgres; # to load the dumpfile from the
        source
        
        When I tried to do another pg_upgradecluster 8.3 main I kept
        on getting
        errors "Server is 8.4 library is 8.3" and then hundreds of
        errors like:
        psql:<stdin>:320652: ERROR: type "geometry" is only a shell
        psql:<stdin>:320664: ERROR: aggregate public.accum(geometry)
        does not
        exist
        
        What I would like is a dump of some ones Postgresql 8.4
        database created
        on a Debian Squeeze operating system for GN 2.7 snapshot. Can
        anyone
        provide this or suggest something else that could help?
        
        Thanks.
        
        John Hockaday
        
        ------------------------------------------------------------------------------
        Live Security Virtual Conference
        Exclusive live event will cover all the ways today's security
        and
        threat landscape has changed and how IT managers can respond.
        Discussions
        will include endpoint security, mobile security and the latest
        in malware
        threats.
        http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
        _______________________________________________
        GeoNetwork-devel mailing list
        GeoNetwork-devel@lists.sourceforge.net
        https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
        GeoNetwork OpenSource is maintained at
        http://sourceforge.net/projects/geonetwork

3

I had a similar problem caused by the fact that I was using postgis and when I upgraded I needed to install the new version of postgis. The way I did it was terrible but it worked :).

One note, GeoNetwork pollutes the main schema with its tables which is a bit unfortunate when upgrading postgres versions and it would be nice to one day make this configurable. But that is another story.

So my upgrade workflow when I had this problem:

  1. pg_dump …
  2. create database in new postgres version
  3. manually install postgis into the new database
  4. add dump back

Step for resulting in lots of errors but no rollbacks. and in the end it worked. The errors were all of the sort Geometry already exists.

These errors were caused by the fact that I had already installed postgis functions, objects, etc… But this was required because of the differing versions. In the end this worked.

Maybe this will help you, maybe not but it is worth a try.

Jesse

On Wed, May 23, 2012 at 1:18 AM, john.hockaday <john.hockaday@anonymised.com…> wrote:

Hi all,

Sorry for cross posting.

I have been trying to migrate an instance of GeoNetwork from one
computer to another. I keep having troubles with the Postgresql
database.

The source computer is running the Debian Lenny operating system with
Postgresql 8.3. This has all the GN configuration and metadata in it.

The target computer is running the Debian Squeeze operating system with
Postgresql 8.4. This is where I would like to copy the source GN onto.

I have another development computer on which I loaded Debian Lenny and
Postgresql 8.3. I thought that I successfully loaded a pg_dumpall of the
source machine into this database. I then did an upgrade of the
operating system to Debian Squeeze which also loaded Postgresql 8.4.

I then did:

pg_dropcluster 8.4 main; # to get rid of the new database
pg_upgradecluster 8.3 main; #to upgrade the database to 8.4

This should have worked but I kept on getting errors about hundreds of
different tables not existing.

I then tried:

pg_dropcluster 8.4 main; # to get rid of the new database
pg_dropcluster 8.3 main; # to get rid of the old database
pg_createcluster 8.3 main; # to create a clean 8.3 cluster
pg_ctlcluster 8.3 main start; # to start the clean 8.3 database
psql -f dumpfile - postgres; # to load the dumpfile from the source

When I tried to do another pg_upgradecluster 8.3 main I kept on getting
errors “Server is 8.4 library is 8.3” and then hundreds of errors like:
psql::320652: ERROR: type “geometry” is only a shell
psql::320664: ERROR: aggregate public.accum(geometry) does not
exist

What I would like is a dump of some ones Postgresql 8.4 database created
on a Debian Squeeze operating system for GN 2.7 snapshot. Can anyone
provide this or suggest something else that could help?

Thanks.

John Hockaday

Live Security Virtual Conference
Exclusive live event will cover all the ways today’s security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

4

I guess the big difference is that I was dealing with a single database so

pg_dump -U www-data -E UTF-8 -Z 9 geocat2 > $DUMP_FILE

was sufficient for me.

for cluster… sorry to say I am out of ideas. I think you need to go to the postgis users list.

Jesse

On Fri, May 25, 2012 at 1:42 AM, john.hockaday <john.hockaday@anonymised.com> wrote:

Hi Jesse,

Thanks for the prompt reply. Please see comments in line below:

On Wed, 2012-05-23 at 08:20 +0200, Jesse Eichar wrote:

I had a similar problem caused by the fact that I was using postgis
and when I upgraded I needed to install the new version of postgis.
The way I did it was terrible but it worked :).

Any solution that works will be good. ;–)

One note, GeoNetwork pollutes the main schema with its tables which is
a bit unfortunate when upgrading postgres versions and it would be
nice to one day make this configurable. But that is another story.

So my upgrade workflow when I had this problem:

  1. pg_dump …

Done.

  1. create database in new postgres version

Is there another way to do this other than pg_createcluster 8.4 main?

Is it a GN database or just the cluster? If the former where can I get
the SQL commands to make the tables and functions?

  1. manually install postgis into the new database

I tried ‘sudo apt-get update postgis’ and I got postgis is already the
latest version.

  1. add dump back

Step for resulting in lots of errors but no rollbacks. and in the end
it worked. The errors were all of the sort Geometry already exists.

I too get a lot of errors. Can I ignore these and hope that GN will
still work?

I seem to have lost the metadata but I hope I can harvest it from the
original GN server.

Thanks in advance for any help you can provide.

JohnH

These errors were caused by the fact that I had already installed
postgis functions, objects, etc… But this was required because of
the differing versions. In the end this worked.

Maybe this will help you, maybe not but it is worth a try.

Jesse

On Wed, May 23, 2012 at 1:18 AM, john.hockaday
<john.hockaday@anonymised.com> wrote:
Hi all,

Sorry for cross posting.

I have been trying to migrate an instance of GeoNetwork from
one
computer to another. I keep having troubles with the
Postgresql
database.

The source computer is running the Debian Lenny operating
system with
Postgresql 8.3. This has all the GN configuration and metadata
in it.

The target computer is running the Debian Squeeze operating
system with
Postgresql 8.4. This is where I would like to copy the source
GN onto.

I have another development computer on which I loaded Debian
Lenny and
Postgresql 8.3. I thought that I successfully loaded a
pg_dumpall of the
source machine into this database. I then did an upgrade of
the
operating system to Debian Squeeze which also loaded
Postgresql 8.4.

I then did:

pg_dropcluster 8.4 main; # to get rid of the new database
pg_upgradecluster 8.3 main; #to upgrade the database to 8.4

This should have worked but I kept on getting errors about
hundreds of
different tables not existing.

I then tried:

pg_dropcluster 8.4 main; # to get rid of the new database
pg_dropcluster 8.3 main; # to get rid of the old database
pg_createcluster 8.3 main; # to create a clean 8.3 cluster
pg_ctlcluster 8.3 main start; # to start the clean 8.3
database
psql -f dumpfile - postgres; # to load the dumpfile from the
source

When I tried to do another pg_upgradecluster 8.3 main I kept
on getting
errors “Server is 8.4 library is 8.3” and then hundreds of
errors like:
psql::320652: ERROR: type “geometry” is only a shell
psql::320664: ERROR: aggregate public.accum(geometry)
does not
exist

What I would like is a dump of some ones Postgresql 8.4
database created
on a Debian Squeeze operating system for GN 2.7 snapshot. Can
anyone
provide this or suggest something else that could help?

Thanks.

John Hockaday

Live Security Virtual Conference
Exclusive live event will cover all the ways today’s security
and
threat landscape has changed and how IT managers can respond.
Discussions
will include endpoint security, mobile security and the latest
in malware
threats.
http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/

GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at
http://sourceforge.net/projects/geonetwork