Query re disk usage
I'm running PG 8.3beta3 on a W2K3 server.
I've set up a tablespace on D drive, with PG itself on C drive and
loaded a bunch of data into a database to test. The directory I've
created the tablespace in on D drive grows to 116Mb - which would be
about right for the amount of data I've plugged in.
(pg_size_pretty(pg_database(size()) certainly corroborates that value
anyway)
I note however, that the pg_database directory on C drive also grows at
the same time to 116MB.
If I truncate the table I've added the data to, both directories shrink
down to around 7 or so Kb.
Why is PG apparently storing my data twice? Is this some sort of
redundancy thing I haven't heard about or have I got something
configured incorrectly? Or am I misinterpreting the way table-spaces are
handled?
The tablespace was set up thusly:
CREATE TABLESPACE "ts_autodrs_main"
OWNER "AutoDRS"
LOCATION 'D:/Database/AutoDRS/Data';
And the table in question defined as:
create table job_classification (
dealer_id text not null ,
date_changed timestamp null ,
time_changed time null ,
job_id text not null ,
des text ,
user_id text null,
access_reports_processed text
) WITHOUT OIDS TABLESPACE ts_autodrs_main
;
Cheers,
P.
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company
Paul Lambert wrote:
I note however, that the pg_database directory on C drive also grows at
the same time to 116MB.
That was meant to say the pg_tblspc directory.
Both directories (my tablespace and pg_tablespace) contain the same set
of files - same names and sizes, eg both contain a file 17177 with a
size of around 58Mb and both contain another file 17179 with a size of
around 53Mb - I'm assuming one being the data, one being the primary key
index.
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company
On Thu, 2007-11-22 at 15:26 +0900, Paul Lambert wrote:
I'm running PG 8.3beta3 on a W2K3 server.
I've set up a tablespace on D drive, with PG itself on C drive and
loaded a bunch of data into a database to test. The directory I've
created the tablespace in on D drive grows to 116Mb - which would be
about right for the amount of data I've plugged in.
(pg_size_pretty(pg_database(size()) certainly corroborates that value
anyway)I note however, that the pg_database directory on C drive also grows at
the same time to 116MB.If I truncate the table I've added the data to, both directories shrink
down to around 7 or so Kb.Why is PG apparently storing my data twice? Is this some sort of
redundancy thing I haven't heard about or have I got something
configured incorrectly? Or am I misinterpreting the way table-spaces are
handled?
Sounds like the WAL log. It's in the pg_xlog directory - verify that
that's where the data is increasing.
The WAL log is global and not per-tablespace, so it doesn't follow your
tablespaces location.
//Magnus
Magnus Hagander wrote:
Sounds like the WAL log. It's in the pg_xlog directory - verify that
that's where the data is increasing.The WAL log is global and not per-tablespace, so it doesn't follow your
tablespaces location.
Nope, it's the files in the pg_tblspc directory on my C drive, they are
named identically to the files in my tablespace directory on D drive and
have identical sizes. When I add data to a table in this tablespace, I
see concurrent increases in file size in both my own tablespace
directory and the pg_tblspc directory tree.
Eg within this directory tree I have a file 17177 which represents my
job_classification table, if I insert a bunch of records, the 17177 file
on my D drive increases in size as does the 17177 file in the pg_tblspc
subdirectory.
I can see the pg_xlog files, which aren't an issue as they are archived
off to E drive.
I've just noticed in the tablespace documentation (Ch 19.6) that PG
makes use of symbolic links that point to any user-defined tablespaces
but AFAIK W2K3 doesn't support symlinks.
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company
Paul Lambert wrote:
I've just noticed in the tablespace documentation (Ch 19.6) that PG
makes use of symbolic links that point to any user-defined tablespaces
but AFAIK W2K3 doesn't support symlinks.
OK, W2K3 supports a thing it calls Junctions, which are similar to
symlinks - and PG appears to be using that in this case.
Crisis averted.
Cheers,
P.
--
Paul Lambert
Database Administrator
AutoLedgers - A Reynolds & Reynolds Company
On Thu, 2007-11-22 at 17:04 +0900, Paul Lambert wrote:
Paul Lambert wrote:
I've just noticed in the tablespace documentation (Ch 19.6) that PG
makes use of symbolic links that point to any user-defined tablespaces
but AFAIK W2K3 doesn't support symlinks.OK, W2K3 supports a thing it calls Junctions, which are similar to
symlinks - and PG appears to be using that in this case.Crisis averted.
I was just going to suggest that. It's a pretty neat feature, but the
support in the GUI for knowing when you're entering one is nonexistant.
If you do "dir" on the commandline it'll tell you the truth.
//Magnus
Magnus Hagander wrote:
On Thu, 2007-11-22 at 17:04 +0900, Paul Lambert wrote:
OK, W2K3 supports a thing it calls Junctions, which are similar to
symlinks - and PG appears to be using that in this case.Crisis averted.
I was just going to suggest that. It's a pretty neat feature, but the
support in the GUI for knowing when you're entering one is nonexistant.
If you do "dir" on the commandline it'll tell you the truth.
Yep, I noticed that... took me a while to find it though. Cheers for the
assistance Magnus.
P.
--
Paul Lambert
Database Administrator
AutoLedgers