Postgres Pro
Downloads
Home   >   mailing lists

Thread: Yikes

Yikes

From
"Chad Thompson"
Date:
It seems that my postgres server was rebooted in the middle of a vacuum full.
I am able to start and stop the server successfully, but when i connect to it w/ pgadmin, postgres restarts.  Here are the logs
 
--successfully shut down postgres
--started it back up w/ "su postgres -c 'pg_ctl start -o -i -D /var/lib/pgsql/data'"
postmaster successfully started
[root@sqlsvr root]# DEBUG:  database system was shut down at 2002-12-10 09:25:32 MST
DEBUG:  checkpoint record is at 20/2ABC0884
DEBUG:  redo record is at 20/2ABC0884; undo record is at 0/0; shutdown TRUE
DEBUG:  next transaction id: 7457784; next oid: 846358289
DEBUG:  database system is ready
 
--go to pgadmin and connect.  All the databases have red X's through them.  Drilled down the tables and brought up contents of a table .. then
 
[root@sqlsvr root]# DEBUG:  server process (pid 22187) was terminated by signal 11
DEBUG:  terminating any other active server processes
NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend
        died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
DEBUG:  all server processes terminated; reinitializing shared memory and semaphores
DEBUG:  database system was interrupted at 2002-12-10 09:25:47 MST
DEBUG:  checkpoint record is at 20/2ABC0884
DEBUG:  redo record is at 20/2ABC0884; undo record is at 0/0; shutdown TRUE
DEBUG:  next transaction id: 7457784; next oid: 846358289
DEBUG:  database system was not properly shut down; automatic recovery in progress
FATAL 1:  The database system is starting up
DEBUG:  ReadRecord: record with zero length at 20/2ABC08C4
DEBUG:  redo is not required
FATAL 1:  The database system is starting up
FATAL 1:  The database system is starting up
FATAL 1:  The database system is starting up
FATAL 1:  The database system is starting up
DEBUG:  database system is ready
DEBUG:  server process (pid 22196) was terminated by signal 11
DEBUG:  terminating any other active server processes
NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend
        died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
NOTICE:  Message from PostgreSQL backend:
        The Postmaster has informed me that some other backend
        died abnormally and possibly corrupted shared memory.
        I have rolled back the current transaction and am
        going to terminate your database system connection and exit.
        Please reconnect to the database system and repeat your query.
DEBUG:  all server processes terminated; reinitializing shared memory and semaphores
DEBUG:  database system was interrupted at 2002-12-10 09:26:07 MST
DEBUG:  checkpoint record is at 20/2ABC08C4
DEBUG:  redo record is at 20/2ABC08C4; undo record is at 0/0; shutdown TRUE
DEBUG:  next transaction id: 7457784; next oid: 846358289
DEBUG:  database system was not properly shut down; automatic recovery in progress
FATAL 1:  The database system is starting up
DEBUG:  ReadRecord: record with zero length at 20/2ABC0904
DEBUG:  redo is not required
FATAL 1:  The database system is starting up
FATAL 1:  The database system is starting up
DEBUG:  database system is ready
Please help.. dont know where to go from here.
 
Thanks
Chad

Undoing a typo?

From
"Steven Ames"
Date:
This is a truly novice question but here goes:
 
Yesterday I was on the command line and issued an UPDATE command. Right after I hit the ENTER key I realized that I forgot to put the WHERE clause in... so... every entry in the table got the update. Ooops. Since I hadn't updated this particular table in a while I just dropped it and put it back in from a backup.
 
My question is... is there a clever way to undo or rollback a mistake you make at the 'psql' prompt?
 
-Steve
 

Re: Undoing a typo?

From
Garrett Bladow
Date:
The clever way is called a transaction.
Before doing any data manipulation, start a transaction.
Like this:
BEGIN;    http://developer.postgresql.org/docs/postgres/sql-begin.html

Then do your update statements;

Then if there was an error type:
ROLLBACK; http://developer.postgresql.org/docs/postgres/sql-rollback.html
This will revert you data back to the state it was in before the transaction.

If there wasn't an error and the data looks sound type:
COMMIT; http://developer.postgresql.org/docs/postgres/sql-commit.html
and the updates will be saved.

-- Garrett Bladow

---- This is what you wrote me ----

:This is a truly novice question but here goes:
:
:Yesterday I was on the command line and issued an UPDATE command. Right after I hit the ENTER key I realized that I
forgotto put the WHERE clause in... so... every entry in the table got the update. Ooops. Since I hadn't updated this
particulartable in a while I just dropped it and put it back in from a backup. 
:
:My question is... is there a clever way to undo or rollback a mistake you make at the 'psql' prompt?
:
:-Steve
:

Re: Yikes

From
Tom Lane
Date:
"Chad Thompson" <chad@weblinkservices.com> writes:
> It seems that my postgres server was rebooted in the middle of a vacuum full.

> DEBUG:  server process (pid 22196) was terminated by signal 11

You need to isolate what's causing that repeatable backend crash.
(A first guess is that some page of data got corrupted during the system
crash --- was this due to a power failure, or just some cowboy hitting
ctrl-alt-del?  But we shouldn't leap to conclusions.)

The standard full-up procedure for investigating backend crashes of
unknown origin is to get a stack backtrace and post it for inspection on
pgsql-hackers or pgsql-bugs.  In this case, though, since we suspect
corrupt data a reasonable shortcut is to try "vacuum verbose analyze"
and see if it makes it all the way through or not.  If not, the table it
was working on when it died is the (or at least a) locus of corruption.
If you're really lucky, the corruption is in an index or a table you can
rebuild easily.  If not, well, we can probably help you scavenge what's
recoverable ... but it's getting a bit far afield for pgsql-novice.
Please do the preliminary investigation and if you need more help,
report in on pgsql-hackers.

            regards, tom lane