Thread: incorrect restore from pg_dumpall
Hello, I am trying to restore my databases stored by a pg_dumpall command in the Karmic Koala box. The restore command is the following: psql -f /media/disk/.../backup -U postgres I have a PostsgreSQL 8.4 installed from repositories with postgis1.4.1. I recognized that the tables with spatial geometries were not restored but at the moment I am almost sure that it's caused by that the older version of postgis in the stored database. What is a kind of mystery for me that data in several tables were not restored, although the table definitions did (it means that I got several tables without data) althoug the data are there in the backup file (I checked physically). I really don't understand what's going on there, I did the same many times before, without any problem. Tomas
In order to restore a backup taken with pg_dumpall you'll want to ensure that the postgis installed in your new system is identical to the postgis in your old one. This is because the postgis function definitions will be looking for a particular postgis library name... the name of the library from your old database. You can hack around this, and have your cake and eat it too, to an extent, by symlinking the name of your old postgis to your new postgis library. P On Fri, Nov 27, 2009 at 4:11 PM, Tomas Lanczos <lanczos@t-zones.sk> wrote: > Hello, > > I am trying to restore my databases stored by a pg_dumpall command in > the Karmic Koala box. The restore command is the following: > > psql -f /media/disk/.../backup -U postgres > > I have a PostsgreSQL 8.4 installed from repositories with postgis1.4.1. > I recognized that the tables with spatial geometries were not restored > but at the moment I am almost sure that it's caused by that the older > version of postgis in the stored database. What is a kind of mystery for > me that data in several tables were not restored, although the table > definitions did (it means that I got several tables without data) > althoug the data are there in the backup file (I checked physically). I > really don't understand what's going on there, I did the same many times > before, without any problem. > > Tomas > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Yes, You were right, the problem was in the different libraries. I worked around by installing the postgis version 1.3.6, reloading the databases and subsequently uprading the postgis to 1.4.1. Thank You for Your help Tomas p.s.: what is really a mystery that also the mentioned table with no data were successfully reloaded, those tables did not contain spatial data ... :) On Fri, 2009-11-27 at 16:28 -0800, Paul Ramsey wrote: > In order to restore a backup taken with pg_dumpall you'll want to > ensure that the postgis installed in your new system is identical to > the postgis in your old one. This is because the postgis function > definitions will be looking for a particular postgis library name... > the name of the library from your old database. > > You can hack around this, and have your cake and eat it too, to an > extent, by symlinking the name of your old postgis to your new postgis > library. > > P > > On Fri, Nov 27, 2009 at 4:11 PM, Tomas Lanczos <lanczos@t-zones.sk> wrote: > > Hello, > > > > I am trying to restore my databases stored by a pg_dumpall command in > > the Karmic Koala box. The restore command is the following: > > > > psql -f /media/disk/.../backup -U postgres > > > > I have a PostsgreSQL 8.4 installed from repositories with postgis1.4.1. > > I recognized that the tables with spatial geometries were not restored > > but at the moment I am almost sure that it's caused by that the older > > version of postgis in the stored database. What is a kind of mystery for > > me that data in several tables were not restored, although the table > > definitions did (it means that I got several tables without data) > > althoug the data are there in the backup file (I checked physically). I > > really don't understand what's going on there, I did the same many times > > before, without any problem. > > > > Tomas > > > > > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > >