Re: does postgresql backup require additional space on disk

On 5/13/19 5:39 PM, Julie Nishimura wrote:
> LOL. Adrian, I so appreciate your help.
>
> So, since this server is obviously very old, and it seems like 3 other
> tablespaces were added in the past (vol1 - 2.7 tb, vol2 - 2.7 tb, vol3 -
> 16 tb), and I am about to add another vol - vol4 (4 tb), I am trying to
> see where the objects are actually located.
>
> So, for example, working only with one database at a time.
>
> The database has the following:
> ALTER DATABASE er_temp
>   SET default_tablespace = 'vol3';
>
> My postgresql.conf also lists the following:
> default_tablespace = 'vol3'
>
> There is one table "test", and its DDL (according to PGAdmin):
> CREATE TABLE test
> (
>   id integer,
>   key character varying,
>   value character varying
> )
> WITH (
>   OIDS=FALSE
> )
> TABLESPACE vol1;
>
> but if I run the following command, I have empty string as tablespace
>
> er_temp=# SELECT tablespace
> FROM pg_tables
> WHERE tablename = 'test' AND schemaname = 'public';
>  tablespace
> ------------
>
> (1 row)

Is test in the public schema?

>
> However, if I create new table, it will go to vol3:
>
> er_temp=# CREATE TABLE test_j
> er_temp-# (
> er_temp(#   id integer,
> er_temp(#   key character varying,
> er_temp(#   value character varying
> er_temp(# );
> CREATE TABLE
> er_temp=# SELECT tablespace
> FROM pg_tables
> WHERE tablename = 'test_j' AND schemaname = 'public';
>  tablespace
> ------------
>  vol3
> (1 row)
>
> So, why would tablespace for "test" show as empty string if it is not
> default? Where are the files for "test" table?
>
> Thanks,
> Julie

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com