does postgresql backup require additional space on disk

classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

does postgresql backup require additional space on disk

Julie Nishimura
Hello,
we are almost out of disk space on one of our servers (99% full). If we run pg_dump to a diff location, does it require any additional disk space on our current server? I am asking, because on some other software, a backup might open transaction which keeps growing and eventually consume all space, keeping tran open for the backup duration. Please clarify? Thanks
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Adrian Klaver-4
On 5/13/19 10:59 AM, Julie Nishimura wrote:
> Hello,
> we are almost out of disk space on one of our servers (99% full). If we
> run pg_dump to a diff location, does it require any additional disk
> space on our current server? I am asking, because on some other
> software, a backup might open transaction which keeps growing and
> eventually consume all space, keeping tran open for the backup duration.
> Please clarify? Thanks

A pg_dump is a point in time snapshot of the database, so if the cluster
is running then it will advance past the dump snapshot. If the cluster
is not active(close off connections to all but pg_dump) then pg_dump
will be the only transaction.

I think the first thing to ask is what you are trying to achieve?

--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Julie Nishimura
We have added new disk volume and about to introduce new tablespace using this additional disk space. After that, I am going to alter all user dbs (and template db as well) by runnig the following command:

CREATE TABLESPACE vol4
OWNER postgres
LOCATION '/data/vol4';

ALTER DATABASE user_db_1
SET default_tablespace = 'vol4';

...for all dbs..

We have more than 70 different databases (the entire server is about 20 tb). However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not have valid backups, unfortunately. So, we were debating if we need to run backups first before introducing all these changes. But we have only 1% left (about 200 gb).

What would you suggest? How would you classify the risk of this operation (creating new tablespace and altering dbs to use it)?

Thanks,
Julie


From: Adrian Klaver <[hidden email]>
Sent: Monday, May 13, 2019 11:19 AM
To: Julie Nishimura; [hidden email]; pgsql-general
Subject: Re: does postgresql backup require additional space on disk
 
On 5/13/19 10:59 AM, Julie Nishimura wrote:
> Hello,
> we are almost out of disk space on one of our servers (99% full). If we
> run pg_dump to a diff location, does it require any additional disk
> space on our current server? I am asking, because on some other
> software, a backup might open transaction which keeps growing and
> eventually consume all space, keeping tran open for the backup duration.
> Please clarify? Thanks

A pg_dump is a point in time snapshot of the database, so if the cluster
is running then it will advance past the dump snapshot. If the cluster
is not active(close off connections to all but pg_dump) then pg_dump
will be the only transaction.

I think the first thing to ask is what you are trying to achieve?

--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Adrian Klaver-4
On 5/13/19 12:33 PM, Julie Nishimura wrote:
> We have added new disk volume and about to introduce new tablespace
> using this additional disk space. After that, I am going to alter all
> user dbs (and template db as well) by runnig the following command:

Not sure why you need to move the template db's they sure not take much
space?

More comments below.

>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server is about 20
> tb). However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not have
> valid backups, unfortunately. So, we were debating if we need to run
> backups first before introducing all these changes. But we have only 1%
> left (about 200 gb).

I have no experience with a setup of this size, someone else will have
to provide real world advice. I would say taking a backup before
preceding is a good idea. Pretty sure time is going to be as much an
issue as space. This would apply to below also. The question is what
options you have to bring cluster or databases down? In particular for
below as:

https://www.postgresql.org/docs/11/sql-alterdatabase.html
"The fourth form changes the default tablespace of the database. Only
the database owner or a superuser can do this; you must also have create
privilege for the new tablespace. This command physically moves any
tables or indexes in the database's old default tablespace to the new
tablespace. The new default tablespace must be empty for this database,
and no one can be connected to the database. Tables and indexes in
non-default tablespaces are unaffected."

