It's Fiday; and certainly a better day than yesterday.
I'm posting this as:
1. A request for tips regarding log management; and
2. A tip to other newbies who are restoring multiple,
large databases.
The tip: Always do 'vacuumdb -a' after restoring a
database.
The question: What are the significant log issues
that dba's should be watching?
Background (long):
I have 3 databases that, in total, are 16 Gigabytes in
size. I had converted them from MySQL; and it was
time to move them to a different subnet. I'm running
PostgreSQL 7.1.2_2 on FreeBSD 4.3 stable (4.4
prerelease). I dumped each database separately,
piping the output through gzip into a *.gz file. I
restored the databases on the new computer by piping
the decompressed data into psql. (I couldn't seem to
get it to work with pg_restore.) The first two
restores went well. The third restore came to an
abrupt end with a message that the file system was
full. Postmaster had died; and I could not restart
the PostgreSQL server. 'du -h' revealed that the
database directories were of the expected sizes; but
also that the pg_xlog direcory was over 10 Gb in size!
Since I could not get the database server to restart
(even after rebooting), I uninstalled Postgres and
dependent applications, deleted the directories, and
started all over. I scripted the restores to run
sequentially overnight; but I put a 'vacuumdb -a'
after each restore.
This morning: The databases are restored. pg_xlog is
16Mb is size. The row counts, via phppgadmin, look
good.
Happy Friday,
Andrew
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/