Disk space with Postgres

[Mohamed R. Arafa]

Hello all,

 I'm working on a Windows XP platform with a Postgres version 8.3 installed. The Data folder holding the database is by default kept under the C:\Program files.

 

I'm facing a problem with the size of the db, which has filled all the available disk space on C:\.

 

Is there a way to change the location of the database to a different directory?

 

 

Regards,

Mohamed Arafa

Geomarketing Sr. Specialist

Mobinil

[Bill Thoen]

Mohamed R. Arafa wrote:
> Hello all,
> I'm working on a Windows XP platform with a Postgres version 8.3

> installed. The *Data* folder holding the database is by default kept
> under the _C:\Program files._

>
> I'm facing a problem with the size of the db, which has filled all the
> available disk space on C:\.
>
> Is there a way to change the location of the database to a different
> directory?
>

You'll probably get a better answer if you ask this one on the
PostgreSQL forum at http://www.postgresql.org/community/lists/, but I
remember from the manual that you can extend your logical database disk
space by adding another cluster on a different drive. Also, if I were
you, I'd think about doing a pg_dumpall and moving the whole database to
another drive. It's not a good idea to crowd your C drive.

--
- Bill Thoen
GISnet - www.gisnet.com
303-786-9961

[Mohamed R. Arafa]

Hi Bill,

Thank you for your advice.

I actually tried using your advice by doing a pg_dumpall and moving the entire database to another drive. But, unfortunately I got some errors. I was not able to connect to the server after editing the '-D' path in the Registry editor. Postgres says: 'can not listen'

I'd appreciate any further advice

 

Thanks again
Mohamed Arafa,

Geomarketing Sr. Specialist

Mobinil

www.mobinil.com

[Bill Thoen]

Mohamed R. Arafa wrote:
> Hi Bill,
> Thank you for your advice.

> I actually tried using your advice by doing a *pg_dumpall* and moving

> the entire database to another drive. But, unfortunately I got some
> errors. I was not able to connect to the server after editing the '-D'

> path in the *Registry editor*. Postgres says: 'can not listen'

> I'd appreciate any further advice

