|
LOL. Adrian, I so appreciate your help.
So, since this server is obviously very old, and it seems like 3 other tablespaces were added in the past (vol1 - 2.7 tb, vol2 - 2.7 tb, vol3 - 16 tb), and I am about to add another vol - vol4 (4 tb), I am trying to see where the objects are actually located.
So, for example, working only with one database at a time.
The database has the following:
ALTER DATABASE er_temp
SET default_tablespace = 'vol3';
My postgresql.conf also lists the following:
default_tablespace = 'vol3'
There is one table "test", and its DDL (according to PGAdmin):
CREATE TABLE test
(
id integer,
key character varying,
value character varying
)
WITH (
OIDS=FALSE
)
TABLESPACE vol1;
but if I run the following command, I have empty string as tablespace
er_temp=# SELECT tablespace
FROM pg_tables
WHERE tablename = 'test' AND schemaname = 'public';
tablespace
------------
However, if I create new table, it will go to vol3:
er_temp=# CREATE TABLE test_j
er_temp-# (
er_temp(# id integer,
er_temp(# key character varying,
er_temp(# value character varying
er_temp(# );
CREATE TABLE
er_temp=# SELECT tablespace
FROM pg_tables
WHERE tablename = 'test_j' AND schemaname = 'public';
tablespace
------------
vol3
(1 row)
So, why would tablespace for "test" show as empty string if it is not default? Where are the files for "test" table?
Thanks,
Julie
From: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Sent: Monday, May 13, 2019 5:09 PM To: Julie Nishimura; pgsql-general@xxxxxxxxxxxxxxxxxxxx; pgsql-general Subject: Re: does postgresql backup require additional space on disk 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 |