>
> What would you suggest? How would you classify the risk of this
> operation (creating new tablespace and altering dbs to use it)?
>
> Thanks,
> Julie
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[hidden email]>
> *Sent:* Monday, May 13, 2019 11:19 AM
> *To:* Julie Nishimura; [hidden email]; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 10:59 AM, Julie Nishimura wrote:
>> Hello,
>> we are almost out of disk space on one of our servers (99% full). If we
>> run pg_dump to a diff location, does it require any additional disk
>> space on our current server? I am asking, because on some other
>> software, a backup might open transaction which keeps growing and
>> eventually consume all space, keeping tran open for the backup duration.
>> Please clarify? Thanks
>
> A pg_dump is a point in time snapshot of the database, so if the cluster
> is running then it will advance past the dump snapshot. If the cluster
> is not active(close off connections to all but pg_dump) then pg_dump
> will be the only transaction.
>
> I think the first thing to ask is what you are trying to achieve?
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Julie Nishimura
Adrian, thanks for your reply. Couple of clarifications/questions:
1) we are on 8.3 for this server
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
ALTER TABLE mytableschema.mytable SET TABLESPACE mynewtablespace

3) I thought if I want to have any newly created dbs go to the new vol, I need to alter template.
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


Am I wrong here?


Thanks,

Julie




From: Adrian Klaver <[hidden email]>
Sent: Monday, May 13, 2019 1:10 PM
To: Julie Nishimura; [hidden email]; pgsql-general
Subject: Re: does postgresql backup require additional space on disk
 
On 5/13/19 12:33 PM, Julie Nishimura wrote:
> We have added new disk volume and about to introduce new tablespace
> using this additional disk space. After that, I am going to alter all
> user dbs (and template db as well) by runnig the following command:

Not sure why you need to move the template db's they sure not take much
space?

More comments below.

>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server is about 20
> tb). However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not have
> valid backups, unfortunately. So, we were debating if we need to run
> backups first before introducing all these changes. But we have only 1%
> left (about 200 gb).

I have no experience with a setup of this size, someone else will have
to provide real world advice. I would say taking a backup before
preceding is a good idea. Pretty sure time is going to be as much an
issue as space. This would apply to below also. The question is what
options you have to bring cluster or databases down? In particular for
below as:

https://www.postgresql.org/docs/11/sql-alterdatabase.html
"The fourth form changes the default tablespace of the database. Only
the database owner or a superuser can do this; you must also have create
privilege for the new tablespace. This command physically moves any
tables or indexes in the database's old default tablespace to the new
tablespace. The new default tablespace must be empty for this database,
and no one can be connected to the database. Tables and indexes in
non-default tablespaces are unaffected."

>
> What would you suggest? How would you classify the risk of this
> operation (creating new tablespace and altering dbs to use it)?
>
> Thanks,
> Julie
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver <[hidden email]>
> *Sent:* Monday, May 13, 2019 11:19 AM
> *To:* Julie Nishimura; [hidden email]; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 10:59 AM, Julie Nishimura wrote:
>> Hello,
>> we are almost out of disk space on one of our servers (99% full). If we
>> run pg_dump to a diff location, does it require any additional disk
>> space on our current server? I am asking, because on some other
>> software, a backup might open transaction which keeps growing and
>> eventually consume all space, keeping tran open for the backup duration.
>> Please clarify? Thanks
>
> A pg_dump is a point in time snapshot of the database, so if the cluster
> is running then it will advance past the dump snapshot. If the cluster
> is not active(close off connections to all but pg_dump) then pg_dump
> will be the only transaction.
>
> I think the first thing to ask is what you are trying to achieve?
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]
lup
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

lup

8 is very old but in 9.4 the doc says


The fourth form (your example) changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. Note that tables and indexes in non-default tablespaces are not affected



On 5/13/19 2:26 PM, Julie Nishimura wrote:
Adrian, thanks for your reply. Couple of clarifications/questions:
1) we are on 8.3 for this server
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
ALTER TABLE mytableschema.mytable SET TABLESPACE mynewtablespace

3) I thought if I want to have any newly created dbs go to the new vol, I need to alter template.
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


