Re: does postgresql backup require additional space on disk - Mailing list pgsql-general
| From | Julie Nishimura |
|---|---|
| Subject | Re: does postgresql backup require additional space on disk |
| Date | |
| Msg-id | BYAPR08MB50145897CE48A0C4D55475A6AC080@BYAPR08MB5014.namprd08.prod.outlook.com Whole thread Raw |
| In response to | Re: does postgresql backup require additional space on disk (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: does postgresql backup require additional space on disk
|
| List | pgsql-general |
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';
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
(1 row)
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@aklaver.com>
Sent: Monday, May 13, 2019 5:09 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; pgsql-general
Subject: Re: does postgresql backup require additional space on disk
Sent: Monday, May 13, 2019 5:09 PM
To: Julie Nishimura; pgsql-general@lists.postgresql.org; 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@aklaver.com>
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; 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@aklaver.com>
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; pgsql-general@lists.postgresql.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.klaver@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
> 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@aklaver.com>
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; 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@aklaver.com>
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; pgsql-general@lists.postgresql.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.klaver@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
pgsql-general by date: