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