performance tuning - splitting data and logs

[WhiteRider]

You should not touch the pg_xlog folder - the only files you can remove are in the pg_log folder.
I can't remember exactly what the pg_xlog folder files are for, but I'm pretty sure they're important to the running of postgres - kraada will probably be able to tell you why when he comes on later.

[kraada]

You can find the real nitty gritty about the WAL on this page (you might want to read 28.1 - 28.3 also, though, for background).

The short answer is yes the WAL directory is necessary. The point of the WAL is to ensure database integrity. When PostgreSQL is told to change a database value, first the change is written to the WAL, then the change is committed to the disk. In this way, if a particularly long and complex update is set in motion and then you lose power, the database can be rolled back (or forward) to a usable state. Without the WAL you would risk database corruption if there were ever an interruption in power (for example). I hope that helps somewhat

[nelliesman93]

I followed the quoted instructions and the links were created but I could never get postgreSQL to restart. The pg_ctl.exe starts up, 3 instances of postgres.exe open and then immediately close. I also tried just opening PT3 to reestablish the postgreSQL but it comes up with not connected DB. I removed the link and am back to normal now but what am I missing? Thanks.

- found a simple tool to create the symbolic link (called a junction). Here's the URL to download if from Msoft.
http://technet.microsoft.com/en-us/sysi ... 96768.aspx
- downloaded the junction.exe, put it into the directory c:\program files\utilities
- shut down postgreSQL
- created a pg_xlog directory on the desired drive, in my case f:\pg_xlog (you can call it anything you want)
- copied the files and sub-directory from c:\program files\postgreSQL\data\pg_xlog to f:\pg_xlog then deleted them from their original location
- opened a command window (Start->Run enter cmd and hit return)
- typed the command "c:\program files\utilities\junction" "c:\program files\postgreSQL\data\pg_xlog" f:\pg_xlog (include the")
- typed exit to close the command window
- restarted postgreSQL

If this doesn't work
- if it is running, shutdown postgreSQL
- start another command window
- type cd "\program files\postgreSQL\data" (include ") and hit enter
- type "c:\program files\utilities\junction" pg_xlog
- if it says "No reparse points found." then for some reason the junction was not created, so recheck your work
- if it says "c:\postgreSQL\pg_xlog: JUNCTION", that means the junction was correctly created but something else is wrong. So now type "c:\program files\utilities\junction" -d pg_xlog. This will destroy the junction. Now, restore the files and sub-directories to the original pg_xlog location. You should be able to restart postgreSQL now.

[kraada]

Was there any error listed in the pg_log file from the attempt?

[nelliesman93]

Was there any error listed in the pg_log file from the attempt?

2010-06-06 00:07:53 CDT LOG: database system was shut down at 2010-06-05 23:55:10 CDT
2010-06-06 00:07:53 CDT PANIC: could not open file "pg_xlog/000000010000000200000054" (log file 2, segment 84): Permission denied


This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

2010-06-06 00:07:53 CDT LOG: startup process (PID 1632) exited with exit code 3
2010-06-06 00:07:53 CDT LOG: aborting startup due to startup process failure
2010-06-06 00:07:53 CDT LOG: loaded library "$libdir/plugins/plugin_debugger.dll"
2010-06-06 00:07:53 CDT FATAL: the database system is starting up

[kraada]

Is that file in the directory you were trying to move? PostgreSQL does need to be able to find the xlog folder.

[nelliesman93]

Is that file in the directory you were trying to move? PostgreSQL does need to be able to find the xlog folder.

No, that file is not listed pg_xlog directory.

I followed the exact instructions in the previous message I posted. All files in the postgreSQL\8.3\data\pg_xlog were copied over to the new drive. I made the link and verified but PT3 does not connect.

[WhiteRider]

If one of the files is missing then it won't work. Kraada isn't working much this weekend and he knows much more about this stuff than me, though, so I'll let him get back to you when he comes on.

[kraada]

Try running pg_resetxlog (in the PostgreSQL/bin directory) and that might fix things for you. If it gives an error when you run it let me know what the error is.

[zubs1aa]

Having your database on the fastest drive will give you the best performance. Writing logs to a separate disk will help because all the disk access on the DB drive will be concentrated on database access.

Can someone summarize this out of the bazillion threads in here?

Process for PT3 on C:, DB's on drive D: and logs on Drive F: ????