Keith Worthington wrote:
On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote
"Keith Worthington" <[EMAIL PROTECTED]> writes:
I have been reading about increasing PostgreSQL performance
by relocating the pg_xlog to a disk other than the one
where the database resides. I have the following pg_xlogs
on my system.
/raid02/databases/pg_xlog
/raid02/rhdb_databases/pg_xlog
/raid02/databases-8.0.0/pg_xlog
/var/lib/pgsql/data/pg_xlog
I have no idea why the forth entry is there. It is in the PostgreSQL
installation directory.
It's there because the RPM sets up a database under /var/lib/pgsql/data.
1) stop the postmaster
2) rm -rf /var/lib/pgsql/data/pg_xlog
3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog
4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog
5) start postmaster
Put the xlog anywhere BUT there!!!!!!!!!
If I can do that and place the pg_xlog in the installation
directory will I create any installation issues the next
time I upgrade PostgreSQL?
Oh, the installation will be just fine ... but your database will not
be after the upgrade wipes out your WAL. Put the xlog under some
non-system-defined directory.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Thanks Tom. I am glad I asked before I leaped. 8-0
Is there a convention that most people follow. It would seem that anywhere in
the installation directory is a bad idea. From what I have read on other
threads it does not want to be in the database directory since in most cases
that would put it on the same disk as the database.
We tend to use somthing that associates the WAL with the appropriate
cluster, like
/var/lib/CLUSTER for the data
/var/lib/CLUSTER_WAL for WAL files.
I am assuming due to lack of reaction that the symbolic link is not an issue.
Is there a cleaner or more appropriate way of moving the pg_xlog.
A symbolic link is the standard way to do it.
Finally, am I correct in assuming that as long as the postmaster is shut down
moving the log is safe?
You are correct. Moving the WAL files with the postmaster running would
be a very bad thing.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
