Re: does postgresql backup require additional space on disk - Mailing list pgsql-general

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@aklaver.com>
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.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.klaver@aklaver.com>
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; pgsql-general@lists.postgresql.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.klaver@aklaver.com
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com