Search Postgresql Archives

Re: does postgresql backup require additional space on disk

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

 



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



------------------------------------------------------------------------
*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





[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