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 | BYAPR08MB5014591FBC5C2CCE085813CAAC0F0@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
Re: does postgresql backup require additional space on disk |
| List | pgsql-general |
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 <adrian.klaver@aklaver.com>
Sent: Monday, May 13, 2019 1:10 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 1:10 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 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 <adrian.klaver@aklaver.com>
> *Sent:* Monday, May 13, 2019 11:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; 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
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
> 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 <adrian.klaver@aklaver.com>
> *Sent:* Monday, May 13, 2019 11:19 AM
> *To:* Julie Nishimura; pgsql-general@lists.postgresql.org; 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
> adrian.klaver@aklaver.com
--
Adrian Klaver
adrian.klaver@aklaver.com
pgsql-general by date: