Thread: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

Hi,

I have a huge Postgres database on my C drive, I am continuously collecting
the data, so unfortunately, the drive is getting full and I wanted to move
it to a map network drive I got. I followed the instruction in the link
below:

http://kb.vircom.com/kbase/default.asp?id=1512&Lang=1&SID=

1 - Stop the Postgres and Modus Monitor services
TIP: save a step & stop both services directly from Administrative Tools >
Services panel on your Windows server

2 - Copy C:\Program Files\PostgreSQL\data\*.* to the destination directory
(ex. D:\pgdata\)

3 - Open the Registry Editor

4 - Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\pgsql-8.0

- Modify ImagePath:

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

To: C:\Program Files\PostgreSQL\bin\pg_ctl.exe" runservice -N "pgsql-8.0" -D
"d:\pgdata\" (where d:\pgdata = your new PostgreSQL directory)


5 - Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installatio ns

- Look for and open the alpha-numeric subfolder

- Modify Data Directory:

From: C:\Program Files\PostgreSQL\data\

To: D:\pgdata\ (where d:\pgdata = your new PostgreSQL directory)


6 - Go to HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Services\pg sql-8.0

- Modify Data Directory:

From: C:\Program Files\PostgreSQL\data\

To: D:\pgdata\ (where d:\pgdata = your new PostgreSQL directory)


IMPORTANT: By default, Modus installs PostgreSQL to run under a user called
Postgres. The data directory requires proper permissions in order to allow
the service to have access.

7 - Open Windows Explorer

- Go to the new PostgreSQL data directory in Windows Explorer
- Right-click and select Properties
- Click on the Security
- Click on Add to add the Postgres user
- Give the Postgres user the permission to Modify the directory


8 - Start the PostgreSQL and Modus Monitor services from the Administrative
Tools > Services panel on your server


But after doing all the things mentioned in the link, when I want to start
the postgres again as a service, it can not be started, it seems it does not
recognize the map network drive , would you please let me know if I am in
the right track or I need to do something else? any help is appreciated.
Thanks.

Elly.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 05/03/12 11:55 AM, EllyR wrote:
> But after doing all the things mentioned in the link, when I want to start
> the postgres again as a service, it can not be started, it seems it does not
> recognize the map network drive , would you please let me know if I am in
> the right track or I need to do something else? any help is appreciated.
> Thanks.

services do not run on your desktop session, they run in their own
service user context, and can't see mapped drives created by your
desktop session.

furthermore, a database server really shouldn't be running on a network
shared drive in the first place, they aren't reliable for random block
write oriented IO, which databases do a LOT of.

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


Re: Move the postgreSQL database from Drive C to Map Network Drive (Called Z)

From
Magnus Hagander
Date:
On Thu, May 3, 2012 at 8:55 PM, EllyR <ela_macky@yahoo.com> wrote:
> Hi,
>
> I have a huge Postgres database on my C drive, I am continuously collecting
> the data, so unfortunately, the drive is getting full and I wanted to move
> it to a map network drive I got. I followed the instruction in the link

It's not supported to run PostgreSQL off a windows network drive. It
might work, but it might also not - most likely it won't. There are
many different scenarios where this can cause corruption, and that's
why it's not supported.

<snip>

> 4 - Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic es\pgsql-8.0

Note that PostgreSQL 8.0 is not supported, and hasn't been for years.
You need to upgrade to at least 8.3 to get a supported version.


> But after doing all the things mentioned in the link, when I want to start
> the postgres again as a service, it can not be started, it seems it does not
> recognize the map network drive , would you please let me know if I am in
> the right track or I need to do something else? any help is appreciated.
> Thanks.

As PostgreSQL runs as it's own service, it has it's own set of network
drives. You need to make that map in the profile of the postgres user
for it to work. I'm pretty sure the instructions you've been following
are designed for moving the installation to a different *local* drive,
not to a network drive, which is why this is not mentioned there.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Fri, May 4, 2012 at 4:55 AM, EllyR <ela_macky@yahoo.com> wrote:
> I have a huge Postgres database on my C drive, I am continuously collecting
> the data, so unfortunately, the drive is getting full and I wanted to move
> it to a map network drive I got.

Are you able to install Postgres on a remote system and then use that
instead of running it locally? That would be far easier.

ChrisA

Yes you are right, I have read that it is not that reliable, but I have to do
something for that, what would be your suggestion for this case? Thanks.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058p5684620.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

One I tried to install it on map network drive, it failed to work, so I had
to install it back on the C drive.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058p5684612.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Thanks for answering,

I have the latest Postgres on my machine, do you know where can I find the
instruction for map network drive? or do you think it won't work at all on
network drive?

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058p5684608.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 05/03/12 5:15 PM, EllyR wrote:
> I have the latest Postgres on my machine, do you know where can I find the
> instruction for map network drive? or do you think it won't work at all on
> network drive?

database servers should not be run in SMB/CIFS network shares.   The
shares just aren't reliable at things like synchronous commit, and
random small block writes, that database servers do a LOT of.



--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


On May 3, 2012, at 20:20, EllyR <ela_macky@yahoo.com> wrote:

> Yes you are right, I have read that it is not that reliable, but I have to do
> something for that, what would be your suggestion for this case? Thanks.

Simplest option: install a new local hard drive and configure a tablespace to use it.

Alternative: Buy and configure a new machine then dump/restore your database to it.

Not enough info on resources and requirements to recommend one over the other or to devise additional alternatives.

David J.


On May 3, 2012, at 20:20, EllyR <ela_macky@yahoo.com> wrote:

> Yes you are right, I have read that it is not that reliable, but I have to do
> something for that, what would be your suggestion for this case? Thanks.

Simplest option: install a new local hard drive and configure a tablespace to use it.

Alternative: Buy and configure a new machine then dump/restore your database to it.

Not enough info on resources and requirements to recommend one over the other or to devise additional alternatives.

David J.


On 05/03/12 10:18 PM, David Johnston wrote:
> On May 3, 2012, at 20:20, EllyR<ela_macky@yahoo.com>  wrote:
>
>> >  Yes you are right, I have read that it is not that reliable, but I have to do
>> >  something for that, what would be your suggestion for this case? Thanks.
> Simplest option: install a new local hard drive and configure a tablespace to use it.
>
> Alternative: Buy and configure a new machine then dump/restore your database to it.

or, if your NAS/SAN supports it, configure an ISCSI device.   except, I
find ad-hoc ISCSI is not very reliable.

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


On 2012-05-04, John R Pierce <pierce@hogranch.com> wrote:
> On 05/03/12 10:18 PM, David Johnston wrote:
>> On May 3, 2012, at 20:20, EllyR<ela_macky@yahoo.com>  wrote:
>>
>>> >  Yes you are right, I have read that it is not that reliable, but I have to do
>>> >  something for that, what would be your suggestion for this case? Thanks.
>> Simplest option: install a new local hard drive and configure a tablespace to use it.
>>
>> Alternative: Buy and configure a new machine then dump/restore your database to it.
>
> or, if your NAS/SAN supports it, configure an ISCSI device.   except, I
> find ad-hoc ISCSI is not very reliable.

Some NASs even run postgresql internally, they may not have enough ram
for extreme performance, but if data volumne is the only issue that
could be a win.

--
⚂⚃ 100% natural

Thanks.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Move-the-postgreSQL-database-from-Drive-C-to-Map-Network-Drive-Called-Z-tp5684058p5708675.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.