Search Postgresql Archives

Re: does postgresql backup require additional space on disk

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


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

From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Monday, May 13, 2019 5:09 PM
To: Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; pgsql-general
Subject: Re: does postgresql backup require additional space on disk
 
On 5/13/19 4:53 PM, Julie Nishimura wrote:
> Adrian,
> I think in my first emails in this chain about the effort i did
> specified it properly:
>
> CREATE TABLESPACE vol4
>> OWNER postgres
>> LOCATION '/data/vol4';
>>
>> ALTER DATABASE user_db_1
>> SET default_tablespace = 'vol4';
>>
>> ...for all dbs..
>
> Then "default" part got lost in emails...:)

Well that was a stupid on my part:(

To add insult to injury:

"No, what the link showed is:

ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace

That is is the same as changing default_tablespace in postgresql.conf.
"

is wrong also. What the above does is change the behavior for that
database only.

>
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 3:27 PM, Julie Nishimura wrote:
>> Adrian, thanks for your reply. I previously found this link, which
>> mentioned 8.2, that is why I assumed it will work with 8.3.
>
> No, what the link showed is:
>
> ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace
>
> That is is the same as changing default_tablespace in postgresql.conf.
>
> It is not the same as:
>
> ALTER DATABASE name SET TABLESPACE
>
>>
>> Too bad we don't have dev with the same version. I guess I will create
>
> You can use the same commands on dev to test, just not ALTER DATABASE
> name SET TABLESPACE as that is not in 8.3.
>
>> new tablespace using new vol, move the smallest db for which I have
>
> You cannot move a db as a unit, you will need to move the individual
> tables/indexes in the db.
>
>> backup, create new test table and examine new/existing tables if they
>> have moved by selecting tablespace from pg_tables... right?
>
> Assuming you either specify the new tablespace on table CREATE or have
> set default_tablespace to the new tablespace.
>
>>
>>
>> http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html
>> Managing disk space using table spaces - Postgres OnLine Journal -
>> Postgres OnLine Journal Magazine Jul 2017 - Dec 2017
>> <http://www.postgresonline.com/journal/archives/123-Managing-disk-space-using-table-spaces.html>
>> Below are steps to creating one. First create a folder on an available
>> disk in your filesystem using an filesystem server administrative login;
>> Next give full rights to the postgres server account (the one the daemon
>> process runs under) or you can change the owner of the folder to the
>> postgres account (in linux you can use chown postgres and on windows
>> just use the administrative properties ...
>> www.postgresonline.com <http://www.postgresonline.com>
>>
>>
>>
>> ------------------------------------------------------------------------
>> *From:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; pgsql-general
>> *Subject:* Re: does postgresql backup require additional space on disk
>> On 5/13/19 1:26 PM, Julie Nishimura wrote:
>>> Adrian, thanks for your reply. Couple of clarifications/questions:
>>> 1) we are on 8.3 for this server
>>
>> 8.3 does not have ALTER DATABASE name SET TABLESPACE :
>>
>> https://www.postgresql.org/docs/8.3/sql-alterdatabase.html
>>
>> It does appear until 8.4.
>>
>>
>>> 2) I was under impression, that "ALTER DATABASE name SET TABLESPACE
>>> new_tablespace" won't move anything, and just utilize the new_tablespace
>>> for new tables/indexes. If we would want to move existing tables, we
>>> would need to move them one by one by
>>
>> No think of ALTER DATABASE name SET TABLESPACE  as a bulk operation of
>> ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
>> across all existing tables.  This assumes the all existing tables live
>> in the  current default tablespace. It is moot in your case as 8.3 is
>> not capable of doing this.
>>
>>> ALTER TABLE /mytableschema.mytable/ SET TABLESPACE /mynewtablespace/
>>> /
>>> /
>>
>> The above is your option in 8.3.
>>
>>> 3) I thought if I want to have any newly created dbs go to the new vol,
>>> I need to alter template.
>>
>> It is not required you can spec the tablespace in the CREATE DATABASE
>> command:
>>
>> https://www.postgresql.org/docs/8.3/sql-createdatabase.html
>>
>> Though you can move them to make the tablespace the default. See above link.
>>
>>
>>> 4) I was also thinking about changing parameters in config file to point
>>> to the newly volume and reload postgresql.conf
>>>
>>> default_tablespace
>>>
>>> temp_tablespaces
>>
>> You could that. If you do it, moving the template tables would be redundant.
>>
>>>
>>>
>>> Am I wrong here?
>>>
>>>
>>> Thanks,
>>>
>>> Julie
>>>
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@xxxxxxxxxxx
>
>
> --
> Adrian Klaver
> adrian.klaver@xxxxxxxxxxx


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux