Re: adding more space to the existing 9.6 cluster

On 2/19/19 5:02 PM, Julie Nishimura wrote:
> Hello, we are almost out of space on our main data directory, and about to
> introduce new directory to our cluster. We cannot use multiple physical
> disks as a single volume, so we are thinking about creation new tablespace.
> Our current data_directory shows as follows:
> /data/postgresql/9.6/main
> postgres=# SELECT spcname FROM pg_tablespace;
>   spcname
> ------------
>  pg_default
>  pg_global
> (2 rows)
>
> We also have 36 existing databases on this cluster.
> If we add new directory, will it be enough to execute the following
> commands in order to force new data there:
> CREATE TABLESPACE tablespace01 LOCATION '/data/postgresql/9.6/main01';
> ALTER DATABASE db_name SET TABLESPACE
> tablespace01
>
> Do I need to repeat it for all our existing databases?

Since the command is ALTER DATABASE <your_user_db>, it seems that yes you
have to do it for all of them.  A simple bash script should knock that out
quickly.

> Should I change our "template*" dbs as well?

If you want *new* databases to automatically go to tablespace01 then alter
template1.

> Do I need to do something else?

Maybe, depending on the size of your databases, and how much down time you
can afford,

https://www.postgresql.org/docs/9.6/sql-alterdatabase.html "This command
physically moves any tables or indexes in the database's old default
tablespace to the new tablespace."

For example, our multi-TB databases are so big that moving it all at once is
unreasonably slow.  And a failure might leave the db is a bad spot.  Thus,
I'd move one table at a time, a few per outage.

Naturally, YMMV.

> Thank you for your advises.

--
Angular momentum makes the world go 'round.