Re: does postgresql backup require additional space on disk

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 <adrian(dot)klaver(at)aklaver(dot)com>
> *Sent:* Monday, May 13, 2019 1:10 PM
> *To:* Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com>
> > *Sent:* Monday, May 13, 2019 11:19 AM
> > *To:* Julie Nishimura; pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com