Am I wrong here?


Thanks,

Julie




From: Adrian Klaver [hidden email]
Sent: Monday, May 13, 2019 1:10 PM
To: Julie Nishimura; [hidden email]; pgsql-general
Subject: Re: does postgresql backup require additional space on disk
 
On 5/13/19 12:33 PM, Julie Nishimura wrote:
> We have added new disk volume and about to introduce new tablespace
> using this additional disk space. After that, I am going to alter all
> user dbs (and template db as well) by runnig the following command:

Not sure why you need to move the template db's they sure not take much
space?

More comments below.

>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server is about 20
> tb). However, for the largest dbs (16tb, 4 tb, and 3 tb), we do not have
> valid backups, unfortunately. So, we were debating if we need to run
> backups first before introducing all these changes. But we have only 1%
> left (about 200 gb).

I have no experience with a setup of this size, someone else will have
to provide real world advice. I would say taking a backup before
preceding is a good idea. Pretty sure time is going to be as much an
issue as space. This would apply to below also. The question is what
options you have to bring cluster or databases down? In particular for
below as:

https://www.postgresql.org/docs/11/sql-alterdatabase.html
"The fourth form changes the default tablespace of the database. Only
the database owner or a superuser can do this; you must also have create
privilege for the new tablespace. This command physically moves any
tables or indexes in the database's old default tablespace to the new
tablespace. The new default tablespace must be empty for this database,
and no one can be connected to the database. Tables and indexes in
non-default tablespaces are unaffected."

>
> What would you suggest? How would you classify the risk of this
> operation (creating new tablespace and altering dbs to use it)?
>
> Thanks,
> Julie
>
> ------------------------------------------------------------------------
> *From:* Adrian Klaver [hidden email]
> *Sent:* Monday, May 13, 2019 11:19 AM
> *To:* Julie Nishimura; [hidden email]; pgsql-general
> *Subject:* Re: does postgresql backup require additional space on disk
> On 5/13/19 10:59 AM, Julie Nishimura wrote:
>> Hello,
>> we are almost out of disk space on one of our servers (99% full). If we
>> run pg_dump to a diff location, does it require any additional disk
>> space on our current server? I am asking, because on some other
>> software, a backup might open transaction which keeps growing and
>> eventually consume all space, keeping tran open for the backup duration.
>> Please clarify? Thanks
>
> A pg_dump is a point in time snapshot of the database, so if the cluster
> is running then it will advance past the dump snapshot. If the cluster
> is not active(close off connections to all but pg_dump) then pg_dump
> will be the only transaction.
>
> I think the first thing to ask is what you are trying to achieve?
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Karsten Hilbert
In reply to this post by Julie Nishimura
On Mon, May 13, 2019 at 07:33:58PM +0000, Julie Nishimura wrote:

> We have added new disk volume and about to introduce new
> tablespace using this additional disk space. After that, I am
> going to alter all user dbs (and template db as well) by
> runnig the following command:
>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server
> is about 20 tb). However, for the largest dbs (16tb, 4 tb,
> and 3 tb), we do not have valid backups, unfortunately. So,
> we were debating if we need to run backups first before
> introducing all these changes. But we have only 1% left
> (about 200 gb).

You could move those databases first for which you do have
backups which should free up space in the old tablespace.
Then take backups of and move the remaining databases,
perhaps one by one.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Julie Nishimura
yeah, good idea. Thanks everybody


From: [hidden email] <[hidden email]> on behalf of Karsten Hilbert <[hidden email]>
Sent: Monday, May 13, 2019 2:12 PM
To: [hidden email]
Subject: Re: does postgresql backup require additional space on disk
 
On Mon, May 13, 2019 at 07:33:58PM +0000, Julie Nishimura wrote:

