Re: does postgresql backup require additional space on disk

er_temp=# select * from pg_tables where tablename = 'test';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
public | test | build | | f | f | f
(1 row)

________________________________
From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Sent: Monday, May 13, 2019 5:52 PM
To: Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)org; pgsql-general
Subject: 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