[postgis-users] Loading a PostGIS dump

[Lee Hachadoorian]

postgis_restore.pl ran overnight and from the time on the error file
appeared to complete early this morning. Total time ~20 hrs.

Paul, thanks for reassuring me that things were probably chugging
along fine, and suggesting using `top` to check the process.

Brian, I was able to query the database during the load and see that
some tables had already been created and some data had already been
loaded.

Regina (?), your suggestion to use `pg_stat_activity` came after I had
left the office, and the load was completed by the time I came in. But
I will keep this in mind. Frankly, I do use this to check status of
long-running queries, but hadn't thought to use it to monitor a load.

Best,
--Lee

On Wed, Aug 26, 2015 at 5:52 PM, Paragon Corporation <lr at pcorp.us> wrote:
> As I recall, I think  pg_stat_activity shows database restore work.  So I
> would run something like
>
>
>
>
>
> SELECT * FROM pg_stat_activity WHERE datname='mydb';
>
>
>
> Replacing mydb with your db.
>
>
>
>
>
> From: postgis-users-bounces at lists.osgeo.org
> [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Brian Wilson
> Sent: Wednesday, August 26, 2015 4:07 PM
> To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> Cc: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Subject: Re: [postgis-users] Loading a PostGIS dump
>
>
>
> Best advice ever: Listen to PAUL not to BRIAN. :-)
>
> I was also going to say "what makes you think it stopped"? Stop is usually
> noisy and ends with a command prompt.
>
> Does it load the tables then set ownership? That would be easy to fix later.
>
> My own Q -- What would be a reasonable query to run to see if it's loading?
>
>
>
> Brian
>
>
>
>
> On Wed, Aug 26, 2015 at 12:18 PM, Paul Ramsey <pramsey at cleverelephant.ca>
> wrote:
>
> The COPY command used to load tables is basically 100% silent, so it
> just might have been working away. A good thing to check is 'top' to
> see if the postgres process is busy using CPU. If so, it's hard at
> work on your process. If not, then perhaps something is awry.
>
> P
>
> On Wed, Aug 26, 2015 at 12:01 PM, Lee Hachadoorian
> <Lee.Hachadoorian+L at gmail.com> wrote:
>> New Linux machine, new PostGIS install (postgresql-9.3-postgis-2.1). I
>> created DB and added extensions. Now trying to load database using
>> postgis_restore.pl. This is a large database (63GB dump, created from
>> DB that was probably 600GB or more).
>>
>> The script ran for a while. Then it stopped at
>>
>>  setval
>> --------
>>      53
>> (1 row)
>>
>> It's been hanging out there for hours now, so I assume something is
>> hopelessly wrong.
>>
>> The loader created foo.dump.lst at 10:50am and I piped errors to a
>> text file. Errors file shows that I did not create a user who owns one
>> of the schemas. `ERROR:  role "geo_user" does not exist` appears 260
>> times.
>>
>> Can I just kill the loader script with Ctrl+C? Is database
>> recoverable? Should I just drop database and try again from the
>> beginning? Is failure related to missing role, or if not how do I
>> figure out what the error is so that I can get this to run to
>> completion?
>>
>> Best,
>> --Lee
>>
>> --
>> Lee Hachadoorian
>> Asst Professor of Geography, Temple University
>> http://geospatial.commons.gc.cuny.edu/
>> _______________________________________________
>> postgis-users mailing list
>> 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
>
>
>
>
> --
>
> Brian Wilson, GISP
>
> @brian_32768
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



-- 
Lee Hachadoorian
Asst Professor of Geography, Dartmouth College
http://freecity.commons.gc.cuny.edu/