Sistematika’s Journal

Diberkati untuk memberkati

Moving PostgreSQL data directory (Windows)

Posted by: Bayu Cahya P on: September 11, 2008

While we installed database PostgreSQL for the first time within Windows XP Home machine, default data directory will be C:\Program Files\PostgreSQL\8.2\data (we are using PostgreSQL 8.2.9 for Windows). For administrative purposes, we just want to move this default directory to another drive.

I have no experience with such task, and starting to find it from internet. From some articles, we found that, we just have to change directory while starting our PostgreSQL services.

Browse your registry editor (regedit), and find key HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\pgsql-8.2. With string value ImagePath, it contains

"C:\Program Files\PostgreSQL\8.2\bin\pg_ctl.exe" runservice -w -N "pgsql-8.2" -D "C:\Program Files\PostgreSQL\8.2\data".

Ok. We will change this value with

"C:\Program Files\PostgreSQL\8.2\bin\pg_ctl.exe" runservice -w -N "pgsql-8.2" -D "F:\test".

And we do copying our data to new directory. Make sure that you don’t copy postmaster.opts. It is so clear instruction. and tried to start this new configuration from our Services.

As per our expectation, this tasks will go smoothly. But a weird things comes and made an unnecessary headache. Shortly, this services can’t be started with no information about whats wrong within our new configuration.

Well, calls Mr Google and there are no clues with our problems. We just tried to call this manually with type

pc_ctl start -w -D "F:\test\"

Fortunately, they gave us some warnings within pg_log directory

2008-09-12 11:50:03 FATAL: role "Bayu" does not exist
2008-09-12 11:50:04 FATAL: role "Bayu" does not exist
2008-09-12 11:50:05 FATAL: role "Bayu" does not exist
2008-09-12 11:50:06 FATAL: role "Bayu" does not exist
2008-09-12 11:50:07 FATAL: role "Bayu" does not exist

I still didn’t understand about why this tasks requires role Bayu. From our logic, it means that PostgreSQL read our credential while loging in to Windows. Since target computer will be used for testing only, recently we add special role “Bayu” to our database with full administrative rights.

Okay, trying to start from this our new directory, PostgreSQL seems running now

F:\>pg_ctl start -w -D "f:\test\"
waiting for server to start...
done server started

I wish, it had been start successfully while we tried to start default PostgreSQL service. But, they still do not working properly. Dooh…. Within our trial and error mode, finally this service run smoothly with registering new service. Here is our task while registering new PostgreSQL service

pg_ctl register -N "PostgreSQL ERP" -w -D "F:\Test\"

Good. Now, we can continue our test case with our ERP :)

1 Response to "Moving PostgreSQL data directory (Windows)"

1 | Primawan

February 5th, 2009 at 1:27 am

makasih banyak ni..
akhirnya bisa jalan lagi servernya

Comment Form

SEO Powered by Platinum SEO from Techblissonline