Re: does postgresql backup require additional space on disk

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...:)

________________________________
From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Sent: Monday, May 13, 2019 4:33 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 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(dot)klaver(at)aklaver(dot)com>
> *Sent:* Monday, May 13, 2019 2:47 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 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(dot)klaver(at)aklaver(dot)com

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