Thread: Restore Crashes Postgres
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
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 >
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 > > >
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.
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+?
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.
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.
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