That doesn't sound good. Again, I think you'd do better to try the
PostgreSQL forum if you want some expert advice on Postgres problems.
Postgres hasn't been very usefull to the average MapInfo user until just
a short while ago, so there aren't a lot of MapInfo / postgres experts
here yet. (But we're learning!)

At any rate, I'd say your next step would be to do a little light
reading (nothing duller than RTFM, but sometimes necessary). The
PostgreSQL manual for 8.3 describes in Chapter 20.6 (see
http://www.postgresql.org/docs/8.3/interactive/manage-ag-tablespaces.html)
how to extend your tablespace to other disks. It sounds like this is
just what you need to do:

"By using tablespaces, an administrator can control the disk layout of a
PostgreSQL installation. This is useful in at least two ways. First, if
the partition or volume on which the cluster was initialized *runs out
of space* and cannot be extended, a tablespace can be created on a
different partition and used until the system can be reconfigured."

Good luck!

--
- Bill Thoen
GISnet - www.gisnet.com
303-786-9961

[Mohamed R. Arafa]

Hi Bill,

You've been really supportive, thank you.

 

The Tablespace seems like a great idea. But unfortunately, Tablespaces work only with operating systems that support 'symbolic links'. Sybmolic links are are not supported on WinXP, which is my os.

 

Anyway, I'm sure I'll find something.

 

Again thank you.

 

Best,

Mohamed Arafa

Geomarketing Senior Specialist

Mobinil

www.mobinil.com

[Bill Thoen]

Mohamed R. Arafa wrote:
> Hi Bill,

> You've been really supportive, thank you.
>

> The *Tablespace* seems like a great idea. But
> unfortunately, *Tablespaces* work only with operating systems that
> support _'symbolic links'_. Sybmolic links are are not supported on

> WinXP, which is my os.
>
> Anyway, I'm sure I'll find something.

Well, thanks for letting me know that. I need to set up a copy of
Postgres on a Windows machine soon, so anything you learn about what
doesn't work in that combination would be good to know. But I shouldn't
be surprised. Open Source stuff is primarily designed for Linux and
support for Windows comes only as a second thought.

[Bo Victor Thomsen]

To Mohamed and Bill -

I don't understand the restrictions about extending Postgres databases:

* XP has a symbolic link function - It's called "junction point"
(http://en.wikipedia.org/wiki/NTFS_junction_point)
* You can extend a Postgres database to another tablespace
* You can create a new tablespace on another drive with the PGAdmin
tool, although you have to make a directory for the tablespace on the
drive first and give the "postgres" user full access-rights to this
directory.
* You can alter a single existing table in a database to be placed on
another (new) tablespace.

I don't know if it's possible to have a table that's placed on two or
more tablespaces.

Regards
Bo Thomsen
CTO
Aestas SMBA
Denmark



Mohamed R. Arafa skrev:

> Hi Bill,
> You've been really supportive, thank you.
>

> The *Tablespace* seems like a great idea. But
> unfortunately, *Tablespaces* work only with operating systems that
> support _'symbolic links'_. Sybmolic links are are not supported on

> WinXP, which is my os.
>
> Anyway, I'm sure I'll find something.
>
> Again thank you.
>
> Best,
> Mohamed Arafa
> Geomarketing Senior Specialist
> Mobinil

> www.mobinil.com <http://www.mobinil.com/>

>
> On Sun, Nov 8, 2009 at 10:23 PM, Bill Thoen <bth...@gisnet.com

> GISnet - www.gisnet.com <http://www.gisnet.com/>

> 303-786-9961
>
>
> >
> > On Sun, Nov 8, 2009 at 3:57 PM, Bill Thoen <bth...@gisnet.com
> <mailto:bth...@gisnet.com>

[Mohamed R. Arafa]

Hello Bo,

Its always nice getting feedback experience from several users.

 

I am no expert in postgres, but I did try creating a Tablespace on a WinXP os. My trial failed and received this message:

 

ERROR: could not set permissions on directory "D:/tablespace": Permission denied

 

Do you think there could be access-rights problems? I'm not sure how to give the "postgres" user full access-rights.

 

The issue about the symbolic link is not really clear to me. The below two links are what drove me to believe that the symbolic link is not available on WinXP. I also understand that the "Junction points" does not allow cross directory access. Am I correct?

 

http://msdn.microsoft.com/en-us/library/aa365680(VS.85).aspx

http://en.wikipedia.org/wiki/NTFS_symbolic_link

 

Any advice is highly appreciated.

 

Regards,

[Bo Victor Thomsen]

Hello Mohamed -

I am pretty sure your problem is the missing permissions on directory "D:\tablespace" for user "postgres".

• Try to right-click on the directory and choose "properties". It will show the properties dialog for the directory.
• This part is tricky - do you have a tab called security? If not, then follow the instruction from this page: "http://www.dougknox.com/xp/tips/xp_security_tab.htm". Remember the warnings given on the web-page :-)
• Click on the security tab - the new dialog will contain two windows. The upper window shows the accounts with access-privileges for the directory. The lower window show the specific access privileges for a single user.
• Push the button "add" and type "postgres" in the new dialog. Check that the username is correct. Then push button "Ok". The user "postgres" is now shown in the upper window.
• Click on user "Postgres" in the upper window
• Put a checkmark in the box at line "Full Access", column "Allow" in the _lower_ window. There should now be checkmarks in all lines, column "Allow" - except for the last line.
• Click on OK

Now you can retry to create a new tablespace for postgres in the directory.

Regarding Symlinks - It's correct, that symlinks in the UNIX sense (allowing link across network shared directories) is only implemented in Vista and later. But junction point works for cross directory access on _local_ hard drives.  

NB ! I'm sitting in front of a _Danish_ version of XP, so it's possible my translation of terms is less than 100 % correct.

Regards

Bo Victor Thomsen
CTO
Aestas SMBA
Denmark

[Mohamed R. Arafa]

Hello Bo,

Yes, you are correct. Everything worked okay. Thanks for all your support.

 

Best,

Mohamed Arafa

Geomarketing Senior Specialist

Mobinil,

www.mobinil.com