Re: adding more space to the existing 9.6 cluster

Thank you for the suggestions! We realized we cannot add more space to the existing cluster due to the hardware limitations. So, we decided to go the other route by introducing new standby on a new host with bigger size for data (with pg_basebackup and putting Master into archive mode), then promote it to the master. Do you have any idea how long it might take to run pg_basebackup for 21 tb database? Is there any gotcha we should be aware of? Thank you for your support and help

Sent from my iPhone

On Feb 21, 2019, at 12:18 AM, Thomas Boussekey <thomas.boussekey@gmail.com> wrote:

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