Re: adding more space to the existing 9.6 cluster

No doubt it'll take a while...

You said you have 36 databases.  Could you move half of them using
pg_dump/pg_restore over a few outage windows?  (Doing it in bite-sized
pieces reduces risk.)

On 2/21/19 2:27 AM, Julie Nishimura wrote:
> 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(dot)boussekey(at)gmail(dot)com
> <mailto:thomas(dot)boussekey(at)gmail(dot)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(at)gmail(dot)com
>> <mailto: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.
>>

--
Angular momentum makes the world go 'round.