Re: Recovering a database in danger of transaction wrap-around

I used plain old VACUUM. Do you think VACUUM FULL might be faster or more
effective?

Here is the Resource Usage section from the config file (sorry for the mild
spam). Everything looks like it is still in a default state. I'm sure
upping the shared_buffers and the max_fsm_pages would make a difference,
but its been a while since I did any real tuning work on postgres db, and
I'm not comfortable in making any changes to a sick database.

One other thing I should mention. We allocate 1gb (out of 8gb) of memory
to shared memory (/proc/sys/kernel/shmmax=1073741824) however when I look
at shared memory (ipcs), the Postgres segments only consume about 12mb.
I'm sure that's a tuning parameter somewhere. Do you think increasing
shared_buffers and max_fsm_pages might help?

#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 1000 # min 16 or max_connections*2, 8KB
each
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # can be 0 or more
# note: increasing max_prepared_transactions costs ~600 bytes of shared
memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1024 # min 64, size in KB
#maintenance_work_mem = 16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB

# - Free Space Map -

max_fsm_pages = 176928 # min max_fsm_relations*16, 6 bytes
each
#max_fsm_relations = 1000 # min 100, ~70 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
#preload_libraries = ''

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits

___________________________________________________________________________________

Steven Rosenstein
IT Architect/Developer | IBM Virtual Server Administration
Voice/FAX: 845-689-2064 | Cell: 646-345-6978 | Tieline: 930-6001
Text Messaging: 6463456978 @ mobile.mycingular.com
Email: srosenst @ us.ibm.com

"Learn from the mistakes of others because you can't live long enough to
make them all yourself." -- Eleanor Roosevelt

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

To: Steven Rosenstein/New York/IBM(at)IBMUS

Cc: pgsql-admin(at)postgresql(dot)org

Date: 01/25/2008 02:11 PM

Subject: Re: [ADMIN] Recovering a database in danger of transaction wrap-around

Steven Rosenstein <srosenst(at)us(dot)ibm(dot)com> writes:
> I did as instructed, and fired up the standalone backend. I then started
> VACUUM. About four days later, the standalone backend terminated with
the
> message:

> WARNING: terminating connection because of crash of another server
process
> DETAIL: The postmaster has commanded this server process to roll back
the
> current transaction and exit, because another server process exited
> abnormally and possibly corrupted shared memory.
> HINT: In a moment you should be able to reconnect to the database and
> repeat your command.
> CONTEXT: writing block 465 of relation 1663/16384/863912

Ugh. Something sent the standalone backend a SIGQUIT signal. You need
to find out what did that.

> I used lsof to monitor which files the backend was actually working on.
It
> took two of the four days for it to vacuum a single table with 43
> one-gigabyte extents. I have one table with over 300 extents. I'm
looking
> at a vacuum process which can ultimately take weeks (if not months) to
> complete.

Yipes. You are just using plain VACUUM, right, not VACUUM FULL?
Have you checked that vacuum_cost_delay isn't enabled?

> Bottom line. Is there *any* way of faking out the 1 million transaction
> limit which prevents the postmaster from running, long enough for me to
use
> pg_dump to rescue the data?

In 8.1 those limits are all hard-wired; you'd need to modify
SetTransactionIdLimit() in src/backend/access/transam/varsup.c
and recompile. Might be worth doing, if you think these tables
have been bloated by a complete lack of vacuuming.

regards, tom lane