> We have added new disk volume and about to introduce new
> tablespace using this additional disk space. After that, I am
> going to alter all user dbs (and template db as well) by
> runnig the following command:
>
> CREATE TABLESPACE vol4
> OWNER postgres
> LOCATION '/data/vol4';
>
> ALTER DATABASE user_db_1
> SET default_tablespace = 'vol4';
>
> ...for all dbs..
>
> We have more than 70 different databases (the entire server
> is about 20 tb). However, for the largest dbs (16tb, 4 tb,
> and 3 tb), we do not have valid backups, unfortunately. So,
> we were debating if we need to run backups first before
> introducing all these changes. But we have only 1% left
> (about 200 gb).

You could move those databases first for which you do have
backups which should free up space in the old tablespace.
Then take backups of and move the remaining databases,
perhaps one by one.

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Adrian Klaver-4
In reply to this post by Julie Nishimura
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Julie Nishimura
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.

Too bad we don't have dev with the same version. I guess I will create new tablespace using new vol, move the smallest db for which I have backup, create new test table and examine new/existing tables if they have moved by selecting tablespace from pg_tables... right?

 





From: Adrian Klaver <[hidden email]>
Sent: Monday, May 13, 2019 2:47 PM
To: Julie Nishimura; [hidden email]; 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Adrian Klaver-4
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 <[hidden email]>
> *Sent:* Monday, May 13, 2019 2:47 PM
> *To:* Julie Nishimura; [hidden email]; 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
> [hidden email]


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Julie Nishimura
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 <[hidden email]>
Sent: Monday, May 13, 2019 4:33 PM
To: Julie Nishimura; [hidden email]; 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 <[hidden email]>
> *Sent:* Monday, May 13, 2019 2:47 PM
> *To:* Julie Nishimura; [hidden email]; 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
> [hidden email]


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Adrian Klaver-4
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 <[hidden email]>
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; [hidden email]; 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 <[hidden email]>
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; [hidden email]; 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
>> [hidden email]
>
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Julie Nishimura
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
------------
 
(1 row)


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 <[hidden email]>
Sent: Monday, May 13, 2019 5:09 PM
To: Julie Nishimura; [hidden email]; 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 <[hidden email]>
> *Sent:* Monday, May 13, 2019 4:33 PM
> *To:* Julie Nishimura; [hidden email]; 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 <[hidden email]>
>> *Sent:* Monday, May 13, 2019 2:47 PM
>> *To:* Julie Nishimura; [hidden email]; 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
>> [hidden email]
>
>
> --
> Adrian Klaver
> [hidden email]


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Adrian Klaver-4
On 5/13/19 5:39 PM, Julie Nishimura wrote:

> 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
> ------------
>
> (1 row)

Is test in the public schema?

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



--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Julie Nishimura
er_temp=# select * from pg_tables where tablename = 'test';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
 public     | test      | build      |            | f          | f        | f
(1 row)


From: Adrian Klaver <[hidden email]>
Sent: Monday, May 13, 2019 5:52 PM
To: Julie Nishimura; [hidden email]; pgsql-general
Subject: Re: does postgresql backup require additional space on disk
 
On 5/13/19 5:39 PM, Julie Nishimura wrote:
> 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
> ------------
>
> (1 row)

Is test in the public schema?

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



--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: does postgresql backup require additional space on disk

Adrian Klaver-4
On 5/13/19 5:56 PM, Julie Nishimura wrote:
> er_temp=# select * from pg_tables where tablename = 'test';
>   schemaname | tablename | tableowner | tablespace | hasindexes |
> hasrules | hastriggers
> ------------+-----------+------------+------------+------------+----------+-------------
>   public     | test      | build      |            | f          | f    
>     | f
> (1 row)
>

Alright.

I don't have time at the moment to create a query, but I would look at
using pg_class(relkind='r' for tables):

https://www.postgresql.org/docs/8.3/catalog-pg-class.html

and joining to pg_tablespace:

https://www.postgresql.org/docs/8.3/catalog-pg-tablespace.html

Order by the tablespace name to see where the tables are located.

--
Adrian Klaver
[hidden email]