Re: does postgresql backup require additional space on disk

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.

Too bad we don't have dev with the same version. I guess I will create new tablespace using new vol, move the smallest db for which I have backup, create new test table and examine new/existing tables if they have moved by selecting tablespace from pg_tables... right?

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

________________________________
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