Thread: Troubles with WAL and pg_control

Troubles with WAL and pg_control

From
Rasmus Mohr
Date:
Hi, we have serious problem with our postgresql db.

A staling query were killed and all the WAL files were deleted. Afterwards
any attempts to restart postmaster fails, probably due to missing links from
pg_control to the WAL files.

How do we solve this problem? Erasing all lines in pg_control did not
help...

--------------------------------------------------------------
Rasmus T. Mohr            Direct  :             +45 36 910 122
Application Developer     Mobile  :             +45 28 731 827
Netpointers Intl. ApS     Phone   :             +45 70 117 117
Vestergade 18 B           Fax     :             +45 70 115 115
1456 Copenhagen K         Email   : mailto:rmo@netpointers.com
Denmark                   Website : http://www.netpointers.com

"Remember that there are no bugs, only undocumented features."
--------------------------------------------------------------

Re: Troubles with WAL and pg_control

From
Rasmus Mohr
Date:
Ignore my previous desperate cry for help with a pg_control problem. It was
solved using:
    /usr/lib/pgsql/contrib/pg_resetxlog/pg_resetxlog $PGDATA

...thx to Google ;-)

--------------------------------------------------------------
Rasmus T. Mohr            Direct  :             +45 36 910 122
Application Developer     Mobile  :             +45 28 731 827
Netpointers Intl. ApS     Phone   :             +45 70 117 117
Vestergade 18 B           Fax     :             +45 70 115 115
1456 Copenhagen K         Email   : mailto:rmo@netpointers.com
Denmark                   Website : http://www.netpointers.com

"Remember that there are no bugs, only undocumented features."
--------------------------------------------------------------

Re: Troubles with WAL and pg_control

From
Tom Lane
Date:
Rasmus Mohr <rmo@Netpointers.com> writes:
> Hi, we have serious problem with our postgresql db.
> A staling query were killed and all the WAL files were deleted.

Just for the benefit of onlookers: that was a really BAD idea.
The pg_xlog and pg_clog files are not optional; you *will* regret it
if you decide to delete 'em.

BTW, if you want to kill a runaway query, it's usually sufficient
to do "kill -INT" on the specific backend doing the query.  This
executes a query-cancel, which should stop the query fairly quickly
without any damage to the rest of the system.

            regards, tom lane