Re: adding more space to the existing 9.6 cluster

Hello all,

If I were in your situation, I would analyze if it could move only a part
of the 36 databases to the new disk.
* Either, I will move some of the databases to the new disk,
* Either, In the largest databases, I will consider to work in multiple
tablespace configuration, using the command ALTER TABLE <<TableName>> SET
TABLESPACE <<TablespaceName>>; Link to the documentation:
https://www.postgresql.org/docs/9.6/sql-altertable.html . to move some
tables to the new disk. You can analyze (depending on your disk and DB
configurations, if it's better to move the very large tables or intensively
used tables.

I hope I'm clear enough!
Feel free to ask for clarification or add new elements to go further on!

Hope this helps,
Have a nice day,
Thomas

Le mer. 20 févr. 2019 à 21:37, Ron <ronljohnsonjr(at)gmail(dot)com> a écrit :

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