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@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 <http://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
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
