Thread: Restore Crashes Postgres

Restore Crashes Postgres

From
Christine Penner
Date:
We are in the process of converting our application to use Postgres.
We have about 5 computers in the office. We frequently create a
database on one computer and then transfer it to the other computers
as needed. We are currently doing this all through pgAdmin.

We backup a data base using the default (Compress) format. We select
the Insert Commands option.
We use restore on the other computers to load the database. We select
the no owner option.

Quite often this works fine but we have had a lot of crashes.
Sometimes we are unable to load a database. Sometimes we load over an
existing database but not always. Doesn't seem to make a difference.

The big problem with the crashes is the only way to be able to use
Postgres at that point is to restart the whole computer.

We are all using 8.3.4 on Windows XP

Christine Penner
Ingenious Software
250-352-9495
christine@ingenioussoftware.com


Re: Restore Crashes Postgres

From
Allan Kamau
Date:
You may want to zero in on the problem by performing database dumps
(using pg_dump and the various options accordingly) then restore on the
failing installation using the psql command.

Maybe (I am guessing here, you haven't elaborated on "crashes") you may
be issuing insert statements (in the pgAdmin generated file) on
previously populated tables. Or perhaps you are inserting (or updating)
dependent (child) tables without populating the parent table while
having the referential integrity constraints present. pg_dump has an
elegant way of avoiding such pitfalls.


Allan.

On Tue, Apr 28, 2009 at 5:43 PM, Christine Penner
<christine@ingenioussoftware.com> wrote:
 > We are in the process of converting our application to use Postgres.
We have
 > about 5 computers in the office. We frequently create a database on one
 > computer and then transfer it to the other computers as needed. We are
 > currently doing this all through pgAdmin.
 >
 > We backup a data base using the default (Compress) format. We select the
 > Insert Commands option.
 > We use restore on the other computers to load the database. We select
the no
 > owner option.
 >
 > Quite often this works fine but we have had a lot of crashes.
Sometimes we
 > are unable to load a database. Sometimes we load over an existing
database
 > but not always. Doesn't seem to make a difference.
 >
 > The big problem with the crashes is the only way to be able to use
Postgres
 > at that point is to restart the whole computer.
 >
 > We are all using 8.3.4 on Windows XP
 >
 > Christine Penner
 > Ingenious Software
 > 250-352-9495
 > christine@ingenioussoftware.com
 >
 > --
 > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 > To make changes to your subscription:
 > http://www.postgresql.org/mailpref/pgsql-general
 >


Re: Restore Crashes Postgres

From
Christine Penner
Date:
The crashes I get are the windows, this program has encountered a
problem and must close, error. At that point I can't do anything with
Postgres. I can't restart the service. I have to restart windows.

Usually we are restoring into an already loaded database but we have
gotten the crashes loading into a new (empty) database. I don't think
its parent child issues. The only constraints we have are for the
primary key's. Also, I'm told it doesn't matter but I always make
sure I don't have the database open when I create a backup.

I'm wondering if there is a better way to do a backup and restore.
I'm really worried that we are going to have a client need a backup
and not be able to load it. Before we release it to clients we will
be using a command line to do the backup and restore but I'm pretty
sure it's the same thing as running it through pgAdmin.

Christine

At 09:47 AM 28/04/2009, you wrote:
>You may want to zero in on the problem by performing database dumps
>(using pg_dump and the various options accordingly) then restore on
>the failing installation using the psql command.
>
>Maybe (I am guessing here, you haven't elaborated on "crashes") you
>may be issuing insert statements (in the pgAdmin generated file) on
>previously populated tables. Or perhaps you are inserting (or
>updating) dependent (child) tables without populating the parent
>table while having the referential integrity constraints present.
>pg_dump has an elegant way of avoiding such pitfalls.
>
>
>Allan.
>
>On Tue, Apr 28, 2009 at 5:43 PM, Christine Penner
><christine@ingenioussoftware.com> wrote:
> > We are in the process of converting our application to use
> Postgres. We have
> > about 5 computers in the office. We frequently create a database on one
> > computer and then transfer it to the other computers as needed. We are
> > currently doing this all through pgAdmin.
> >
> > We backup a data base using the default (Compress) format. We select the
> > Insert Commands option.
> > We use restore on the other computers to load the database. We
> select the no
> > owner option.
> >
> > Quite often this works fine but we have had a lot of crashes. Sometimes we
> > are unable to load a database. Sometimes we load over an existing database
> > but not always. Doesn't seem to make a difference.
> >
> > The big problem with the crashes is the only way to be able to use Postgres
> > at that point is to restart the whole computer.
> >
> > We are all using 8.3.4 on Windows XP
> >
> > Christine Penner
> > Ingenious Software
> > 250-352-9495
> > christine@ingenioussoftware.com
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>


Re: Restore Crashes Postgres

From
Alvaro Herrera
Date:
Christine Penner wrote:
> The crashes I get are the windows, this program has encountered a
> problem and must close, error. At that point I can't do anything with
> Postgres. I can't restart the service. I have to restart windows.

The PostgreSQL logs should have more info regarding the crash.  I think
you really need to solve this crash; it should normally never happen.
(Postgres can be configured so that the log ends up in the Windows event
log, by setting log_destination=eventlog in postgresql.conf.  If you
haven't set that up, it's probably ending up in a file somewhere).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Restore Crashes Postgres

From
Scott Marlowe
Date:
On Tue, Apr 28, 2009 at 11:56 AM, Christine Penner
<christine@ingenioussoftware.com> wrote:
> The crashes I get are the windows, this program has encountered a problem
> and must close, error. At that point I can't do anything with Postgres. I
> can't restart the service. I have to restart windows.
>
> Usually we are restoring into an already loaded database but we have gotten
> the crashes loading into a new (empty) database. I don't think its parent
> child issues. The only constraints we have are for the primary key's. Also,
> I'm told it doesn't matter but I always make sure I don't have the database
> open when I create a backup.
>
> I'm wondering if there is a better way to do a backup and restore. I'm
> really worried that we are going to have a client need a backup and not be
> able to load it. Before we release it to clients we will be using a command
> line to do the backup and restore but I'm pretty sure it's the same thing as
> running it through pgAdmin.

I'm really starting to wonder about your hardware and / or OS.  Are
you running any anti-virus software (if so turn it off and / or
uninstall it)?  Have you tested your machine with something like
memtest86+?

Re: Restore Crashes Postgres

From
Christine Penner
Date:
I looked at the log files. The log file for one crash I had looks
like it got to loading data in one of the larger tables. Finished
inserting a record and stopped. It did complain about the fact that
the tables already existed but seemed to continue and loaded about 49
tables before it quit. It didn't give any messages at that point.

This is the start of the log file after I restarted the computer
after a postgres crash:
2009-04-27 18:00:31 PDT LOG:  database system was interrupted; last
known up at 2009-04-27 17:45:18 PDT
2009-04-27 18:00:31 PDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2009-04-27 18:00:31 PDT FATAL:  the database system is starting up
2009-04-27 18:00:31 PDT LOG:  database system was not properly shut
down; automatic recovery in progress
2009-04-27 18:00:31 PDT LOG:  record with zero length at 0/268F86E8
2009-04-27 18:00:31 PDT LOG:  redo is not required
2009-04-27 18:00:31 PDT LOG:  database system is ready to accept connections
2009-04-27 18:00:32 PDT LOG:  autovacuum launcher started

This is the start of the log file after I tried to restore the database again:
2009-04-27 18:19:12 PDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2009-04-27 18:19:20 PDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2009-04-27 18:19:44 PDT LOG:  loaded library
"$libdir/plugins/plugin_debugger.dll"
2009-04-27 18:19:44 PDT ERROR:  language "plpgsql" already exists
2009-04-27 18:19:44 PDT STATEMENT:
       CREATE PROCEDURAL LANGUAGE plpgsql;

Scott:
>I'm really starting to wonder about your hardware and / or OS.  Are
>you running any anti-virus software (if so turn it off and / or
>uninstall it)?  Have you tested your machine with something like
>memtest86+?
All computers are running NOD32 for anti-virus. All are updated and
running. I haven't run Memtest86 on any of them. This has happened on
5 different computers including my personal laptop which is never
connected to the network at the office.

Christine

At 11:35 AM 28/04/2009, you wrote:
>Christine Penner wrote:
> > The crashes I get are the windows, this program has encountered a
> > problem and must close, error. At that point I can't do anything with
> > Postgres. I can't restart the service. I have to restart windows.
>
>The PostgreSQL logs should have more info regarding the crash.  I think
>you really need to solve this crash; it should normally never happen.
>(Postgres can be configured so that the log ends up in the Windows event
>log, by setting log_destination=eventlog in postgresql.conf.  If you
>haven't set that up, it's probably ending up in a file somewhere).
>
>--
>Alvaro Herrera                                http://www.CommandPrompt.com/
>The PostgreSQL Company - Command Prompt, Inc.


Re: Restore Crashes Postgres

From
Scott Marlowe
Date:
On Tue, Apr 28, 2009 at 1:44 PM, Christine Penner
<christine@ingenioussoftware.com> wrote:
> Scott:
>>
>> I'm really starting to wonder about your hardware and / or OS.  Are
>> you running any anti-virus software (if so turn it off and / or
>> uninstall it)?  Have you tested your machine with something like
>> memtest86+?
>
> All computers are running NOD32 for anti-virus. All are updated and running.
> I haven't run Memtest86 on any of them. This has happened on 5 different
> computers including my personal laptop which is never connected to the
> network at the office.

Can you uninstall NOD32 and test again?  Antivirus software is
notoriously intrusive and can have bad interactions with pgsql.  If
things run properly with it uninstalled, then it's time to investigate
how to either get NOD32 to get along with pgsql or find a replacement.

Re: Restore Crashes Postgres

From
Tom Lane
Date:
Christine Penner <christine@ingenioussoftware.com> writes:
> This is the start of the log file after I tried to restore the database again:
> 2009-04-27 18:19:12 PDT LOG:  loaded library
> "$libdir/plugins/plugin_debugger.dll"
> 2009-04-27 18:19:20 PDT LOG:  loaded library
> "$libdir/plugins/plugin_debugger.dll"
> 2009-04-27 18:19:44 PDT LOG:  loaded library
> "$libdir/plugins/plugin_debugger.dll"

Hmm ... try removing the plugin_debugger and see if things get any more
stable.  (See shared_preload_libraries and/or local_preload_libraries
in postgresql.conf.)

> All computers are running NOD32 for anti-virus.

And turn that off too to see if it makes a difference.

            regards, tom lane