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